Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Reuse Recordset?

Posted on 2004-10-08
5
Medium Priority
?
901 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 800 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 600 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 600 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
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.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

715 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