Recipe 7.10 Detect Whether an Object Exists

7.10.1 Problem

You create and delete objects as your application runs. At some point, you need to be able to tell whether an object exists and make decisions based on that fact. But you can't find a function in Access that will tell you if a specific object already exists. Are you missing something? This ought to be a basic part of the product!

7.10.2 Solution

You haven't missed anything: Access really doesn't supply a simple method of determining if a specific object already exists. On the other hand, this is really quite simple, as long as you understand two important concepts: Access's support for DAO Container objects, and the ways you can use error handling to retrieve information. This solution uses these two subjects to provide a function you can call to check for the existence of any object.

Load and run frmTestExist from 07-10.MDB. This form, shown in Figure 7-14, lets you specify an object name and its type and then tells you whether that object exists. Certainly, you wouldn't use this form as-is in any application its purpose is to demonstrate the acbDoesObjExist function in basExists (07-10.MDB). To make your exploration of frmTestExist easier, Table 7-8 lists the objects that exist in 07-10.MDB. Try entering names that do and don't exist, and get the types right and wrong, to convince yourself that the acbDoesObjExist function does its job correctly.

Figure 7-14. frmTestExist lets you check for the existence of any object in the current database
figs/acb2_0714.gif

Table 7-8. The sample objects in 07-10.MDB

Object name

Object type

tblTest

Table

qryTest

Query

frmTest

Form

frmTestExist

Form

basExists

Module

Follow these steps to use acbDoesObjExist in your own applications:

  1. Import the module basExists from 07-10.MDB. This module contains the acbDoesObjExist function.

  2. To check for the existence of any object, call acbDoesObjExist, passing to it the name of the object to check for and a value from the AcObjectType enumeration indicating the object's type. The type parameter must be chosen from the values acTable, acQuery, acForm, acReport, acMacro, or acModule. For example, to check for the existence of a table named "Customers", call acbDoesObjExist like this:

    If acbDoesObjExist("Customers", acTable) Then    ' You know the table exists. Else    MsgBox "The table 'Customers' doesn't exist!" End If

7.10.3 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

7.10.4 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), provides complete documentation of the DAO object model.



Access Cookbook
Access Data Analysis Cookbook (Cookbooks)
ISBN: 0596101228
EAN: 2147483647
Year: 2003
Pages: 232

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net