Question

MS Access - Same Table Shows Different Record Counts

I have a user who has two computers, one with Access 2007 and the other with Access 2010. The database she uses is on a shared network drive. She opens the same database (which I confirmed is the same copy) and the same table in that database, but gets a different record count in Access 2007 than in Access 2010. No filtering is being applied. What could cause this?

Discussion is locked
Follow
Reply to: MS Access - Same Table Shows Different Record Counts
PLEASE NOTE: Do not post advertisements, offensive materials, profanity, or personal attacks. Please remember to be considerate of other members. If you are new to the CNET Forums, please read our CNET Forums FAQ. All submitted content is subject to our Terms of Use.
Reporting: MS Access - Same Table Shows Different Record Counts
This post has been flagged and will be reviewed by our staff. Thank you for helping us maintain CNET's great community.
Sorry, there was a problem flagging this post. Please try again now or at a later time.
If you believe this post is offensive or violates the CNET Forums' Usage policies, you can report it below (this will not automatically remove the post). Once reported, our moderators will be notified and the post will be reviewed.
Comments
- Collapse -
Answer
Pictures please.
- Collapse -
Screen shots

Apparently the forums do not allow you to upload files, only to provide links to URLs. I do not have a URL available to post these. However I can reaffirm that in Access 2007, the row count is 214,381. In Access 2010 the row count is 212,635. Again, same table, same database, same file name and location, no filtering.

- Collapse -
I goofed here.
- Collapse -
Answer
Re: record counts

- So there is an a and a b, and a < b. What records are in b and not in a?
- An .mdb, .accdb, MS SQL Server or still another database system?

Kees

- Collapse -
Re: Record Counts

Hi Kees:

It's an .mdb file. The table is a local (not linked) table. I got my user to export the data from this table in both Access 2007 and Access 2010. She sent me the Excel export files and I brought them into a new database and ran a query. I found around 1,800 records that are in the 2007 version that aren't in the 2010 version. The records are not blank (at least some fields are populated).

In response to Robert's suggestion, I did create a form in the 2010 version and added a field to it to display the record count, and it matched the record count showing in table view.

- Collapse -
Oh so close.

I can't tell from your last sentence if you fixed it or not.

In my SQL work the primary key field must have content or the record does not count.

It sounds to me that your field contents are hit and miss?

One last thing. In some rare instances I find the Language version of the OS, App and Database plays a role but that's pretty rare but noteworthy.

- Collapse -
Sorry for any confusion

The issue is not fixed. The primary key is populated in all records. Some other fields in the table are null but not the primary key, and there are no records where the primary key is the only populated field.

I had my user do an Excel export from both Access 2007 and Access 2010 in order to identify whether the fields are just hidden in 2010 or completely missing. The 2007 version of the export has all the records; the 2010 version does not. And this (again) is the same table in the same database in the same shared network location. It's very strange.

The OS is Windows 7 in both instances.

- Collapse -
Over a network there can be locked reccords.
- Collapse -
Data Corrected via Workaround

Hi Robert:

I was able to recreate the table in Access 2010 using the Access 2007 data export as my data source. All of the records are there now. This doesn't solve the mystery and I would really like to understand how something like this can happen, but at least my user can get back to work.

As for your questions -- I saw the behavior both on the network and after copying the database to my local drive. On the network there was only one user in the database while this behavior was observed.

CNET Forums