Syntax error (Access 2000)

Topic

New Reply

I want to make a code for creating a new table but i get the error syntax error in field definition:
Dim str As String
str = ” CREATE TABLE ThirdTable (Productid Autonumber, Items4 Single, ProductID Constraint PRIMARY KEY)”
CurrentDb.Execute str
Could you help me ?

Reply | Quote

Replies

Jet SQL uses other names for some of the data types. Use COUNTER or AUTOINCREMENT instead of AutoNumber. The official term for Single is REAL, but Single is accepted too.
For a single-field key, you *must* specify the key immediately after the field definition, not separately, and you must provide a name for the key.

Note: although str is allowed as name for a variable, it is also the name of a built-in function, so it is better to avoid it. Use strSQL instead.

Dim strSQL As String
strSQL = “CREATE TABLE ThirdTable (Productid AUTOINCREMENT Constraint PrimaryKey PRIMARY KEY, Items4 Single)”
CurrentDb.Execute strSQL

Reply | Quote