CREATE TABLE syntax

[David Campbell]

I am using ODBC to connect to an Access file.

I want to programmatically create a table.

I want the primary key for the table to include 2 fields.

Something like this:

CREATE TABLE ABC

(

FIELD_01 VARCHAR(10),

FIELD_02 VARCHAR(10),

FIELD_03 VARCHAR(10)

PRIMARY KEY (FIELD_01,FIELD_02)

)

The above syntax fails.



What is the SQL syntax to create a table with a multi-field primary key?



thanks,

David

 

[SirPoonga]

In Access VBA I use something like

'DAO objects
Dim db As Database
Dim tdf As tabledef

With tdf
.Fields.Append .CreateField("Warehouse", dbText, 2)
.Fields("Warehouse").AllowZeroLength = True
.Fields.Append .CreateField("Part Number", dbText, 15)
.Fields("Part Number").AllowZeroLength = True
.Fields.Append .CreateField("Description", dbText, 30)
.Fields("Description").AllowZeroLength = True
.Fields.Append .CreateField("Exemption Code", dbInteger)
.Fields("Exemption Code").AllowZeroLength = True
End With
db.TableDefs.Append tdf

db.Close

 

[Douglas J. Steele]

Try something like:

CREATE TABLE ABC
(
FIELD_01 CHAR(10),
FIELD_02 CHAR(10),
FIELD_03 CHAR(10)
CONSTRAINT PRIMARY_KEY UNIQUE
(FIELD_01,FIELD_02)
)

I'm not sure, though, that that will actually create a primary index, just a
unique one.

If you actually need a primary index, you may be better off using CREATE
INDEX:

CREATE UNIQUE INDEX PRIMARY_KEY
ON ABC (FIELD_01, FIELD_02)
WITH PRIMARY

 

[onedaywhen]

I am using ODBC to connect to an Access file.

Your DDL works for me as posted when using the Jet 4.0 OLE DB provider,
which supports a richer DDL syntax.

Jamie.

--