Getting an incorrect recordcount from a DAO jet database

Question

0.00/5 (No votes)

See more:

I am trying to modify some old legacy VB6 code that uses a DAO Jet database.

The problem is the RecordSet RecordCount property.
The database contains 17953 records.

The code does a MoveFirst and MoveLast, which is supposed set the RecordCount, but the RecordCount property only returns 17855 (98 short)

The code then loads ALL the 17953 records into a ListView, so all records are accessed and are correctly loaded into the Listview, but even then the RecordCount still only returns 17855 !

Is there any other way to get the RecordCount to be correct?

What I have tried:

The simplified code is something like this.

Dim Dsi As DAO.Recordset
Set Dsi = Dbs.OpenRecordset(tblMessages, dbOpenTable)
...
Dsi.MoveLast
Dsi.MoveFirst
...
'Simple loop
Do Until Dsi.EOF
(load record into ListView)
Dsi.MoveNext
Loop
...

Posted 3-Jan-18 22:46pm

n4tm

Updated 4-Jan-18 0:29am

Add a Solution

Comments

F-ES Sitecore 4-Jan-18 4:51am

If you need to access all records then use a clientside cursor so you don't need to movedlast\movefirst. As for your missing records, dump the IDs or whatever to a file\log to identify which records are missing and see if you can see any common link.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2018-01-04T00:29:00

Start by checking the database:

SQL

SELECT COUNT(*) FROM MyTable

will tell you how many rows there are. Use Access to run the query and see what you get back. At a guess - and without access to your DB that's all it can be - you are confusing an ID number with a row number, and there are "gaps" in the IDs where rows have been deleted.

And do your users a favour: Don't just dump 18,000 rows into any display control: it's not a management number for humans. Page it, let them search it, filter it: but displaying much more than 50 rows is unreasonable because it takes too long to find the row you are interested in. And it makes your app slow. Combine the two, and your users will hate the app, and for good reason.