SQL Query: ON UPDATE CASCADE

it seems Access doesn't like:
"CREATE TABLE tbl_Style (ID AUTOINCREMENT CONSTRAINT bla PRIMARY KEY, Header1 INTEGER CONSTRAINT C1 REFERENCES tbl_Logo (ID) ON UPDATE CASCADE)"

It just gives me the 'error in create statement' and points to the update keyword. (same thing with on delete cascade).

How do I do this? some other keyword in stead of UPDATE?
thanks bunch
nivenAsked:
Who is Participating?
 
DedushkaCommented:
OK,
I'll post here additional info if I found new one.
If you want to grade me this points for my hard efforts, I'll take it with greate pleasure :-)))
Have a nice day,
Dedushka
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
The short answer is no, there is no other keyword.  JET's version of SQL is not the same in many respects to ANSI SQL.  

  It's been getting closer and closer with each release, but it's still not totally there yet.

  You didn't mention version, so I can't be more specific then that.

JimD.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Just one additional note: if you using A2000 with a adp project then it's a whole different story.

JimD.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
nivenAuthor Commented:
I use Access 2000
(bunch 'O SPs installed of course, winnt5)
no adp project (whatever that is, just sql)
(which is being generated by another app.)
0
 
DedushkaCommented:
Hi,
some info from online Help:
-----------------------
This example creates a new table with two text fields and an Integer field. The SSN field is the primary key.

Sub CreateTableX3()
     Dim dbs As Database

    ' Modify this line to include the path to Northwind
    ' on your computer.

    Set dbs = OpenDatabase("Northwind.mdb")

    ' Create a table with three fields and a primary
    ' key.

    dbs.Execute "CREATE TABLE NewTable " _
        & "(FirstName CHAR, LastName CHAR, " _
        & "SSN INTEGER CONSTRAINT MyFieldConstraint " _
        & "PRIMARY KEY);"

    dbs.Close

End Sub

Syntax for constraint:

CONSTRAINT name
    {PRIMARY KEY (primary1[, primary2 [, ...]]) |
    UNIQUE (unique1[, unique2 [, ...]]) |
    NOT NULL (notnull1[, notnull2 [, ...]]) |
    FOREIGN KEY [NO INDEX] (ref1[, ref2 [, ...]]) REFERENCES foreigntable [(foreignfield1 [, foreignfield2 [, ...]])]
    [ON UPDATE CASCADE | SET NULL]
    [ON DELETE CASCADE | SET NULL]}

Consider the following definition of the table Orders, which defines a foreign key relationship referencing the primary key of the Customers table:

CREATE TABLE Orders (OrderId INTEGER PRIMARY KEY, CustId INTEGER, OrderNotes NCHAR VARYING (255), CONSTRAINT FKOrdersCustId FOREIGN KEY (CustId) REFERENCES Customers ON UPDATE CASCADE ON DELETE CASCADE
-----------------------------
Now, instead of

"CREATE TABLE tbl_Style (ID AUTOINCREMENT CONSTRAINT bla PRIMARY KEY, Header1 INTEGER CONSTRAINT C1 REFERENCES tbl_Logo (ID) ON UPDATE CASCADE)"

try to use:

CREATE TABLE tbl_Style (ID AUTOINCREMENT PRIMARY KEY, Header1 INTEGER, CONSTRAINT C1 FOREIGN KEY (ID) REFERENCES  tbl_Logo (ID) ON UPDATE CASCADE);

HTH,
Dedushka
0
 
nivenAuthor Commented:
tried that, exactly the same error :(
(error in create, update is highlighted)

Does that work with your version of access btw?
0
 
DedushkaCommented:
Just a moment, I'll try to test it...
0
 
DedushkaCommented:
First of all you want to have one-to-many relationship from tbl_Logo (where ID is primary key) to tbl_Style, so your ID field in tbl_Style can not be Autonumber and unique, it must be LONG and Foreign key, so use this:

Sub CreateTableX3()
     Dim dbs As Database

    ' Modify this line to include the path to Northwind
    ' on your computer.

    Set dbs = OpenDatabase("Northwind.mdb")

    ' Create a table
    dbs.Execute "CREATE TABLE tbl_Style " _
    & "(ID LONG CONSTRAINT C1 REFERENCES tbl_Logo, " _
    & "Header1 INTEGER);"

    dbs.Close

End Sub

The procedure creates tbl_Style and set relationship, I tested it. But unfortunately, when I try to use ON UPDATE CASCADE, I get an error. Maybe we need to create a table and then use ALTER for establish Cascade update/delete?
0
 
DedushkaCommented:
ALTER TABLE  also don't recognize ON UPDATE CASCADE, so I can propose to Use the DAO CreateRelation method instead.

Here is sample from Acc2000 online Help:
-------------
CreateRelation Method Example (MDB)

The following example creates a new Relation object that defines a relationship between a Categories table and a Products table. The Categories table is the primary table in the relationship, and the Products table is the foreign table. The CategoryID field is the primary key in the Categories table, and a foreign key in the Products table.

To test this example in the Northwind sample database, click Relationships on the Tools menu, and delete the relationship between the Categories table and the Products table. Close the Relationships window, saving the current configuration at the prompt. Run the following procedure. Then view the Relationships window again to see the new relationship.

Sub NewRelation()
    Dim dbs As Database, rel As Relation, fld As Field
   
    ' Return reference to current database.
    Set dbs = CurrentDb
    ' Create new Relation object and specify foreign table.
    Set rel = dbs.CreateRelation("CategoryProducts", _
        "Categories", "Products")
    ' Set attributes to enforce referential integrity.
    rel.Attributes = dbRelationUpdateCascade And _
        dbRelationDeleteCascade
    ' Create field in Relation object.
    Set fld = rel.CreateField("CategoryID")
    ' Specify field name in foreign table.
    fld.ForeignName = "CategoryID"
    ' Append Field object to Fields collection of Relation object.
    rel.Fields.Append fld
    ' Append Relation object to Relations collection.
    dbs.Relations.Append rel
    dbs.Relations.Refresh
    Set dbs = Nothing
End Sub
--------

Best regards,
Dedushka
0
 
nivenAuthor Commented:
That might be the problem, the createmethod works, but not when you write it out in an sql statement,which is (unfortunately) what I need...
0
 
DedushkaCommented:
Can I do something else for you?
0
 
nivenAuthor Commented:
Well, I'd like to know if Access2000 just doesn't support ansi-sql and isn't able to execute the 'on update' statements,
Or if it can be done, and if so, then how?

Frankly, I'm becoming pretty fed up with Access,
might just swith to sql-server...

Thanks anyway
0
 
DedushkaCommented:
It seems that Acc2000 don't support ON UPDATE in SQL statement. I've tried several different ways without success. Certainly you can open relationships window and manually set reference integrity constrains for cascade update and cascade delete.
BTW I never use in code data definition language (DDL)statements. All DB designing tasks (table and relationships defining, setting all restriction and constraints, etc.) I perform through database window.
Are you absolutely need to set ON UPDATE CASCADE using DDL?

Thank you for good question,
best regards,
Dedushka.
0
 
nivenAuthor Commented:
I'm afraid so, our application generates tables dynamically, we don't really want to tell the users to manually set relationships :)

But it can be done in sql-server I believe,
and we're going to switch to that anyway,
it would have been nice in access...

Thanks again,
regards,
niven
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Niven,

  As I said right off the bat, the keyword is not supported in the SQL statement.  If you want to create table relationships dynamically, then you'll have to use the DAO method.

JimD.
0
 
nivenAuthor Commented:
Thanks,
I'll now go and whine to M$ :)
0
 
LStampfliCommented:
I know that this is an old post, but if you are searching and using it like I am, you might like to know that there is an error in the code sample written above.  If used as is, it will create the relation, but NOT the cascade delete option.  The line which reads:

   rel.Attributes = dbRelationUpdateCascade And _
        dbRelationDeleteCascade

Should read:

   rel.Attributes = dbRelationUpdateCascade + _
        dbRelationDeleteCascade

If you make this change, the example will do what it is supposed to do.

It is certainly quite lame that Access SQL does not support the SQL keyword ON DELETE CASCADE.

Lisa S
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.