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.

 

[RoyVidar]

Enter a module, use the Tools menu, click references. Browse to find Microsoft DAO 3.6 Object Library.

Roy-Vidar

 

[RoyVidar]

Oups, not only find, check it to;-)

 

[tekquest]

hi roy,

its ticked, is that all i need to do?

Thanks for the ultrafast reply!!!!

 

[tekquest]

so in real terms, its not a &quot;reference as such&quot; its just a selection of a global item?

 

[RoyVidar]

'nother oups, where to place the code.

You need to &quot;put it in a sub&quot; - i e before the whole code (before the Dim db... statement), you'l need a line stating:

Sub YourSubName()

and after the code:

End Sub

You should also try Debug | Compile. If that's not giving any errors - i hope the redness in both code and face diminishes;-)

Next step would be calling the code from somewhere, but do first try these steps and then report back.

HTH Roy-Vidar

 

[RoyVidar]

Didn't see your last posting before my last posting.

What DAO refers to, is Data Access Objects, which in general terms would be &quot;ways of getting info to and from tables/recordsets&quot;. Objects with corresponding methods for manipulating recordsets. The DAO library was default in Access 97. Later versions uses ADO (Active Data Objects) by default, which has somewhat different syntax.

Therefore - &quot;checking&quot; DAO, makes this library available in Access 2000+

Lots of the code samples on this site uses DAO, since there are lot of A97 users, lot of users of later versions comfortable with using the &quot;old&quot; library...

Roy-Vidar

 

[tekquest]

Hi Roy,

This is an Access 97 db, just to let you know now.

I try to run the code and it has &quot;variable not defined&quot;, it has this error on the line *** (I've put next to the code)

--------------

Sub mybutton12355()

Dim Db As DAO.Database
Dim strSQL As String
'
strSQL = &quot;DELETE FROM RecTrans WHERE ContractNumber IN SELECT &quot;
***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&quot;
FROM Rectrans);
Db.Execute strSQL

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

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

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

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

Set Db = CurrentDb

End Sub

--------------

Basically, there is a &quot;main table&quot; that holds all the contract numbers and their dates, and several &quot;child&quot; tables that hold other details (including the contract numbers that pertain to each table), but are referenced back by a relationship to the main table.

I had a query that would look over the main table (rectrans) and find records (based on the max date) and add +7 years to them, and if the computers clock was more than 7 years old from the last date, it can delete it!

This worked, but it wasn't deleting the related records from the child tables, I cannot add referential integrity + cascade deletes due to an initial design flaw which can't be undone.

Thats why this approach is more suitable.

I would appreciate any ideas you may have, but I am completely stuck!!

Thanks for your help so far, its much appreciated.

Luke

 

[RoyVidar]

Hi!

VBA (VB) is a programming language that interprets line by line before excecutioning the code. The code someone has provided for you, would probably work, had the SQL-string been placed on one line, in stead of &quot;breaking them up with ENTER&quot;.

Most of us, prefer having the SQL strings on more lines, to enhance the readibility of it. To have VBA understand that &quot;several lines&quot; are to be interpretated as one line, one needs to use &quot;linebreaking&quot; symbols. In VBA this is underscore (_).

So, in the code below, the sql-strings are &quot;tweaked&quot; to be interpreted as a single line. Note that since this is text (enclosed with double quotes), you also need the textconcationation symbol ampersand (&). (As you could do in Excel, having for instance FirstName in cell A1 and LastName in B1, putting the formula '=B1 & &quot;,&quot; & A1' in cell C1, would provide 'Doe, Jane' as result.)

BTW - the set db = currentdb() in the bottom of your code, should read
set db = nothing (as in the original) Why?

Although Access is said to be able to clear object references when code excecution is finished, it dos not always do so. Therefore it's considered good praxis to &quot;turn of the lights manually before leaving&quot; -> setting all declared/instanciated object references to nothing. (set db = currentdb() instanciates an object of the type DAO.database, with the value of the current database (currentdb()), you might consider it to be a handle, with which you can access the execute method of the databaseobject (or you could have opened a DAO.recordset...).

Sub mybutton12355()

Dim Db As DAO.Database
Dim strSQL As String
'
strSQL = &quot;DELETE FROM RecTrans WHERE
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;
'
Db.Execute strSQL

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

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

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

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

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

Set Db = nothing

End Sub

Disclaimer; since this is provided on a Sunday, I haven't run this thru Access VBA. There might be some more tweaking with the quotes, but please report back;-)

Roy-Vidar

 

[tekquest]

Hi Roy,

Thanks for that, much appreciated. The reason I put &quot;setdb = current db&quot; is because I was trying to get it to work! lol.

I get a compile error on the line:

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;

The rest is no longer in red, so I am guessing this is reading the lines correctly now!!!

Thanks once again, appreciate your time.

 

[RoyVidar]

Yep1 - you'll need the set db=currentdb() at the top of the routine (forgot that in my previous post). In the bottom set db =nothing.
Yep2 - &quot;There might be some more tweaking with the quotes, but please report back&quot;

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;

The line was a bit to long to fit - basically

strsql = &quot;some selects and stuff &quot; & _
&quot;more text bla bla bla &quot; & _
&quot;løsdkjløsdkjflsdkfj &quot;

As stated - for strings to be interpreted as one line every line that are to be concatinated whith the next line, needs the linebreak symbol (_) and textconcatination symbol (&)

Roy-Vidar

 

[tekquest]

Hi Roy,

Ok, I have made the modifications to the code, and this is what I have now, the form also has a button / reference to this code.

Private Sub mybutton12355_Click()

Set Db = CurrentDb()

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;
'
Db.Execute strSQL

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

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

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

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

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

Set Db = Nothing

End Sub

When run, it gives a run time error 3075, &quot;in operator without () in query expression 'contractnumber IN SELECT RECTRANS.Contractnumber FROM RECTRANS'.

Then when I go to debug the line &quot;db.execute strSQL&quot; is highlighted in yellow.

thanks again for the ultrafast reply!!!

 

[RoyVidar]

Oups - that must be my typo (again);-(

I must have deleted a start parantheses when working with your code.

strSQL = &quot;DELETE FROM RecTrans WHERE &quot; & _
&quot;ContractNumber IN (SELECT &quot; & _
&quot;RECTRANS.ContractNumber &quot; & _
&quot;FROM RECTRANS &quot; & _
etc...

Sorry.

Roy-Vidar

 

[tekquest]

hi roy,

don't be sorry, i am more than thankful that your taking the time to help me!!!

i have inserted the piece of code above, and now when i press the button (when i compile the module there are no errors) it doesnt do anything.

I've also put my clock forward to 2012 as it addes 7 years to the last contract date, there are many in there that have end dates of 01.02.2000 so these should delete.

here is the code, I have added &quot;docmd.setwarnings (true)&quot; to see if it does anything, but it doesn't.

------

Private Sub mybutton12355_Click()

Set Db = CurrentDb()

DoCmd.SetWarnings (True)

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;
'

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

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

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

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

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

Set Db = Nothing

End Sub

-------

any ideas?

 

[RoyVidar]

he he - I've never used db.execute before. Give me some moments to check it out. Roy-Vidar

 

[tekquest]

Thank you mate

 

[RoyVidar]

I've made it work a couple of times, but I'm not getting the results I want. There are some examples in the help files if you want to chekc it out, but... (in case of errors, it wasn't enough to use the ordinary error object, one ha to use the dbengine.errors collection)

I always use a simpler approach:

docmd.runsql strSQL

That works! If you'l use it simply replace all db.execute thingies with the docmd thingie.

When testing, I always keep the warnings on.

If you get any more errors now, I'd just try putting '*' in the sql strings:

&quot;Delete * from...&quot;

Afterwards, I add the
docmd.setwarnings false
docmd.runsql strSQL
docmd.setwarnings true

above and below every runsql statement.

HTH Roy-Vidar

 

[tekquest]

Hi Roy,

I have added those lines of code in, now it pops up with a question &quot;you are about to delete 0 records from 0 fields&quot;

now i know this isnt an error message, but for some reason it doesnt seem to be attempting to delete any records, even with the date in 2012, the select query shows all the data to be purged (which is a seperate item all together)

here is the new updated code:

------

Private Sub mybutton12355_Click()

Set Db = CurrentDb()

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;
'

strSQL = &quot;DELETE FROM ADV WHERE ContractNumber NOT IN (Select ContractNumber &quot; & _
&quot;FROM Rectrans);&quot;
DoCmd.RunSQL strSQL
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.RunSQL strSQL
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.RunSQL strSQL
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.RunSQL strSQL
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.RunSQL strSQL
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

Set Db = Nothing

End Sub

------

thanks again for everything, you have been more than helpful!! :-D

 

[RoyVidar]

This is an area in which I'm far from fluent. Whenever I, after a lot of trouble, can't make a VBA sql string query work but have it working as an ordinary query, I run the stored query in stead;-)

docmd.openquery &quot;qryNameOfDeleteQuery&quot;, acviewnormal

Try posting the question in a new thread in forum701.

It's been a rather long &quot;reference in DAO&quot;;-)

And now, since you're using docmd.runsql (and perhaps .openquery), you can remove all thingies with db/database.

Roy-Vidar

 

[tekquest]

Thanks mate for all your help, I have posted a question in the forum 701 and made a ref to you.

thanks again for everything, appreciate you taking the time out to help me,

Best regards,

Luke