Creating Databases at Runtime

Tuesday Nov 19th 2002 by John Percival
Share:

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:

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

    Share:
    Home
    Mobile Site | Full Site
    Copyright 2017 © QuinStreet Inc. All Rights Reserved