Referential Integrity in CREATE TABLE command

U

user01

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
 
A

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

user01

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
 
M

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

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.

--
 
J

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.

--
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top