Microsoft Data Access Object, or DAO, is a library that
ships with Microsoft Access. It allows you to create, maintain, and manage
databases. It also provides various means of performing the necessary
operations on a database.

Microsoft DAO is a library published by
Microsoft. When you start Microsoft Access, the DAO library is
available by default and ready to be used; that is, you don't have to "load" or "add" it to your database.

Practical
Learning: Introducing Microsoft DAO

Start Microsoft Access

Click Blank Desktop Database

Set the file name as Kolo Bank

Click Create

On the Ribbon, click File and click Options

In the left list of the Access Options dialog box, click Current
Database

Click the Detail section of the form
and, in the Property Sheet, change the Back Color to: Background Form:

Save the forrn as Database Objects

Add three buttons as follows:

Control

Name

Caption

Button

cmdCreateCustomersTable

Create Customers Accounts Table

Button

cmdCreateCustomerBillsTable

Create Customers Bills Table

Button

cmdClose

Close

Save the form

The Database Engine of DAO

As you may know already, the main program that performs all types of operations in a database is referred to as a database engine. In the DAO library, this program is represented by an object (or class) named
DBEngine, which is a static class; this means that you don't have to declare a variable of type DBEngine,
you can used it directly and access its members by qualifying them from its name.

The DAO Namespace

The DAO library and the Microsoft Access Object Library (MAOL) have a lot in common and they use the
same functionalities. To let you distinguish objects of DAO, this library has a
namespace named DAO. Based on this, when you want to use a class that is
appropriate to DAO, you should qualify that class from the DAO namespace.

The Microsoft Access Object Library and DAO

In this and the lesson that follow, any concept we will
refer to in DAO also applies to the Microsoft Access Object Library (MAOL).
The differences are that:

In the MAOL, (all) variables are declared using the Object class while
variables in DAO are declared using appropriate names of classes

If a variable is declared using the Object class, the
IntelliSense is not available for that variable in the Code Editor

Introduction to Databases in DAO

Introduction to the Database Class

To support databases, the DAO library provides a class named Database.
To use it, first declare a variable from this class. Here is an example:

Private Sub cmdCreate_Click()
Dim db As DAO.Database
End Sub

You can omit the DAO namespace. After declaring the variable, you can use the database as you see fit.

Closing a DAO Database

After using a database, you should close it (you are not
closing the database itself but you are dismissing the variable) to release the
resources it was consuming. To do this, you can call the Close() method
of the Database object. Here is an example:

Private Sub cmdCreate_Click()
Dim db As Database
db.Close
End Sub

While a database is being used, it consumes computer
resources. When you don't need the Database variable anymore, you should
free the resources it was using so they can become available to other
applications of the same computer. To remove the variable from memory, assign Nothing to
it using the Set operator. Here is an example:

As an alternative, if you are assigning Nothing to the variable, you can omit calling the Close() method.

Creating a Database in DAO

DAO is one of the earliest and reliable libraries of Microsoft Access.
It is is used by applications
published by companies other than Microsoft. Based on this, DAO supports all
types of operations that can be performed on a database. One of these basic
operations consists of creating a database.

To support
the creation of a database, the DBEngne class of the DAO library is equipped with
a method named
CreateDatabase. Its syntax is:

Public Sub CreateDatabase(ByVal Name As String, _
ByVal Locale As String, _
ByVal Optional options As Variant) As Database

Because the DBEngine object is already
recognized in the current database, you can omit it when calling the CreateDatabase()
method. The arguments of this method are the same as those of the CreateDatabase()
method of the Workspace class we reviewed for the Microsoft Access Object
library.

When the CreateDatabase() method has finished, it returns a reference to the database that was created. You must obtain that reference. The
database is recognized as the Database object of DAO. To get it, first declare a variable of type Database. To get a
reference to the new database, use the Set operator and assign the returned value of the method to your Database variable. Here is an
example that creates a new database named Exercise.accdb in the current folder:

When the DBEngine.OpenDatabase() method has been called, it
returns a Database object. If you want to continue doing anything on the open
database, you must get this Database value. Here is an example of getting it:

Private Sub cmdOpenDatabase_Click()
Dim db As DAO.Database
Set db = OpenDatabase("Example.accdb"). . . Now you can use the Database object
db.Close
Set db = Nothing
End Sub

Remember that the other arguments of the
DBEngine.OpenDatabase() method follow the same description we saw for the
Workspace class.

Getting a Reference to the Current Database

Opening a database allows you to get a reference to it and
do what you want on that database. Sometimes you want to work on the current
database. Instead of opening a different database, to let you access
the current database, Microsoft Access provides a static object named CurrentDb.
To use it, assign it to your Database variable using the Set operator.
Here is an example:

The Query argument can be a DDL statement which would
consist of creating a table or another object of that kind. The second argument
is a member of an enumeration named RecordsetOptionEnum. The possible value are:

RecordsetOptionEnum Member

Description

dbDenyWrite

The user is denied the Write
priviledge

dbInconsistent

The database will execute
inconsistent updates

dbConsistent

The database will execute the
consistent updates

dbSQLPassThrough

The database will execute a SQL
statement intended for ODBC

dbFailOnError

The execution will stop if there is
an error

dbSeeChanges

The database engine will display an
error if more than one user tries to edit the same record

To create a table, first declare a
variable of type TableDef and then initialize it with the CreateTableDef()
method of the current database object. This method can take as argument the name of
the new table. Here is an example:

To provide access to a DAO.TableDef object, the DAO
namespace is equipped with a property named TableDef. Before creating a
table, you should first declare a variable of type DAO.TableDef. To
initialize the variable, use the
Set operator to assign
the CreateTableDef() method of a database you have previously opened.

After initializing the table, you can add it to the database by
passing it to the Append() method of the TableDefs property of the
database that will receive the table. This would be done as follows:

To get a reference to a table in DAO, access the table by its index or its name from the TableDefs
collection, and use the
Set operator to assign that reference to your previously declared DAO.TableDef variable. Here is an example that gets a reference
to the first table of the current database:

Private Sub cmdContrators_Click()
Dim curDatabase As Database
Dim tblContractors As TableDef
' Get a reference to the current database
Set curDatabase = CurrentDb
' Get a reference to a table named Contractors
Set tblContractors = curDatabase.TableDefs(0)
curDatabase.Close
Set curDatabase = Nothing
End Sub

Deleting a Table in the DAO Library

You delete a table in DAO using the same technique we saw
for the Microsoft Access Object
Library: pass the name of the undesired table to the Detele()
method of the DAO.TableDefs property of the database. Here is an example:

A column of a table is also called a field. To support columns, the DAO library provides
a class named DAO.Field or Field. To let you access the collection of fields of a table in
DAO, the TableDef object is equipped with a property named
Fields, which, as its name indicates, is a collection. Each member of that
collection is a DAO.Field object.

Accessing a Field

The number of columns
of a table is given by a property named Count from the Fields
collection.
To identify each column of a table, the Fields collection is
equipped with a property named Item. This type of property is also
referred to as indexed because it takes an argument that identifies the
particular member you want to access in the collection.

To access a column, you can pass its name or its index to
the Item() indexed property. If you know the name of the column, you can pass it
as a string. Here is an example:

Fields.Item("[Last Name]")

Item is the default property of a Fields collection.
Therefore, you can omit Item(). Based on this, you can also write:

Fields("[Last Name]")

If you don't know the name of a column or you prefer to
access it by its index, you can pass that index to the Item property. Remember
that the index starts at 0, followed by 1, and so on. Based on this, to access
the third column of a table, you would use either of these two:

Fields.Item(2)
Fields(2)

Creating a Column in DAO

Before creating a column in the DAO library,
declare a variable of
type DAO.Field. Here are examples:

The first argument is the name of the column you want to create. Here is an example:

Private Sub cmdCreateTable_Click()
Dim dbExercise As Database
Dim tblEmployees As TableDef
Dim fldEmployeeNumber As Field
' Specify the database to use
Set dbExercise = DBEngine.OpenDatabase("C:\My Documents\Exercise.accdb")
' Create a new TableDef object
Set tblEmployees = dbExercise.CreateTableDef("Employees")
Set fldEmployeeNumber = tblEmployees.CreateField("EmployeeNumber")
End Sub

All three arguments are optional. If you call this method
without the first and the second arguments, at some point, before actually
finalizing the creation of the column, you must provide their values.

After creating the column, you can add it to the table. To
let you add the column to the table, the Fields collection
of the DAO.TableDef class is equipped with a method named Append.
Its syntax is:

To add a new column to an existing table, when using DAO, declare
a variable of type DAO.Field. Then call the CreateField() method of the
table and pass the name of the column, call the Append() method
of the Fields collection of the table and pass it the DAO.TableDef
object.
Here is an example:

Private Sub cmdAddColumn_Click()
Dim colFullName As DAO.Field
Dim curDatabase As DAO.Database
Dim tblStudents As DAO.TableDef
' Get a reference to the current database
Set curDatabase = CurrentDb
' Get a reference to a table named Customers
Set tblStudents = curDatabase.TableDefs("Students")
Set colFullName = tblCustomers.CreateField("FullName", DataType)
tblCustomers.Fields.Append colFullName
curDatabase.Close
Set curDatabase = Nothing
End Sub

Deleting a Column

If you are programmatically maintaining a column,
because a column must belong to a table, before performing any operation
on it, you must first obtain a reference to the table. To programmatically delete a column, call the Delete()
method of the DAO.TableDef variable and pass it the name of the column.
The syntax of this method is:

TableDef.Fields.Delete ColumnName

In this formula, replace ColumnName with the
name of the column you want to delete. Here is an example:

Private Sub cmdModifyPersons_Click()
Dim tblPersons As DAO.TableDef
Dim curDatabase As DAO.Database
' Get a reference to the current database
Set curDatabase = CurrentDb
' Get a reference to a table named Customers
Set tblPersons = curDatabase.TableDefs("Persons")
tblPersons.Fields.Delete "DateHired"
curDatabase.Close
Set curDatabase = Nothing
End Sub

The Data Type of a Field

Introduction

We already know how to start a table by calling the CreateField() method that takes three arguments. Here is an example:

If you are creating a field that will use long text, specify its data type as dbMemo or DB_MEMO.
Here is an example:

Private Sub cmdTableCreation_Click()
Dim colFullName As DAO.Field
Dim curDatabase As DAO.Database
Dim tblStudents As DAO.TableDef
Dim colAnnualReview As DAO.Field
' Get a reference to the current database
Set curDatabase = CurrentDb
' Create a new table named Students
Set tblStudents = curDatabase.CreateTableDef("Students")
Set colFullName = tblStudents.CreateField("FullName", dbText, 60)
tblStudents.Fields.Append colFullName
Set colAnnualReview = tblStudents.CreateField("AnnualReview", dbMemo)
tblStudents.Fields.Append colAnnualReview
' Add the Students table to the current database
curDatabase.TableDefs.Append tblStudents
DoCmd.SelectObject acTable, "Students", True
curDatabase.Close
Set curDatabase = Nothing
Application.RefreshDatabaseWindow
End Sub

Integral Fields

Introduction

Like the Microsoft Access Object Library, the DAO supports
creating integer-based fields on a table. When creating a field that would be
used for small numbers, you can set its data type to either DB_BYTE
or dbByte. If you are creating a column for regular
numbers, set its data type to either DB_INTEGER or dbInteger. If
the column will use small to large numbers, set its data type
to either DB_LONG or dbLong.

You can create an integer-based field whose values automatically increase
with each new record. To support this, use the Attributes property of the
DAO.Field class and apply the dbAutoIncrField constant to it. Here is an example:

If you are creating the table in DAO, set the column's data type as dbBinary or DB_BINARY.

Indexes

Introduction

An index is a field or a list of fields from a table so the records can
be quickly located when necessary. To suppport indexes, the DAO provides a class
named Index. Use it to declare a variable for an index of a table. The Index class
is equipped with properties and methods that allow it to make issues related and
not directly related to indexes.

Creating an Index

To let you create and apply an index on a table, the TableDef class is equipped with a method named CreateIndex.
Its syntax:

Public Function CreateIndex(ByVal Optional Name As String) As Index

This method takes one optional argument. Here is an example
of calling this method without the argument:

You can call this
method as many times as you need to create different fields for the index. The
fields of an index are stored in a collection named Fields that is represented in the Index class as a property of the same name.

After creating a field for an index, to let you add it to the index, the Fields
property of the Index class is equipped with a method named Append.

You can create as many indexes as you want. To support indexes, the TableDef class has a property named Indexes, which is a collection.
After creating an index variable, to let you add it to the table, the TableDef.Indexes collection is equipped with the Append() method.
Here is an example of calling it:

An index must have a name. To support the name of an index,
the Index class is equipped with a property named Name.

The TableDef.CreateIndex() method takes an optional
argument that is the name of the index to create. If
you omit that argument, you must assign a name to the variable before finalizing the creation of the index.
Otherwise, you can specify the name of the index as the argument to this method.
Here is an example:

An index is a field or a group of fields on which data
searches can be performed. When creating an index, you must provide at least one
field of the table. When you call the Index.CreateField() method, its
optional argument is the name of a field from the table. Here is an example of
specifying it:

To let you make sure each record of a table is unique, the Index class is equipped with a Boolean property named Unique.

Requiring a Value for a Field

When a user is creating a record, to let you make sure that a value is always provided for a field, when creating its index, the Index class is equipped with a Boolean property named Required.

Using or Ignoring Null Values in an Index

Indexing consists of creating a list of the values
stored in a table. When this is done, some fields may be empty. When an index is
being created, you can ask the database engine to consider or ignoring empty
fields in a column. To assist you with this, the Index class is equipped with a Boolean property named IgnoreNulls.

Data Relationships

Introduction

The DAO (and the MAOL for that matter) support the ability
to create relationships among the tables of a database. DAO and MAOL directly
support primary keys and relationships.

A Primary Key

A primary key is a field or a group of fields that makes
sure that each record is unique in a table. In the DAO and MAOL, a primary key
is a characteristic of an index. To support primary keys, the Index class
is equipped with a Boolean property named Primary. When creating an
index, to indicate that its field is the primary key, set its Primary property to True.
The table that has the primary is also referred to as the primary table or the
parent table.

To combine the values, you can use the Or operator.

Practical Learning: Creating Tables

On the form, right-click the Locations button and click Build Event...

A foreign key is a field that related the records of a
parent table to the records of another table. The table that has the foreign key
is also referred to as the foreign table or the child table.

Data Relationships

A data relationship is the flow of records from one table to
another. This means that you must establish a relation between two tables. To
support this, the DAO library provides a class named Relation. Use it to
declare a variable to create and manage relationships.

To let you create a relationship, the DAO.Database class is equipped with a method named CreateRelation. Its syntax
is:

All arguments are optional. The first argument is the name
of the relationship. The name can be anything you want. The second argument is
the name of the table that has the primary key column. The third argument is the
name of the table that has the foreign key column.

Referential Integrity

Referential integrity specifies what should happen when a record of a child table is changed or deleted. The last argument of the DAO.Database.CreateRelation()
method is in charge of referential integrity. It is a member of the RelationAttributeEnum enumeration. You can use one value or a combination of values. The members and their values are:

RelationAttributeEnum Member

Value

Description

dbRelationUnique

1

This member is used for a One-To-One relationship

dbRelationDontEnforce

2

Referential integrity will no be applied

dbRelationInherited

4

A relationship will be created

dbRelationUpdateCascade

256

When a record is changed in the child
table, the corresponding records in the parent table is updated

dbRelationDeleteCascade

4096

When a record is deleted in the child
table, the corresponding records in the parent table is also deleted