VBA Delete table module

[tekquest]

Hi all,

I have an access database (97) and I have a main table (Rectrans) and several child tables (ADV, CBL etc)

Basically, I want to be able to delete records that are more than 7 years old from the last date in the Rectrans table, and any related records in the child tables.

Here is the code:

------

Private Sub mybutton12355_Click()

Set Db = CurrentDb()

Dim strSQL As String
'
strSQL = "DELETE FROM RecTrans WHERE " & _
"ContractNumber IN SELECT " & _
"RECTRANS.ContractNumber " & _
"FROM RECTRANS " & _
"GROUP BY RECTRANS.ContractNumber " & _
"HAVING (((DateAdd('yyyy',7,Max([RECTRANS].[TransactionDate])))<Now())));"
'

strSQL = "DELETE FROM ADV WHERE ContractNumber NOT IN (Select ContractNumber " & _
"FROM Rectrans);"
DoCmd.RunSQL strSQL
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

strSQL = "DELETE FROM CBL WHERE ContractNumber NOT IN (Select ContractNumber " & _
"FROM Rectrans);"
DoCmd.RunSQL strSQL
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

strSQL = "DELETE FROM LEASE WHERE ContractNumber NOT IN " & _
"(Select ContractNumber FROM Rectrans);"
DoCmd.RunSQL strSQL
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

strSQL = "DELETE FROM PL WHERE ContractNumber NOT IN " & _
"(Select ContractNumber FROM Rectrans);"
DoCmd.RunSQL strSQL
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

strSQL = "DELETE FROM HP WHERE ContractNumber NOT IN " & _
"(Select ContractNumber " & _
"FROM Rectrans);"
DoCmd.RunSQL strSQL
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

Set Db = Nothing

End Sub

------

Bascially, when I put my clock forward 7 years, it should delete several records (I have checked this via a select query) but it doesn't, it just says "you are about to delete 0 records"

Can someone please verify my code?

Thanks

 

[Fornatian]

You don't need to do this in VBA at all. If your relationships are set up with cascade deletes, all child table records will automatically be deleted.

As for identifying records older than 7 years, this SQL will do the trick

SELECT DateSample.MyDate
FROM DateSample
WHERE (((DateSample.MyDate)<=DateAdd("yyyy",-7,(SELECT Max(DateSample.MyDate) FROM DateSample;))));

DateSample is the table
MyDate is the field to compare

 

[tekquest]

Can't do cascade deletes, the initial designer made a big mistake, there is now data in the master table and child tables that are different, it also cant be deleted...

So this is really the only way to go.

 

[Pat Hartman]

If there is data in the child table that has no parent, there is no logical way to get to it. It can't be showing in any subforms or subreports because it is orphan data. It is really MUCH better to correct the data. Then you can set RI to prevent new orphans from being created and cascade delete to delete all related records with a single query.

If you feel uncomfortable deleting the orphans, then create dummy parents.

 

[tekquest]

Dear Pat,

I have not been able to add referential integrity, let me explain the databases structure and why RI will never work.

there is a main table called "RECTRANS" which holds all the records contract details, then there are 7 child tables, each contain different types of contracts, for example:

ADV is all contract details (e.g. name, address, telephone details) and ADV it has a reference to the Rectrans table, based on the contract number.

CBL is another child table, that has many records with name, addresses, etc, and a relationship to rectrans.

I can only add a relationship for each of these child tables if they have the same data, Rectrans would have an entry for CBL (B1234) and the CBL table would have this contract also.

ADV would not have any reference to B1234, but a different naming structure, for example: A1234, so when I try to add RI to anything, it errors with violation rules, unless i delete everything thats not related to ADV, so contracts in Rectrans and ADV match.

This is the reason why the VBA statement will have to manually delete these records and then check each of the child tables for data that it is missing.

So basically, I need the VBA statement to delete the records from the Rectrans table, then look over the child tables for missing entries...


Thanks,

Luke

 

[Pat Hartman]

You still haven't explained why you cannot fix the data. How can it be valid to have orphan records in the ADV table? Also, if there is only one many-side table with a problem, establish RI and cascade delete on all the others. Your delete queries all link to the RecTrans table on ContractNumber so I don't understand why you can't make a relationship on that field.

Why are you running each query twice?

Fix your delete query. You were missing the * following the Delete and there is no reason to use an aggregate function to select the rows:
strSQL = "DELETE * FROM RecTrans WHERE DateAdd('yyyy',7,[RECTRANS].[TransactionDate]) < Date();"

If you insist on doing this the hard way, you should resequence the events. Make a select query that selects the rows to be deleted from RecTrans. Then join that query to each of the related tables in a delete query. Once all the many-side records are deleted, you can then run the delete query against RecTrans.

FYI
1. It is more efficient to store your queries as querydefs rather than creating them with VBA. The queries are not dynamic (they don't change at runtime) and so there is no reason to not store them.
2. Subselects are not optimized well in Jet. Joins are far superiour.
3. Since you are not using a single query to accomplish all the deleting, you leave yourself open to partial updates. You should be using a Transaction around the set of delete queries so that if one fails, all are rolled back and nothing is deleted. That is what would happen if you were doing this properly with cascade delete. Jet would ensure that the entire transaction was complete before committing it.