Using Access 2000 VBA, how to create a new table ?

[Guest]

Trying to create a new table in Access 2000 VBA without having to resort to
the old TABLEDEF method. Example: In a few lines of code, how do I make a
table named MYTABLE with TEXT_VAR(50), NUM_VAR(Double), YESNO_VAR(Yes/No) ...
just an empty table structure ... not created from anything else. This
should be simple, but the on-line help is (again) pretty useless ...

I've been messing arround with RUNSQL, but that seems grossly un-eligant. I
refuse to install VBA references to older versions of Access to do this !

Thanks for suggestions/assistance.

Alan

 

[Allen Browne]

The example shows how to create most of the field types and set most of the
settable properties, by executing the DDL query statement under ADOX. Much
(but not all) of this could also be executed under DAO with :
dbEngine(0)(0).Execute strSql, dbFailOnError

Note that there are some very important properties you can't set in JET
purely with DDL, such as the Allow Zero Length property, setting a Format or
Caption for a column, or asking for a Check Box as the DisplayControl for a
yes/no field. For these, you need to use DAO.

The example:
Dim cmd As New ADODB.Command
Dim strSql As String

cmd.ActiveConnection = CurrentProject.AccessConnection

strSql = "CREATE TABLE Table1 " & _
"(MyID COUNTER CONSTRAINT PrimaryKey PRIMARY KEY, " & _
"Surname TEXT(30) WITH COMP NOT NULL, " & _
"FirstName TEXT(20) WITH COMP, " & _
"Inactive YESNO, " & _
"HourlyFee CURRENCY DEFAULT 0, " & _
"PenaltyRate DOUBLE, " & _
"BirthDate DATE, " & _
"Notes MEMO, " & _
"CONSTRAINT FullName UNIQUE (Surname, FirstName));"
cmd.CommandText = strSql
cmd.Execute
Debug.Print "Table1 created."

For a comparion between the field type names to use in the DDL statement, in
DAO, in ADO, and in the Access interface, see:
Field type names (JET, DDL, DAO and ADOX)
at:
http://allenbrowne.com/ser-49.html

 

[Guest]

In Access Help, From contents, select "Microsoft Jet SQL Reference, Data
Definition Language, CREATE TABLE Statement.

 

[Guest]

THANK YOU ! Much appreciated. (Man, you're good!) ;-)

 

[Tim Ferguson]

Trying to create a new table in Access 2000 VBA without having to
resort to the old TABLEDEF method.

Not quite sure what the problem is, but this always works for me:

jetSQL = "CREATE TABLE MyTable " & _
"( MyIDNum INTEGER PRIMARY KEY, " & _
" MyNumber DOUBLE NULL, " & _
" MyName VARCHAR(32) NULL " & _
")"

Set db = CurrentDB()
db.Execute jetSQL, dbFailOnError


or else this, if you prefer

adoSQL = "CREATE TABLE MyTable " & _
"( MyIDNum INTEGER PRIMARY KEY, " & _
" MyNumber DOUBLE NOT NULL " & _
" DEFAULT 9999.9999, " & _
" MyName VARCHAR(32) NULL " & _
")"

Set conn = CurrentProject().Connection
db.Execute adoSQL, False, adCmdText

Note the capabilities of the more recent version of Jet are only
available using ADO. Using a different engine may require a different
syntax again.

Hope that helps


Tim F