Access97 - Create Table Via Code 1

[SHardy]

Would someone be able to give me basic instruction on how to create a new table in an Access97 database via VBA? I need to create a table with the required fields, and to be able to set the field properties (data type/format, indexes, etc).

Thanks.

 

[fcail]

Hi,
You should use SQL instructions in a VBA function.

Sub CreateTable1()
Dim dbs As Database
Set dbs = OpenDatabase(".mdb&quot
' Create a table with two text fields.
dbs.Execute "CREATE TABLE ThisTable " _
& "(FirstName CHAR, LastName CHAR, " _
dbs.Close
End Sub

Have a look in access help, look for CREATE TABLE.

Regards.

Fcail

 

[SHardy]

So simple it should've been obvious. Only one other question on this then. When defining the data types, the text descriptions differ to those used by Access.

Example:
When using the CREATE TABLE method defining a field as "INTEGER", the field is created as a "Long Integer" rather than an "Integer".

Where can a find a list of data type definitions for SQL?

 

[fcail]

Access SQL
------------
integer integer ( long integer)
integer SMALLINT ( short integer )
text varchar(NN)
date date

For more informations, look in microsoft jet help, you can access it by the SQL mode.

Fcail

 

[SHardy]

Thanks!