Microsoft KB Archive/101674

{|
 * width="100%"|

ACC1x: How to Get a List of Object Names in Microsoft Access

 * }

Q101674

-

The information in this article applies to:


 * Microsoft Access versions 1.0, 1.1

-

SUMMARY
There is no built-in mechanism in Microsoft Access version 1.x for enumerating a list of form, report, macro, or module names. This article demonstrates an Access Basic function, GetObjectNames, that you can use to list the names of these objects as well as table and query names.

NOTE: The technique described below relies on the use of system tables stored with your database. These tables are undocumented and are subject to change in future versions of Microsoft Access.

MORE INFORMATION
This article assumes that you are familiar with Access Basic and with creating Microsoft Access applications with the programming tools provided with Microsoft Access.

First, create a new module with the code listed below.

NOTE: In the following sample code, an underscore (_) is used as a line continuation character. Remove the underscore from the end of the line when re-creating this code in Access Basic.

 '******************************************************************** ' MODULE DECLARATIONS '********************************************************************

Option Explicit

'******************************************************************** ' FUNCTION: GetObjectNames ' ' PURPOSE: '   Fills the string array passed as an argument with a list of names '   of objects of type ObjectType in the currently opened user '   database. ' ' ARGUMENTS: '   ObjectType - A string representing the object whose names are to '                 be retrieved. ObjectType can be one of the '                following: ' '                Tables, Queries, Forms, Reports, Macros, Modules ' '   Names      - A single dimensional array of type string ' ' RETURN: '   The number of names stored in the Names array. The names '   are stored beginning from 0 to GetObjectNames - 1. ' ' NOTES: '   This function utilizes information stored in the MySysObject table '   of the currently opened user database. The system tables are '   undocumented and are subject to change in future versions of '    Access. ' '******************************************************************** Function GetObjectNames (ByVal ObjectType, Names As String) Dim db As Database Dim ss As Snapshot Dim Count Dim SQL Dim Msg As String

SQL = "Select Name,Type from MSysObjects Where Type="

Select Case ObjectType Case "Tables" SQL = SQL & "1 And Left(Name,1)<>'~' And Left(Name,4) <> _                 ""MSys"" Order By Name;" Case "Queries" SQL = SQL & "5 And Left(Name,1)<>'~' And Mid(Name, _                 Len(Name) - 3) <> ""0000"" Order By Name;" Case "Forms" SQL = SQL & "-32768 And Left(Name,1)<>'~' Order By Name;" Case "Reports" SQL = SQL & "-32764 And Left(Name,1)<>'~' Order By Name;" Case "Macros" SQL = SQL & "-32766 And Left(Name,1)<>'~' Order By Name;" Case "Modules" SQL = SQL & "-32761 And Left(Name,1)<>'~' Order By Name;" Case Else Msg = "Object Name """ & ObjectType & """ is an invalid" Msg = Msg & " argument to Function GetObjectNames!" MsgBox Msg, 16, "GetObjectNames" Exit Function End Select

Set db = CurrentDB Set ss = db.CreateSnapshot(SQL)

ss.MoveLast If ss.RecordCount > 0 Then ReDim Names(0 To ss.RecordCount - 1) Else GetObjectNames = 0 Exit Function End If

ss.MoveFirst Count = 0 Do While Not ss.EOF Names(Count) = ss![name] Count = Count + 1 ss.MoveNext Loop

GetObjectNames = ss.RecordCount End Function

'******************************************************************** ' FUNCTION: TestGetObjectNames ' ' PURPOSE: Used to demonstrate and test the GetObjectNames function ' ' ARGUMENTS: '   ObjectType - A string representing the object whose names are to '                 be retrieved. ObjectType can be one of the following: ' '                Tables, Queries, Forms, Reports, Macros, Modules ' '******************************************************************** Function TestGetObjectNames (ObjectType) Dim Count, i  ReDim Names(0) As String

Count = GetObjectNames(ObjectType, Names)

Debug.Print "Count: " & Count

For i = 0 To Count - 1 Debug.Print Names(i) Next i

End Function

How to Use the GetObjectNames Function
The function TestGetObjectNames, above, demonstrates how to use the GetObjectNames function.


 * 1) With the module open in Design view, choose Immediate Window from the View menu.
 * 2) Type the following and press ENTER:

? TestGetObjectNames("Forms")

Result: A list of forms in the currently-open database will print in the Immediate window.