Access™ 2007 VBA Programmer's Reference

6.8. 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.

6.8.1. 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 ...