Ms Access Set Cascade-to-null Constraint To Existing Table

May 08, 2024 Post a Comment

Using a MS Acess 2007 database accessed by vb.net application I have two existing table Members ------- ID name bandID ----------------------- 0 Pierre 1 1 Char

Solution 1:

You can create this kind of constraint in Access, but only through the Jet OLE DB Provider and ADO. For example, with the database in Access, you could create the constraint by running the following VBA code:

CurrentProject.Connection.Execute "ALTER TABLE membres ADD CONSTRAINT membresBands_FK FOREIGN KEY (bandID) REFERENCES bands(ID) ON DELETE SET NULL"

Solution 2:

I had to wait eight hours to post this...

Using a visual basic module

'Define the bit value for the relation Attributes.PublicConst dbRelationCascadeNull AsLong = &H2000PublicFunction MakeRel()
    'Purpose: Create a Cascade-to-Null relation using DAO.Dim db As DAO.Database
    Dim rel As DAO.Relation
    Dim fld As DAO.Field

    Set db = CurrentDb()
    'Arguments for CreateRelation(): any unique name, primary table, related table, attributes.Set rel = db.CreateRelation("membre_bands", "bands", "membres", dbRelationCascadeNull)
    Set fld = rel.CreateField("ID")  'The field from the primary table.
    fld.ForeignName = "band"'Matching field from the related table.
    rel.Fields.Append fld                    'Add the field to the relation's Fields collection.
    db.Relations.Append rel                  'Add the relation to the database.'Report and clean up.
    Debug.Print rel.Attributes
    Set db = NothingEndFunction

then call the MakeRel function

function found on http://allenbrowne.com/ser-64.html

Solution 3:

AFAIK, there is NO Cascade to Null in Access. Only Cascade Delete and Cascade Update.

Solution 4:

Options for cascading effects in various DBMS are:

ON DELETE SET NULL

ON DELETE CASCADE

ON DELETE RESTRICT

ON DELETE NO ACTION

I think MS-Access has the first two. So, it should be:

ON DELETE SET NULL

Solution 5:

The docs for Access say that it supports the following referential triggered actions:

ONDELETE CASCADE
ONUPDATE CASCADE
ONDELETESETNULLONUPDATESETNULL

...However, in practice it only supports the first three i.e. does not support ON UPDATE SET NULL. To further clarify, the engine does not support the ON UPDATE SET NULL referential triggered action at all i.e. not just the DDL syntax.

Lab Sql