Access 2003 VBA Programmer's Reference

6.8. Creating Schema Objects with DAO

Sometimes you need to create data access objects on the fly. For example, you might be creating objects that you will later delete, to fill a temporary need. Much of DAO's power lies in its ability to create things like tables and queries programmatically.

Let's say you inherit a manufacturing company from a long lost, yet recently deceased uncle. This company makes copper-plated widgets, but since he never actually sold any because of the absence of an invoicing system, 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 our table schema in code.

The basic procedure for doing so is this:

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.

6.8.1.1. Creating Tables and Fields

For our invoicing system, we 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, delete 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 ...