Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Nov 2001
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cascade delete command (Access 2000)

    cascade delete related records


    I have to link two tables in a remote db and create relationships.I also need to
    set up the three requiremenets for the referential ingerity:

    1. enforce referential integrity
    2. cascade update related fields
    3. cascade delete related records

    I have suceeded with the first two, but i could not succeed with number 3, namely
    cascade delete related records.For this purpose i have set the following relationship:
    Set rel1 = .CreateRelation("ClientIDRelationship", tdf1.Name, tdf2.Name, dbRelationDeleteCascade)

    But somehow it doesnt work.The third box in the relationship window remanis
    blank after i have carried out the function. It follows that my command for
    cascade delete related records is wrong.
    Can somebody help me ?

    Below is my function


    Public Function LinkCallsClients()
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim db As Database
    Set db = CurrentDb

    Dim rel As DAO.Relation
    Dim rel1 As DAO.Relation
    Dim tdf1 As DAO.TableDef
    Dim tdf2 As DAO.TableDef
    With db
    Set tdf1 = .TableDefs!TblClients
    Set tdf2 = .TableDefs!CallsClients
    Set rel = .CreateRelation("ClientIDRelationship", tdf1.Name, tdf2.Name, dbRelationUpdateCascade)
    Set rel1 = .CreateRelation("ClientIDRelationship", tdf1.Name, tdf2.Name, dbRelationDeleteCascade)

    rel.Fields.Append rel.CreateField("ClientID")
    rel.Fields!Clientid.ForeignName = "ClientID"
    .Relations.Append rel
    .Close
    End With
    End Function

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cascade delete command (Access 2000)

    What you are doing in your code is trying to create two different relationships, one (rel) with cascading updates, and another (rel1) with cascading deletes. But what you want is one relationship with both properties. To do this, you add the attributes together.

    Note: you write that you want to link tables in a remote database, but you use CurrentDb, that is the current database. There is no point in closing the current database after finishing the code, but you should always set object variables to Nothing. This frees the memory they used.

    Public Function LinkCallsClients()
    Dim db As DAO.Database
    Dim rel As DAO.Relation

    Set db = CurrentDb
    ' Create one relation with attibutes added together
    Set rel = db.CreateRelation("ClientIDRelationship", "TblClients", "CallsClients", _
    dbRelationUpdateCascade + dbRelationDeleteCascade)
    ' Set join fields
    rel.Fields.Append rel.CreateField("ClientID")
    rel.Fields!ClientID.ForeignName = "ClientID"
    ' Append relation
    db.Relations.Append rel

    ' Clean up
    Set rel = Nothing
    Set dbs = Nothing
    End Function

  3. #3
    2 Star Lounger
    Join Date
    Nov 2001
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cascade delete command (Access 2000)

    Many thanks. Just to let you know that my db works perfectly now ! Vey grateful about that.

  4. #4
    2 Star Lounger
    Join Date
    Nov 2001
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cascade delete command (Access 2000)

    I am very happy and successful with my new function.Actually i use it for remote database and not for the current.
    I wonder could i further simplify it. I use different functions for relating different tables.I see that i always repeat dimming the db , the password,
    and i always open the database in one and the same place, named by me as Bepath.
    Is it possible to refer to these as constant or as a separate function,
    and then to remain only the settings of the relations?

    Below is my whole function


    On Error Resume Next
    Dim db As DAO.Database
    Dim wsp As DAO.Workspace
    Dim StrPassword As String
    StrPassword = "classified"
    Set wsp = DAO.DBEngine.Workspaces(0)
    Dim rel As DAO.Relation
    Set db = wsp.OpenDatabase(BEpath, False, False, ";PWD=" & StrPassword)

    Note : i repeat the above lines in all my other functions


    ' Create one relation with attibutes added together
    Set rel = db.CreateRelation("ClientIDRelationship", "TblClients", "CallsClients", dbRelationUpdateCascade + dbRelationDeleteCascade)
    'Set join fields
    rel.Fields.Append rel.CreateField("ClientID")
    rel.Fields!Clientid.ForeignName = "ClientID"
    ' Append relation
    db.Relations.Append rel
    ' Clean up
    Set rel = Nothing
    Set db = Nothing
    End Function

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cascade delete command (Access 2000)

    You can make db into a public variable, and BEPath into a public constant. To do so, declare them at the top of a standard module, not inside a procedure or function:

    Public db As DAO.Database
    Public Const BEPath = "FatabasesSomething.db"
    Public Const strPassWord = "classified"

    Create an initialize routine and a destroy routine:

    Public Sub InitializeDb()
    Set db = OpenDatabase(BEpath, False, False, ";PWD=" & strPassword)
    End Sub

    Public Sub DestroyDb()
    db.Close
    Set db = Nothing
    End Sub

    Call InitializeDb once, before your other functions, and call DestroyDb when you're finished. You don't need to declare and set db and strPassword in each function.

  6. #6
    2 Star Lounger
    Join Date
    Nov 2001
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cascade delete command (Access 2000)

    Thank you very much for your advice which i followed again sucessfully.
    I wonder could you help me with a specific table called order details.
    This table lies between the tables "orders" and the table "products".
    The two functions seem not to work together and mostly the link between products and order details is not realised, and i get only the link between orders and order details.
    The table order details contains no primary key, although my colleagues say
    i should have two primary keys, for orderid and for productid.Is it so?
    My question is, how get i get both functions work?



    Public Function LinkProductsAndOrderDetails()
    Set rel = db.CreateRelation("CustomerIDRelationship", "products", "order details", dbRelationUpdateCascade + dbRelationDeleteCascade)
    rel.Fields.Append rel.CreateField("ProductID")
    rel.Fields!Productid.ForeignName = "ProductID"
    ' Append relation
    db.Relations.Append rel
    End Function
    Public Function LinkOrdersAndOrderDetails()
    Set rel = db.CreateRelation("CustomerIDRelationship", "orders", "order details", dbRelationUpdateCascade + dbRelationDeleteCascade)
    rel.Fields.Append rel.CreateField("OrderID")
    rel.Fields!Orderid.ForeignName = "OrderID"
    ' Append relation
    db.Relations.Append rel
    End Function

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cascade delete command (Access 2000)

    Your problem is mainly that you are giving both relationships the same name "CustomerIDRelationship". Give each relationship a unique (and preferably meaningful) name.

    Your Order Details table should have a composite primary key, that is a key on the combination of two fields: OrderID and ProductID:

    <table border=1><td>Index Name</td><td>Field Name</td><td>Sort Order</td><td>PrimaryKey</td><td>OrderID</td><td>Ascending</td><tr><td>

  8. #8
    2 Star Lounger
    Join Date
    Nov 2001
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cascade delete command (Access 2000)

    It works so nice now !! About the order details table, how can i add the composite key in the table order details through code?I can only do it
    for the first key, but Access does not allow me to put the second key.Perhaps there is
    a command for the composite key?

    dbs.Execute "ALTER TABLE [order details] ADD CONSTRAINT PrimaryKey Primary Key(orderID);"
    dbs.Execute "ALTER TABLE [order details] ADD CONSTRAINT PrimaryKey Primary Key(ProductID);"

  9. #9
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Cascade delete command (Access 2000)

    If you want to add a multiple-field Primary Index to an existing table, the syntax is:

    ALTER TABLE OrderDetails ADD CONSTRAINT PrimaryKey PRIMARY KEY (OrderID, ProductID);

    For more details, look up ALTER TABLE Statement and CONSTRAINT Clause in Access Help under MS Jet SQL Reference general topic, Data Definition Language (DDL) subtopic.

    HTH

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •