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.

Page 1 of 4



This article was originally published on November 20, 2002