Creating Databases at Runtime
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.
Page 2 of 4