deleting a record from a table - integrity

[sja13]

Hi Guys, it's the amateur seeking help again!

I want to delete a record from a Table, but that record might be referenced by an entry in another Table.
For example i have a Table of Locations, and a Table of Boards. Boards exist in Locations.
If my user chooses to delete a Location, I'd like to prevent them by identifying that there are Board(s) in that Location.

I faintly remember that in Access 2003, there was a setting such that "Delete Integrity" was maintained, i.e. the Deletion caused a warning that the database would be compromised.

My research suggested that

 DoCmd.SetWarnings True
 DoCmd.RunCommand acCmdSelectRecord
 DoCmd.RunCommand acCmdDeleteRecord

would achieve my aim - that of automatically generating a warning.
All it seems to de is to warn be I am about to "delete (1) records".
Can anyone point me in the right direction?
Any help would be gratefully appreciated....

 

[pbaldy]

You can set referential integrity in the relationships window.

 

[sja13]

Hi pbaldy....

I already have referential integrity at relationship level.

What I DON'T have is a functioning brain!

I set up a test whereby a Board was in a Location, then tried to delete the Board.

Of course, this worked (hence the post).

When I tried to delete the Location, it correctly said "You can't. You have references in the Board table".

One day, I'll grow up, but in the meantime I apologise for wasting your time....

 

[static]

If you turn referential integrity on you can also turn on cascade deletes.

You should always try to code around things yourself rather than leaving them to Access.

Private Sub Command0_Click()
    Dim db As DAO.Database
    Set db = CurrentDb
    
    Const locationid As Long = 1 '<-- get this value from a control

    'check if any boards exist for this location
    If Not db.OpenRecordset("select 1 from boards where locationid=" & locationid).EOF Then
        'boards with this location exist
        If MsgBox("delete this location and associated boards?", vbYesNo) = vbNo Then
            MsgBox "Delete cancelled"
            Exit Sub
        End If
    End If
    db.Execute "delete * from locations where locationid=" & locationid
End Sub

The above checks if boards exist for locations, and if so gives a warning.
If cascade deletes is turned on locations and boards are deleted.

If cascade deletes isn't turned on, you would need separate delete statements for each table (in reverse relationship order.)

 

[pbaldy]

One day, I'll grow up, but in the meantime I apologise for wasting your time....

No worries, glad you got it sorted out.

 

[MrHans]

Hi Static, why do you choose this method (open recordset and test EOF) instead of a dcount or dlookup?

And don't you have to explicitly close the recordset as well?

Just wondering.
Thx

 

[The_Doc_Man]

The BEST way to handle this is to add a flag to any table in which a record could become obsolete. Then if you set the flag rather than deleting the record, you totally maintain integrity. And, when you do any JOIN queries, you can include a WHERE clause to tell it to ignore obsoleted records.