Referential Integrity in CREATE TABLE command

[Guest]

The MS ACCESS online guide states that ON UPDATE CASCADE or ON UPDATE SET
NULL (as well as ON DELETE CASCADE or ON DELETE SET NULL) options can be
used with Referential Integrity constraints in the CREATE TABLE command. But
every time I try to execute the CREATE TABLE command chown below, I get a
syntax error with the ON DELETE/ ON UPDATE optionS. Does ACCESS not support
the ON UPDATE/ON DELETE options at all?

CREATE TABLE OrderTbl
( OrdNo CHAR(8),
OrdDate DATE NOT NULL,
CustNo CHAR(8) NOT NULL,
EmpNo CHAR(8),
CONSTRAINT PKOrderTbl PRIMARY KEY (OrdNo) ,
CONSTRAINT FKCustNo FOREIGN KEY (CustNo) REFERENCES Customer
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT FKEmpNo FOREIGN KEY (EmpNo) REFERENCES Employee
ON DELETE CASCADE ON UPDATE CASCADE )

The Customer and Employee Tables have already been created.
Thanks in advance for your responses

 

[Allen Browne]

How did you try to execute this query?

From memory, this kind of thing works when executed programmatically under
ADO, but not under DAO. Therefore it would not work in the query window.

 

[Guest]

Thank you kindly. Yes I did try to execute it from the query window. I do
not know the acronyms ADO and DAO mean. If it is not inconvenient, can you
tell me what is the difference, or point me to a source where I can find out
what is the difference between ADO and DAO?
thanks much

 

[Douglas J. Steele]

Follow the links on
http://msdn2.microsoft.com/en-us/library/aa141802(office.10).aspx to get a
reasonable introduction.

 

[Michael Gramelspacher]

might be all you need:

Sub CreateTables()

With CurrentProject.Connection

..Execute _
"CREATE TABLE OrderTbl" & _
" (OrdNo CHAR(8)" & _
", OrdDate DATETIME NOT NULL" & _
", CustNo CHAR(8) NOT NULL" & _
", EmpNo CHAR(8)" & _
", CONSTRAINT PKOrderTbl" & _
" PRIMARY KEY (OrdNo)" & _
", CONSTRAINT FKCustNo" & _
" FOREIGN KEY (CustNo) REFERENCES Customer" & _
" ON DELETE CASCADE ON UPDATE CASCADE" & _
", CONSTRAINT FKEmpNo" & _
" FOREIGN KEY (EmpNo) REFERENCES Employee
" ON DELETE CASCADE ON UPDATE CASCADE);"

End With
End Sub

in Inmmediate Window: call CreateTables and press Enter key.

 

[Jamie Collins]

How did you try to execute this query?

From memory, this kind of thing works when executed programmatically under ADO, but not under DAO.

That's correct.

Therefore it would not work in the query window.

That's incorrect. Consider that an mdb can be opened in the Access
interface version 2002 and above in ANSI-92 Query Mode e.g. see:

About ANSI SQL query mode (MDB)
http://office.microsoft.com/en-us/access/HP030704831033.aspx
"In Microsoft Access 2000 using ADOX, you could programmatically
create queries that used ANSI-92 SQL syntax. However, any queries you
created were not visible in the Database window because there was no
option to set this mode in the user interface. Now in Access 2002 or
later, you can set the ANSI SQL query mode through the user interface
for the current database and as the default setting for new
databases."

In Access2007 (and Access 2003 IIRC) this mode is the default. While
in this mode, the OP's code would run from a Query object in the
Access user interface (subject to it being syntactically correct, of
course).

Allen, I've seen you mention a number of times that you think the
Access user interface uses DAO to execute the SQL in Query objects.
Can you post a link to any KB article etc that would substantiate this
position? TIA.

I don't think your position is the correct one, however. DAO in Access
2002 did not support ANSI-92 Query Mode syntax therefore we must
conclude that the Access user interface could only use DAO some of the
time (if at all) and presumably ADO (or something else) at other
times.

[FWIW my own view is that the Access interface uses the Jet DLLs
directly but I don't have anything to substantiate my position <g> ]

Jamie.

--

 

[Jamie Collins]

Does ACCESS not support
the ON UPDATE/ON DELETE options at all?

You need to be in ANSI-92 Query Mode to be able to execute SQL DDL
with a REFERENCES clause.

As others here seems to agree, prehaps the easiest way is to use an
ADO connection because the OLE DB providers only support ANSI-92 Query
Mode. However, you can indeed open you mdb in this mode in the Access
interface to be able to run the SQL from a Query window. See:

About ANSI SQL query mode (MDB)
http://office.microsoft.com/en-us/access/HP030704831033.aspx

The referential actions that Jet supports are NO ACTION (default),
CASCADE and SET NULL. Be warned, though, that ON UPDATE SET NULL is
not supported, despite the Access Help suggesting that it is.

Jamie.

--