-1

I am new to Microsoft Access.

How can I use the same recordset variable on different tables?

Cody Gray - on strike
  • 239,200
  • 50
  • 490
  • 574
user596226
  • 1
  • 1
  • 1
  • I have no idea what you are asking – Mitch Wheat Jan 31 '11 at 01:30
  • 1
    Show us an example of what you're trying to do. – Bill the Lizard Jan 31 '11 at 01:32
  • I'm guessing you are writing a VBA script procedure. What I'd try is setting the "object" variable that you are using for the recordset to Null when you are finished with the first table. It will hopefully free up any allocated resources associated with the object, and you can then reinitialize it to hold the results of another query, associated with the same table or a different one. – hardmath Jan 31 '11 at 01:42
  • 1
    Do **NOT** set it to NULL. Set it to Nothing. And Close it first. – RolandTumble Jan 31 '11 at 18:47
  • 1
    I agree that setting the variable to Nothing is correct, NULL is wrong. But setting the variable to a new object value has the same effect on the underlying reference as setting the variable to Nothing. – Paul Keister Feb 01 '11 at 00:23

2 Answers2

2

Yes you can so long as you don't expect the variable to be pointing to two recordsets.

However the question is why would you? What would you expect to save? Resources? Not a big deal. Also code readability would suffer. If you a week from now or someone else later are looking at your code they might not realize what is going on. Thus I'd suggest a recordset variable per table/query.

Also there is the scope of the variable. If defined in a subroutine/function then it's visible only in that sub/function. If at the top it will be visible to all sou/function in that form/report/module. If you state that it's global while in a module then it will be visible everywhere.

So the question is why do you ask?

Tony Toews
  • 7,850
  • 1
  • 22
  • 27
  • I could see re-using a record set variable if you had to loop and perform the same operation through a number of identical tables. But then again, if are they identical, why are they separate. – BIBD Jan 31 '11 at 15:36
  • I disagree that reusing a variable for a new object is categorically bad practice. CodeSlave's example is relevant. – Paul Keister Feb 01 '11 at 00:26
  • Paul, in CodeSlave's specific example sure. But in general I would say you shouldn't be reusing the same variable for two different things in the same subroutine/function as that gets confusing in code. – Tony Toews Feb 01 '11 at 03:49
  • Tony, you are essentially saying that object assignment is bad practice except following or as part of initialization. This is a very broad generalization, and I can't imagine it being a useful guideline. I would agree with the affirmative form of your critique: don't hesitate to define a new object variable if it adds clarity. – Paul Keister Feb 06 '11 at 08:10
  • Paul, I have no idea what you are reading into my posting in your first two sentences. – Tony Toews Feb 07 '11 at 19:58
0

Yes, but you must be sure to close the recordset before you re-assign the recordset variable to another recordset. Here is an example:

Dim rs As DAO.Recordset
Dim bTimeToChangeRecordsets As Boolean

Set rs = CurrentDb.OpenRecordset("Contacts")

'add business logic here'

If bTimeToChangeRecordsets Then
    rs.Close
    'setting to Nothing is not necessary here,'
    'because setting to a new recordset instance has the '
    'same effect on the variable reference count '
    Set rs = CurrentDb.OpenRecordset("Comments")
End If

'more business logic'

rs.Close
Set rs = Nothing

Many of the comments on this question have focused on the need to set the recordset variable to Nothing, whether and when this is necessary, and whether it is also necessary to call Close on the recordset before releasing the recordset reference. There is some excellent commentary on this topic spread out accross quite a few questions on stack overflow; this one is particularly relevant to your situation. I would also direct you to this knowledgebase article and this in-depth book excerpt.

At risk of oversimplifying, I can summarize the issue this way: if Access reference counting worked well, it would not be necessary to worry about explicitly releasing references by setting them to Nothing, nor explicitly closing recordsets. Howerver, practical experience tells us that, given the behavior of Access, both of these habits should be part of best practice coding for VBA.

Community
  • 1
  • 1
Paul Keister
  • 12,851
  • 5
  • 46
  • 75
  • 2
    Actually no you don't need to close the variable before reusing it. – Tony Toews Jan 31 '11 at 03:41
  • If you leave the recordset open, there will be no way to close it, since the reference is lost. This is not good. – Paul Keister Jan 31 '11 at 06:44
  • 2
    Actually, the record set should be cleaned up by garbage collection (eventually) even if you don't close it. However, good practice would be to close it before moving on to another table/query. This will let it happen a little sooner. This is why my old sloppy DBs from the '90s when I didn't explicitly close the record sets didn't come to a screeching halt. – BIBD Jan 31 '11 at 15:26
  • CosdeSlave, yes, I agree that you should close recordsets when you're done with them. But we're talking about reusing a recordset variable without closing it. – Tony Toews Jan 31 '11 at 19:36
  • 2
    Tony, you seem to be mixing things up. Closing the recordset IS required because otherwise, you may have a memory leak. Theoretically, garbage collection should release the memory, but we all know perfectly that VBA's reference counting is imperfect and doesn't clean up reliably when it should. Setting the variable to Nothing is a completely different step -- that's not the memory structure itself you're operating on, but the POINTER to it. You're setting the pointer back to Nothing. When re-using it, you don't need to do that because you're setting it to point to a different memory structure. – David-W-Fenton Feb 06 '11 at 01:36