Medium 9781565924352

DAO Object Model: The Definitive Reference: The Definitive Reference

Views: 2913
Ratings: (0)

Each iteration of Windows has meant a corresponding improvement in the techniques used for transferring data among its applications. Today's leading technique is called Automation. It allows you to work directly with objects in an application's interface using their object models. But if you want to write code in a programming language, such as Visual Basic, in order to work with the apps that support Automation, you must understand the inner workings of an application's object model--or in the case of Microsoft's Access, its two object models.Microsoft Access is the bestselling stand-alone relational database program for Windows offering both power and ease of use. And in many respects, Microsoft has made Automation the centerpiece of its vision for application development. DAO Object Model: The Definitive Reference will guide you through the Access object models, allowing you, with the support of Automation, to reference the application components you want to manipulate. An understanding of the object models is essential for developers who work with data in Access tables, or who want to manipulate components of the Access interface from other Office apps. The Data Access Objects (DAO) model is used to write and read data in Access tables. The Access object model is used to manipulate forms, reports, queries, macros, and other components of the Access interface, including most of the commands by means of the DoCmd object.This book will include an introduction and a brief description of the differences between VBA (used in most Office applications) and VBScript (used in Outlook). This chapter will also cover Office utilities and add-ons helpful in writing and debugging code, such as the Object Browser, the Integrated Development Environment (IDE) for VBA and the Interactive Debugger for VBScript. The book will then be divided into two parts; one covering the Access Object Model and the other, the Data Access Objects. Each section will have a description of what the object represents; listings of properties, events, and methods; and one or more code samples illustrating its use in VBA and/or VBScript code. Each property, event, or method section will have an explanation of the language element, and many will have code samples (either VBA or VBScript) as well.This book will detail, to an advanced user or keen intermediate user, the Access object models and how they are used. It will be the reference guide VB developers reach for when working with data in Access tables, or for manipulating components of the Access interface from other Office applications.

List price: $27.95

Your Price: $22.36

You Save: 20%

 

61 Slices

Format Buy Remix

Early and Late Binding

PDF

2

Chapter 1: Introduction

object model (for example, JScript or Perl running in IIS/ASP, or C/C++). But in real life, Visual Basic for Applications (VBA) or VBScript (VBS) are the most common languages used to work with object models, probably by several orders of magnitude. An object generally represents something you work with in the application’s interface—for example, Access tables, forms, and reports; Word documents, tables, and words; Excel worksheets, charts, and ranges; and so forth.

Additionally, some of the objects in an object model may be collections of other objects, such as the Reports collection in Access (which is a collection of all open

Report objects) or the Worksheets collection in Excel (which is a collection of all the Worksheet objects in a particular workbook).

When you write code in a dialect of Visual Basic to work with other applications that support Automation, you need to understand the server application’s object model, so that you will be able to reference the appropriate application components as represented in its object model, and use their methods and properties to achieve the desired results. While the names of objects in an object model may be familiar to you from working with the corresponding objects in the interface (for example, Access tables, forms, and reports), in other cases the object names may not be familiar from working in the application’s interface (such as the Access

 

The Object Models for Working with Access

PDF

6

Chapter 1: Introduction

Figure 1-2. The References dialog with references set to the ADO and DAO object libraries

The Object Models for

Working with Access

While most applications have only one object model, Access has three: one for the interface, one for data stored in Access databases, and one for data stored in either

Access databases or external data sources. (One of them—ADO—is a recent addition to Microsoft’s stable of data access technologies and new to Access 2000.)

The Access Object Model

The Access object model (shown in Figure 1-3) represents the Access interface elements (forms, reports, and modules) and a good deal of the application’s functionality via the Screen and DoCmd objects. Although there is some overlap of functionality between the Access and DAO object models (via the DBEngine object, located under the Application object), the Access object model is primarily used to work with Access objects, such as forms and reports.

The DAO Object Model

The DAO (Data Access Objects) object model represents the data stored in Access tables. You need to work with the DAO object model both within Access VBA and when working with data stored in Access databases from other applications, using

 

The Visual Basic Dialects

PDF

The Visual Basic Dialects

11

If you get compile errors after converting an Access 97 database to

Access 2000, this may be because the ADO reference is located above the DAO reference in the References list. If you don’t anticipate using ADO in the database, just uncheck its reference; otherwise, move the DAO reference above the ADO reference in the list, so any ambiguous objects will be interpreted as belonging to the

DAO object model.

To prevent confusion (particularly likely with recordsets, which are the most commonly used objects in both object models), use the prefix DAO when declaring a

DAO database or recordset and the prefix ADODB when declaring a recordset, as in the following code samples (and Example 1-3):

Dim dbs as DAO.Database

Dim rst as DAO.Recordset

Dim rst as ADODB.Recordset

(You have to work down to recordsets from databases in DAO, while in ADO you can create a recordset variable directly.)

If you only need to work with a single object model (DAO or ADO) in your code, you can eliminate the need to use prefixes by checking only the appropriate library in the References dialog so as to remove ambiguity in your code. However, it may be prudent to use prefixes even in that case, as you may need to use the other object model at a later time. If you use prefixes right from the start, you won’t have to rewrite your code later on when you add the second reference.

 

DBEngine Properties

PDF

26

Chapter 2: DBEngine Object

DBEngine Properties

DefaultPassword

Data Type

String

Description

When it is initialized, uses a case-sensitive string to set the password used to create the default workspace. The password string can be 1–20 characters in length for Jet workspaces or any length for ODBCDirect workspaces. Any character is permitted except ASCII 0. By default, DefaultPassword is a zero-length string, which means that the database is not password protected. DefaultPassword must be set before the default workspace is used in order to have any effect. Use this method if you want to assign a certain password to all new databases. See the

DefaultUser entry for a code sample using this method.

DefaultType

Data Type

Long

Description

Sets or returns a value dictating what type of workspace ( Jet or ODBCDirect) the next Workspace object created will be. The property can be set to the values listed in Table 2-5.

Table 2-5. The Values of the DefaultType Property

Named Constant

Value

Description

dbUseJet

 

DBEngine Methods

PDF

30

Chapter 2: DBEngine Object

Table 2-7. Jet Engine and Application Versions (continued)

Jet Version and Year

Access

Visual Basic

Excel

Visual C++

2.0 (1994)

2.0

N/A

N/A

N/A

2.5 (1995)

N/A

4.0 (16-bit)

N/A

N/A

3.0 (1995)

95 (7.0)

4.0 (32-bit)

95 (7.0)

4.x

3.5 (1996)

97 (8.0)

5.0

97 (8.0)

5.0

3.6 (1999)

2000 (9.0)

N/A

N/A

N/A

VBA Code

This example reports on the Jet version currently in use in the running database:

Private Sub cmdVersion_Click()

MsgBox "Currently using Jet, v. " & DBEngine.Version

End Sub

DBEngine Methods

BeginTrans

DBEnginw.BeginTrans()

The BeginTrans method is listed in Access 97 Help as a method of the DBEngine object. Actually, it is a method of the Workspace object, one of the members of the Workspaces collection under the DBEngine object, so it will be discussed in

Chapter 4, Workspaces Collection and Workspace Object. This error has been corrected in Access 2000 Help.

CommitTrans

DBEngine.CommitTrans()

As with BeginTrans, this method is actually a method of the Workspace object and will be discussed in Chapter 4.

 

Errors Collection Properties

PDF

44

Chapter 3: Errors Collection and Error Object

Errors Collection Properties

Count

Type

Integer

Description

Returns the number of Error objects in the Errors collection. Collection numbering is zero-based (the first member is numbered 0), so if you loop through the Errors collection, you should start with 0 and end with Count –1. The Count property is never Null; instead, if there are no errors, its value is 0, indicating that there is no collection to count.

With previous versions of Access, the Count property was needed to loop through this collection. Since Access 95, it is no longer needed for this purpose; you can use the more efficient For Each...Next construct instead.

Item

DBEngine.Errors.Item(Index)

Type

Error object

Description

Returns the Error object found at the Index ordinal position in the Errors collection. Since Item is the default member of the Errors collection, the Item property need not be explicitly referenced in code. For example, the following two statements each retrieve the last Error object from the Errors collection:

 

Errors Collection Methods

PDF

Description

45

Errors Collection Methods

Refresh

Errors.Refresh

Updates the objects in the Errors collection so that the Number property of the last member of the Errors collection matches the value of the VBA Err object. This is necessary if you are going to use a Select Case statement to identify the particular DAO error or errors that occurred, for example, in an error-handling construct.

Error Object Properties

Description

Type

String

Description

Describes the error. You can use this string in standard error-handling code when you don’t want (or need) to write your own custom error messages, as in the following code sample.

VBA Code

Figure 3-2 shows the dialog box generated by the code in this example, which lists all of the Error object’s properties:

Public Function ErrorProps()

On Error GoTo ErrorPropsError

Dim dbs As Database

Dim strError As String

Dim err As Error

'Try to set dbs to a nonexistent database to

'generate a DAO error.

Set dbs = OpenDatabase("None.mdb")

ErrorPropsExit:

Exit Function

ErrorPropsError:

 

Error Object Properties

PDF

Description

45

Errors Collection Methods

Refresh

Errors.Refresh

Updates the objects in the Errors collection so that the Number property of the last member of the Errors collection matches the value of the VBA Err object. This is necessary if you are going to use a Select Case statement to identify the particular DAO error or errors that occurred, for example, in an error-handling construct.

Error Object Properties

Description

Type

String

Description

Describes the error. You can use this string in standard error-handling code when you don’t want (or need) to write your own custom error messages, as in the following code sample.

VBA Code

Figure 3-2 shows the dialog box generated by the code in this example, which lists all of the Error object’s properties:

Public Function ErrorProps()

On Error GoTo ErrorPropsError

Dim dbs As Database

Dim strError As String

Dim err As Error

'Try to set dbs to a nonexistent database to

'generate a DAO error.

Set dbs = OpenDatabase("None.mdb")

ErrorPropsExit:

Exit Function

ErrorPropsError:

 

Workspaces Collection Properties

PDF

Count

51

Access to the Workspace Object

Creatable

No

Returned by

The CreateWorkspace method of the DBEngine object

The Workspaces property of the DBEngine object

Workspaces Collection Properties

Count

Data Type

Integer

Description

Gives the number of objects in the Workspaces collection. In previous versions of

Access, Count was useful for setting up loops to process all objects in a collection, as in the following code example. However, the For Each...Next loop is a more efficient way of iterating through the members of a collection (see the

Append section for an example of using For Each...Next to process all members of the Workspaces collection).

VBA Code

Private Sub cmdCount_Click()

Dim

Dim

Dim

Dim

Dim

Dim

Dim

Dim

wks As Workspace wks1 As Workspace wks2 As Workspace wks3 As Workspace wks4 As Workspace prp As Property intCount As Integer wksCount As Integer

Set wks1 = Workspaces(0)

Set wks2 = CreateWorkspace("Workspace1", "Admin", _

"", dbUseJet)

Workspaces.Append wks2

Set wks3 = CreateWorkspace("Workspace2", "Admin", _

 

Workspaces Collection Methods

PDF

Append

53

Set wks = DBEngine.Workspaces.Item(0)

Set wks = DBEngine.Workspaces(0)

Similarly, the following two statements are equivalent; both retrieve the default workspace by name:

Set wks = DBEngine.Workspaces.Item("#Default Workspace#")

Set wks = DBEngine.Workspaces("#Default Workspace#")

Workspaces Collection Methods

Append

Workspaces.Append object

Description

Adds a newly created Workspace object to the Workspaces collection, as in the example code.

VBA Code

The CreateWorkspace method of the DBEngine object is used to return a reference to object, which is then added to the Workspaces collection by using its

Append method. Note that there is no need to explicitly create the default workspace, or Workspaces(0), so when you explicitly create a workspace, it is the second (or higher) workspace in the Workspaces collection:

Private Sub cmdAppend_Click()

Dim wks As Workspace

Dim wks2 As Workspace

Dim prp As Property

Set wks2 = CreateWorkspace("Workspace1", "Admin", _

"", dbUseJet) wks2.Name = "Workspace 2"

 

Workspace Object Properties

PDF

54

Chapter 4: Workspaces Collection and Workspace Object wks2.Close

End Sub

Delete

Although Delete is listed as a method of the Workspaces collection, it will cause an error if you try to use it. Just use the Close method of the Workspace object to remove a workspace you have created, as in the preceding code sample.

Refresh

Much like the Delete method, although Refresh is listed as a method of the Workspaces collection, it is nonfunctional for the Workspaces collection. However, a call to the Refresh method generates neither a compile-time nor a runtime error; it simply has no effect.

Workspace Object Properties

DefaultCursorDriver

Data Type

Long

Description

Sets or returns the cursor driver type used on a connection created by the OpenConnection or OpenDatabase methods for ODBCDirect workspaces. Possible values are shown in Table 4-6.

Table 4-6. The DefaultCursorDriver Intrinsic Constants

Named Constant

Value

Description

dbUseDefaultCursor

-1

(Default) Uses server-side cursors if the server supports them; otherwise uses the ODBC Cursor

 

Workspace Object Methods

PDF

58

Chapter 4: Workspaces Collection and Workspace Object

UserName

Data Type

String

Description

Represents the owner of a Workspace object. In the case of a Jet workspace, this is a User object in the Users collection, or a Group object in the Groups collection.

Workspace Object Methods

BeginTrans object.BeginTrans

Description

Starts a new transaction during a workspace session. It is paired with the CommitTrans method, which ends the transaction, or the Rollback method, which aborts the transaction.

VBA Code

This VBA code illustrates the use of the BeginTrans, CommitTrans, and Rollback methods to enclose a loop in which the user is asked to confirm increasing a price field by 10%. Apart from the individual choice offered for each record, the user has a chance to undo the entire transaction by selecting not to commit when all the records have been processed. If the user chooses to commit the transaction, the wks.CommitTrans method is used; otherwise, wks.Rollback is used to roll back (undo) the individual changes, leaving the prices unchanged:

 

Databases Collection Properties

PDF

80

Chapter 5: Databases Collection and Database Object

Table 5-3. Database Object Methods (continued)

Method

Description

Execute

Executes a query or SQL statement that does not return a recordset

MakeReplica

Makes a new replica from another database replica

NewPassword

Changes a database’s password

OpenRecordset

Opens a recordset

PopulatePartial

Synchronizes a partial replica with a full database replica

Synchronize

Synchronizes two replicas that are members of the same replica set

Access to the Database Object

Creatable

No

Returned by

The Database property of the Connection object

The CreateDatabase method of the DBEngine object

The OpenDatabase method of the DBEngine object

The CreateDatabase method of the Workspace object

The Databases property of the Workspace object

The OpenDatabase method of the Workspace object

Databases Collection Properties

Count

Data Type

Integer

Description

The number of objects in a collection. In previous versions of Access, Count was useful for setting up loops to process all objects in a collection, as in the following code; however, the For Each...Next loop is a more efficient way of iterating through the members of a collection. The following code lists the name of all the open databases in the Debug window. You should see four names listed—the three opened from code, plus the database from which the code is running.

 

Databases Collection Methods

PDF

82

Chapter 5: Databases Collection and Database Object

You can also retrieve a particular Database object by name. The following code fragment, for instance, retrieves the Northwind database by name:

Dim wks As Database

Set wks = DBEngine.Workspaces(0).Databases("C:\Documents\Northwind.mdb")

Note that the database’s name as supplied to the Item property corresponds to its

Name property, which includes its path as well as its filename. Since the Item property is the default member of the Databases collection, it does not need to be explicitly specified when retrieving a particular Database object. In other words, the following two statements are equivalent; both retrieve the same database:

Set dbs = DBEngine.Workspaces(0).Databases.Item(0)

Set dbs = DBEngine.Workspaces(0).Databases(0)

Similarly, the following two statements both retrieve the same database by name:

Set dbs = DBEngine.Workspaces(0).Databases.Item(strName)

Set dbs = DBEngine.Workspaces(0).Databases(strName)

Databases Collection Methods

 

Database Object Properties

PDF

82

Chapter 5: Databases Collection and Database Object

You can also retrieve a particular Database object by name. The following code fragment, for instance, retrieves the Northwind database by name:

Dim wks As Database

Set wks = DBEngine.Workspaces(0).Databases("C:\Documents\Northwind.mdb")

Note that the database’s name as supplied to the Item property corresponds to its

Name property, which includes its path as well as its filename. Since the Item property is the default member of the Databases collection, it does not need to be explicitly specified when retrieving a particular Database object. In other words, the following two statements are equivalent; both retrieve the same database:

Set dbs = DBEngine.Workspaces(0).Databases.Item(0)

Set dbs = DBEngine.Workspaces(0).Databases(0)

Similarly, the following two statements both retrieve the same database by name:

Set dbs = DBEngine.Workspaces(0).Databases.Item(strName)

Set dbs = DBEngine.Workspaces(0).Databases(strName)

Databases Collection Methods

 

Database Object Methods

PDF

Close

95

Debug.Print "Jet engine version: " & dbsJet.Version

End Sub

The code prints the following information to the Debug Window:

ODBCDirect connection version: 03.51.1029.00

Jet engine version: 3.0

Database Object Methods

Close database.Close

Closes a Database object and removes it from the Databases collection. If you try to close a database that has already been closed, runtime error 3420, “Object invalid or no longer set,” occurs.

If you close a database without first closing any open recordsets in it, the recordsets will be closed without saving any pending edits and without a warning.

As an alternative to using the Close method to close a database, you can set its variable equal to Nothing.

VBA Code

This code opens a database based on Northwind.mdb, then opens a recordset based on its Customers table, lists all the company names to the Debug window, and finally closes the recordset and the database:

Private Sub cmdClose_Click()

Dim wks As Workspace

Dim dbs As Database

Dim rst As Recordset

Set wks = DBEngine.Workspaces(0)

 

Containers Collection Properties

PDF

Count

131

Table 6-3. Container Object Properties (continued)

Property

Description

Permissions

Indicates the permissions belonging to the user of a Container object

UserName

The name of the Container object’s user

Access to the Container Object

Creatable

No

Returned by

The Containers property of the Database object

Containers Collection Properties

Count

Data Type

Integer

Description

Indicates the number of objects in a collection. It can be used to iterate through the members of a collection, as in the following VBA code, though this method has generally been replaced by the more efficient For Each...Next looping structure.

VBA Code

Private Sub cmdCount_Click()

Dim

Dim

Dim

Dim

Dim

Dim

Dim

dbs As Database strDBName As String ctr As Container doc As Document docs As Documents i As Integer intCount As Integer

strDBName = "D:\Documents\Northwind.mdb"

Set dbs = OpenDatabase(strDBName)

Set ctr = dbs.Containers("Reports")

Set docs = ctr.Documents intCount = docs.Count

Debug.Print intCount & " reports in Reports Container:"

For i = 1 To intCount

 

Containers Collection Methods

PDF

AllPermissions

133

Containers Collection Methods

Refresh

Containers.Refresh

Updates the objects in a collection, taking into account any recently made changes, such as a newly created table.

VBA Code

This code should be run either from Access or from a VB or VBA project to which a reference to the Access object model has been added:

Private Sub cmdRefresh_Click()

Dim dbs As Database

Set dbs = CurrentDb dbs.Containers.Refresh

End Sub

Container Object Properties

AllPermissions

Data Type

Long

Description

Returns all the permissions belonging to the current user of the Container object, including both user-specific and group permissions. (By contrast, the Permissions property includes only the user’s own permissions.) All Container objects can return the values listed in Table 6-4; in addition, the Databases container and all

Document objects in a Documents collection may return the values in Table 6-5.

Table 6-4. The AllPermissions Return Values for Container Objects

Named Constant

Value

Description

dbSecReadDef

 

Load more


Details

Print Book
E-Books
Slices

Format name
PDF
Encrypted
No
Sku
9781449363444
Isbn
9781449363444
File size
0 Bytes
Printing
Not Allowed
Copying
Not Allowed
Read aloud
No
Format name
PDF
Encrypted
No
Printing
Allowed
Copying
Allowed
Read aloud
Allowed
Sku
In metadata
Isbn
In metadata
File size
In metadata