UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Opening A Dao Recordset Multiple Times, Access 2016    
 
   
WillC
post Apr 2 2019, 08:00 AM
Post#1



Posts: 6
Joined: 2-April 19



Hi there,

My first post, so please go easy on me! ;-)

I consider myself a reasonably competent Access VBA programmer, but every so often I encounter a particular scenario that gets me thinking. It's one to which, despite extensive searching of the internet, I have never found a definitive answer. (Sincere apologies if this subject has already been discussed within this forum, in which case I'd be grateful if someone could point me in the right direction). Allow me to explain.

Imagine we have a simple loop. It could be a coded For..Next loop, a Do.. While loop, Or it could simply be a section of code that is executed each time a user presses a button on a form.

Within the loop, we generate a string for an SQL Query. We then open a DAO recordset based upon the generated SQL query. The recordset is opened on a local table within our current Access database.
The point of the loop is to change the SQL statement upon which the recordset is based, with each pass of the loop, thus enabling retrieval of a different set of data with each pass. (Yes, I know I could use a Querydef, but bear with me...)

To illustrate via a very simple example, suppose we wish to run a series of queries upon a database table that contains ordering information. We want to individually pull out product codes 1 to 5, so we execute the following code (variable declarations and other non-relevant code no shown)

'-------------------------------------------
For ProdCode = 1 to 5
Set rstOrders = CurrentDB.Openrecordset("SELECT * from TblOrders WHERE product_code = " & ProdCode & " ORDER BY order_number;", dbOpenSnapshot)
With rstOrders
Do

<Do some other stuff here...>

.MoveNext
Loop until .EOF

End With

Next ProdCode
'-------------------------------------------

My question is simply this: Should I close the recordset after each iteration of the loop? ie, in the above example, should I use 'rstOrders.Close' prior to hitting the 'Next ProdCode' statement?

The above code as it stands appears to work perfectly, with no unwanted side effects. I've examined the recordsets collection and can clearly see that the code does NOT generate multiple copies of the rstOrders recordset.

So, should I ensure that the recordset (or any recordset for that matter) is closed before reopening it with a new SQL string? Or doesn't it matter? And if it doesn't matter, then why doesn't it matter? Does VBA take care of cleaning up any loose ends behind the scenes?

Many thanks, in anticipation of an answer from someone with more experience and knowledge than myself :-)

Will.

Go to the top of the page
 
ADezii
post Apr 2 2019, 09:14 AM
Post#2



Posts: 2,553
Joined: 4-February 07
From: USA, Florida, Delray Beach


I have used this approach many times and have never had a problem with it.
Go to the top of the page
 
theDBguy
post Apr 2 2019, 09:45 AM
Post#3


Access Wiki and Forums Moderator
Posts: 76,081
Joined: 19-June 07
From: SunnySandyEggo


Hi Will,

Welcome to UtterAccess!
welcome2UA.gif

The common practice is to close what you opened. However, if you're reusing an object, then there's no need to close it before reusing it. You could just close it when you're done with it.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
pere_de_chipstic...
post Apr 2 2019, 09:52 AM
Post#4


UtterAccess Editor
Posts: 10,497
Joined: 8-November 07
From: South coast, England


welcome2UA.gif

I too have used this approach many times without issue, the only improvement I might suggest is to instantiate the current db as a DAO.Database variable before the loop, as this could improve its response time:

CODE
Dim dbs as DAO.Database
Dim rstOrders as DAO.Recordset
Dim strSQL as String
Dim ProdCode as Byte

Set dbs = CurrentDB

For ProdCode = 1 to 5
strSQL = "SELECT * from TblOrders WHERE product_code = " & ProdCode & " ORDER BY order_number;"
Set rstOrders = dbs.Openrecordset(strSQL, , dbOpenSnapshot)
While Not rstOrders.EOF

<Do some other stuff here...>

.MoveNext
WEND
Next ProdCode



hth (hope this helps)
This post has been edited by pere_de_chipstick: Apr 2 2019, 09:56 AM
Reason for edit: Amended code for Loop

--------------------
Warm regards
Bernie
Go to the top of the page
 
ADezii
post Apr 2 2019, 09:56 AM
Post#5



Posts: 2,553
Joined: 4-February 07
From: USA, Florida, Delray Beach


There is also a little known feature of ADODB whereas you can process multiple SQL Statements, delimited by a semi-colon, into a single ADODB Recordset. This approach uses the NextRecordset Method of an ADODB Recordset Object. I have never used it and do not know of it's advantages/disadvantages.
Go to the top of the page
 
cheekybuddha
post Apr 2 2019, 10:14 AM
Post#6


UtterAccess VIP
Posts: 11,464
Joined: 6-December 03
From: Telegraph Hill


I would close at the end of each loop.

Each loop essentially instantiates a new recordset into the rstOrders object variable.

Even though, DAO is now better than it used to be, it's better not to rely on destroying the object to close it. In the old days it was a notorious memory leak, not closing DAO recordsets before destroying them.
CODE
For ProdCode = 1 to 5
  Set rstOrders = CurrentDB.Openrecordset("SELECT * from TblOrders WHERE product_code = " & ProdCode & " ORDER BY order_number;", dbOpenSnapshot)
  With rstOrders
    Do
      <Do some other stuff here...>
      .MoveNext
    Loop until .EOF
    .Close     ' <-- close here
  End With
Next ProdCode
Set rstOrders = Nothing


hth,

d

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


Regards,

David Marten
Go to the top of the page
 
WillC
post Apr 3 2019, 02:55 AM
Post#7



Posts: 6
Joined: 2-April 19



Hi Adezii,

Thanks very much for the reply, and for your ADO suggestion (I've been meaning to explore the ADO side of things for a while). Much appreciated.

Will.
Go to the top of the page
 
WillC
post Apr 3 2019, 02:57 AM
Post#8



Posts: 6
Joined: 2-April 19



Hi DBGuy,
Many thanks for the forum welcome, and for confirming your approach and experiences with the method I'm using :-) Good to know.
Cheers,
Will.
This post has been edited by WillC: Apr 3 2019, 02:58 AM
Go to the top of the page
 
WillC
post Apr 3 2019, 03:00 AM
Post#9



Posts: 6
Joined: 2-April 19



Hey Bernie,
Many thanks for confirming my approach, and for the suggestions you made. I'll incorporate these into my code :-)
Cheers,
Will.
Go to the top of the page
 
WillC
post Apr 3 2019, 03:09 AM
Post#10



Posts: 6
Joined: 2-April 19



Hi David,
Thank you for your reply; much appreciated.
I was originally thinking that some form of duplication might arise as a result of not closing the recordset (as you've potentially highlighted), but having checked the recordsets collection and finding none, I guessed my fears were unfounded (which seems to be backed up by the other replies I've received). Is there any way to interrogate VBA to check whether the duplication you suspect is happening is actually occurring? (As you say, it was almost a certainty with early versions of VBA).
Regardless, I take the point that closing the recordset (it's only one line of code after all) will certainly do no harm and could potentially avoid problems down the line as the code executes.
Cheers,
Will.
Go to the top of the page
 
cheekybuddha
post Apr 3 2019, 05:02 AM
Post#11


UtterAccess VIP
Posts: 11,464
Joined: 6-December 03
From: Telegraph Hill


>> I was originally thinking that some form of duplication might arise as a result of not closing the recordset <<

No, each time you set the recordset, it will destroy the previous one (the destructor (Terminate()) will be called on the recordset class)

The problem used to be that DAO recordsets did not destroy properly all their internal class objects if .Close hadn't been called first. I *think* that is no longer the case, but it's better to be safe than sorry!

hth,

d

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


Regards,

David Marten
Go to the top of the page
 
WillC
post Apr 3 2019, 05:47 AM
Post#12



Posts: 6
Joined: 2-April 19



Hi David,

Your reply most certainly DID help! My main issue concerned my lack of understanding about what internal mechanisms (if any) were brought into play when opening an already opened recordset. Your explanation of the automatic execution of the destructor (Terminate()) process to destroy the previous instance of the recordset (at least within the later versions of VBA) neatly resolves that question for me. Thank you.
Thanks again to everyone else that has taken the trouble to respond to my question and to offer tips and advice for improvement. Very much appreciated.

Cheers,

WIll.
Go to the top of the page
 
cheekybuddha
post Apr 3 2019, 10:31 AM
Post#13


UtterAccess VIP
Posts: 11,464
Joined: 6-December 03
From: Telegraph Hill


Hi Will,

yw.gif

We're all pleased to help.

thumbup.gif

d

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


Regards,

David Marten
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    25th August 2019 - 06:09 AM