6.4. The DBEngine Object

The DBEngine object is a property of the Access Application object, and represents the top-level object in the DAO model. The DBEngine object contains all the other objects in the DAO object hierarchy, yet unlike many of the other DAO objects, you can't create additional DBEngine objects.

The DBEngine object contains two major collections: Workspaces and Errors. These are described in this section because they relate so closely to the DBEngine object.

6.4.1. The Workspaces Collection

A workspace is a named user session that contains open databases and provides the facility for transactions and (depending on the type of workspace) user- and group-level security. As you can have more than one workspace active at any time, the Workspaces collection is the repository for all the workspaces that have been created.

As mentioned earlier, DAO maintains two different object models, depending on the type of data source you want to manipulate, and it is the Workspace object that determines which model you use: Microsoft Jet or ODBCDirect.

To access Microsoft Jet databases and ODBC or installable ISAM data sources through the Jet database engine, you use the Microsoft Jet workspace. To access ODBC data sources through DAO (bypassing Jet), you use ODBCDirect workspaces. The type of data source you connect to, and the DAO objects, properties, and methods you can use, depends on the type of workspace you create.

For a list of the collections, objects, and methods supported by Microsoft Jet and ODBCDirect workspaces, refer to Appendix C.

The Workspace object contains four different object collections. In Microsoft Jet workspaces, these are Databases, Groups, and Users. In ODBCDirect workspaces, they are the Databases and Connections collections. Each of these collections is described in later sections.

6.4.1.1. Creating a Workspace

When you first refer to a Workspace object, or one of its collections, objects, methods, or properties, you automatically create the default workspace, which can be referenced using the following syntaxes: DBEngine. Workspaces(0), DBEngine(0), or simply Workspaces(0).

The default workspace is given the name #Default Workspace#. In the absence of user- and group-level security, the default workspace's UserName property is set to Admin. If security is implemented, the UserName property is set to the name of the user who logged on.

You don't have to do anything to begin using a Microsoft Jet workspace; Access creates one by default unless you explicitly create an ODBCDirect workspace. To use an ODBCDirect workspace, you either set the DBEngine's DefaultType property to dbUseODBC, or set the CreateWorkspace method's Type property to dbUseODBC, when you create the workspace.

The basic procedure for creating a new workspace is as follows:

You can use a workspace without appending it to the Workspaces collection, but you must refer to it using the object variable to which it was assigned. You will not be able to refer to it through the Workspaces collection until it is appended.

The following example demonstrates how to create both a Microsoft Jet workspace and an ODBCDirect workspace, and print their Name properties:

Dim wsJet As DAO.Workspace
Dim wsODBC As DAO.Workspace

'Create a new Microsoft Jet workspace
Set wsJet = DBEngine.CreateWorkspace( _
        "myJetWS", strUserName, strPassword, dbUseJet)

'Create a new ODBCDirect workspace
Set wsODBC = DBEngine.CreateWorkspace( _
        "myODBCWS", strUserName, strPassword, dbUseODBC)

'Append the workspaces to the collection
Workspaces.Append wsJet
Workspaces.Append wsODBC

'Print the names of all the workspaces
Debug.Print "wsJet.Name: " & wsJet.Name       'myJetWS
Debug.Print "wsODBC.Name: " & wsODBC.Name     'myODBCWS

'Clean up
wsODBC.Close
wsJet.Close
Set wsODBC = Nothing
Set wsJet = Nothing

If you just want to use the default workspace, you can either refer to it as DBEngine(0), or create a reference to it in the same way you create references to other Access or DAO objects.

'Create a reference to the default workspace
Set wsJet1 = DBEngine(0)
Debug.Print "wsJet1.Name: " & wsJet1.Name    '#Default Workspace#

Since you're not creating a new workspace object, there is no need to append it to the Workspaces collection.

Finally, there is one other way to create a new workspace. To maintain compatibility with previous versions of DAO, Access 2003 still provides the DefaultWorkspaceClone method.

'Create a clone of the default workspace
Set wsJet2 = Application.DefaultWorkspaceClone
Debug.Print "wsJet2.Name: " & wsJet2.Name    '#Clone Access#

The DefaultWorkspaceClone method creates a clone (identical copy) of the default workspace, whatever it happens to be. The cloned workspace takes on properties identical to those of the original, with the exception of its Name property, which is set to #Clone Access#.

You would use the DefaultWorkspaceClone method where you want to operate two independent transactions simultaneously without needing to prompt the user again for the username and password.

6.4.1.2. Using Transactions

A transaction is defined as a delimited set of changes that are performed on a database's schema or data. They increase the speed of actions that change data, and enable you to undo changes that have not yet been committed.

Transactions offer a great deal of data integrity insurance for situations where an entire series of actions must complete successfully, or not complete at all. This is the all-or-nothing principle that is employed in most financial transactions.

For example, when your employer transfers your monthly salary from their bank to yours, two actions actually occur. The first is a withdrawal from your employer's account, and the second is a deposit into yours. If the withdrawal completes, but for some reason, the deposit fails, you can argue until you're blue in the face, but your employer can prove that they paid you, and are not likely to want to do so again. Similarly, your bank will not be too impressed if the withdrawal fails, but the deposit succeeds. The reality is that the bank will take the money back, and you still end up with no salary. Either way, you get shafted! If, however, the two actions are enclosed in a single transaction, they must both complete successfully, or the transaction is deemed to have failed, and both actions are rolled back (reversed).

You begin a transaction by issuing the BeginTrans method against the Workspace object. To write the transaction to disk, you issue the CommitTrans method, and to cancel, or roll back the transaction, strangely enough, you issue the Rollback method.

Normally, transactions are cached, and not immediately written to disk. But if you're in a real hurry to get home at five o'clock, and immediately switch off your computer before the cache is written to disk, your most recent changes are lost. In Microsoft Jet workspaces, you can force the database engine to immediately write all changes to disk, instead of caching them. You do this by including the dbFlushOSCacheWrites constant with CommitTrans. Forcing immediate writes may affect your application's performance, but the data integrity benefits may outweigh any performance hit in certain situations.

The following code segment demonstrates a typical funds transfer transaction using a Microsoft Jet workspace. As transactions operate at workspace level, you can use them in exactly the same way with ODBCDirect workspaces.

In the following example, and in other examples given throughout this chapter, we have deviated from the Reddick object-naming convention by varying the names for Workspace, Database, and Recordset object variables. We do this because it sometimes makes the code easier to understand. In the following example code, rather than extend the length of the two Database object names, we have given them the names dbC and dbX, for the current and external databases respectively. They could just as easily have been named dbsC and dbsX.

Public Sub TransferFunds()
    Dim wrk As DAO.Workspace
    Dim dbC As DAO.Database
    Dim dbX As DAO.Database

    Set wrk = DBEngine(0)
    Set dbC = CurrentDb
    Set dbX = wrk.OpenDatabase("c:TempmyDB.mdb")

    On Error GoTo trans_Err

    'Begin the transaction
    wrk.BeginTrans
        'Withdraw funds from one account table
        dbC.Execute "UPDATE Table1.....", dbFailOnError

        'Deposit funds into another account table
        dbX.Execute "INSERT INTO Table22.....", dbFailOnError

    'Commit the transaction
    wrk.CommitTrans dbFlushOSCacheWrites

trans_Exit:
    'Clean up
    wrk.Close
    Set dbC = Nothing
    Set dbX = Nothing
    Set wrk = Nothing
    Exit Sub

trans_Err:
    'Roll back the transaction
    wrk.Rollback
    Resume trans_Exit
End Sub

In the above example, changes to both databases either will complete as a unit, or will be rolled back as a unit.

You don't need to use transactions, but if you do, they can be nested up to five levels. It is also important to understand that transactions are global to the workspace—not the database. For example, if you make changes to two databases in the same workspace, and you roll back the changes to one of those databases, the changes made to the other database will also be rolled back.

6.4.2. The Errors Collection

The first thing to remember about the DAO Errors collection is that it is not the same as the VBA.Err object. The VBA.Err object is a single object that stores information about the last VBA error. The DAO Errors collection stores information about the last DAO error.

Any operation performed on any DAO object can generate an error. The DBEngine.Errors collection stores all the error objects that are added as the result of an error that occurs during a single DAO operation. Each Error object in the collection, therefore, contains information about only one error.

Having said that, some operations can generate multiple errors, in which case the lowest level error is stored in the collection first, followed by the higher level errors. The last error object usually indicates that the operation failed. Enumerating the Errors collection allows your error handling code to more precisely determine the cause of the problem, and to take the most appropriate remedial action.

When a subsequent DAO operation generates an error, the Errors collection is cleared and a new set of Error objects is added to the collection. This happens regardless of whether you have retrieved the previous errors' information or not. So you can see that unless you retrieve the information about an error as soon as it occurs, you may lose it if another error happens in the meantime. Each error obliterates and replaces its predecessor—a bit like politics really.

One last point to note is that an error that occurs in an object that has not yet been added to its collection, is not added to the DBEngine.Errors collection, because the "object" is not considered to be an object until it is added to a collection. In such cases, the error information will be available in the VBA.Err object.

To fully account for all errors, your error handler should verify that the error number returned by both the VBA.Err object and the last member of the DBEngine.Error object are the same. The following code demonstrates a typical error handler:

intDAOErrNo = DBEngine.Errors(DBEngine.Errors.Count - 1).Number

If VBA.Err <> intDAOErrNo Then
    DBEngine.Errors.Refresh
End If

For intCtr = 0 To DBEngine.Errors.Count - 1
    Select Case DBEngine.Errors(intCtr).Number
        Case 1
            'Code to handle error
        Case 2
            'Code to handle error
        '
        'Other Case statements
        '

Case 99
            'Code to handle error
    End Select
Next intCtr