Syntax error in CONSTRAINT clause in MAKE TABLE query

[Bilbo_Baggins_Esq]

Can anybody help me understand why I get the error
"Syntax error in CONSTRAINT clause"?

I get it in Access 2003 and Access 2007.
Both are clean installs with no add-ins

Running this code in VBA, or pasting the SQL directly in a query results in the same error.

Sub test()
sTableName = "Test"

sSQL = "CREATE TABLE " & sTableName & "_Config (" _
& "[idConfig] Int Primary Key," _
& "[Config] Memo," _
& "[Instrument] int," _
& "[Serial_No] Text(25)," _
& "[Firmware] int," _
& "[Orientation] int," _
& "[Sensors] int," _
& "[Sensor_Size] float," _
& "[Sensor_1_Distance] float)"

DoCmd.RunSQL sSQL



sSQL = "CREATE TABLE " & sTableName & "_Leader (" _
& "[idLeader] Int Primary Key," _
& "[idGroup] int," _
& "[idFile] int," _
& "[idConfig] int," _
& "[DateTime] DateTime," _
& "[Heading] float, [Pitch] float, [Roll] float," _
& "[Pressure] float, [Depth_BSL] float, [Height_ASB] float,"
_
& "[Min_Valid_Sensor] int, [Max_Valid_Sensor] int," _
& "[ASM_Bed_Level] float," _
& "CONSTRAINT [FK_Test_Leader_idConfig] FOREIGN KEY ([idConfig]) REFERENCES [Test_Config] ([idConfig]) ON DELETE CASCADE ON UPDATE CASCADE" _
& ")"

DoCmd.RunSQL sSQL

End Sub

The first table is created just fine.
And if I leave the constraint clause out, the second table is created also.
If I try to run it as an SQL query right in Access, after the error is delivered, it highlights the word DELETE, and if I reverse the update and delete portions, it highlights the word UPDATE.

I have looked up the clause in Access help and even using their example, i get the same error.


Any help will surely be appreciated!

 

[jdraw]

Can anybody help me understand why I get the error
"Syntax error in CONSTRAINT clause"?

I get it in Access 2003 and Access 2007.
Both are clean installs with no add-ins

Running this code in VBA, or pasting the SQL directly in a query results in the same error.

Sub test()
sTableName = "Test"

sSQL = "CREATE TABLE " & sTableName & "_Config (" _
& "[idConfig] Int Primary Key," _
& "[Config] Memo," _
& "[Instrument] int," _
& "[Serial_No] Text(25)," _
& "[Firmware] int," _
& "[Orientation] int," _
& "[Sensors] int," _
& "[Sensor_Size] float," _
& "[Sensor_1_Distance] float)"

DoCmd.RunSQL sSQL



sSQL = "CREATE TABLE " & sTableName & "_Leader (" _
& "[idLeader] Int Primary Key," _
& "[idGroup] int," _
& "[idFile] int," _
& "[idConfig] int," _
& "[DateTime] DateTime," _
& "[Heading] float, [Pitch] float, [Roll] float," _
& "[Pressure] float, [Depth_BSL] float, [Height_ASB] float,"
_
& "[Min_Valid_Sensor] int, [Max_Valid_Sensor] int," _
& "[ASM_Bed_Level] float," _
& "CONSTRAINT [FK_Test_Leader_idConfig] FOREIGN KEY ([idConfig]) REFERENCES [Test_Config] ([idConfig]) ON DELETE CASCADE ON UPDATE CASCADE" _
& ")"

DoCmd.RunSQL sSQL

End Sub

The first table is created just fine.
And if I leave the constraint clause out, the second table is created also.
If I try to run it as an SQL query right in Access, after the error is delivered, it highlights the word DELETE, and if I reverse the update and delete portions, it highlights the word UPDATE.

I have looked up the clause in Access help and even using their example, i get the same error.


Any help will surely be appreciated!

I think the syntax of your SQL to create the primary key is incorrect.
Here is a sample showing a CONSTRAINT that identifies the Primary key.

CREATE TABLE Employees (
First_Name TEXT(20)
, Last_Name TEXT(25)
, dob DATETIME
, CONSTRAINT Employees_PK Primary Key (First_Name, Last_Name, dob)
);

 

[Bilbo_Baggins_Esq]

The constraint in question is not adding a primary key, but it is adding a foreign key.

I have managed to resolve the issue using ADO as follows:

Sub Create_2_Tables_With_1_FK_Constraint_Via_ADO()

    sTableName = "Test"
 
    CurrentProject.Connection.Execute _
        "CREATE TABLE " & sTableName & "_Config (" _
        & "[idConfig] Int Primary Key," _
        & "[Config] Memo," _
        & "[Instrument] int," _
        & "[Serial_No] Text(25)," _
        & "[Firmware] int," _
        & "[Orientation] int," _
        & "[Sensors] int," _
        & "[Sensor_Size] float," _
        & "[Sensor_1_Distance] float)"

    CurrentProject.Connection.Execute _
        "CREATE TABLE " & sTableName & "_Leader (" _
        & "[idLeader] Int Primary Key," _
        & "[idGroup] int," _
        & "[idFile] int," _
        & "[idConfig] int," _
        & "[DateTime] DateTime," _
        & "[Heading] float, [Pitch] float, [Roll] float," _
        & "[Pressure] float, [Depth_BSL] float, [Height_ASB] float," _
        & "[Min_Valid_Sensor] int, [Max_Valid_Sensor] int," _
        & "[ASM_Bed_Level] float," _
        & "CONSTRAINT [FK_Test_Leaderid_Config] FOREIGN KEY([idConfig]) REFERENCES [Test_Config] ([idConfig]) ON DELETE CASCADE ON UPDATE CASCADE" _
        & ")"

End Sub

As usual, I was over complicating it...