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.
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.
Page 1 of 4