rs.MoveNext increasing recordcount

[303factory]

Hi

I've recently started changing all my code from ADO to DAO. I've noticed this causing some behaviour I dont understand with recordsets.

Dim rs As DAO.Recordset
strSQL = "SELECT * FROM tblExtensions 
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
rs.MoveFirst
intCounter = 0
intArraySize = rs.RecordCount
If Not intArraySize = 0 Then ReDim strGetUserFiles(intArraySize - 1)
Do While Not rs.EOF
       strGetUserFiles(intCounter) = rs.Fields("Extension")
        intCounter = intCounter + 1
        rs.MoveNext
 Loop

The strange thing is that every time rs.MoveNext is executed the rs.RecordCount increases by 1, and rs.EOF never happens. This is causing my code to fall over. Any idea why this is happening?

 

[DCrake]

If I'm not mistaken when using DAO you have to use Rs.MoveLast in order to get the correct Rs.Recordcount

So the code would be

Set Rs = CurrentDb.OpenRecordset(strsql)
Rs.MoveLast
IntArraySize = Rs.Recordcount
Rs.MoveFirst

don't know why you think that the code errors I cannot see anything wrong. But you can change the rs.Fields(...) to Rs("...")

You could always slip in a escape sequence, such as

If IntCounter > intArraySize Then Exit Do

Have you done a C & R lately?

David

 

[303factory]

If I'm not mistaken when using DAO you have to use Rs.MoveLast in order to get the correct Rs.Recordcount

So the code would be

Set Rs = CurrentDb.OpenRecordset(strsql)
Rs.MoveLast
IntArraySize = Rs.Recordcount
Rs.MoveFirst

don't know why you think that the code errors I cannot see anything wrong. But you can change the rs.Fields(...) to Rs("...")

You could always slip in a escape sequence, such as

If IntCounter > intArraySize Then Exit Do

Have you done a C & R lately?

David

Aah that makes sense.. I was getting a 'subscript out of range' error for my array! I didnt realise the recordcount meant to be a large number (88) not the recordcount of 1 I originally got. Using your example code fixes the problem and returns the correct recordcount.

Thanks!

 

[LPurvis]

Bear in mind that both ADO and DAO support the GetRows method which, in each case, returns a two-dimensional array of the data requested.
In your case you would likely request only the "Extension" field.

Dim rs As DAO.Recordset

strSQL = "SELECT Extension FROM tblExtensions WHERE Extension Is Not Null"
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
With rs
    .MoveLast
    .MoveFirst
    strGetUserFiles = .GetRows(.RecordCount)
End With

You just have to bear in mind that multi-dimensionality of the array.

However, just to ask, why are you dumping the recordset contents into an array?
Is there any particular reason? (As you already have the recordset loaded, it's in memory - and they are generally much more functional than arrays).

Cheers.

 

[303factory]

Bear in mind that both ADO and DAO support the GetRows method which, in each case, returns a two-dimensional array of the data requested.
In your case you would likely request only the "Extension" field.

Dim rs As DAO.Recordset
 
strSQL = "SELECT Extension FROM tblExtensions WHERE Extension Is Not Null"
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
With rs
    .MoveLast
    .MoveFirst
    strGetUserFiles = .GetRows(.RecordCount)
End With

You just have to bear in mind that multi-dimensionality of the array.

However, just to ask, why are you dumping the recordset contents into an array?
Is there any particular reason? (As you already have the recordset loaded, it's in memory - and they are generally much more functional than arrays).

Cheers.

Hmm I see.. I should probably re-write this bit of code! I don't know the reason the recordset is dumped into an array. Unfortunately I inherited this software from a long gone employee and havent had a chance to write it from scratch so I'm dealing with lots of inneficient and outdated code

 

[LPurvis]

Well, if you don't know why it's doing what it's doing (i.e. you're not fully up to speed with the code) why are you rewriting it to use DAO instead of ADO?

I'd have thought there were other benefits to be had before that.
Is it a performance issue? (Then there might be other things at play rather than just the library used - as, all things being equal, there should be very little between them).

I wouldn't necessarily advocate GetRows in general (as mentiond prefer recordsets themselves) - just offered it as a simpler alternative. But if you're unsure as to the "why" then the multi-dimensional nature of it likely won't sit with whatever it's passed to - in whatever process is ongoing there.

Cheers.

 

[303factory]

I was told to move to DAO because we're switching from an access back end to a MySQL back end using linked tables. Apparently DAO is much more efficient for usings tables linked to MySQL. It's starting to look like the change to DAO is going to cause more problems than its worth..
I really need a few months to learn the ins and outs and write a proper piece of software but unfortuantely the budget is tight and the time is short so stuck with doing a bodge job

 

[LPurvis]

Well, not having used MySQL myself I can't say.
However I'm unclear on what difference is being implied.
(For example using linked tables to that server BE then Jet performs the wrapping and your local library, ADO or DAO, accesses those wrapped requests).

Who told you? ;-)

Cheers.

 

[303factory]

I dont understand the details but the recommendation came on this forum in the following thread

http://www.access-programmers.co.uk/forums/showthread.php?p=844381#post844381

I was hoping it would be a simple case of replacing the lines that open recordsets, but since it's causing other bits of code to fail due to the way the recordsets work (also needing rs.Edit before I can update!) it may not be feasable to make this switch this time.

 

[LPurvis]

Right.

I suppose it's a bit like a woodsman chopping logs in his yard.
Another woodsman passes by and casually mentions - "I ususally use a curved edge axe because it's less likely bounce off".
And so the first woodsman goes off and merrily hand grinds all of his axes to be curved, comes back and the passing woodsman says "Oh, unless you're using Elm trees like you are now".

I'm not suggesting that David would change his viewpoint, his comment is correct.
But I don't consider it something to be too concerned about. (To the extent of changing all your code before testing).
The analogy isn't that your situation is such that the advice doesn't apply (i.e. "Elm trees") but that based on a single passing comment - it's too much of a leap of change, when there might be reason why it was chosen originally.

OK, DAO's interface with Jet is inherent (i.e. we don't see it - there's certainly no exposure of it).
ADO has to use the OLEDB provider for Jet due to it's non-specific provider specification (i.e. ADO can be used for a very wide variety of sourses).
The difference this makes is usually very small.
And, as I mentioned, there are reasons why ADO might have been implemented.
It might just be that the application was created in Access 2000/2002 and the original developer didn't break from the default references supplied.
Or there might have been functionality used that made the choice for them (I'm assuming more than a pre-poulated recordcount though ;-)

I'd say by all means check.
Test DAO and ADO equivalents against your eventual source.
See if there is a perceivable performance difference.
Even if there is, then decide if it's of an extent such that you want to invest this time.

Cheers.

 

[303factory]

I see.. well thank you for your advice as you can probably tell im fairly out of my depth with all this. Since I'm already neck deep in code changes and have a few days spare I might try finish the switch and see if it causes any problems. As it's quite a complex system I might argue that it's safer to stick with ADO if problems keep cropping up.

 

[gemma-the-husky]

just a couple of thoughts

a) before opening the recordset you could do a
dcount("*","myquery") to get the count

b) arrays can be tricky, as they are zero based
so if you have 100 items, and resize an array to have 100 slots, then the upperbound will actually be array(99). ARRAY(100) will give a subscript.
error. ~looking at your orginal code, I thought this may be the reason you are getting the error. clearly an rs.movenext of itself can't affect the true recordcount

 

[LPurvis]

Just to reassure you, I'm not saying you couldn't grasp the coding differences required. I'm sure you would if so inclined and a book could help you do so (for example this has a good section for direct comparisons between DAO and ADO - and I'm not just suggesting it because my mate wrote those chapters ;-).

It's just that the issues you were encountering were from venturing into territory that I don't believe was a priority for you.
Becomming well versed with the application you've been tasked with maintaining is more important to my mind.

And in other news... ;-)
I personally wouldn't be issuing a DCount before opening the recordset. If there are matching records then you've just hit the database twice in order to retrieve them. If there aren't then returning zero records in a recordset isn't necessarily a bigger overhead than a Count request.

And yes Array's need to be considered. Not just that they are zero based, for they aren't necessarily depending upon settings (for example the Option Base declaration) they could be 1 based by default or anything if you declare them explicitly. Hence using LBound and UBound are vital rather to avoid assuming anything.

The true RecordCount is indeed what we perceive the eventual count should be.
The RecordCount as returned by DAO is accurate in that it's as many rows of the key index that have currently been loaded. Moving next increases that. Moving last fills it. With the correct cursor type in ADO it's not generally a consideration. Your code was failing and was corrected by fully fetching the filled recordset.

Cheers.

 

[Julian]

Just remove dbOpenDynaset. That works as it opens a table-type recordset rather than a dynaset (which only populates the full recordset once navigated to the end).

 

[LPurvis]

>> "Just remove dbOpenDynaset"
Well... not really.
If you look at the source of the recordset as in the question:
strSQL = "SELECT * FROM tblExtensions"
it's a SQL statement. By default DAO will open a Dynaset recordset on such a source. You can't even force a TableType on such a source statement either.
You'd have to make the source be a table name, e.g.
strSQL = "tblExtensions"

However even then - there's a strong requirement that the table is local to the front end where the code is running. Otherwise you can't open a TableType recordset upon it (without opening a dedicated DAO database object on the target MDB and then opening your recordset against that).

That's all perfectly viable, but it's not as simple as just removing the dbOpenDynaset type.
And you need to be sure you want a TableType. It's not just the case that they're "better", they have trade-offs all their own. (Doesn't everything?)

Cheers.