Thank you for being a valued part of the CNET community. As of December 1, 2020, the forums are in read-only format. In early 2021, CNET Forums will no longer be available. We are grateful for the participation and advice you have provided to one another over the years.

Thanks,

CNET Support

Question

MS Access - Same Table Shows Different Record Counts

Sep 29, 2015 11:12AM PDT

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

- Collapse -
Answer
Pictures please.
Sep 29, 2015 11:19AM PDT
- Collapse -
Screen shots
Sep 29, 2015 12:34PM PDT

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.
Sep 29, 2015 12:49PM PDT
- Collapse -
Answer
Re: record counts
Sep 29, 2015 11:51AM PDT

- 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
Sep 30, 2015 9:20AM PDT

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.
Sep 30, 2015 9:39AM PDT

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
Sep 30, 2015 9:48AM PDT

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.
Sep 30, 2015 9:57AM PDT
- Collapse -
Data Corrected via Workaround
Sep 30, 2015 11:12AM PDT

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.