Access Cookbook - Ken Getz [163]
If acbDoesObjExist("Customers", acTable) Then
' You know the table exists.
Else
MsgBox "The table 'Customers' doesn't exist!"
End If
Discussion
The acbDoesObjExist function, shown in full here, checks for the existence of an object by attempting to retrieve that object's Name property. Because every object that exists exposes a Name property, this action can't fail unless the object doesn't exist. In skeleton format, the code works like this:
Dim strName As String
On Error Goto acbDoesObjExist_Err
strName = obj.Name
acbDoesObjExist = True
acbDoesObjectExist_Exit:
Exit Function
acbDoesObjectExist_Err:
acbDoesObjExist = False
Resume acbDoesObjectExist_Exit
That is, the code sets up an error handler and then attempts to retrieve the Name property of the requested object. If it succeeds, the code falls through, sets the return value to True, and returns. If it triggers an error, the procedure can be assured that the object doesn't exist, and it will return False.
The only other issue is how to convert a string containing the name of the object and an integer containing its type to a real object reference. This is where the Jet engine's Container objects come in handy. The Container collections, supplied by Access so the Jet engine can support security for all the Access objects, contain collections of Document objects (one for each saved object in your database). The Containers collection contains collections named Tables, Forms, Reports, Scripts (that's macros for us users!), and Modules. Except for tables and queries, the code checks in those collections of documents, looking for the document whose name you've supplied. For tables and queries, it's simpler to use the TableDefs and QueryDefs collections directly. Access lumps tables and queries together in the Tables container, but keeps them separate in the TableDefs and QueryDefs collections. If the code looked in the Tables container, it would have to take an extra step to distinguish tables from queries; that step isn't necessary if it uses the collections instead.
The code for acbDoesObjExist is as follows:
Public Function acbDoesObjExist( _
strObj As String, objectType As AcObjectType)
Dim db As DAO.Database
Dim strCon As String
Dim strName As String
On Error GoTo HandleErr
Set db = CurrentDb( )
Select Case objectType
Case acTable
strName = db.TableDefs(strObj).Name
Case acQuery
strName = db.QueryDefs(strObj).Name
Case acForm, acReport, acMacro, acModule
Select Case objectType
Case acForm
strCon = "Forms"
Case acReport
strCon = "Reports"
Case acMacro
strCon = "Scripts"
Case acModule
strCon = "Modules"
End Select
strName = db.Containers(strCon).Documents(strObj).Name
End Select
acbDoesObjExist = True
ExitHere:
Exit Function
HandleErr:
acbDoesObjExist = False
Resume ExitHere
End Function
Note that in the Select Case statement, the code first checks to see if you're asking about a table or a query. If so, it looks in the appropriate collection:
Select Case objectType
Case acTable
strName = db.TableDefs(strObj).Name
Case acQuery
strName = db.QueryDefs(strObj).Name
.
.
.
End Select
If not, it assigns to strCon the name of the container it will need and then attempts to retrieve the Name property of the particular document within the selected container:
Case acForm, acReport, acMacro, acModule
Select Case objectType
Case acForm
strCon = "Forms"
Case acReport
strCon = "Reports"
Case acMacro
strCon = "Scripts"
Case acModule
strCon = "Modules"
End Select
strName = db.Containers(strCon).Documents(strObj).Name
See Also
If you haven't done much investigation of DAO in Access, you may find it useful to study the appropriate chapters in the Building Applications manual that ships with Access. Though complete coverage of DAO is beyond the scope of this book, there are several examples using DAO in other chapters, especially Chapter 4 and Chapter 6. In addition, DAO Object Model: The Definitive Reference, by Helen Feddema (O'Reilly),