Creating Databases at Runtime

In the Books Online, there is an example of how to create the Biblio database using these methods. It has several errors in, so here is a new corrected version, showing you how to create a more complex database:

Dim mydb As Database
Set mydb = CreateDatabase _
   ("C:Biblinew.mdb" _
   , dbLangGeneral)
Dim AuTd As TableDef
Dim AuFlds(2) As Field
Dim AuIdx As Index
Dim AuIdxFld As Field

' Create new TableDef for Authors table.
Set AuTd = mydb.CreateTableDef _
   ("Authors")

' Add fields to MyTableDef.
Set AuFlds(0) = AuTd.CreateField _
   ("Au_ID", dbLong)
' Make it a counter field.
AuFlds(0).Attributes = dbAutoincrfield

Set AuFlds(1) = AuTd.CreateField _
   ("Author", dbText)
AuFlds(1).Size = 50

AuTd.Fields.Append AuFlds(0)
AuTd.Fields.Append AuFlds(1)

' Now add an Index.
Set AuIdx = AuTd.CreateIndex _ 
   ("Au_ID")
AuIdx.Primary = True
AuIdx.Unique = True

Set AuIdxFld = AuIdx.CreateField _
   ("Au_ID")
' Append Field to Fields collection
' of Index object.
AuIdx.Fields.Append AuIdxFld

' Append Index to Indexes collection.
AuTd.Indexes.Append AuIdx

' Append TableDef to TableDefs collection.
mydb.TableDefs.Append AuTd

Dim TitTd As TableDef
Dim TitFlds(5) As Field
Dim TitIdx(3) As Index
Dim TitIdxFlds(2) As Field

' Create new TableDef for Titles table
Set TitTd = mydb.CreateTableDef _
   ("Titles")

' Create fields for Titles Table
Set TitFlds(0) = TitTd.CreateField _
   ("Title", dbText)
TitFlds(0).Size = 100
Set TitFlds(1) = TitTd.CreateField _
   ("Year Published", dbInteger)
Set TitFlds(2) = TitTd.CreateField _
   ("ISBN", dbText)
TitFlds(2).Size = 20
Set TitFlds(3) = TitTd.CreateField _
   ("Au_ID", dbLong)
Set TitFlds(4) = TitTd.CreateField _
   ("PubID", dbLong)
' Append fields to Titles TableDef.

TitTd.Fields.Append TitFlds(0)
TitTd.Fields.Append TitFlds(1)
TitTd.Fields.Append TitFlds(2)
TitTd.Fields.Append TitFlds(3)
TitTd.Fields.Append TitFlds(4)

' Now add indexes.
Set TitIdx(0) = TitTd.CreateIndex()
TitIdx(0).Name = "ISBN"
TitIdx(0).Primary = True
TitIdx(0).Unique = True
Set TitIdxFlds(0) = TitIdx(0).CreateField _
   ("ISBN")

Set TitIdx(1) = TitTd.CreateIndex()
TitIdx(1).Name = "Au_ID"
TitIdx(1).Primary = False
TitIdx(1).Unique = False
Set TitIdxFlds(1) = TitIdx(1).CreateField _
   ("Au_ID")

Set TitIdx(2) = TitTd.CreateIndex()
TitIdx(2).Name = "PubID"
TitIdx(2).Primary = False
TitIdx(2).Unique = False
Set TitIdxFlds(2) = TitIdx(2).CreateField _
   ("PubID")

' Append fields to Index object of Titles Table.
TitIdx(0).Fields.Append TitIdxFlds(0)
TitIdx(1).Fields.Append TitIdxFlds(1)
TitIdx(2).Fields.Append TitIdxFlds(2)

' Append Indexes to Titles TableDef
TitTd.Indexes.Append TitIdx(0)
TitTd.Indexes.Append TitIdx(1)
TitTd.Indexes.Append TitIdx(2)

' Save TableDef definition by appending it
' to TableDefs collection.
mydb.TableDefs.Append TitTd

Dim PubTd As TableDef
Dim PubFlds(10) As Field
Dim PubIdx As Index
Dim PubIdxFld As Field
Dim i As Integer

' Create new TableDef for Publishers table.
Set PubTd = mydb.CreateTableDef _
   ("Publishers")

' Add field to PubTd.
Set PubFlds(0) = PubTd.CreateField _
   ("PubID", dbLong)
' Make it a counter field.
PubFlds(0).Attributes = dbAutoincrfield
Set PubFlds(1) = PubTd.CreateField _
   ("Name", dbText)
PubFlds(1).Size = 50
Set PubFlds(2) = PubTd.CreateField _
   ("Company Name", dbText)
PubFlds(2).Size = 255
Set PubFlds(3) = PubTd.CreateField _
   ("Address", dbText)
PubFlds(3).Size = 50
Set PubFlds(4) = PubTd.CreateField _
   ("City", dbText)
PubFlds(4).Size = 20
Set PubFlds(5) = PubTd.CreateField _
   ("State", dbText)
PubFlds(5).Size = 10
Set PubFlds(6) = PubTd.CreateField _
   ("Zip", dbText)
PubFlds(6).Size = 15
Set PubFlds(7) = PubTd.CreateField _
   ("Telephone", dbText)
PubFlds(7).Size = 15
Set PubFlds(8) = PubTd.CreateField _
   ("Fax", dbText)
PubFlds(8).Size = 15
Set PubFlds(9) = PubTd.CreateField _
   ("Comments", dbText)
PubFlds(9).Size = 50

' Save Field objects by appending 
' to Fields collection.
For i = 0 To 9
PubTd.Fields.Append PubFlds(i)
Next i

' Now add an index.
Set PubIdx = PubTd.CreateIndex _
   ("PubID")
PubIdx.Primary = True
PubIdx.Unique = True

Set PubIdxFld = PubIdx.CreateField _
   ("PubID")
PubIdx.Fields.Append PubIdxFld

PubTd.Indexes.Append PubIdx

' Save TableDef object by appending 
' it to TableDefs collection.
mydb.TableDefs.Append PubTd

MsgBox ("Database Created")
mydb.Close

Well, that's about it. Both methods have their advantages and disadvantages. If you are wanting to create a relatively simple database, then the SQL method is probably better and easier, but if you want control over what is happening, then you should find it easier to use the DAO method. You can also create properties, QueryDefs and Relation objects in much the same way, using DAO. Obviously, you will want to use error handling, and in the end you may want to set the objects to nothing when you have finished in order to release memory.

Page 4 of 4



This article was originally published on November 20, 2002