Loop to delete record(s) in multiple tables Access 2003

[clloyd]

I have a database with over 20 tables. I need to delete one to many record(s) in all tables by using a loop code. Some tables with have one record, multiple records or no records for the criteria given. I can not do cascade delete because there are to many tables and I don't want to write 20 plus delete queries. Does anyone have any ideas how to accomplish this.

[epots9]

Moved to the Access Forums where the resident experts can better assist you.

**Moved from Programming Challenges.

[shenkel55]

I'm new here... I answered the wrong question and I am trying to delete this post

[shenkel55]

I have a database with over 20 tables. I need to delete one to many record(s) in all tables by using a loop code. Some tables with have one record, multiple records or no records for the criteria given. I can not do cascade delete because there are to many tables and I don't want to write 20 plus delete queries. Does anyone have any ideas how to accomplish this.

Ok clloyd, maybe I didn't answer the wrong question yesterday. When you say "one to many records," are you referring to a relationship between the tables or the quantity of records you are wanting to delete in each table? Could you please post just a little more information? I may have a simple answer for you.

[clloyd]

Ok clloyd, maybe I didn't answer the wrong question yesterday. When you say "one to many records," are you referring to a relationship between the tables or the quantity of records you are wanting to delete in each table? Could you please post just a little more information? I may have a simple answer for you.

Sorry I was on other projects. In all the tables there is a client number that is the the relationship. In some tables there will be only one record for that client while in others there could be 50 records. When a I need to close a client record I want to delete everything in that database connected to that unique client number. Thanks

[Stewart Ross]

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.

[code=vb]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.OpenR ecordset("tempo rary table")
DoCmd.Setwarnin gs False
Do while not RS.EOF
strTableName = RS![tablename field]
strSQL = "DELETE * from " & strTableName & " WHERE [your customer ID field] = " & [Your customer ID] & ";"
DoCmd.RunSQL(st rSQL)
RS.Movenext
loop
DoCmd.Setwarnin gs True
RS.Close
[/code]
-Stewart

[clloyd]

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.

[code=vb]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.OpenR ecordset("tempo rary table")
DoCmd.Setwarnin gs False
Do while not RS.EOF
strTableName = RS![tablename field]
strSQL = "DELETE * from " & strTableName & " WHERE [your customer ID field] = " & [Your customer ID] & ";"
DoCmd.RunSQL(st rSQL)
RS.Movenext
loop
DoCmd.Setwarnin gs True
RS.Close
[/code]
-Stewart

Thank you for the suggestion. This is an odd Database in that it is a current picture so to speak. We don't keep historical data for this database. If a location were to come back we would re-enter everything new as there could be a lot of differences. It is however rare that that happens. Thank you however for the above code.