Syntax error in CONSTRAINT clause in MAKE TABLE query (1 Viewer)

Bilbo_Baggins_Esq · Apr 7, 2008

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.

Code:

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 · Apr 7, 2008

Bilbo_Baggins_Esq said:
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.

Code:

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 · Apr 7, 2008

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:

Code:

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...

Syntax error in CONSTRAINT clause in MAKE TABLE query (1 Viewer)

Bilbo_Baggins_Esq

Registered User.

jdraw

Super Moderator

Bilbo_Baggins_Esq

Registered User.

Similar threads

Users who are viewing this thread