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:
- Create the table
- Create field(s)
- Set properties for fields as required
- Append all fields to fields collection of table
- Create index
- Set properties for index
- Create necessary fields for the index
- Set properties for fields as required
- Append necessary fields to fields collection of index
- Append all indexes to indexes collection of table
- 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 BooksTdThat 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