Creating Schema Objects with DAO
Sometimes you need to create data access objects on-the-fly. Much of DAO's power lies in its capability to create things such as tables and queries programmatically.
Let's say you inherit a copper-plated widget manufacturing company from an uncle. He never actually sold any because of the absence of an invoicing system, so you decide to implement one. Naturally enough, you'll want to create a database schema to record the details of the invoices you issue to your customers: one table for the invoice header, and one for the line items.
Like the man says, "experience is the best teacher," so to learn how to do it, let's just jump right in and create a table schema in code. Here's the basic procedure:
- Create the header table (tblInvoice), including its fields.
- Create the line items table (tblInvItem), including its fields.
- Create the indexes for both tables.
- Create the relationship between the two tables.
Creating Tables and Fields
For the invoicing system, you have two tables to create. The basic procedure for creating a table in code is as follows:
- Check if the table already exists, and if so, rename it. You could also choose to delete the table instead of renaming it.
- Create the table object using the Database's CreateTableDef method.
- Create the Field objects in memory, using the TableDef's CreateField method, setting each field's attributes as appropriate.
- Append each Field object to the TableDef's Fields collection.
- Append the TableDef object to the Database's TableDefs collection.
- Refresh the TableDefs collection to ensure it is up-to-date, and optionally call Application.RefreshDatabaseWindow to refresh the Navigation pane.
The header table stores the basic high-level information about each invoice, such as the invoice number, date, and the customer ID. The following example demonstrates how to create a new table called tblInvoice and add four fields to it. First, declare all the objects needed to create the table:
Public Sub CreateInvoiceTable() Dim dbs As DAO.Database Dim tdf As DAO.TableDef Dim fldInvNo As DAO.Field Dim fldInvDate As DAO.Field Dim fldCustID As DAO.Field Dim fldComments As DAO.Field Set dbs = CurrentDb On Error Resume Next 'If the table already exists, rename it If IsObject(dbs.TableDefs("tblInvoice")) Then DoCmd.Rename "tblInvoice_Backup", acTable, "tblInvoice" End If On Error GoTo 0 'Create the table definition in memory Set tdf = dbs.CreateTableDef("tblInvoice")
At this point, you have created the new TableDef, but it exists only in memory. It won't become a permanent part of the database until you add it to the TableDefs collection. Before you do that, however, you need to add one or more fields to the table, because you can't save a table that has no fields. You add the fields like this:
'Create the field definitions in memory Set fldInvNo = tdf.CreateField("InvoiceNo", dbText, 10) fldInvNo.AllowZeroLength = False fldInvNo.Required = True 'The InvoiceNo field could also have been specified thus: 'Set fldInvNo = tdf.CreateField() 'With fldInvNo ' .Name = "InvoiceNo" ' .Type = dbText ' .Size = 10 ' .AllowZeroLength = False ' .Required = True 'End With Set fldInvDate = tdf.CreateField("InvoiceDate", dbDate) fldInvDate.Required = True Set fldCustID = tdf.CreateField("CustomerID", dbLong) fldCustID.Required = True Set fldComments = tdf.CreateField("Comments", dbText, 50) fldComments.AllowZeroLength = True fldComments.Required = False 'Append the fields to the TableDef's Fields collection tdf.Fields.Append fldInvNo tdf.Fields.Append fldInvDate tdf.Fields.Append fldCustID tdf.Fields.Append fldComments
The table still needs to be added to the TableDefs collection to make it a permanent fixture. Once you've done that, refresh the TableDefs collection to ensure it is up-to-date, because in a multiuser application, the new table may not be immediately propagated to other users' collections until you do: 'Append the TableDef to the Database's TableDefs collection
dbs.TableDefs.Append tdf 'Refresh the TableDefs collection dbs.TableDefs.Refresh Application.RefreshDatabaseWindow Set fldInvNo = Nothing Set fldInvDate = Nothing Set fldCustID = Nothing Set fldComments = Nothing Set tdf = Nothing Set dbs = Nothing End Sub
Next, you need to create a table to store the invoice line items, including the product ID, the number of items sold, and their individual unit price. Because the total invoice price and tax can be calculated at runtime, you won't violate normalization rules by creating fields for these items.
The following code creates a new table called tblInvItem, and adds five fields to it. It is based on the same basic procedure for creating tables, but includes an additional attribute definition, dbAutoIncrField, to create an AutoNumber field.
Public Sub CreateInvItemTable() Dim dbs As DAO.Database Dim tdf As DAO.TableDef Dim fldInvItemID As DAO.Field Dim fldInvNo As DAO.Field Dim fldProductID As DAO.Field Dim fldQty As DAO.Field Dim fldUnitPrice As DAO.Field Set dbs = CurrentDb On Error Resume Next 'If the table already exists, rename it If IsObject(dbs.TableDefs("tblInvItem")) Then DoCmd.Rename "tblInvItem_Backup", acTable, "tblInvItem" End If 'Create the table definition in memory Set tdf = dbs.CreateTableDef("tblInvItem") 'Create the field definitions in memory Set fldInvItemID = tdf.CreateField("InvItemID", dbLong) 'Make the field an AutoNumber datatype fldInvItemID.Attributes = dbAutoIncrField fldInvItemID.Required = True Set fldInvNo = tdf.CreateField("InvoiceNo", dbText, 10) fldInvNo.Required = True fldInvNo.AllowZeroLength = False Set fldProductID = tdf.CreateField("ProductID", dbLong) fldProductID.Required = True Set fldQty = tdf.CreateField("Qty", dbInteger) fldQty.Required = True Set fldUnitPrice = tdf.CreateField("UnitCost", dbCurrency) fldUnitPrice.Required = False 'Append the fields to the TableDef's Fields collection tdf.Fields.Append fldInvItemID tdf.Fields.Append fldInvNo tdf.Fields.Append fldProductID tdf.Fields.Append fldQty tdf.Fields.Append fldUnitPrice 'Append the TableDef to the Database's TableDefs collection dbs.TableDefs.Append tdf 'Refresh the TableDefs collection dbs.TableDefs.Refresh Application.RefreshDatabaseWindow Set fldInvItemID = Nothing Set fldInvNo = Nothing Set fldProductID = Nothing Set fldQty = Nothing Set fldUnitPrice = Nothing Set tdf = Nothing Set dbs = Nothing End Sub
In this tutorial:
- Using DAO to Access Data
- Data Access Objects
- New Features in DAO
- Referring to DAO Objects
- The DBEngine Object
- Using Transactions
- The Errors Collection
- The Databases Collection
- The CurrentDb() Function
- Opening an External Database
- Closing and Destroying Database Object References
- DAO Property Types
- Setting and Retrieving Built-In Object Properties
- Setting and Retrieving SummaryInfo Properties
- Creating Schema Objects with DAO
- Creating Indexes
- Creating Relations
- Creating Multi-Value Lookup Fields
- Database Encryption with DAO
- Setting Encryption Options
- Managing Access (JET) Security with DAO
- Creating Security Objects
- Creating and Deleting Groups
- Managing Passwords
- Data Access with DAO
- Modifying a QueryDef
- Filtering and Ordering Recordsets
- Navigating Recordsets
- BOF, EOF
- Navigating Recordsets with Multi-Value Lookup Fields
- Bookmarks and Recordset Clones
- Finding Records
- Working with Recordsets
- Using Arrays with Recordsets
- Working with Attachment Fields
- Append Only Fields