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.

 

[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