Alison Balteramp;#039;s Mastering Microsoft Office Access 1002003 [Electronic resources] نسخه متنی

Understanding Access's Object Model

Now that I've discussed the concept of objects, properties, methods, and events in a general sense, I'm going to switch the discussion to the objects that are natively part of Microsoft Access. Databases are composed of objects, such as the tables, queries, forms, reports, data access pages, macros, and modules that appear in the Database window. They also include the controls (text boxes, list boxes, and so on) on a form or report. The key to successful programming lies in your ability to manipulate the database objects using VBA code at runtime. It's also very useful to be able to add, modify, and remove application objects at runtime.

The Application Object

At the top of the Access Object Model, you will find the Application object, which refers to the active Access application. It contains all Access's other objects and collections, including the Forms collection, the Reports collection, the DataAccessPages collection, the Modules collection, the CurrentData object, the CurrentProject object, the CodeProject object, the CodeData object, the Screen object, and the DoCmd object. You can use the Application object to modify the properties of, or execute commands on, the Access application itself, such as specifying whether Access's built-in toolbars are available while the application is running.

Application Object Properties

The Application object has a rich list of properties. A relatively new property introduced with Access 2002 is the BrokenReference property. You use this property to determine whether any broken references exist within the current project. The property is equal to True if broken references exist, and False if no broken references are identified. The property eliminates the need to iterate through each reference, determining whether any references are broken. The following code returns the value of the BrokenReference property:

Public Function IdentifyBrokenReference() As Boolean
'Return whether or not broken references are identified
'within the current project
IdentifyBrokenReference = Application.BrokenReference
End Function

Application Object Methods

Just as the Application object has a rich list of properties, it also has a rich list of methods. A relatively new method introduced with Access 2002 is the CompactRepair method, which allows you to programmatically compact and repair a database, without declaring ActiveX Data Objects (ADO) objects. The code looks like this:

Sub CompactRepairDB()
Dim strFilePath As String
'Store path of current database in a variable
strFilePath = CurrentProject.Path
'If destination database exists, delete it
If Len(Dir(strFilePath & "\Chap8Small.mdb")) Then
Kill strFilePath & "\Chap8Small.mdb"
End If
'Use the CompactRepair method of the Application object
'to compact and repair the database
Application.CompactRepair strFilePath & "\Chap8Big.mdb", _
strFilePath & "\Chap8Small.mdb", True
End Sub

This code uses the Path property of the CurrentProject object to extract the path of the current project and place it into a string variable. Covered later in this chapter, the CurrentProject object returns a reference to the current database project. The code uses the Dir to evaluate whether the database called Chap8Small.mdb exists. If it does, the code uses the Kill command to delete the file. Finally, the code uses the CompactRepair method to compact the Chap8Big.mdb file into Chap8Small.mdb.

Another method introduced with Access 2002 is the ConvertAccessProject method. This method allows you to programmatically convert an Access database from one version of Access to another. Here's an example:

Sub ConvertAccessDatabase()
Dim strFilePath As String
'Store current file path into variable
strFilePath = CurrentProject.Path
'Delete destination database if it exists
If Len(Dir(strFilePath & "\Chap8V97.mdb")) Then
Kill strFilePath & "\Chap8V97.mdb"
End If
'Convert source database to Access 97 file format
Application.ConvertAccessProject strFilePath & "\Chap8Big.mdb", _
strFilePath & "\Chap8V97.mdb", _
DestinationFileFormat:=acFileFormatAccess97
End Sub

This code first places the path associated with the current project into a variable called strFilePath. Next, it determines whether a file called Chap8V97.mdb exists. If it does, it deletes the file. Finally, the code uses the ConvertAccessProject method of the Application object to convert an Access 2002 database called Chap8Big.mdb to the Access 97 file format. The destination file is called Chap8V97.mdb. Different constants are used for the DestinationFileFormat parameter to designate conversion of the source file to different versions of Access.

The Forms Collection

The Forms collection contains all the currently open forms in the database. Using the Forms collection, you can perform an action, such as changing the color, on each open form.

NOTE

The Forms collection isn't the same as the list of all forms in the database; that list is part of the CurrentProject object discussed later in this chapter.

The code that follows iterates through the Forms collection, printing the name of each form. It is found in the basApplicationObject module within the Chap8Ex database. It begins by establishing a form object variable. It then uses the For Each…Next construct to loop through each form in the Forms collection (the collection of open forms), printing its name. Before running the code, open a few forms. Run the code and then take a look in the Immediate window. Close a couple of the forms and rerun the code. The list of forms displayed in the Immediate window should change.

Sub IterateOpenForms()
'Declare a form object variable
Dim frm As Form
'Use the form object variable to point at each form in the Forms collection
For Each frm In Forms
'Print the name of the referenced form to the Immediate window
Debug.Print frm.Name
Next frm
End Sub
Chapter 15, "Debugging: Your Key to Successful Development." You can easily invoke it using the Ctrl+G keystroke combination.

NOTE

Notice that it is not necessary to refer to Application.Forms. This is because the Application object is always assumed when writing VBA code within Access.

The Reports Collection

Just as the Forms collection contains all the currently open forms, the Reports collection contains all the currently open reports. Using the Reports collection, you can perform an action on each open report.

The code that follows iterates through the Reports collection, printing the name of each open report. It is found in basApplicationObject. It begins by establishing a report object variable. It then uses the For Each…Next construct to loop through each report in the Reports collection (the collection of reports open in print preview), printing its name.

Sub IterateOpenReports()
'Declare a report object variable
Dim rpt As Report
'Use the report object variable to point at each report in the Reports collection
For Each rpt In Reports
'Print the name of the referenced report to the Immediate window
Debug.Print rpt.Name
Next rpt
End Sub

The DataAccessPages Collection

Just as the Forms collection contains all the currently open forms and the Reports collection contains all the currently open reports, the DataAccessPages collection contains all the currently open data access pages. Using the DataAccessPages collection, you can perform an action on each open data access page.

The code that follows iterates through the DataAccessPages collection, printing the name of each open data access page. It is found in basApplicationObject. It establishes a DataAccessPage object variable. It then uses the For Each…Next construct, along with the object variable, to point at each data access page in the DataAccessPages collection (the collection of open data access pages). The code prints the name of each data access page to the Immediate window.

Sub IterateOpenDataAccessPages()
'Declare a data access page variable
Dim dap As DataAccessPage
'Use the data access page object variable to point at
'each data access page in the DataAccessPages collection
For Each dap In DataAccessPages
'Print the name of the referenced data access page
'to the Immediate window
Debug.Print dap.Name
Next dap
End Sub

The Modules Collection

The Modules collection contains all the standard and class modules that are open. All open modules are included in the Modules collection, regardless of whether they're compiled and whether they contain code that's currently running.

The CurrentProject Object

The CurrentProject object returns a reference to the current project. The CurrentProject contains properties such as Name, Path, and Connection. It contains the following collections: AllDataAccessPages, AllForms, AllMacros, AllModules, and AllReports. You can use these collections to iterate through all the data access pages, forms, macros, modules, and reports stored in the database. These collections differ from the DataAccessPages, Forms, Macros, Modules, and Reports collections in that they refer to all objects stored in the current project, rather than to just the open objects.

The following code retrieves the Name and Path properties of the current project. It uses the With...End With construct to retrieve the properties of the CurrentProject object.

Sub CurrentProjectObject()
With CurrentProject
Debug.Print .Name
Debug.Print .Path
End With
End Sub

The AllForms Collection

As previously mentioned, the CurrentProject object contains collections that refer to the various objects in your database. The following code iterates through the AllForms collection of the CurrentProject, printing the name of each form.

Sub IterateAllForms()
Dim vnt As Variant
'Loop through each form in the current project,
'printing the name of each form to the Immediate window
With CurrentProject
For Each vnt In .AllForms
Debug.Print vnt.Name
Next vnt
End With
End Sub

NOTE

It's easy to confuse the AllForms collection of the CurrentProject object with the Forms collection. The AllForms collection of the CurrentProject object comprises all the saved forms that are part of the database; the Forms collection comprises only the forms currently running in memory. If you want to see a list of all the forms that make up a database, you must use the AllForms collection of the CurrentProject object. However, if you want to change the caption of all the open forms, you must use the Forms collection.

The AllReports Collection

The AllReports collection allows you to loop through all reports in the current project. The example that follows prints the name of each report stored in the database referenced by the CurrentProject object.

Sub IterateAllReports()
'Declare iteration variable
Dim vnt As Variant
'Loop through each report in the current project,
'printing the name of each report to the Immediate window
With CurrentProject
For Each vnt In .AllReports
Debug.Print vnt.Name
Next vnt
End With
End Sub

The AllMacros Collection

The AllMacros collection is a collection that allows you to iterate through all macros stored in the current project. The example that follows prints the name of each macro stored in the database referenced by the CurrentProject object.

Sub IterateAllMacros()
'Declare iteration variable
Dim vnt As Variant
'Loop through each macro in the current project,
'printing the name of each macro to the Immediate window
With CurrentProject
For Each vnt In .AllMacros
Debug.Print vnt.Name
Next vnt
End With
End Sub

The AllModules Collection

The AllModules collection is another collection associated with the CurrentProject object. The code that follows iterates through all modules located in the database referenced by the CurrentProject object. The name of each module is printed to the Immediate window.

Sub IterateAllModules()
'Declare iteration variable
Dim vnt As Variant
'Loop through each module in the current project,
'printing the name of each module to the Immediate window
With CurrentProject
For Each vnt In .AllModules
Debug.Print vnt.Name
Next vnt
End With
End Sub

The AllDataAccessPages Collection

The AllDataAccessPages collection allows you to programmatically manipulate all data access pages found in the database referenced by the CurrentProject object. The code that follows iterates through the AllDataAccessPages collection, printing the name of each data access page.

Sub IterateAllDAPs()
'Declare iteration variable
Dim vnt As Variant
'Loop through each data access page in the current project,
'printing the name of each data access page to the Immediate window
With CurrentProject
For Each vnt In .AllDataAccessPages
Debug.Print vnt.Name
Next vnt
End With
End Sub

The CurrentData Object

Whereas you use the CurrentProject object to access and manipulate the application components of your database, you use the CurrentData object to reference the data elements of the database. The CurrentData object contains six collections: AllDatabaseDiagrams, AllQueries, AllStoredProcedures, AllTables, AllViews, and AllFunctions. You use these collections to iterate through and manipulate all the database diagrams, queries, stored procedures, views, and functions stored in the database. The sections that follow cover the AllTables and AllQueries collections. The AllDatabaseDiagrams, AllStoredProcedures, AllViews, and AllFunctions collections are available only in Access Data Projects and are discussed in detail in

Alison Balter's Mastering Access 2002 Enterprise Development .

The AllTables Collection

The AllTables collection is used to iterate through all tables in the database referenced by the CurrentData object as shown in the following code. It prints the name of each table in the database.

Sub IterateAllTables()
'Declare looping variable
Dim vnt As Variant
'Loop through each table in the database
'referenced by the CurrentData object
With CurrentData
For Each vnt In .AllTables
'Print the name of the table
Debug.Print vnt.Name
Next vnt
End With
End Sub

The AllQueries Collection

You use the AllQueries collection to iterate through all queries located in the database referenced by the CurrentData object. The following example loops through all queries in the database referenced by the CurrentData object. The name of each query is printed to the Immediate window.

Sub IterateAllQueries()
'Declare looping variable
Dim vnt As Variant
'Loop through each query in the database
'referenced by the CurrentData object
With CurrentData
For Each vnt In .AllQueries
'Print the name of the table
Debug.Print vnt.Name
Next vnt
End With
End Sub

The CodeProject Object

You use the CodeProject object when your database implements code libraries. It is similar to the CurrentProject object, but is used to reference the properties and collections stored within the library database. Chapter 24, "Creating Your Own Libraries," covers library databases.

The CodeData Object

Just as the CodeProject object is used to reference the application objects stored within a library database, the CodeData object is used to reference the data elements of a code library. These include the database diagrams, queries, stored procedures, tables, views, and functions stored within the library.

The Screen Object

You can use the Screen object to refer to the form, datasheet, report, data access page, or control that has the focus. The Screen object contains properties that refer to the active form, active report, active control, and previous control. Using these properties, you can manipulate the currently active form, report, or control, as well as the control that was active just before the current control. If you try to refer to the Screen object when no form or report is active, a runtime error occurs.

The DoCmd Object

The DoCmd object is used to perform macro commands or Access actions from VBA code; it's followed by a period and the name of an action. Most of the DoCmd actionsthe OpenQuery action, for examplealso require arguments. The OpenQuery action is used to execute an Access query. It receives the following arguments:

• Query Name
  The name of the query you want to execute

• View
  Datasheet, Design, or Print preview

• Data Mode
  Add, edit, or read-only

Here's an example of the OpenQuery action of the DoCmd object:

DoCmd.OpenQuery "qryCustomers", acNormal, acReadOnly

The OpenQuery action is performed by the DoCmd object. The first argument, the query name, is "qryCustomers". This is the name of the query that's opened in Datasheet view (rather than Design view or Print preview). It's opened in read-only mode, meaning the resulting data can't be modified.