MS-Access / Getting Started

---

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 one or more errors. 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 enables 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 error 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

The Databases Collection

Using DAO, you can have more than one database open in Access at any time. If you're using an .accdb or .mdb database file, you already have one database open (called the current database). Using the Workspace object's OpenDatabase method, as shown earlier in the example in the "Using Transactions" section, you can open more than one database, and operate on them under the same workspace context. Indeed, if you were to define more than one Workspace object, you could have several databases open, each operating under a different workspace context. The Databases collection contains and manages all databases currently open in the workspace.

The Default (Access) Database

Unless you're working with an Access Data Project, when you create a database in Access, it is automatically added to the Databases collection.

Among its properties and methods, the Database object contains five collections: TableDefs, Containers, QueryDefs, Recordsets, and Relations. Each of these collections and their respective objects and properties are discussed in later sections. In most cases, you will be working with the default Microsoft Access database, which you can refer to using any of the following syntaxes:

DBEngine.Workspaces("#Default Workspace#").Databases(0)
DBEngine.Workspaces(0).Databases(0)
DBEngine(0).Databases(0)
DBEngine(0)(0)
CurrentDb()

The current user's default database is an object that you will use quite a lot. Although you can work with it using any of the reference methods listed, in most cases it is often more convenient to assign it to an object variable.

Dim dbs As DAO.Database
Set dbs = DBEngine(0)(0)

But far and away the most common method is to use the CurrentDb() function, described in the following section.