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


---------------------------------------------------------------------
The information in this article applies to:

 - Microsoft Access versions 1.0, 1.1, and 2.0
---------------------------------------------------------------------

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;"
      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

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 """ & 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

How to Use the Function
-----------------------

1. With the module containing the function open in Design view, choose
   Immediate Window from the View menu.

2. 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.
