Microsoft KB Archive/119478

INF: Sample Function to Determine If Object Exists in Database PSS ID Number: Q119478 Article last modified on 08-22-1994

1.00 1.10 2.00

WINDOWS

= SUMMARY =

This article describes a sample user-defined function that you can use to check for the existence of an object 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 Access Basic and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information on Access Basic, please refer to the “Introduction to Programming” manual in Microsoft Access version 1.x, or the “Building Applications” manual in version 2.0.

= MORE INFORMATION =

To create the function, open a new module and enter the appropriate code 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 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;&quot; Case &quot;Reports&quot; SQL = SQL & &quot;-32764 And [Name] = ‘&quot; & ObjectName & “’ Order By  Name;&quot; Case &quot;Macros&quot; SQL = SQL & &quot;-32766 And [Name] = ’&quot; & ObjectName & &quot;’ Order By  Name;” Case “Modules” SQL = SQL & &quot;-32761 And [Name] =’&quot; & ObjectName & &quot;’ Order By  Name;” Case Else Msg = “Object Name”&quot;&quot; & ObjectType & &quot;&quot;&quot; is an invalid&quot; Msg = Msg & &quot; argument to function ObjectExists_1x!&quot; 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

Microsoft Access Version 2.0
‘******************************************************************** ’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”&quot;&quot; & ObjectType & &quot;&quot;&quot; is an invalid&quot; Msg = Msg & &quot; argument to function ObjectExists_20!&quot; 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 = &quot;&quot; Then Found_Object% = 0 End If

ObjectExists_20% = Found_Object%

End Function

How to Use the Function
  With the module containing the function open in Design view, choose Immediate Window from the View menu.   If you are using Microsoft Access version 1.x, type the following line in the Immediate window, then press ENTER: ?ObjectExists_1x(“Tables”,“Employees”) If you are using Microsoft Access version 2.0, type the following line in the Immediate window, then press ENTER: ?ObjectExists_20(“Tables”,“Employees”) 

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

= REFERENCES =

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

Microsoft Access “Building Applications,” version 2.0, Chapter 7, “Objects and Collections,” pages 170-181

Additional reference words: 1.00 1.10 2.00 KBCategory: KBSubcategory: MdlDao

=
================================================================

Copyright Microsoft Corporation 1994.