Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

اینجــــا یک کتابخانه دیجیتالی است

با بیش از 100000 منبع الکترونیکی رایگان به زبان فارسی ، عربی و انگلیسی

Access Cookbook, 2nd Edition [Electronic resources] - نسخه متنی

Ken Getz; Paul Litwin; Andy Baron

| نمايش فراداده ، افزودن یک نقد و بررسی
افزودن به کتابخانه شخصی
ارسال به دوستان
جستجو در متن کتاب
بیشتر
تنظیمات قلم

فونت

اندازه قلم

+ - پیش فرض

حالت نمایش

روز نیمروز شب
جستجو در لغت نامه
بیشتر
لیست موضوعات
توضیحات
افزودن یادداشت جدید










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 applicationits 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


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.


/ 232