Access and SQL - Best Practices for Allowing Deletes?

[misscrf]

I know this is a pretty general topic, but I hope no one will get mad at me for asking this question here. I have an application and because of how it will be used, I want to make sure it is set up in a way that has tight control over what records people can delete, and which combo values can be added to, or not.

This application is a SQL database, with an Access front-end, for data entry. The only Access "reporting" I do, is a sort of build your own report form, with listboxes for optional filtering, and 1 listbox for choosing which fields to select and group by. When the report is run, it is actually a query that appears in a subform object, so they can sort, re-filter, and copy to excel or email. The rest of the reporting is SSRS.

The main question I'm turning over is that there are 76 combos in this app. I used the following code to identify them and their row source:

Public Function getCombos()

On Error Resume Next
Dim obj As AccessObject, dbs As Object
Dim ctrl As Control
Dim frm As Form

Set dbs = Application.CurrentProject

For Each obj In dbs.AllForms
DoCmd.OpenForm obj.Name, acDesign
    For Each ctrl In Forms(obj.Name).Controls
        If ctrl.ControlType = acComboBox Then
            Debug.Print obj.Name & "^" & ctrl.Name & "^" & ctrl.RowSource
        End If
    Next ctrl
DoCmd.Close acForm, obj.Name, acSaveNo
Next obj

End Function

I am working through which ones a user should be able to "maintain", which I do through a universal maintenance form. Others have a row source from another database, and cannot be changed. The values are what they are, and I use a default message in the Not In List, to tell the user that info.

Now, I want to make sure in every place, that a user can clearly understand if they can delete a record or not. I don't want a messy database, so I want users to be able to clean up bad records. I don't want them to orphan records either.

So records, in terms of deleting might be: 1) a "sub" record, or one with no "sub" records to it 2) a list of values, which may be tied to multiple records 3) a record with 1 or more sub-records. If only one, it's not hard to check that. If there are multiple, I either need a control table to tell me which ones to check, or I have to hard code which ones to check

I used this sql query, to get all the relationships:

SELECT tr.name 'pTable'
--, cr.name 'pColumn', 
,tp.name 'cTable'
--, cp.name 'cColumn', fk.name 'Index Name'
FROM sys.foreign_keys fk
LEFT JOIN sys.tables tp ON fk.parent_object_id = tp.object_id
LEFT JOIN sys.tables tr ON fk.referenced_object_id = tr.object_id
LEFT JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
LEFT JOIN sys.columns cp ON fkc.parent_column_id = cp.column_id AND fkc.parent_object_id = cp.object_id
LEFT JOIN sys.columns cr ON fkc.referenced_column_id = cr.column_id AND fkc.referenced_object_id = cr.object_id
order by tr.name

The reason I have to check them all in the VBA, is that if I let it just send the request (delete) to SQL, I will end up with ODBC referential integrity errors. Those are good errors, in the sense that they prevent a bad delete, but they are a horrible message for the user experience.

I'm just looking for any great methods people use to handle this, across an application. I have most all of the not in list code handled to either let them add values, tell the user it is a fixed list, or tell them the list comes from another system and that they must address that list with the owners of that system.

Deleting is the big one I'm working through now, and I would love some thoughts from those who have mastered how to best handle it.

Thanks!


misscrf

It is never too late to become what you could have been ~ George Eliot

 

[MajP]

This is really hard to follow. There is a combination of background information and problem statement. Can you restate very clearly what you are wanting to do. I am not sure if you are talking about deleting records at the form level or deleting records that are the rowsource of the controls. I interpret this to mean throughout the application certain records can be deleted and certain cannot. Maybe a few examples would help.

 

[Darrylles]

Personally, I don't allow deletion of ANY record; for reasons of traceability.
If the data becomes too large - auto-archive all records over x years / months old into an archive database periodically (maybe every week / month / year).

Your 'referential integrity' errors are not good, they are just fortuitous, and indicate a poor relational design or poor error trapping.

You can enable cascading deletes within your table design, this enables a 1 record delete (from a 1-many-many etc) to delete all of its children - without error, but, I would only contemplate that if it was pre-empted by a process that first archived those records.

A 'better' system in my view is (when considering the 1-many delete above), would be to re-direct the user to view (and confirm) all child records that will be deleted.
Out-of-sight is out-of-mind so-to-speak, e.g. "what you don't know can't hurt you", "shut your eyes and hope for the best".
Point is, if the user is forced to visit the records to be deleted, that user may well realise "Oh-ho - I didn't want THAT one deleted!"

So, on attempting to delete the parent record, the user will be prompted with: "You cannot delete this record as it has 10 children - do you wish to view them first or cancel?"
On viewing: they are shown a list of key information about 10 child records, which they can quickly scan through.
On confirmation thereon - it may be the case that THOSE 10 have 10 children each, which the user is ALSO warned about, and a list of 100 key fields from those records are displayed to the user in order for them to quickly scan through.

Only on confirmation to proceed, should the 'cascaded' delete happen.

A quick scan of the scope of their 'simple' deletion may make a user re-think (saving you from an embarrassing support call to have your application re-instated from tape-backup by system support (and losing all user changes since that simple, single deletion)).

This is simply my view - not 'my way or the highway'.

ATB,

Darrylle

 

[Andrzejek]

To build on Darrylle's suggestion,
I allow to Delete a record when it is not complicated, i.e. no children records. Ease to re-create the info by the User. In all other situations, I have a Status field (Defaults to 'A' - Active), so when user says: Delete, I ask: "Are you sure?" if 'Yes' I just update the Status field to 'D' (Deleted) and never show Deleted records (unless somebody really wants to see them). This way to un-delete a record or records is as simple as changing the Status field.

Just a suggestion...

Have fun.

---- Andy

There is a great need for a sarcasm font.