Detecting existing objects in Access 97

Question

What is the shortest and fastest code to detect MS Access objects existence ?

Answer

From: Shamil Salakhetdinov <shamil@marta.darts.spb.ru>
To: Microsoft Access Database Discussion List <ACCESS-L@PEACH.EASE.LSOFT.COM>
Subject: Re: Question: Detecting existing objects in Access 97
Date: 19 May 1998 0:07

Here is the shortest (?) code:

Function ObjExists(ByRef rdbs As Database, _
                   ByVal vstrObjType As String, _
                   ByVal vstrObjName As String) As Boolean
    On Error Resume Next
    Select Case vstrObjType
    Case "Table":
        ObjExists = (rdbs.TableDefs(vstrObjName).Name = vstrObjName)
    Case "Query":
        ObjExists = (rdbs.QueryDefs(vstrObjName).Name = vstrObjName)
    Case Else
       ' vstrObjName should be equal to "Script" for macro 
       ObjExists = (rdbs.Containers(vstrObjType & "s").Documents(vstrObjName).Name _
                   = vstrObjName)
    End Select
    Err.Clear
End Function

As for your timing - try this:

Sub Test()
    Dim dbs As Database
    
    Set dbs = DBEngine(0).OpenDatabase("")
    
    Dim i As Integer
    Dim strTblName As String
    Dim intCnt As Long
    Dim intIdx As Integer
    
    intCnt = dbs.TableDefs.Count - 1
    Randomize
    
    Debug.Print Now
    For i = 1 To 10000
        intIdx = Int((intCnt + 1) * Rnd)
        strTblName = dbs.TableDefs(intIdx).Name
        'strTblName = dbs.TableDefs(intIdx).Name & "*"
        TableExists2 dbs, strTblName
    Next i
    
    Debug.Print Now
    
    For i = 1 To 10000
        intIdx = Int((intCnt + 1) * Rnd)
        strTblName = dbs.TableDefs(intIdx).Name
        'strTblName = dbs.TableDefs(intIdx).Name & "*"
        TableExists dbs, strTblName
    Next i
    
    Debug.Print Now
    
End Sub

Function TableExists2(ByRef rdbs As Database, ByVal vstrName As String) As Boolean
    On Error Resume Next
    TableExists2 = (rdbs.TableDefs(vstrName).Name = vstrName)
    Err.Clear
End Function

Function TableExists(ByRef rdbs As Database, sName As String) As Boolean
     Dim db As Database
     Dim tbloop As TableDef
     Dim tbls As TableDefs
     
     Set db = rdbs
     Set tbls = db.TableDefs
     For Each tbloop In tbls
         If tbloop.Name = sName Then
             TableExists = True
             Exit Function
         End If
     Next tbloop
     TableExists = False
End Function

It showed me that "cycless" TableExists function runs ~10 times faster. (Pentium 166/64MB/WinNT 4.0/ Access 97, mdb with 110 tables)

HTH,
Shamil