Referential Integrity on Linked Tables

[Ray]

I have a db with multiple linked tables. As new features have been added I
have created new dbs with the additional tables required and have linked them
to the original.

Lets call the original front end DB1
The first set of linked tables are in DB2
The 2nd set of linked tables are in DB3

I have a relationship between records in DB2 and records in DB3 that is
defined in DB1 (the front end)

When I look up a record in DB1 the data is pulled from both DB2 and DB2 and
is displayed in forms and subforms. All the links work and the correct data
is displayed from both linked tables.

However, when I delete a record monly the information in DB2 is deeleted. I
need to be able to set up a cascade between DB2 and DB3.

Is there any way to do this with Access?

Any help appreciated.

Ray

 

[Rick Brandt]

I have a relationship between records in DB2 and records in DB3 that
is defined in DB1 (the front end)
However, when I delete a record monly the information in DB2 is
deeleted. I need to be able to set up a cascade between DB2 and DB3.
Is there any way to do this with Access?

You have to use code to simulate RI in that situation. You cannot enforce RI at
the database engine level across files.

 

[Ray]

You have to use code to simulate RI in that situation. You cannot enforce RI at
the database engine level across files.

Thanks for thye answer.

I am not an accomplished coder though .. perhaps you could help.

I currently have this code driven from a form button. The key (ID no) is on
the form where the DELETE buton is clicked.

Private Sub Command23_Click()
On Error GoTo Err_Command23_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
DoCmd.Close

stDocName = "members"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command23_Click:
Exit Sub

Err_Command23_Click:
MsgBox Err.Description
Resume Exit_Command23_Click

End Sub

I would think I need to add code above the

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
DoCmd.Close

portion to select the recordset (db and rablke??) and use the GoToRecord
action to find the record whose key matches the key present in my form, and
delete the record.

Would all that work?

The help for the GoToRecord action

"DoCmd.GoToRecord acDataForm, "Employees", acGoTo, 7" is pretty confusing to
me.

It looks like I have to use a numeric expression to determine which record
to select but I need to find the record that matches my key.

Can you help?

RAy
"

 

[Ray]

Thanks for thye answer.

I am not an accomplished coder though .. perhaps you could help.

I currently have this code driven from a form button. The key (ID no) is on
the form where the DELETE buton is clicked.

Private Sub Command23_Click()
On Error GoTo Err_Command23_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
DoCmd.Close

stDocName = "members"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command23_Click:
Exit Sub

Err_Command23_Click:
MsgBox Err.Description
Resume Exit_Command23_Click

End Sub

I would think I need to add code above the

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
DoCmd.Close

portion to select the recordset (db and rablke??) and use the GoToRecord
action to find the record whose key matches the key present in my form, and
delete the record.

Would all that work?

The help for the GoToRecord action

"DoCmd.GoToRecord acDataForm, "Employees", acGoTo, 7" is pretty confusing to
me.

It looks like I have to use a numeric expression to determine which record
to select but I need to find the record that matches my key.

Can you help?

RAy
"

I have found a work around for this. Since I already have the form open
with the key in it, I just run a macro that stops warnings and then runs a
delete wuery with the criteria set to the key dis[layed in the form.

May be cumbersome, but it works.

Thanks for your replies.

Ray