Registrations are now open. Join us today!
There is still a lot of work to do on the wiki yet! More information about editing can be found here.
Already have an account?

Microsoft KB Archive/101674

From BetaArchive Wiki

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.



REFERENCES

Microsoft Access "Introduction to Programming," version 1.1, Chapter 8, "Manipulating Data," pages 124-127

Keywords : kbprg
Issue type : kbhowto
Technology :


Last Reviewed: November 4, 2000
© 2001 Microsoft Corporation. All rights reserved. Terms of Use.