Introduction to DAO
|
|
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
and, in the Property Sheet, change the following characteristics:
and click the form
and click the form
| Control | Name | Caption | |
| Label |
|
Tables | |
| Line |
 |
||
| Button |
 |
cmdAccountsTypes | Accounts Types |
| Button |
|
cmdTransactionsTypes | Transactions Types |
| Button |
|
cmdAccountsStatus | Accounts Status |
| Button |
|
cmdChargesReasons | Charges Reasons |
| Button |
|
cmdCurrenciesTypes | Currencies Types |
| Button |
|
cmdLocations | Bank Locations |
| Button |
|
cmdEmployees | Employees |
| Button |
|
cmdCustomers | Customers |
| Button |
|
cmdAccountsHistories | Accounts Histories |
| Button |
|
cmdTransactions | Transactions |
| Button |
|
cmdClose | Close |
and, in the Property Sheet, change the following characteristics:
| Control | Name | Caption | |
| Button |
|
cmdCreateCustomersTable | Create Customers Accounts Table |
| Button |
|
cmdCreateCustomerBillsTable | Create Customers Bills Table |
| Button |
|
cmdClose | Close |
The Database Engine of DAO
As you may know already, the main program that performs all types of operations in a database is referred to as a database engine. In the DAO library, this program is represented by an object (or class) named DBEngine, which is a static class; this means that you don't have to declare a variable of type DBEngine, you can used it directly and access its members by qualifying them from its name.
The DAO Namespace
The DAO library and the Microsoft Access Object Library (MAOL) have a lot in common and they use the same functionalities. To let you distinguish objects of DAO, this library has a namespace named DAO. Based on this, when you want to use a class that is appropriate to DAO, you should qualify that class from the DAO namespace.
 |
The Microsoft Access Object Library and DAOIn 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:
|
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
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 SubSPrivate 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 SubPrivate Sub cmdClose_Click()
DoCmd.Close
End Sub
| Control | Name | Caption | |
| Button |
|
cmdSubmitNewBill | Submit New Bill |
| Button |
|
cmdClose | Close |
| Control | Name | Caption | |
| Button |
|
cmdCreateCustomerAccount | Create Customer Account |
| Button |
|
cmdClose | Close |
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
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 SubPrivate Sub cmdClose_Click()
DoCmd.Close
End SubPrivate 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 SubIntroduction 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
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 SubA 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
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
|
|
||
| Previous | Copyright © 2005-2022, FunctionX, Inc. | Next |
|
|
||