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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

BUD4NK8 - 20 Jul 2005 17:40 GMT

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

Reply to this Message

Allen Browne - 20 Jul 2005 18:03 GMT

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

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Trying to create a new table in Access 2000 VBA without having to resort
> to
[quoted text clipped - 12 lines]
>
> Alan

Reply to this Message

BUD4NK8 - 20 Jul 2005 18:12 GMT

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

> 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
[quoted text clipped - 48 lines]
> >
> > Alan

Reply to this Message

Klatuu - 20 Jul 2005 18:08 GMT

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

> 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
[quoted text clipped - 8 lines]
>
> Alan

Reply to this Message

Tim Ferguson - 20 Jul 2005 18:13 GMT

> 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

Reply to this Message