Adding a reference to a DAO 2

[tekquest]

Hi all,

I have been given a piece of code, and advised that in order to get it to work, I need to add a reference to my "DAO" in access... (my face when I read this > :-S??)

Now, i am not an advanced VBA programmer and i dont even think you would call me a beginner.

Can anyone advise me as to how and where i would add the below code to my access module?

----------

Dim Db as DAO.Database
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())));&quot;
'
db.Execute strSQL

strSQL = &quot;DELETE FROM ADV WHERE ContractNumber NOT IN (Select ContractNumber
FROM Rectrans);
Db.Execute strSQL

strSQL = &quot;DELETE FROM CBL WHERE ContractNumber NOT IN (Select ContractNumber
FROM Rectrans);
Db.Execute strSQL

strSQL = &quot;DELETE FROM LEASE WHERE ContractNumber NOT IN (Select
ContractNumber FROM Rectrans);
Db.Execute strSQL

strSQL = &quot;DELETE FROM PL WHERE ContractNumber NOT IN (Select ContractNumber
FROM Rectrans);
Db.Execute strSQL

strSQL = &quot;DELETE FROM HP WHERE ContractNumber NOT IN (Select ContractNumber
FROM Rectrans);
Db.Execute strSQL

Set Db = Nothing

----------

I tried pasting this into my module, but it had all sorts of errors and most of the lines were red.

Thanks for reading, appreciate your time.

 

[tekquest]

Roy,

Is it possible to make the first statement:

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

Do the deletions first? then analyse the rest of the table for related records to be deleted???

Thanks!! :-D

 

[RoyVidar]

Hi!

I've gone thru the code again, done some corrections - NOTE from a programming perspective, removing the DAO/Database references. Changed the number of docmd's, adding a parantheses (as discussed earlyer)

Yes - after each sql string is assigned, you must perform a delete, which is done thru docmd.runsql strSQL

To check what really happens (or not), I usually place a comment on the warning false statements.
'docmd.setwarnings false

This is to see that it performs what it should.

Now, if it doesn't work, I suspect it's because of the queries. That's, as previously posted, not something I feel &quot;fluent&quot; enough in to assist with.

Roy-Vidar


Private Sub mybutton12355_Click()

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


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

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

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

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

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

End Sub

 

[tekquest]

Roy,

Got it,

strSQL = &quot;DELETE FROM RecTrans WHERE &quot; & _
&quot;ContractNumber IN (SELECT &quot; & _
&quot;RECTRANS.ContractNumber &quot; & _
&quot;FROM RECTRANS &quot; & _
&quot;GROUP BY RECTRANS.ContractNumber &quot; & _
&quot;HAVING (((DateAdd('yyyy',7,Max([RECTRANS].[TransactionDate])))<Now())));&quot;
DoCmd.RunSQL strSQL
DoCmd.SetWarnings False

Thanks heaps, works perfectly!!!