Can I use the same recordset variable on different tables?

I am new to Micr开发者_如何学Cosoft Access.

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

---

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?

---

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.