MS-Access / Getting Started

---

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:

1. Create the header table (tblInvoice), including its fields.
2. Create the line items table (tblInvItem), including its fields.
3. Create the indexes for both tables.
4. 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:

1. Check if the table already exists, and if so, rename it. You could also choose to delete the table instead of renaming it.
2. Create the table object using the Database's CreateTableDef method.
3. Create the Field objects in memory, using the TableDef's CreateField method, setting each field's attributes as appropriate.
4. Append each Field object to the TableDef's Fields collection.
5. Append the TableDef object to the Database's TableDefs collection.
6. 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