Cascade delete command (Access 2000)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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 = “F:DatabasesSomething.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.

Reply | Quote

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

Reply | Quote

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:

Index Name	Field Name	Sort Order
PrimaryKey	OrderID	Ascending

Reply | Quote

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);”

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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);”

Reply | Quote

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:

Index Name	Field Name	Sort Order
PrimaryKey	OrderID	Ascending

Reply | Quote

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

Reply | Quote

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 = “F:DatabasesSomething.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.

Reply | Quote

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

Reply | Quote