Hi. What you ask is possible, though not easy to achieve. Could I ask you to consider why you should delete such data at all?
It is not normal practice with relational databases to deliberately and permanently remove customer records from a database when the account is no longer active. Losing data like this means you will never be able to answer queries such as 'how many customers are no longer active with our business'; or 'how many customers have closed their accounts with us in the past year', say. You will not be able to mailshot these clients, or send advertising fliers out saying how much you would welcome their business.
A different approach which I take myself in a Human Resources context for personnel who have left the business is to include a status field in the person record - the client record in your case. A simple boolean called 'current' would do, along with an exit date to show when the person's account was closed. You change current to False for customers who are no longer active, and populate the leaving date accordingly.
To make sure you only view active customers in your main forms and reports you would add the Status field to the underlying queries and select only the currently-active records for display or reporting.
The advantage of doing so is that once all the changes are made you only need to modify two fields in one form to record a customer as no longer active. You would continue to hold the customer records, but this is no disadvantage at all. As the customer record is involved in all your main one-to-many relationships, including the status criteria of selecting only Current records will make sure that you do not see inactive customer records, without doing any deletions at all.
Although this does not answer the question you posed, which is all about programming multiple deletes, I would urge you to consider what I have suggested above. Once data is deleted there is no going back, and personally I think the value of the lost data (even from accounts which are closed) far outweighs the minor inconvenience of classifying accounts as inactive and continuing to carry the records. As an aside, it will also ensure that there is no attempt made to re-use the unique customer ID associated with each customer.
Please consider what I have suggested above.
If you really want to pursue the deletion aspect it is possible, but Access does not provide multi-table delete facilities in SQL. This would lead to processing multiple deletes in VB code loops. Easy enough to achieve if you have a list of the table names whose records are to be deleted, stored in a temporary table say. The loop would simply process one table name after another and run a delete for each. Sample skeleton VB code for this is provided below.
- Dim RS as DAO.RecordSet
-
Dim strTableName as String
-
Dim strSQL as String
-
' Open temporary table storing table names of tables
-
' to delete records with particular customer IDs
-
' Must be ordered by innermost (many side) relationships first - don't attempt
-
' to delete records on the One side of a relationship when there exist
-
' many side relationships
-
Set RS = CurrentDB.OpenRecordset("temporary table")
-
DoCmd.Setwarnings False
-
Do while not RS.EOF
-
strTableName = RS![tablename field]
-
strSQL = "DELETE * from " & strTableName & " WHERE [your customer ID field] = " & [Your customer ID] & ";"
-
DoCmd.RunSQL(strSQL)
-
RS.Movenext
-
loop
-
DoCmd.Setwarnings True
-
RS.Close
-
-Stewart
93
