Microsoft KB Archive/90989

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

Article ID: 90989

Article Last Modified on 1/18/2007

-

APPLIES TO


 * 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

-



This article was previously published under Q90989



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



SUMMARY
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 &quot;Building Applications with Microsoft Access&quot; 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 &quot;Introduction to Programming&quot; manual in Microsoft Access version 1.x or the &quot;Building Applications&quot; 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 &quot;Tables&quot; Find_Object = DB.TableDefs(ObjectName$).Name

Case &quot;Queries&quot; Find_Object = DB.QueryDefs(ObjectName$).Name

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

End If

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

End Select

If Err = 3265 Or Find_Object = &quot;&quot; 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 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. '********************************************************************  '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 = &quot;Select Name, Type from MSysObjects Where Type=&quot;

ObjectExists_1x% = 0

Select Case ObjectType Case &quot;Tables&quot; SQL = SQL & &quot;1 And [Name] = '&quot; & ObjectName & &quot;'Order By _ Name;&quot; Case &quot;Queries&quot; SQL = SQL & &quot;5 And [Name] = '&quot; & ObjectName & &quot;'Order By Name;&quot; Case &quot;Forms&quot; SQL = SQL & &quot;-32768 And [Name] = '&quot; & ObjectName & &quot;' Order By _ Name;&quot; Case &quot;Reports&quot; SQL = SQL & &quot;-32764 And [Name] = '&quot; & ObjectName & &quot;' Order By _ Name;&quot; Case &quot;Macros&quot; SQL = SQL & &quot;-32766 And [Name] = '&quot; & ObjectName & &quot;' Order By _ Name;&quot; Case &quot;Modules&quot; SQL = SQL & &quot;-32761 And [Name] = '&quot; & ObjectName & &quot;' Order By _ Name;&quot; Case Else Msg = &quot;Object Name &quot;&quot;&quot; & ObjectType & &quot;&quot;&quot; is an invalid&quot; Msg = Msg & &quot; argument to function ObjectExists_1x!&quot; MsgBox Msg, 16, &quot;ObjectExists_1x&quot; 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
 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). If you are using Microsoft Access version 1.x, type the following line in the Immediate window, and then press ENTER:

?ObjectExists_1x(&quot;Tables&quot;,&quot;Employees&quot;)

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(&quot;Tables&quot;,&quot;Employees&quot;)



If a table named Employees exists in the current database, - 1 will be returned. If no table named Employees exists, 0 will be returned.

Keywords: kbhowto kbprogramming KB90989

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.