Creating Databases at Runtime Los Angeles CA

provided by: 
Originally published at Internet.com

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: * field1 is the name of the field * type is the type of the field, valid options are: * TEXT - String; up to 255 characters, defined by the size parameter * MEMO - String; up to 1.2GB of characters, variable length * NUMBER - Sub types are: BYTE - 8-bit number INTEGER - 16-bit number LONG - 32-bit number SINGLE - 32-bit floating point DOUBLE - 64-bit floating point (default) * DATETIME - Date; a date / time * CURRENCY - Currency; very accurate, storing values with 15 digits before decimal point, and up to 4 after * YESNO - Boolean * OLEOBJECT - Object; any valid OLE Object * BINARY - Number; a number with up 255 digits, defined by size parameter

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) = PubTTd.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.