CODEWITHSUNDEEP
ms-accessvbams-access-2010
qmckinsey
qmckinsey

Reputation: 305

Recordset Explained?

Recently I've come to realize that a recordset may be a much simpler (less useful) object than I had originally thought. I'm looking to see if others can confirm or deny my suspicions. Let me explain. When I create two DAO recordset objects, say rstA and rstB, and set them both to the same table, say TableA, like so,

Set rstA = CurrentDb.OpenRecordset("TableA", dbOpenDynaset)
Set rstB = CurrentDb.OpenRecordset("TableA", dbOpenDynaset)

then they do not behave autonomously. For instance, moving records, using rstA.MoveNext, then it does the same for rstB.

Originally, I was thinking of the recordsets as "full blown" objects, but this behaviour indicates that they are merely Pointers to the table. So, the table is holding it's own pointer to indicate the current record, which would be why moving this pointer with either rstA or rstB causes one to effect the other.

Is this correct or am I simply doing something wrong?

Upvotes: 0

Views: 519

Answers (2)

qmckinsey
qmckinsey

Reputation: 305

After learning a thing or two about VBA, I think I may have found a solution to this problem. At the time I asked this question, I was not aware of the Let keyword. If I had been aware, then I probably would have tried the following:

Let rstA = CurrentDb.OpenRecordset("TableA", dbOpenDynaset)
Let rstB = CurrentDb.OpenRecordset("TableA", dbOpenDynaset)

I still think that the OpenRecordset method generates a hidden recordset object, so that using the Set keyword simply hands out the address of this hidden recordset object. I have not tested this theory yet, as at the time, I simply went with a completely different way to solve my problem. Just wanted to update the post in case it may help someone else later.

Upvotes: -1

LittleBobbyTables - Au Revoir
LittleBobbyTables - Au Revoir

Reputation: 32690

You're doing something wrong.

Each recordset should be an independant object, and using rstA.MoveNext should have no impact on rstB.

Try the following code:

MsgBox rstA(0)
MsgBox rstB(0)

rstA.MoveNext

MsgBox rstA(0)
MsgBox rstB(0)

Assuming that the next values in each recordset are different than the previous, the second MsgBox rstA(0) should alert the second record in TableA, whereas the second MsgBox rstB(0) will still alert the first record in TableA.

Upvotes: 8

Related Questions