Microsoft Access 2010 Bible

Part II: Programming Microsoft Access

502

The DAO Database object

A Database object represents a data source and is analogous to an ADO Connection object.

Access is able to directly open a number of different database formats. When working directly with

the ACE or Jet database engines, a database could be any number of sources: a dBASE file, a

FoxPro file, another .mdb, or even an ODBC data source. The distinguishing feature is how you

set your database object variables.

The following code refers to the currently open Microsoft Access database:

Dim db As DAO.Database

Set db = CurrentDb

CurrentDb is a method of the Access Application object, which represents the entire Access

environment and all its objects. CurrentDb is a fast, easy way to open the database that the user

is currently working with.

It’s also possible to open an Access database outside of the current database:

Dim db As DAO.Database

Set db = OpenDatabase(“C:\Northwind.mdb”)

Notice that the OpenDatabase method accepts the path to an existing .mdb or .accdb file. The

OpenDatabase method may fail, depending on whether the external Access database is available,

or whether its current state prevents opening from another Access application.

As with ADO objects, be sure to prefix DAO object type declarations with DAO so that Access is

clear as to which library to use when setting up the object.

Note
ACEDAO objects use DAO as the prefix, just as DAO 3.6 objects do.

The DAO TableDef object

The DAO TableDef object represents a table in an Access database. The table may be local or

linked to the current database. The following procedure (which is included in the Chapter13.

accdb example database) creates a new table named MyTempTable, adds three text fields to it,

and adds the table to the current database’s TableDefs collection.

Public Sub CreateNewTableDef()

Dim db As DAO.Database

Dim tdfNew As DAO.TableDef

‘ The next statement instructs Access

‘ to ignore errors that may occur if

‘ the table does not already exist:

On Error Resume Next

‘Delete the new table if it exists:

db.TableDefs.Delete “MyTempTable”

‘ Return to default error handling: