Solved

Reuse Recordset?

Posted on 2004-10-08
5
890 Views
Last Modified: 2012-06-27
I'm wondering if there is any reason why I should not reset a recordset and query definition rather than create a new one.  See code snippet for what I'm talking about.

----------------------------------
Code Snippet
----------------------------------

Dim db As DAO.Database
Dim qAppt As DAO.QueryDef
Dim rAppt As DAO.Recordset
Dim pAppt As DAO.Parameters

Set db = CurrentDb()
Set qAppt = db.QueryDefs("qfrmAppts_Validate") 'set query definition for the first time
qAppt.Parameters("vDate") = Me!fldInsertDate
Set rAppt = qAppt.OpenRecordset(dbOpenDynaset) 'set recordset for the first time

If Not rAppt.EOF Then
    Do some stuff.........
End If

Set qAppt = db.QueryDefs("qryAppts_Insert") 'reset query definition
Set rAppt = qAppt.OpenRecordset(dbOpenDynaset) 'reset recordset

While Not rAppt.EOF
    Do some stuff.........
Wend


Thanks!

Michelle
0
Comment
Question by:mixxie
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12261949
Not really, so long as you remember to close and set the objects to Nothing in order to release the memory that they have taken up. I often reuse recordset objects (in particular) in the same piece of code.
0
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 200 total points
ID: 12261957
The main reason you might want to declare a different object for each recordset that you want to use is really code clarity. It can be confusing if you are trying to determine which source table/query the recordset is referencing at a particular time.
0
 
LVL 8

Assisted Solution

by:James Bendall
James Bendall earned 150 total points
ID: 12261962
Hi Michelle,

As a matter of good practice, I'd recommend Cosing the Recordset object before you attempt to reuse it. It might also be a good idea to explicitly set the QueryDef to Nothing before reusing it.

Hope something here helps

JB
0
 
LVL 51

Assisted Solution

by:Steve Bink
Steve Bink earned 150 total points
ID: 12263325
Just had to chime in...same thing, different expert.

I normally do NOT reuse objects, but that's because I do not want to come back to a project 6 months from now and wonder "why the $%#@ am I using query 1's recordset to generate query 2's report?!?"  Do not dismiss the affect clear and concise coding has on a project.

Other than that, sure thing!  Reusability is exactly why object-oriented programming was invented in the first place.  JB's suggestion is a good one...clean up after yourself before reusing, and I would add a comment notating the change in responsibilities for the object as well.
0
 

Author Comment

by:mixxie
ID: 12275947
Thanks, guys!  I appreciate the quick responses!

Michelle
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question