Creating Databases at Runtime

Creating Databases at Runtime

There may be times when you need to dynamically create a database at runtime that is created from user inputs. For example, you might be interested in designing an application that is a user questionnaire that will be filled out electronically. After users complete their answers the application must parse the fields, add them to the database, and then add the user's responses to the questionnaire accordingly.

There are two ways to do this. One uses the Data Definition Language (DDL) of Structured Query Language (SQL), and the other uses the create methods of DAO to add tables and fields. Both of these require you to add a reference to MS DAO, preferably version 3.5, but any version should do. Let's look at the SQL Method first.

SQL Method

In the SQL method we must first create an instance of a database object, on which we can build the tables:

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

The actual code for executing the SQL statement is very easy, but creating the actual statement is the tricky part. To execute it, all you need to do is:

mydb.Execute mySQLstring
mydb.Close

Let's have a look in more detail at the actual statement, as the help file says:

CREATE TABLE table (field1 type [(size)] [NOT NULL] [index1] [, field2 type [(size)] [NOT NULL] [index2] [, ...]] [, CONSTRAINT multifieldindex [, ...]])

In the preceding SQL statement table is the name of the table to create (surprisingly enough!). But, it is the field definition that we are interested in.

field1 type [(size)] [NOT NULL] [index1]

Let's look at this syntax in more depth next:

BYTE - 8-bit number

INTEGER - 16-bit number

LONG - 32-bit number

SINGLE - 32-bit floating point

DOUBLE - 64-bit floating point (default)

Using NOT NULL means that the field is required, and valid data must be entered on the part of the user.

Also, index1 and the CONSTRAINT clause can be used to set up primary and foreign keys, and also index. However, we will not cover these in this article, as we are looking at creating databases only.

The easiest way to understand this is through examples. I will assume that you have inserted the code to open the database, which is as above, and to execute the statement and close the database, as above. The only thing that we need do is to set the mySQLstring variable. For example the full code would be:

Dim mydb As Database
Set mydb = CreateDatabase( _
  "C:mydb.mdb", dbLangGeneral)
mySQLstring = "CREATE TABLE ThisTable _
  (FirstName TEXT, LastName TEXT);"
mydb.Execute mySQLstring
mydb.Close

This will create a new table called 'ThisTable' with two text fields, of length 255.

mySQLString = "CREATE TABLE ThisTable _
  (FirstName TEXT (20), LastName TEXT (30),_
  IDNum LONG CONSTRAINT myconstraint _
  PRIMARY KEY);"

This creates a table with three fields: two TEXT fields, one of length 20, and the other of length 30, and one LONG INTEGER field, which is the primary key.

mySQLString = "CREATE TABLE _
  ThisTable _
  (Male YESNO, DateOfBirth _
  DATETIME NOT NULL, Data BINARY _
  (24));"

This creates a table with three fields: Male, which is a Boolean yes/no field; DateOfBirth, which is of Date type, and is required; and data, a binary field of length 24 digits.

There is not much more than this to it. If you are having problems, try going through each field one by one and examining where the error occurs. Once you have created the table, you can add and remove columns using the "ALTER TABLE" statement. To delete entire tables or indexes within tables, use the "DROP" statement. As far as I know, it's not possible to implement an AutoNumber field using SQL commands. For more information, refer to help and books online.

This method is probably easier than the other, however it will only work with Access MDB databases, and has no AutoNumber facility.

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.

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.