SQL DDL

[Guest]

I have a script to create tables and relationships. It works fine when I run
it using Query Analyzer in SQL Server. However, Access seems to choke on DDL
that attempts to both create a table and set a relation in one SQL statement.
For example, I have found that running just this much as one query chokes in
Access:

CREATE TABLE Senators (
SenatorID INTEGER NOT NULL PRIMARY KEY,
SenatorName VARCHAR(100) NOT NULL,
DistrictID INTEGER NOT NULL UNIQUE,
CONSTRAINT fk__senators__districts FOREIGN KEY (DistrictID)
REFERENCES Districts (DistrictID)
ON UPDATE CASCADE ON DELETE CASCADE
)
;

When I break it up into two DDL queries, it seems to work okay, however, I
have not quite figured out how to get the last line to work, which sets
Cascade Update and Cascade Delete:

CREATE TABLE Senators (
SenatorID INTEGER NOT NULL PRIMARY KEY,
SenatorName VARCHAR(100) NOT NULL,
DistrictID INTEGER NOT NULL UNIQUE
)
;

followed by:

ALTER TABLE Senators ADD CONSTRAINT fk__senators__districts FOREIGN KEY
(DistrictID) REFERENCES Districts (DistrictID);

works in Access.

What's the correct syntax for specifying Cascade Update and Cascade Delete?

Thanks in advance

 

[Guest]

Hi, Joe.

What's the correct syntax for specifying Cascade Update and
Cascade Delete?

Believe it or not, you’re using the correct syntax and Jet 4.0 supports it,
but not in the SQL View pane. You’ll have to use the ADO library commands in
VBA to create the SQL string to execute, then execute the command. For
example:

CurrentProject.Connection.Execute "ALTER TABLE Senators " & _
"ADD CONSTRAINT FK_Senators_Districts " & _
"FOREIGN KEY (DistrictID) REFERENCES Districts (DistrictID) " & _
"ON UPDATE CASCADE " & _
"ON DELETE CASCADE;"

DAO doesn’t seem to work correctly for this. You may want to try something
like this air code if you need DAO, instead:

db.Relations!MyRelation.Attributes = dbRelationDeleteCascade AND _
dbRelationUpdateCascade


HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of rewarding
the contributors who have taken the time to answer questions correctly.