syntax error in constraint clause

[bilbo]

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!

[FishVal]

Hi, bilbo.

It seems that constraints (at least DRI) are not supported via DAO interface. But via ADO it runs flawlessly.

Public Sub CreateTableWithDRIConstraint()
 
    'ADO connection performs operation flawlessly
    CurrentProject.Connection.Execute _
        "CREATE TABLE tblChild (keyID Long, CONSTRAINT FK_keyID FOREIGN KEY (keyID)" & _
        " REFERENCES tbl(keyID) ON DELETE CASCADE);"
    'DAO connection throws an error
    CurrentDb.Connection.Execute _
        "CREATE TABLE tblChild (keyID Long, CONSTRAINT FK_keyID FOREIGN KEY (keyID)" & _
        " REFERENCES tbl(keyID) ON DELETE CASCADE);"
 
End Sub
 

Regards,
Fish

[bilbo]

Thanks!!!

I'll add this to the long and growing list of things I tried to over complicate!

I was able to adapt my code using your tip and it works just great!

Thanks loads!

[FishVal]

You are quite welcome.

Best regards,
Fish