ID: Q90989
The information in this article applies to:
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
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
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 = "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
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).
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 : 1.0 1.10 2.0 7.0 97
Platform : WINDOWS
Hardware : x86
Issue type : kbhowtoLast Reviewed: November 21, 1998