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

This article was previously published under Q90989
This article has been archived. It is offered "as is" and will no longer be updated.
Moderate: Requires basic macro, coding, and interoperability skills.
SUMMARY
This article shows you how to create a sample user-defined function tocheck if an object exists in the current database before you create a newobject (such as a table, form, or report) in the database.

This article assumes that you are familiar with Visual Basic forApplications and with creating Microsoft Access applications using theprogramming tools provided with Microsoft Access. For more informationabout 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 MicrosoftAccess versions 1.x and 2.0. For more information about Access Basic,please refer to the "Introduction to Programming" manual in MicrosoftAccess version 1.x or the "Building Applications" manual in MicrosoftAccess version 2.0
MORE INFORMATION
To create the function, open a new module and enter the code appropriatefor your version of Microsoft Access.

NOTE: In the following sample code, an underscore (_) at the end of a lineis used as a line-continuation character. Remove the underscore from theend 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

NOTE: In the following sample code, an underscore (_) at the end of a lineis used as a line-continuation character. Remove the underscore from theend of the line when re-creating this code in Access Basic.
  '********************************************************************   '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")
If a table named Employees exists in the current database, - 1 will bereturned. If no table named Employees exists, 0 will be returned.
Properties

Article ID: 90989 - Last Review: 12/04/2015 09:19:36 - Revision: 2.3

Microsoft Access 1.0 Standard Edition, Microsoft Access 1.1 Standard Edition, Microsoft Access 2.0 Standard Edition, Microsoft Access 95 Standard Edition, Microsoft Access 97 Standard Edition

  • kbnosurvey kbarchive kbhowto kbprogramming KB90989
Feedback