ACC: How to Find If an Object Exists in a Database

• Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

This article shows you how to create a sample user-defined function to check if an object exists in the current database before you create a new object (such as a table, form, or report) in the database.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access versions 1.x and 2.0. For more information about Access Basic, please refer to the "Introduction to Programming" manual in Microsoft Access version 1.x or the "Building Applications" manual in Microsoft Access version 2.0

MORE INFORMATION

To create the function, open a new module and enter the code appropriate for your version of Microsoft Access.

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

Microsoft Access Versions 2.0, 7.0, and 97

 '********************************************************************
   'Declarations section of the module

  '********************************************************************

   Option Explicit

  '********************************************************************
   ' FUNCTION: ObjectExists_20()
   '
   ' PURPOSE:
   '    Determines whether the specified object exists in the
   '    current 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
   '
   '    ObjectName - A string representing the name of the specified
   '                 ObjectType.
   '
   ' RETURNS:
   '    True (-1), if the object exists.
   '    False (0), if the object does not exist.
   '
   ' NOTES:
   '    This function uses data access objects (DAO) to determine if
   '    the specified object exists in the current database.
   '
  '********************************************************************
   Function ObjectExists_20% (ObjectType$, ObjectName$)
   On Error Resume Next

   Dim Found_Object%, Find_Object As String, ObjectNum As Integer
   Dim DB As Database, T As TableDef
   Dim Q As QueryDef, C  As Container
   Dim Msg As String
   Found_Object% = -1
   Set DB = dbengine(0)(0)

   Select Case ObjectType$
   Case "Tables"
       Find_Object = DB.TableDefs(ObjectName$).Name

   Case "Queries"
       Find_Object = DB.QueryDefs(ObjectName$).Name

   Case Else
       If ObjectType$ = "Forms" Then
           ObjectNum = 1
       ElseIf ObjectType$ = "Modules" Then
           ObjectNum = 2
       ElseIf ObjectType$ = "Reports" Then
           ObjectNum = 4
       ElseIf ObjectType$ = "Macros" Then
           ObjectNum = 5
       Else
            Msg = "Object Name """ & ObjectType & """ is an invalid"
            Msg = Msg & " argument to function ObjectExists_20!"
            MsgBox Msg, 16, "ObjectExists_20"
            Exit Function

       End If

       Set C = DB.Containers(ObjectNum)
       Find_Object = C.Documents(ObjectName$).Name

   End Select

   If Err = 3265 Or Find_Object = "" Then
       Found_Object% = 0
   End If

   ObjectExists_20% = Found_Object%

   End Function 

Microsoft Access Version 1.x

  '********************************************************************
   'Declarations section of the module
 '********************************************************************

   Option Explicit

 '********************************************************************
   ' FUNCTION: ObjectExists_1x()
   '
   ' PURPOSE:
   '    Determines whether the specified object exists in the
   '    current 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
   '
   '    ObjectName - A string representing the name of the specified
   '                 ObjectType.
   '
   ' RETURNS:
   '    True (-1), if the object exists.
   '    False (0), if the object does not exist.
   '
   ' NOTES:
   '    This function uses information stored in the MSysObjects
   '    system table of the current database. The system tables
   '    are undocumented and are subject to change in future versions of
   '    Microsoft Access.
   '
 '********************************************************************
   Function ObjectExists_1x% (ObjectType As String, ObjectName As String)
      On Error Resume Next

      Dim db As Database
      Dim ss As Snapshot
      Dim SQL
      Dim Msg As String

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

      ObjectExists_1x% = 0

      Select Case ObjectType
         Case "Tables"
            SQL = SQL & "1 And [Name] = '" & ObjectName & "'Order By _
                Name;"
      Case "Queries"
         SQL = SQL & "5 And [Name] = '" & ObjectName & "'Order By Name;"
      Case "Forms"
         SQL = SQL & "-32768 And [Name] = '" & ObjectName & "' Order By _
         Name;"
      Case "Reports"
         SQL = SQL & "-32764 And [Name] = '" & ObjectName & "' Order By _
         Name;"
      Case "Macros"
         SQL = SQL & "-32766 And [Name] = '" & ObjectName & "' Order By _
         Name;"
      Case "Modules"
         SQL = SQL & "-32761 And [Name] = '" & ObjectName & "' Order By _
         Name;"
      Case Else
         Msg = "Object Name """ & ObjectType & """ is an invalid"
         Msg = Msg & " argument to function ObjectExists_1x!"
         MsgBox Msg, 16, "ObjectExists_1x"
         Exit Function
      End Select

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

      ss.MoveLast
      If ss.RecordCount > 0 Then
         ObjectExists_1x% = -1
      End If

   End Function 

How to Use the Function

1. Open the module containing the function in Design view, and on the View menu, click Debug Window (or Immediate Window in version 2.0 or earlier).



2. If you are using Microsoft Access version 1.x, type the following line in the Immediate window, and then press ENTER:

   ?ObjectExists_1x("Tables","Employees")

   If you are using Microsoft Access versions 2.0, 7.0, or 97, type the following line in the Debug window, (or Immediate window) and then press ENTER:

   ?ObjectExists_20("Tables","Employees")

Additional query words:

Keywords : kbprg MdlDao PgmObj
Version : WINDOWS:1.0,1.1,2.0,7.0,97
Platform : WINDOWS
Issue type : kbhowto