Home

Introduction to DAO

The Microsoft Data Access Objects (DAO) Library

Introduction

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

  1. Start Microsoft Access
  2. Click Blank Desktop Database
  3. Set the file name as Kolo Bank
  4. Click Create
  5. On the Ribbon, click File and click Options
  6. In the left list of the Access Options dialog box, click Current Database
  7. In the main list, click Overlapping Windows
  8. Click OK on the dialog box
  9. Click OK on the message box
  10. On the Ribbon, click File and click Close
  11. In the list of files, click Kolo Bank1
  12. On the Ribbon, click Create and click Form Design
  13. Double-click the Properties button Properties and, in the Property Sheet, change the following characteristics:
    Caption: Kolo Bank - Central Processing
    Auto Center: Yes
    Record Selectors: No
    Navigation Buttons: No
  14. Click the Detail section of the form and, in the Property Sheet, change the Back Color to: Background Form:
  15. Save the forrn as Central Processing
  16. From the Controls section of the Ribbon, click the Label Label and click the form
  17. Type Tables
  18. From the Controls section of the Ribbon, click the Button Button and click the form
  19. Complete the design of the form as follows (we applied the Bodoni MT Black font with size 20 to all labels and buttons):

    Kolo Bank - Central Processubg

    Control Name Caption
    Label Label   Tables
    Line Line    
    Button Text Box cmdAccountsTypes Accounts Types
    Button Text Box cmdTransactionsTypes Transactions Types
    Button Text Box cmdAccountsStatus Accounts Status
    Button Text Box cmdChargesReasons Charges Reasons
    Button Button cmdCurrenciesTypes Currencies Types
    Button Button cmdLocations Bank Locations
    Button Button cmdEmployees Employees
    Button Button cmdCustomers Customers
    Button Button cmdAccountsHistories Accounts Histories
    Button Button cmdTransactions Transactions
    Button Button cmdClose Close
  20. Close the form
  21. When asked whether you want to save, click Yes
  22. On the Ribbon, click File and click Open
  23. In the list of files, click ESCAPE from Lesson 29
  24. On the Ribbon, click Create and click Form Design
  25. Double-click the Properties button Properties and, in the Property Sheet, change the following characteristics:
    Caption: ESCAPE - Database Objects
    Auto Center: Yes
    Record Selectors: No
    Navigation Buttons: No
  26. Click the Detail section of the form and, in the Property Sheet, change the Back Color to: Background Form:
  27. Save the forrn as Database Objects
  28. Add three buttons as follows:

    ESCAPE - Communications Center

    Control Name Caption
    Button Button cmdCreateCustomersTable Create Customers Accounts Table
    Button Button cmdCreateCustomerBillsTable Create Customers Bills Table
    Button Button cmdClose Close
  29. 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.

Author Note

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:

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

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:

Private Sub cmdCreate_Click()
    Dim db As 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 a method named OpenDatabase. Its syntax is:

Public Function 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 Database
    
    Set curDatabase = CurrentDb

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

Executing a SQL Statement

Executing a DDL Statement

To make it possible to process a SQL statement, the Database class is equipped with a method named Execute. Its syntax is:

Public Sub Execute(ByVal Query As String, ByVal Optional Options As Variant)

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
dbAppendOnly The record set must be opened as a snapshot using a SQL statement
dbRunAsync The SQL statement will be executed asynchronously
dbExecDirect The database will execute the statement directly

Practical Learning: Executing a DDL Statement

  1. On the form, right-click the Create Customers Accounts Table button and click Build Event...
  2. In the Choose Builder dialog box, click Code Builder and click OK
  3. Implement the event as follows:
    Private Sub cmdCreateCustomersTable_Click()
        Dim dbCommunication As Database
        
        Set dbCommunication = CurrentDb
        
        dbCommunication.Execute "CREATE TABLE Customers" & _
                                "(" & _
                                "   CustomerID           COUNTER(1, 1),   " & _
                                "   AccountNumber        Text(12) UNIQUE, " & _
                                "   FirstName            Text(20),        " & _
                                "   LastName             Text(20),        " & _
                                "   Address              String(100),     " & _
                                "   City                 CHAR(40),        " & _
                                "   County               VARCHAR(40),     " & _
                                "   State                char(2),         " & _
                                "   ZIPCode              text(20),        " & _
                                "   CableTVBasicFee      Double,          " & _
                                "   UsesDVRService       Bit,             " & _
                                "   UsesSportsPackage    Logical,         " & _
                                "   InternetBasicFee     Number,          " & _
                                "   ProvidesOwnModem     YesNo,           " & _
                                "   InternetSpeedApplied BYTE " & _
                                ");", RecordsetOptionEnum.dbDenyWrite
                                
        Application.RefreshDatabaseWindow
    End Sub
  4. In the Object combo box, select cmdCreateCustomersBillsTable
  5. Implement the event as follows:
    SPrivate Sub cmdCreateCustomersBillsTable_Click()
        Dim dbCommunication As Database
        
        Set dbCommunication = CurrentDb
        
        dbCommunication.Execute "CREATE TABLE CustomersBills" & _
                                "(" & _
                         "   ReceiptNumber         AUTOINCREMENT(100001, 1), " & _
                                "   AccountNumber         Text(12), " & _
                                "   CableTVBasicFee       DOUBLE,   " & _
                                "   IncludesDVRService    YesNo,    " & _
                                "   DVRServiceFee         double,   " & _
                                "   IncludesSportsPackage Logical,  " & _
                                "   SportsPackageFee      double,   " & _
                                "   FCCFee                Double,   " & _
                                "   CableTVCountyTaxes    double,   " & _
                                "   CableTVStateTaxes     Double,   " & _
                                "   CableTVTotal          double,   " & _
                                "   InternetBasicFee      double,   " & _
                                "   IncludesModemLease    Bit,      " & _
                                "   ModemFee              Number,   " & _
                                "   InternetSpeedApplied  BYTE,     " & _
                                "   InternetCountyTaxes   double,   " & _
                                "   InternetStateTaxes    Double,   " & _
                                "   InternetTotal         double,   " & _
                                "   TotalAmountDue        double " & _
                                ");", RecordsetOptionEnum.dbConsistent
                                 
        Application.RefreshDatabaseWindow
    End Sub
  6. In the Object combo box, select cmdClose
  7. Implement the event as follows:
    Private Sub cmdClose_Click()
        DoCmd.Close
    End Sub
  8. Close Microsoft Visual Basic and return to Microsoft Access
  9. Save and close the form
  10. In the Navigation Pane, right-click New Customer Bill and click Design View
  11. Add two buttons to the Form Footer section as follows:

    ESCAPE - New Customer Bill

    Control Name Caption
    Button Text Box cmdSubmitNewBill Submit New Bill
    Button Button cmdClose Close
  12. Save and close the form
  13. Add two buttons to the Form Footer section as follows:

    ESCAPE - New Customer Account

    Control Name Caption
    Button Text Box cmdCreateCustomerAccount Create Customer Account
    Button Button cmdClose Close
  14. Save the form

Executing a DML Statement

The Query argument can also be a DML statement. This gives you the ability to create records using an INSERT statement.

Practical Learning: Executing a DML Statement

  1. On the form, right-click the Create Customer Account and click Build Event...
  2. In the Choose Builder dialog box, click Code Builder and click OK
  3. Implement the event as follows:
    Private Sub cmdCreateCustomerAccount_Click()
        Dim dbCommunication As Database
        
        Set dbCommunication = CurrentDb
        
        If IsNull(txtAccountNumber) Then
            MsgBox "You must provide an account number for the new customer.", _
                   vbOKCancel Or vbInformation, "Eastern Shore Communications Company"
            Exit Sub
        End If
    
        dbCommunication.Execute ("INSERT INTO Customers(AccountNumber, FirstName, LastName, Address, City, County, State, ZIPCode, CableTVBasicFee, UsesDVRService, UsesSportsPackage, InternetBasicFee, ProvidesOwnModem, InternetSpeedApplied) " & _
                                "VALUES('" & txtAccountNumber & "', '" & txtFirstName & "', '" & txtLastName & "', '" & txtAddress & "', '" & txtCity & "', '" & txtCounty & "', '" & txtState & "', '" & txtZIPCode & "', " & CDbl(Nz(txtCableTVBasicFee)) & ", " & chkUsesDVRService.Value & ", " & chkUsesSportsPackage.Value & ", " & CDbl(Nz(txtInternetBasicFee)) & ", " & chkProvidesOwnModem.Value & ", " & fraInternetSpeedApplied.Value & ");")
        
        MsgBox "An account for the new customer has been created.", _
               vbOKCancel Or vbInformation, "Eastern Shore Communications Company"
        DoCmd.Close
    End Sub
  4. In the Object combo box, select cmdClose
  5. Implement the event as follows:
    Private Sub cmdClose_Click()
        DoCmd.Close
    End Sub
  6. In the Project window dialog box, double-click Form_New Customer Bill
  7. In the Object combo box, select cmdSubmitNewBill
  8. Implement the event as follows:
    Private Sub cmdSubmitNewBill_Click()
        Dim dbCommunication As Database
        
        Set dbCommunication = CurrentDb
        
        If IsNull(txtAccountNumber) Then
            MsgBox "You must provide an account number for the customer whose bill you are preparing.", _
                   vbOKCancel Or vbInformation, "Eastern Shore Communications Company"
            Exit Sub
        End If
    
        dbCommunication.Execute ("INSERT INTO CustomersBills(AccountNumber, CableTVBasicFee, " & _
                                 "IncludesDVRService, DVRServiceFee, IncludesSportsPackage, " & _
                                 "SportsPackageFee, FCCFee, CableTVCountyTaxes, CableTVStateTaxes, " & _
                                 "CableTVTotal, InternetBasicFee, IncludesModemLease, ModemFee, " & _
                                 "InternetSpeedApplied, InternetSpeedFee, InternetCountyTaxes, " & _
                                 "InternetStateTaxes, InternetTotal, TotalAmountDue) " & _
                                 "VALUES('" & txtAccountNumber & "', " & CDbl(Nz(txtCableTVBasicFee)) & _
                                 ", " & chkIncludesDVRService.Value & ", " & CDbl(Nz(txtDVRServiceFee)) & _
                                 ", " & chkIncludesSportsPackage.Value & ", " & CDbl(Nz(txtSportsPackageFee)) & _
                                 ", " & CDbl(Nz(txtFCCFee)) & ", " & CDbl(Nz(txtCableTVCountyTaxes)) & _
                                 ", " & CDbl(Nz(txtCableTVStateTaxes)) & ", " & CDbl(Nz(txtCableTVTotal)) & _
                                 ", " & CDbl(Nz(txtInternetBasicFee)) & ", " & chkIncludesModemLease.Value & _
                                 ", " & CDbl(Nz(txtModemFee)) & ", " & _
                                 IIf(txtInternetSpeedApplied = "Starter", 1, IIf(txtInternetSpeedApplied = "Blast", 2, 3)) & _
                                 ", " & CDbl(Nz(txtInternetSpeedFee)) & ", " & CDbl(Nz(txtInternetCountyTaxes)) & _
                                 ", " & CDbl(Nz(txtInternetStateTaxes)) & ", " & CDbl(Nz(txtInternetTotal)) & _
                                 ", " & CDbl(Nz(txtTotalAmountDue)) & ");")
        
        MsgBox "The customer bill has been prepared, approved, and submited.", _
               vbOKCancel Or vbInformation, "Eastern Shore Communications Company"
        ResetForm
    End Sub
  9. Close Microsoft Visual Basic and return to Microsoft Access
  10. Save the form

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. Remember that you can omit DAO.

The Tables of a DAO Database

The tables of a DAO database are stored in a collection named 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 let you programmatically create a table in the DAO Library, the Database class is equipped with a method named CreateTableDef. Its syntax is:

Public Function CreateTableDef(ByVal Optional Name As String,
			       ByVal Optional Attributes As Variant,
			       ByVal Optional SourceTableName As String,
			       ByVal Optional Connect As String) As TableDef

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:

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

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:

Private Sub cmdCreateTable_Click()
    Dim dbExercise As Database
   Dim tblEmployees As 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 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:

Private Sub cmdDeleteTable_Click()
    Dim curDatabase As 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 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:

Private Sub cmdCreateTable_Click()   
    Dim fldEmployeeNumber As DAO.Field
    Dim fldAreaCode 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 let you create a column,  the DAO.TableDef class is equipped with a method named CreateField. Its syntax is:

Public Function CreateField(ByVal Optional ColumnName As String, _
			    ByVal Optional DataType As FieldType, _
			    ByVal Optional FieldSize As Integer) As Field

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:

Public Function Append(ByVal Object As Object) As Field

This can 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

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

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.

Introduction to Short Text 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

The Size of a Text-Based Field

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 are examples:

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("FirstName", dbText, 25)
    tblEmployees.Fields.Append colEmployee
    Set colEmployee = tblEmployees.CreateField("LastName", DB_TEXT, 25)
    tblEmployees.Fields.Append colEmployee
    
    dbFunDS.TableDefs.Append tblEmployees
    
    dbFunDS.Close
    Set dbFunDS = Nothing

    Application.RefreshDatabaseWindow
End Sub

A Long Text Field

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.

Here is an example:

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

An Automatically Incrementing Value for 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 colFullName As DAO.Field
   Dim colEmployeeID As DAO.Field
   Dim curDatabase As DAO.Database
   Dim tblEmployees As DAO.TableDef

   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.

Decimal Number-Based Fields

Single-Precision Numeric Fields

To create a field that will use single-precision real numbers, specify its data type as either dbSingle or DB_SINGLE. Here is an example:

Private Sub cmdCreateTable_Click()
    Dim fldFullName As DAO.Field
    Dim fldWeeklyHours As DAO.Field
    Dim curDatabase As DAO.Database
    Dim tblEmployees As DAO.TableDef
    Dim fldEmployeeNumber 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

Double-Precision Numeric Value Fields

To create a field for decimal numbers with double-precision, set its data type as dbDouble or DB_DOUBLE.

Currency Fields

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

Other Types of Fields

Boolean Fields

If you are creating a field that will use Boolean values, specify its data type as DB_BOOLEAN. Here is an example:

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

Date/Time Fields

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

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

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

Private Sub cmdCreateTable_Click()
    Dim fldMake As DAO.Field
    Dim idxGenerators As DAO.Index
    Dim dbPowerCompany As DAO.Database
    Dim tblElectricGenerators As DAO.TableDef

    Set dbPowerCompany = CurrentDb
    Set tblElectricGenerators = dbPowerCompany.CreateTableDef("Generators")
    
    Set fldMake = tblElectricGenerators.CreateField("Make", dbText, 25)
    tblElectricGenerators.Fields.Append fldMake

    ' Start creating the index
    Set idxGenerators = tblElectricGenerators.CreateIndex()
    
    dbPowerCompany.TableDefs.Append tblElectricGenerators
    
    Application.RefreshDatabaseWindow
End Sub

In order to add an index to a table, you must add an index field to the index variable. To support this, the Index class is equipped with a method named CreateField. Its syntax is:

Public Function CreateField(ByVal Optional Name As String) As Field

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

Private Sub cmdCreateTable_Click()
    Dim fldMake As DAO.Field
    Dim idxGenerators As DAO.Index
    Dim fldGeneratorIndex As DAO.Field
    Dim dbPowerCompany As DAO.Database
    Dim tblElectricGenerators As DAO.TableDef

    Set dbPowerCompany = CurrentDb
    Set tblElectricGenerators = dbPowerCompany.CreateTableDef("Generators")
    
    Set fldMake = tblElectricGenerators.CreateField("Make", dbText, 25)
    tblElectricGenerators.Fields.Append fldMake

    ' Start creating the index
    Set idxGenerators = tblElectricGenerators.CreateIndex()
    ' Create a table field for the index
    Set fldGeneratorIndex = idxGenerators.CreateField()
    
    dbPowerCompany.TableDefs.Append tblElectricGenerators
    
    Application.RefreshDatabaseWindow
End Sub

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:

Private Sub cmdCreateTable_Click()
    Dim fldMake As DAO.Field
    Dim idxGenerators As DAO.Index
    Dim fldGeneratorIndex As DAO.Field
    Dim dbPowerCompany As DAO.Database
    Dim tblElectricGenerators As DAO.TableDef

    Set dbPowerCompany = CurrentDb
    Set tblElectricGenerators = dbPowerCompany.CreateTableDef("Generators2")
    
    Set fldMake = tblElectricGenerators.CreateField("Make", dbText, 25)
    tblElectricGenerators.Fields.Append fldMake

    ' Start creating the index
    Set idxGenerators = tblElectricGenerators.CreateIndex()
    ' Create a table field for the index
    Set fldGeneratorIndex = idxGenerators.CreateField()
    ' Add the field to the table
    idxGenerators.Fields.Append fldGeneratorIndex
    ' Add the index to the table
    tblElectricGenerators.Indexes.Append idxGenerators
    
    dbPowerCompany.TableDefs.Append tblElectricGenerators
    
    Application.RefreshDatabaseWindow
End Sub

The Name of an Index

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:

Private Sub cmdCreateTable_Click()
    Dim fldMake As DAO.Field
    Dim idxGenerators As DAO.Index
    Dim dbPowerCompany As DAO.Database
    Dim tblElectricGenerators As DAO.TableDef

    Set dbPowerCompany = CurrentDb
    Set tblElectricGenerators = dbPowerCompany.CreateTableDef("Generator")
    
    Set fldMake = tblElectricGenerators.CreateField("Make", dbText, 25)
    tblElectricGenerators.Fields.Append fldMake

    ' Start creating the index
    Set idxGenerators = tblElectricGenerators.CreateIndex("GeneratorKey")
    
    dbPowerCompany.TableDefs.Append tblElectricGenerators
    
    Application.RefreshDatabaseWindow
End Sub

The Field of an Index

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:

Private Sub cmdCreateTable_Click()
    Dim fldMake As DAO.Field
    Dim idxGenerators As DAO.Index
    Dim fldMachineNumber As DAO.Field
    Dim fldGeneratorIndex As DAO.Field
    Dim dbPowerCompany As DAO.Database
    Dim tblElectricGenerators As DAO.TableDef

    Set dbPowerCompany = CurrentDb
    Set tblElectricGenerators = dbPowerCompany.CreateTableDef("Generators2")
    
    Set fldMachineNumber = tblElectricGenerators.CreateField("MachineNumber", dbText, 12)
    tblElectricGenerators.Fields.Append fldMachineNumber
    
    Set fldMake = tblElectricGenerators.CreateField("Make", dbText, 25)
    tblElectricGenerators.Fields.Append fldMake

    ' Start creating the index
    Set idxGenerators = tblElectricGenerators.CreateIndex("GeneratorKey")
    ' Create a table field for the index
    Set fldGeneratorIndex = idxGenerators.CreateField("MachineNumber")
    ' Add the field to the table
    idxGenerators.Fields.Append fldGeneratorIndex
    ' Add the index to the table
    tblElectricGenerators.Indexes.Append idxGenerators
    
    dbPowerCompany.TableDefs.Append tblElectricGenerators
    
    Application.RefreshDatabaseWindow
End Sub

You can also access the Name property of a Field variable and use it. Here is an example:

Private Sub cmdCreateTable_Click()
    . . .

    ' Create a table field for the index
    Set fldGeneratorIndex = idxGenerators.CreateField(fldMachineNumber.Name)
    
    . . .
    
End Sub

Indexes and Record Management

Unique Records

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

  1. On the form, right-click the Locations button and click Build Event...
  2. In the Show Builder dialog box, click Code Builder and click OK
  3. Implement the event as follows:
    Private Sub cmdLocationsTable_Click()
        Dim fldLocation As Field
        Dim idxLocations As Index
        Dim dbKoloBank As Database
        Dim tblLocations As TableDef
    
        Set dbKoloBank = CurrentDb
        Set tblLocations = dbKoloBank.CreateTableDef("Locations")
        
        Set fldLocation = tblLocations.CreateField("LocationCode", dbText, 10)
        tblLocations.Fields.Append fldLocation
        
        Set fldLocation = tblLocations.CreateField("LocationName", DB_TEXT, 50)
        tblLocations.Fields.Append fldLocation
        
        Set fldLocation = tblLocations.CreateField("Address", dbText, 50)
        tblLocations.Fields.Append fldLocation
        
        Set fldLocation = tblLocations.CreateField("City", DB_TEXT, 40)
        tblLocations.Fields.Append fldLocation
        
        Set fldLocation = tblLocations.CreateField("State", dbText, 2)
        tblLocations.Fields.Append fldLocation
        
        Set fldLocation = tblLocations.CreateField("ZIPCode", DB_TEXT, 20)
        tblLocations.Fields.Append fldLocation
        
        Set fldLocation = tblLocations.CreateField("Notes", dbMemo)
        tblLocations.Fields.Append fldLocation
    
        Set idxLocations = tblLocations.CreateIndex("PK_Locations")
        idxLocations.Unique = True
        idxLocations.Primary = True
        idxLocations.Required = True
        
        Set fldLocation = idxLocations.CreateField("LocationCode")
        idxLocations.Fields.Append fldLocation
        tblLocations.Indexes.Append idxLocations
        
        dbKoloBank.TableDefs.Append tblLocations
        
        Application.RefreshDatabaseWindow
        dbKoloBank.Close
        cmdLocationsTable.Enabled = False
    End Sub

A Foreign Key

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:

Public Function CreateRelation(ByVal Optional Name As String,
			       ByVal Optional PrimaryTable As String,
			       ByVal Optional ForeignTable As String,
			       ByVal Optional Attributes) As Relation

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
dbRelationLeft 16777216 The relationship is made from the parent to the child tables
dbRelationRight 33554432 The relationship is made from the child to the parent tables

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

Practical Learning: Creating Tables

  1. In the Object combo box, select cmdEmployeesTable
  2. Implement the event as follows:
    Private Sub cmdEmployeesTable_Click()
        Dim fldEmployee As Field
        Dim idxEmployees As Index
        Dim dbKoloBank As Database
        Dim tblEmployees As TableDef
        Dim relEmployees As Relation
        
        Set dbKoloBank = CurrentDb
        Set tblEmployees = dbKoloBank.CreateTableDef("Employees")
    
        Set fldEmployee = tblEmployees.CreateField("EmployeeNumber", dbText, 10)
        tblEmployees.Fields.Append fldEmployee
        
        tblEmployees.Fields.Append tblEmployees.CreateField("FirstName", dbText, 20)
        tblEmployees.Fields.Append tblEmployees.CreateField("MiddleName", DB_TEXT, 20)
        tblEmployees.Fields.Append tblEmployees.CreateField("LastName", dbText, 20)
        tblEmployees.Fields.Append tblEmployees.CreateField("LocationCode", DB_TEXT, 10)
        tblEmployees.Fields.Append tblEmployees.CreateField("Title", dbText, 50)
        tblEmployees.Fields.Append tblEmployees.CreateField("CanCreateNewAccount", dbBoolean)
        tblEmployees.Fields.Append tblEmployees.CreateField("Address", dbText, 50)
        tblEmployees.Fields.Append tblEmployees.CreateField("City", dbText, 40)
        tblEmployees.Fields.Append tblEmployees.CreateField("State", DB_TEXT, 2)
        tblEmployees.Fields.Append tblEmployees.CreateField("ZIPCode", DB_TEXT, 20)
        tblEmployees.Fields.Append tblEmployees.CreateField("HourlySalary", dbDouble)
    
        Set idxEmployees = tblEmployees.CreateIndex("PK_Employees")
        idxEmployees.Unique = True
        idxEmployees.Primary = True
        idxEmployees.Required = True
        
        Set fldEmployee = idxEmployees.CreateField("EmployeeNumber")
        idxEmployees.Fields.Append fldEmployee
        tblEmployees.Indexes.Append idxEmployees
        
        dbKoloBank.TableDefs.Append tblEmployees
    
        Set relEmployees = dbKoloBank.CreateRelation("EmployeesLocations", "Locations", "Employees", _
                                                     dbRelationUpdateCascade Or dbRelationDeleteCascade Or dbRelationLeft)
        relEmployees.Fields.Append relEmployees.CreateField("LocationCode")
        relEmployees.Fields!LocationCode.ForeignName = "LocationCode"
        dbKoloBank.Relations.Append relEmployees
        
        Application.RefreshDatabaseWindow
        dbKoloBank.Close
        
        cmdEmployeesTable.Enabled = False
    End Sub
  3. Close Microsoft Visual Basic and return to Microsoft Access
  4. Switch the form to Form View
  5. In the Tables section of the form, click the Locations and the Employees buttons
  6. Close the Central Processing form
  7. When asked whether you want to save the form, click Yes
  8. On the Ribbon, click Database Tools and click Relationships
  9. Position the items as you see fit. Here is an example:

    Kolo Bank - Data Relationships

  10. To close the Relationships window, click the Close button on the Ribbon
  11. When asked whether you want to save, click Yes
  12. Close Microsoft Access
 
 
   
 

Previous Copyright © 2005-2016, FunctionX, Inc. Next