Lesson 24: The Microsoft Data Access Objects Library (DAO)

	The Microsoft Data Access Objects Library (DAO)

Microsoft Data Access Objects (DAO)

Introduction to DAO

Microsoft Data Access Objects is a library published by Microsoft. The library can be used to create and manage databases. When you start Microsoft Acess, DAO is available by default and ready to be used; that is, you don't have to "load" or "add" it to your database.

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 (in some programming languages such as C++, C#, and Visual Basic, etc, we consider it 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

As you will see throughout here, 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.

DAO and Databases

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

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 DAO.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:

Private Sub cmdCreate_Click()
    Dim db As DAO.Database
    
    db.Close
    
    Set db = Nothing
End Sub

Creating a Database in DAO

DAO is one of the most ancient and reliable libraries of Microsoft Access and 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:

DBEngine.CreateDatabase(ByVal Name As String, _
	ByVal locale As String, _
	ByVal 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:

Private Sub cmdCreate_Click()
    Dim db As DAO.Database

    Set db = CreateDatabase("Exercise.accdb", dbLangGeneral)
    
    db.Close
    Set db = Nothing
End Sub

Opening a Database With DAO

To use a database, of course you must first open it. To support this operation, the DBEngine class of the DAO library provides the OpenDatabase() method whose syntax is:

Public Function DBEngine.OpenDatabase(ByVal Name As String, _
	              ByVal Options As Boolean, _
	      ByVal ReadOnly As Boolean, _
	      ByVal Connect As String) As Database

The arguments of this method are exactly the same as those of the Workspace class of the Microsoft Access Object library. Here is an example of calling the method:

Private Sub cmdOpenDatabase_Click()
    
    OpenDatabase("Example.accdb")
    
End Sub

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:

Private Sub cmdCurrentDatabase_Click()
    Dim curDatabase As DAO.Database
    
    Set curDatabase = CurrentDb

    . . . Use the curDatabase variable however you want
	
    curDatabase.Close
    Set curDatabase = Nothing
End Sub

Introduction to the Tables of a Dadabase

A Table Definition in a DAO Database

In DAO, a table is an object of type DAO.TableDef.

The Tables of a DAO Database

The tables of a DAO database are stored in a collection called TableDefs. To give you access to this collection, the DAO namespace contains a collection named TableDefs that is of type DAO.TableDefs.

Creating a Table in DAO

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 table, use the Set operator to assign the CreateTableDef() method of a database you have previously opened. Here is an example:

Private Sub cmdCreateTable_Click()
   Dim dbExercise As DAO.Database
   Dim tblEmployees As DAO.TableDef

   ' Open the database
   Set dbExercise = DBEngine.OpenDatabase("C:\My Documents\Exercise.accdb")

   ' Create a new TableDef object.
   Set tblEmployees = dbExercise.CreateTableDef("Employees")

   . . .

End Sub

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:

Private Sub cmdCreateTable_Click()
    Dim dbExercise As DAO.Database
   Dim tblEmployees As DAO.TableDef

   ' Open the database
   Set dbExercise = DBEngine.OpenDatabase("C:\My Documents\Exercise.accdb")

   ' Create a new TableDef object.
   Set tblEmployees = dbExercise.CreateTableDef("Employees")

   . . .

   ' Add the new table to the database.
   dbExercise.TableDefs.Append tblEmployees

   dbExercise.Close
   Set dbExercise = Nothing
End Sub

A Reference to a Table

To get a reference to a table in DAO, access the table by its index or its name from the TableDefs property, 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 DAO.Database
    Dim tblContractors As DAO.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:

Private Sub cmdDeleteTable_Click()
    Dim curDatabase As DAO.Database

    Set curDatabase = CurrentDb

    curDatabase.TableDefs.Delete "Books"

    curDatabase.Close
    Set curDatabase = Nothing
End Sub

Introduction to the Columns of a Table

Introduction to Fields

A column of a table is also called a field. To support columns, the DAO library provides the DAO.Field class10:00 AM 12/21/2011. To 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.

Characteristics of 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 that 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 it. Based on this, we 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:

Private Sub cmdCreateTable_Click()   
    Dim fldEmployeeNumber As DAO.Field
    Dim fldFirstName As DAO.Field
    Dim fldLastName As DAO.Field
End Sub

Also, first initialize the table that will hold the field(s). Here is an example:

Private Sub cmdCreateTable_Click()
   Dim dbExercise As DAO.Database
   Dim tblEmployees As DAO.TableDef

   ' Open the database
   Set dbExercise = DBEngine.OpenDatabase("C:\My Documents\Exercise.accdb")

   ' Create a new TableDef object.
   Set tblEmployees = dbExercise.CreateTableDef("Employees")

   . . .

End Sub

After initializing the table, you can add the desired columns to it. To create a column, you can call the CreateField() method of the DAO.TableDef variable and assign it to the column variable. The formula to follow is:

Set fldEmployeeNumber = tblEmployees.CreateField(ByVal ColumnName As String, _
	       	 ByVal DataType As FieldType, _
	       	 ByVal FieldSize As Integer)

The arguments of this method follow exactly the same descriptions we reviewed for the Microsoft Access Object Library.

After creating the column, you can add it to the table. To do this, you can pass it to the Append() method of the Fields collection of the DAO.TableDef class. This would be done as follows:

Private Sub cmdCreateTable_Click()
    Dim dbExercise As DAO.Database
    Dim tblEmployees As DAO.TableDef
    Dim fldEmployeeNumber As DAO.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(ColumnName, DataType, FieldSize)
    tblEmployees.Fields.Append fldEmployeeNumber
    

    ' Add the new table to the database.
    dbExercise.TableDefs.Append tblEmployees

    dbExercise.Close
    Set dbExercise = Nothing
    
    Application.RefreshDatabaseWindow
End Sub

To specify the name of the new column, when calling the DAO.Database.CreateTableDef() method, pass the first argument as a string. Here is an example:

Private Sub cmdCreateTable_Click()
    Dim dbExercise As DAO.Database
    Dim tblEmployees As DAO.TableDef
    Dim fldEmployeeNumber As DAO.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", DataType, FieldSize)
    tblEmployees.Fields.Append fldEmployeeNumber
    

    ' Add the new table to the database.
    dbExercise.TableDefs.Append tblEmployees

    dbExercise.Close
    Set dbExercise = Nothing

    Application.RefreshDatabaseWindow
End Sub

Columns Maintenance

Adding a New Column

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 curDatabase As Object
    Dim tblStudents As Object
    Dim colFullName As Object

    ' 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 curDatabase As DAO.Database
    Dim tblPersons As DAO.TableDef

    ' 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:

Set fldEmployeeNumber = tblEmployees.CreateField("EmployeeNumber", DataType, FieldSize)

The second argument of the CreateField() method specifies the type of values that would be entered into the column.

Text-Based Fields

To support text-based columns, the DAO library provides the DB_TEXT and the dbText data types. Here is an example:

Private Sub cmdCreateTable_Click()
    Dim dbExercise As DAO.Database
    Dim tblEmployees As DAO.TableDef
    Dim fldEmployeeNumber As DAO.Field
    
    ' Specify the database to use
    Set dbExercise = DBEngine.OpenDatabase("Exercise.accdb")

    ' Create a new TableDef object.
    Set tblEmployees = dbExercise.CreateTableDef("Employees")

    Set fldEmployeeNumber = tblEmployees.CreateField("EmployeeNumber", DB_TEXT)
    tblEmployees.Fields.Append fldEmployeeNumber

   ' Add the new table to the database.
   dbExercise.TableDefs.Append tblEmployees

   dbExercise.Close
   Set dbExercise = Nothing

   Application.RefreshDatabaseWindow
End Sub

Integral Fields

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 field that would use general natural numbers, you can set its data type to either DB_INTEGER or dbInteger. If you are creating a field that can contain very large numbers, set its data type to either DB_LONG or dbLong.

Here is an example of a table that is being created with three fields that use integral types:

Private Sub cmdCreateTable_Click()
    Dim dbCurrent As DAO.Database
    Dim tblCustomers As DAO.TableDef
    Dim fldCustomerName As DAO.Field
    Dim fldCategory As DAO.Field
    Dim fldContractStatus As DAO.Field
    Dim fldContractLength As DAO.Field
    
    ' Specify the database to use
    Set dbCurrent = CurrentDb

    ' Create a new TableDef object.
    Set tblCustomers = dbCurrent.CreateTableDef("Customers")

    Set fldCustomerName = tblCustomers.CreateField("CustomerName", DB_TEXT)
    tblCustomers.Fields.Append fldCustomerName
    
    Set fldCategory = tblCustomers.CreateField("Category", DB_INTEGER)
    tblCustomers.Fields.Append fldCategory
    
    Set fldContractStatus = tblCustomers.CreateField("Contract Status", DB_BYTE)
    tblCustomers.Fields.Append fldContractStatus
    
    Set fldContractLength = tblCustomers.CreateField("Contract Length", DB_LONG)
    tblCustomers.Fields.Append fldContractLength

   ' Add the new table to the database.
   dbCurrent.TableDefs.Append tblCustomers

   dbCurrent.Close
   Set dbCurrent = Nothing

   Application.RefreshDatabaseWindow
End Sub

Boolean Fields

If you are creating a column that would hold Boolean values, specify its data type as DB_BOOLEAN. Here is an example:

Private Sub cmdTableCreation_Click()
    Dim curDatabase As DAO.Database
    Dim tblStudents As DAO.TableDef
    Dim colFullName As DAO.Field
    Dim colWasTransfered As DAO.Field

    Set curDatabase = CurrentDb
    
    Set tblStudents = curDatabase.CreateTableDef("Students")
    
    Set colFullName = tblStudents.CreateField("FullName", dbText)
    tblStudents.Fields.Append colFullName
    
    Set colWasTransfered = tblStudents.CreateField("WasTransfered", DB_BOOLEAN)
    tblStudents.Fields.Append colWasTransfered
    
    curDatabase.TableDefs.Append tblStudents

    curDatabase.Close
    Set curDatabase = Nothing

    Application.RefreshDatabaseWindow
End Sub

Single-Precision Fields

To create a field that holds single-precision real numbers, specify the column's data type as either dbSingle or DB_SINGLE. Here is an example:

Private Sub cmdCreateTable_Click()
    Dim curDatabase As DAO.Database
    Dim tblEmployees As DAO.TableDef
    Dim fldEmployeeNumber As DAO.Field
    Dim fldFullName As DAO.Field
    Dim fldWeeklyHours As DAO.Field
    
    ' Specify the database to use
    Set curDatabase = CurrentDb

    ' Create a new TableDef object.
    Set tblEmployees = dbExercise.CreateTableDef("Employees")

    Set fldEmployeeNumber = tblEmployees.CreateField("EmployeeNumber", dbLong)
    tblEmployees.Fields.Append fldEmployeeNumber
    
    Set fldFullName = tblEmployees.CreateField("FullName", dbText)
    tblEmployees.Fields.Append fldFullName
    
    Set fldWeeklyHours = tblEmployees.CreateField("WeeklyHours", dbSingle)
    tblEmployees.Fields.Append fldWeeklyHours

   ' Add the new table to the database.
   curDatabase.TableDefs.Append tblEmployees
   
   curDatabase.Close
   Set curDatabase = Nothing

   Application.RefreshDatabaseWindow
End Sub

Currency Fields

To create a field that holds currency values in DAO, set its data type to dbCurrency or DB_CURRENCY.

Date/Time Fields

If you are creating a column to hold date or time values, set its data type as either DB_DATE or dbDate. Here is an example:

Private Sub cmdTable_Click()
    Dim curDatabase As DAO.Database
    Dim tblContractors As DAO.TableDef
    Dim fldName As DAO.Field
    Dim fldStartDate As DAO.Field
    Dim fldEndDate As DAO.Field

    Set curDatabase = CurrentDb
    Set tblContractors = curDatabase.CreateTableDef("Contractors")
    
    Set fldName = tblContractors.CreateField("Contractor Name", DB_TEXT, 100)
    tblContractors.Fields.Append fldName
    Set fldStartDate = tblContractors.CreateField("Start Date", dbDate)
    tblContractors.Fields.Append fldStartDate
    Set fldEndDate = tblContractors.CreateField("End Date", dbDate)
    tblContractors.Fields.Append fldEndDate
    
    curDatabase.TableDefs.Append tblContractors
    
    MsgBox "A table named Contractors has been created."
   
    curDatabase.Close
    Set curDatabase = Nothing

    Application.RefreshDatabaseWindow
End Sub

Binary Fields in DAO

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

Creating a Memo Field in the DAO

If you are creating the table using DAO and want to use long text values on a column, specify its data type as dbMemo. Here is an example:

Private Sub cmdTableCreation_Click()
   Dim curDatabase As DAO.Database
   Dim tblStudents As DAO.TableDef
   Dim colFullName As DAO.Field
   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)
   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

Introduction to Record Sets in DAO

Overview

As you may know already, a table or a query contains a series of records. To perform some operations on the series, the records may have to be treated as a group. Such a group or series of records is called a record set.

The Type of Recordset Objects

As you may know and as we will review later, one of the most common operations you will perform on a record set consist of navigating among the records. When doing this, the result you get depends on the type of record set. Based on their behaviors, there are two primary categories of record sets:

A table-type is a record set whose values come straight from a table
A dynaset is a record set created from a query. Actually, the idea is that, after creating the record set, if the (any) value in the set changes, the record set is "dynamically" updated, which means the result you see in the record is the actual data
A snapshot is a list of records based on the way the records were when the set was created or generated. If changes were made on the records (such as somebody changing a value of a field or somebody deleting a value in a field) after you created the record set, the snapshot record set would not show the changes. If you want to see the changes, you must refresh the record set, which is equivalent to rebuilding the record set

Creating a Recordset

To support a group of records as a set, the DAO library provides the DAO.Recordset class. Therefore, the primary way to create a record set is to declare a variable of type DAO.Recordset. How you get the set of records depends on the technique you use.

Once again, remember that the MAOL and DAO do things the same way. This means that the way you create a DAO record set is the same we saw with the MAOL:

To create a record set that uses the records of a table, declare a variable of type DAO.Recordset and pass the name of the table to it. To support this technique, the DAO.Database class is equipped with a method named OpenRecordset. This is the same type of method we saw with the MAOL and its syntax is:
```
expression.OpenRecordset(Name, Type, Options, LockEdit)
```
Only the first argument is required. Therefore, to create a record set from a table, pass the name of that table to the DAO.Database.OpenRecordset() method and use the Set operator to get the returned value. Here is an example:
```
Private Sub cmdGetRecordSet_Click()
    Dim dbExercise As DAO.Database
    Dim rsEmployees As DAO.Recordset

    Set dbExercise = CurrentDb
    Set rsEmployees = dbExercise.OpenRecordset("Employees")
    
    dbExercise.Close
    Set dbExercise = Nothing
End Sub
```

In DAO (and MAOL), the TableDef class is equipped with a method named OpenRecordset that holds its record set. To retrieve the record set of the table, first get a reference to that table. Then use the Set operator to assign a call toTableDef.OpenRecordset(). Here is an exampleP:

Private Sub cmdCreateRecordSet_Click()
    Dim dbExercise As DAO.Database
    Dim rsCustomers As DAO.Recordset
    Dim tblCustomers As DAO.TableDef

    ' Get a reference to the current database
    Set dbExercise = CurrentDb
    ' Get a reference to a table named Customers
    Set tblCustomers = dbExercise.TableDefs("Customers")
    ' Create a Recordset object from the specified table
    Set rsCustomers = tblCustomers.OpenRecordset

    dbExercise.Close
    Set dbExercise = Nothing
End Sub

Some controls like the combo box, the list box, the form, or the report hold a list of records, which means they have their own record set. Such controls have a property named Recordset. To get a reference to that record set, declare a DAO.Recordset variable and assign the control's Recordset value to it. Here is an example:
```
Private Sub cmdGetRecordSet_Click()
    Dim dbExercise As DAO.Database
    Dim rsEmployees As DAO.Recordset

    Set dbExercise = CurrentDb

    Set rsEmployees = Me.Recordset

    dbExercise.Close
    Set dbExercise = Nothing
End Sub
```
A form or report has a property named RecordSource that feeds it the values from a table. To get the set of records that the RecordSource property holds, access the RecordsetClone property of the form or report

Closing a Record Set

After using a DAO.Recordset object, you should (strongly) close it. To close a record set, you can call its Close() method. Here is an example:

Private Sub cmdCreateRecordSet_Click()
    Dim dbExercise As DAO.Database
    Dim rsCustomers As DAO.Recordset
    Dim tblCustomers As DAO.TableDef

    Set dbExercise = CurrentDb
    Set tblCustomers = dbExercise.TableDefs("Customers")
    Set rsCustomers = tblCustomers.OpenRecordset
    
    rsCustomers.Close
    dbExercise.Close
    Set dbExercise = Nothing
End Sub

After closing the record set, you should release the resources it was using and make them available to other applications that would need them. To do this, assign the Nothing value to the DAO.Recordset object. Here is an example:

Private Sub cmdCreateRecordSet_Click()
    Dim dbExercise As DAO.Database
    Dim rsCustomers As DAO.Recordset
    Dim tblCustomers As DAO.TableDef

    Set dbExercise = CurrentDb
    Set tblCustomers = dbExercise.TableDefs("Customers")
    Set rsCustomers = tblCustomers.OpenRecordset
    
    rsCustomers.Close
    Set rsCustomers = Nothing
    
    dbExercise.Close
    Set dbExercise = Nothing
End Sub

Introduction to Records

Introduction to Data Entry

Before performing actions on a table, you first need to get a reference to that table, which is equivalent to opening a record set. We already saw different ways to do that, such as calling the OpenRecordset() method of the Database class. Remember that there are other ways to create or open a record set. Once you have the record set, you can perform data entry on it. This is done using the same steps for the MAOL. To start, call the AddNew() method of the Recordset class. Here is an example of calling it:

Private Sub cmdCreateEmployee_Click()
    Dim dbExercise As DAO.Database
    Dim rsEmployees As DAO.Recordset
    
    Set dbExercise = CurrentDb
    Set rsEmployees = dbExercise.OpenRecordset("Employees")
    
    rsEmployees.AddNew
    
    dbExercise.Close
    Set dbExercise = Nothing
End Sub

To let you specify the value of a field, first access that field using either its index or its name applied to the Recordset variable. Use its Value property to assign the desired value. You can do this for the various fields whose values you want to create. To finalize the action, call the Update method of the Recordset class.

Data Entry By Type

The way you provide a value for a field depends on its type. For example, the value of a text-based column must be provided in double-quotes. Here is an example:

Private Sub cmdCreateEmployee_Click()
    Dim dbExercise As DAO.Database
    Dim rsEmployees As DAO.Recordset
    
    Set dbExercise = CurrentDb
    Set rsEmployees = dbExercise.OpenRecordset("Employees")
    
    rsEmployees.AddNew
    rsEmployees("EmployeeName").Value = "John Schwartz"
    rsEmployees.Update
    
    dbExercise.Close
    Set dbExercise = Nothing
End Sub

To specify value or a Boolean field, assign True or False to its Value property. Here is an example:

Private Sub cmdCreateEmployee_Click()
    Dim dbExercise As DAO.Database
    Dim rsEmployees As DAO.Recordset
    
    Set dbExercise = CurrentDb
    Set rsEmployees = dbExercise.OpenRecordset("Employees")
    
    rsEmployees.AddNew
    rsEmployees("EmployeeName").Value = "John Schwartz"
    rsEmployees("IsFullTime").Value = True
    rsEmployees.Update
    
    dbExercise.Close
    Set dbExercise = Nothing
End Sub

To specify the value of a number-based field, simply assign the desired value to its Value property. Here is an example:

Private Sub cmdCreateEmployee_Click()
    Dim dbExercise As DAO.Database
    Dim rsEmployees As DAO.Recordset
    
    Set dbExercise = CurrentDb
    Set rsEmployees = dbExercise.OpenRecordset("Employees")
    
    rsEmployees.AddNew
    rsEmployees("EmployeeNumber").Value = 92073
    rsEmployees("EmployeeName").Value = "John Schwartz"
    rsEmployees("IsFullTime").Value = True
    rsEmployees.Update

    dbExercise.Close
    Set dbExercise = Nothing
End Sub

To perform data entry of date or time fields in DAO, create the date and/or time value between # and #, then assign it to the Value property of the DAO.Field object. Here is an example:

Private Sub cmdCreateEmployee_Click()
    Dim dbExercise As DAO.Database
    Dim rsEmployees As DAO.Recordset
    
    Set dbExercise = CurrentDb
    Set rsEmployees = dbExercise.OpenRecordset("Employees")
    
    rsEmployees.AddNew
    rsEmployees("EmployeeNumber").Value = 92073
    rsEmployees("DateHired").Value = #4/12/2010#
    rsEmployees("EmployeeName").Value = "John Schwartz"
    rsEmployees("IsFullTime").Value = True
    rsEmployees.Update

    dbExercise.Close
    Set dbExercise = Nothing
End Sub

You can also provide the value in double-quotes. Here is an example:

Private Sub cmdCreateEmployee_Click()
    Dim dbExercise As DAO.Database
    Dim rsEmployees As DAO.Recordset
    
    Set dbExercise = CurrentDb
    Set rsEmployees = dbExercise.OpenRecordset("Employees")
    
    rsEmployees.AddNew
    rsEmployees("EmployeeNumber").Value = 94055
    rsEmployees("DateHired").Value = "10/05/2008"
    rsEmployees("EmployeeName").Value = "Alain Binam"
    rsEmployees("IsFullTime").Value = False
    rsEmployees.Update

    dbExercise.Close
    Set dbExercise = Nothing
End Sub

Assistance With Data Entry

The Size of a Field

The DAO library uses the same approach as the Microsoft Access Object Library to specify the number of characters of a text-based field. Therefore, when creating a text-based column, to specify the maximum number of characters it can have, pass a third argument to the DAO.TableDef.CreateField() method as a number. Here is an example:

Private Sub cmdCreateTable_Click()
    Dim dbExercise As DAO.Database
    Dim tblEmployees As DAO.TableDef
    Dim fldEmploymentStatus As DAO.Field
    
    ' Specify the database to use
    Set dbExercise = CurrentDb

    ' Create a new TableDef object.
    Set tblEmployees = dbExercise.CreateTableDef("Employees")

    Set fldEmploymentStatus = _
    		tblEmployees.CreateField("EmploymentStatus", DB_TEXT, 80)
    tblEmployees.Fields.Append fldEmploymentStatus

   ' Add the new table to the database.
   dbExercise.TableDefs.Append tblEmployees
   dbExercise.Close

   Application.RefreshDatabaseWindow
End Sub

Private Sub cmdCreateEmployeesTable_Click()
    Dim dbFunDS As DAO.Database
    Dim colEmployee As DAO.Field
    Dim tblEmployees As DAO.TableDef
    
    Set dbFunDS = CurrentDb
    Set tblEmployees = dbFunDS.CreateTableDef("Employees")
    
    Set colEmployee = tblEmployees.CreateField("EmployeeNumber", dbLong)
    tblEmployees.Fields.Append colEmployee
    Set colEmployee = tblEmployees.CreateField("DateHired", dbDate)
    tblEmployees.Fields.Append colEmployee
    Set colEmployee = tblEmployees.CreateField("FirstName", dbText, 25)
    tblEmployees.Fields.Append colEmployee
    Set colEmployee = tblEmployees.CreateField("LastName", dbText, 25)
    tblEmployees.Fields.Append colEmployee
    Set colEmployee = tblEmployees.CreateField("FullName", dbText, 50)
    tblEmployees.Fields.Append colEmployee
    Set colEmployee = tblEmployees.CreateField("Title", dbText, 50)
    tblEmployees.Fields.Append colEmployee
    Set colEmployee = tblEmployees.CreateField("HourlySalary", dbDouble)
    tblEmployees.Fields.Append colEmployee
    Set colEmployee = tblEmployees.CreateField("Notes", dbMemo)
    tblEmployees.Fields.Append colEmployee
    
    dbFunDS.TableDefs.Append tblEmployees
    
    dbFunDS.Close
    Set dbFunDS = Nothing

    Application.RefreshDatabaseWindow
End Sub

Automatically Incrementing the Value of a Field

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:

Private Sub cmdTable_Click()
   Dim curDatabase As DAO.Database
   Dim tblEmployees As DAO.TableDef
   Dim colEmployeeID As Field
   Dim colFullName As Field

   Set curDatabase = CurrentDb
   Set tblEmployees = curDatabase.CreateTableDef("Employees")
    
   Set colEmployeeID = tblEmployees.CreateField("EmployeeID", dbLong)
   colEmployeeID.Attributes = dbAutoIncrField
   tblEmployees.Fields.Append colEmployeeID
   Set colFullName = tblEmployees.CreateField("FullName", dbText)
   tblEmployees.Fields.Append colFullName
    
   curDatabase.TableDefs.Append tblEmployees
   MsgBox "A table named Employees has been created"
   
   curDatabase.Close
   Set curDatabase = Nothing

   Application.RefreshDatabaseWindow
End Sub

After creating the field, when performing data entry, remember that you will not provide a value for the auto-incrementing field.

Setting a Default Value for a Field

In the DAO library, the DAO.Field class is equipped with the DefaultValue property. To specify a default value for a new field you are creating, assign that value to it. Here is an example:

Private Sub cmdCreateTable_Click()
    Dim dbExercise As DAO.Database
    Dim tblEmployees As DAO.TableDef
    Dim fldEmployeeNumber As DAO.Field
    Dim fldEmployeeName As DAO.Field
    Dim fldEmploymentStatus As DAO.Field
    
    ' Specify the database to use
    Set dbExercise = CurrentDb

    ' Create a new TableDef object.
    Set tblEmployees = dbExercise.CreateTableDef("Employees")

    Set fldEmployeeNumber = tblEmployees.CreateField("EmployeeNumber", DB_TEXT, 10)
    fldEmployeeNumber.Required = True
    tblEmployees.Fields.Append fldEmployeeNumber

    Set fldEmployeeName = tblEmployees.CreateField("EmployeeName", DB_TEXT, 100)
    tblEmployees.Fields.Append fldEmployeeName

    Set fldEmploymentStatus = _
        tblEmployees.CreateField("EmploymentStatus", DB_TEXT, 20)
    fldEmploymentStatus.DefaultValue = "Full Time"
    tblEmployees.Fields.Append fldEmploymentStatus

   ' Add the new table to the database.
   dbExercise.TableDefs.Append tblEmployees
   dbExercise.Close
   Set dbExercise = Nothing

   Application.RefreshDatabaseWindow
End Sub

Once a field has a default value, it can be skipped during data entry, in which case the default value would be used for its value.

Field Nullity

In the DAO library, the Field class provides the Required property. You can use this property to allow the user to skip a field during data entry. You can also use this property to make sure a value is entered for the field before the record is considered complete. The default value of this property is False. If you set it to True, a value must always be entered for the field. Here is an example:

Private Sub cmdCreateTable_Click()
    Dim dbExercise As DAO.Database
    Dim tblEmployees As DAO.TableDef
    Dim fldEmployeeNumber As DAO.Field
    Dim fldEmployeeName As DAO.Field
    Dim fldEmailAddress As DAO.Field
    
    ' Specify the database to use
    Set dbExercise = CurrentDb

    ' Create a new TableDef object.
    Set tblEmployees = dbExercise.CreateTableDef("Employees")

    Set fldEmployeeNumber = tblEmployees.CreateField("EmployeeNumber", DB_TEXT)
    fldEmployeeNumber.Required = True
    tblEmployees.Fields.Append fldEmployeeNumber

    Set fldEmployeeName = tblEmployees.CreateField("EmployeeName", DB_TEXT)
    tblEmployees.Fields.Append fldEmployeeName

    Set fldEmailAddress = tblEmployees.CreateField("EmailAddress", DB_TEXT)
    tblEmployees.Fields.Append fldEmailAddress

   ' Add the new table to the database.
   dbExercise.TableDefs.Append tblEmployees
   dbExercise.Close

   Application.RefreshDatabaseWindow
End Sub

If you try or the user tries skipping a field whose Required property is set to True, the database would produce an error.

The Characteristics of a Record Set

Introduction

We saw that, to create a record set, you could call the OpenRecordset() method of a DAO.Database variable. Its syntax is

expression.OpenRecordset(Name, Type, Options, LockEdit)

A record set follows some rules in the way it is created or the way it is used. The rules concern the name or source of data and the type of record set, etc. So far, we provided the source of data as a name of a table.

The Type of Record Set

A record set must specify the way its records would be accessed and used. This information is provided by the second argument of the OpenRecordset() method. The value of this argument is (must represent) a member of the RecordsetTypeEnum enumeration. Its values follow the description we saw for the MAOL. The values are:

RecordsetTypeEnum Member	Constant Value	Description
dbOpenTable	1	The first argument is provided as the name of a table. This is the table value (if you don't pass the second argument, dbOpenTable is used)
dbOpenDynaset	2	The record set represents one or more tables. New records can be created on the record set. Existing records can be edited or deleted
dbOpenSnapshot	4	The record set represents a series of records at one particular time and does not or cannot reflect changes made after the record set was created
dbOpenForwardOnly	8	When using the record set, the user can move forward but not backward
dbOpenDynamic	16	The record set is automatically updated if there are changes on the set

The Options of a Record Set

Further options are used to control the actions that can be performed on the Recordset object. These options are specified through the third argument of the OpenRecordset() method. The options are members of the RecordsetOptionEnum enumeration and they can be provided as an OR combination. The available values are:

RecordsetOptionEnum Member	Constant Value	Description
dbDenyWrite	1	The user cannot change a record in the record set
dbDenyRead	2	While a person is using the record set, other people cannot access the records in that set
dbReadOnly	4	The record set is open to only see the values in the records
dbAppendOnly	8	The record set must be opened as a dynaset. Then, a user can add a new record to the record set but cannot see the existing records
dbInconsistent	16	The record set must be opened as a dynaset or a snapshot. The record(s) can be updated and an update in one record can affect other records of the same dynaset
dbConsistent	32	The record set must be opened as a dynaset or a snapshot. The record(s) can be updated but only if an update will not change other records of the same dynaset. Although you can combine some values of the the RecordsetOptionEnum enumeration, you can use either dbInconsistent or dbConsistent but not both
dbSQLPassThrough	64	The record set must be opened as a snapshot using a SQL statement
dbFailOnError	128	If an error occurs, the record set is dismissed
dbForwardOnly	256	The record set must be created as a forward-only snapshot
dbSeeChanges	512	The record set must be created as a dynaset. If a problem occurs, the database engine generates a run-time error
dbRunAsync	1024	The record set is created as an asynchronous SQL statement
dbExecDirect	2048	The record set executes withoug first calling the SQLPrepare() function

Remember that the value you specify for this argument depends on the second and may depend on the fourth arguments.

Locking the Record Set

The last argument controls how to deal with simultaneous connections to the same database. The value of this argument is a member of the LockTypeEnum enumeration. The available values are:

LockTypeEnum Member	Constant Value
dbOptimisticValue	1
dbOptimistic	3
dbOptimisticBatch	5

Record Navigation in a Record Set

Besides creating a record set, probably the most common operation you perform on a record set is to navigate among records. The DAO.Recordset class supports the operations throught various method:

The MoveFirst() method is used to navigate to the first record
The MoveLast() method is used to navigate to the last record
The MovePrevious() method is used to navigate to the previous record
The MoveNext() method is used to navigate to the next record

The Move() method is used to navigate to a specific record. You must pass the index of that record to the method. Here is an example:

Private Sub cmdMovePosition_Click()
   Dim dbVideoCollection As DAO.Database
   Dim rstVideos As DAO.Recordset

   Set dbVideoCollection = CurrentDb
   Set rstVideos = dbVideoCollection.OpenRecordset("Videos")

   rstVideos.Move 6
End Sub

When navigating among records, you must avoid moving below the first record or above the last record. To assist you with this, the DAO.Recordset class is equipped with the BOF() and the EOF() methods. The BOF() method is used to check whether navigation is trying to reach below the first record. The EOF() method is used to check whether navigation is getting higher than the last record. Both methods return a Boolean value.

Field Selection

Introduction

So far, when creating a record set, we used the name of a table. This is equivalent to selecting all records of the table. As an alternative, you can specify only some fields and/or some records that would constitute the record set. To do this, when calling the OpenRecordset() method of the DAO.Recordset class, pass the desired SQL statement as the first argument.

To create the SQL statement, use everything you know about SELECT operations:

You can select one field from a table. Here is an example that selects a column from a table named Employees:

Private Sub cmdCreateRecordSet_Click()
    Dim dbExercise As DAO.Database
    Dim rsEmployees As DAO.Recordset
    
    Set dbExercise = CurrentDb
    Set rsEmployees = dbExercise.OpenRecordset("SELECT EmployeeNumber FROM Employees")
    
    dbExercise.Close
    Set dbExercise = Nothing
End Sub

To select more than one column, separate them with commas. The formula to follow would be:
```
SELECT Column1, Column2, Column_n FROM WhatObject;
```

To select all columns from a table, use either * or ALL * in the placeholder of the fields. Here is an example:

Private Sub cmdCreateRecordSet_Click()
    Dim dbExercise As DAO.Database
    Dim rsEmployees As DAO.Recordset
    
    Set dbExercise = CurrentDb
    Set rsEmployees = dbExercise.OpenRecordset("SELECT ALL * FROM Employees")
    
    dbExercise.Close
    Set dbExercise = Nothing
End Sub

To further restrict the records or values, you can add a condition to the SQL statement.

Creating a Query

A query is a technique of creating and saving a SQL statement for later use. To let you programmatically create a query in DAO, the DAO.Database class is equipped with the CreateQueryDef() method. The syntax of this method is:

CreateQueryDef(NewQueryName, SQLStatement)

The first argument is the name you will give to the new query. The second argument is a SQL statement that specifies the contents of the query. Here is an example that uses the Microsoft Access Object Library:

Private Sub cmdCreateQuery_Click()
    Dim dbExercise As DAO.Database
    Dim qryEmployees As DAO.QueryDef
    
    Set dbExercise = CurrentDb
    Set qryEmployees = _
        dbExercise.CreateQueryDef("StaffMembers", _
                                  "SELECT EmployeeNumber, EmployeeeName " & _
                                  "FROM Employees")
    
    dbExercise.Close
    Set dbExercise = Nothing
   
    Application.RefreshDatabaseWindow
End Sub