March 3, 2021
Hot Topics:

Creating Databases at Runtime

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

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

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 _

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.

  ThisTable _
  (Male YESNO, DateOfBirth _

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.

Page 2 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