Creating Databases at Runtime

DAO Method

This method uses the DAO's Create methods to create TableDef, Field, and Index objects to make the database. To start, let's create the database in the same way as we did with the SQL method:

Dim mydb As Database
Set mydb = CreateDatabase _
 ("C:mydb.mdb", dbLangGeneral)

Use the Dim statement to create new object variables for each object in your database. In addition to the DBEngine and default Workspace objects that make up the working environment, you will need:

• One Database object
• One TableDef object for each table
• One Field object for each field in each table
• One Index object for each index in each table

In essence this is what you must do:

1. Create the table
2. Create field(s)
3. Set properties for fields as required
4. Append all fields to fields collection of table
5. Create index
6. Set properties for index
7. Create necessary fields for the index
8. Set properties for fields as required
9. Append necessary fields to fields collection of index
10. Append all indexes to indexes collection of table
11. Append table to tables collection of the database

Although this seems a lot more complicated, it provides much more control and is easier to debug in the long run.

1) Create the table. This just creates a new TableDef object that we will be working with:

Dim BooksTd As TableDef
Set BooksTd = mydb.CreateTableDef _
   ("Books")

2 & 3) Create fields and Set properties for fields as required

Dim BooksFlds(1) As Field
Set BooksFlds(0) = BooksTd.CreateField _
   ("Book_ID", dbLong) 
' Add fields to MyTableDef.
BooksFlds(0).Attributes = dbAutoincrfield _
   ' Make it an autonumber field.
Set BooksFlds(1) = BooksTd.CreateField _
   ("Author", dbText)
BooksFlds(1).Size = 50

4) Append all fields to fields collection of table

BooksTd.Fields.Append BooksFlds(0)
BooksTd.Fields.Append BooksFlds(1)

5 & 6) Create index and Set Properties for fields as required: similar method to creating the field objects

Dim BooksIdx As Index
Set BooksIdx = BooksTd.CreateIndex _
   ("Au_ID")
BooksIdx.Primary = True
BooksIdx.Unique = True

7 & 8) Create necessary fields for the index and Set properties for fields as required

Dim IdxFlds As Field
Set IdxFlds = BooksIdx.CreateField _
   ("Au_ID")

9) Append neccessary fields to fields collection of index

BooksIdx.Fields.Append IdxFlds

10) Append all indexes to indexes collection of table

BooksTd.Indexes.Append BooksIdx

11) Append table to tables collection of the database

mydb.TableDefs.Append BooksTd

That is all the code that it takes to create a two field table with an index. Although this seems a lot longer, it is much easier to follow.

Page 3 of 4



This article was originally published on November 20, 2002