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

• 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")
   

REFERENCES