March 5, 2021
Hot Topics:

Creating Databases at Runtime

  • By John Percival
  • Send Email »
  • More Articles »

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

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

    Enterprise Development Update

    Don't miss an article. Subscribe to our newsletter below.

    Thanks for your registration, follow us on our social networks to keep up-to-date