Data discrepancy in a SINGLE Access file

Biggles1
Biggles1 used Ask the Experts™
on
I have a small "network" consisting of three computers and a "File Server" (peer to peer).  This supports an Access 216 database.  The server has a file that contains the "Back End" Tables, and each PC (Call them PCA, PCB, PCC) has an identical copy of the User Interface.  This has worked well for years.

PROBLEM:  All of a sudden some of the data displayed on each PC is different from data displayed on the others.  Example: Updating a record on one PC is NOT reflected on the other two [b/].  The same data table can have DIFFERENT NUMBER OF RECORDS on each PC[b/].  Worse:  If I open the Back End file on the "server" I find that in many cases the data there is also different from that on  some or all the PC's

I HAVE MADE VERY SURE THAT THERE IS ONLY ONE COPY OF THE DATA ON THE SERVER that is accessed by all three PC's

Example  The User Interface on PCA  can show 100 records for the widgets table and the User Interface on PCB may show 110 records while the User Interface on PCC could have either the same number (100 or 110) or it could have 95 records.


Is it possible that the file server has created separate copies of the same table.  I searched using a File Search program but could not fine any copies of the table(s).

Has anyone experienced something similar?  This is only happening since I installed Access 2016.  It never happened with Access 2003, 2007, 2010, 2013 etc.....

I look forward to hearing your comments.


Thanks,

Biggles1
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
President / Owner CARDA Consultants Inc.
Distinguished Expert 2018
Commented:
On each PC, use the Linked Table Manager to examine what back-end file is being used.  The only way the data can differ is if they are pointing to different back-end files, or if the have different filters applied to their forms...

You could also examine the tables to see if the table data is the same, in which case this would clearly indicate a query/form/VBA issue in the way the data is being presented.
John TsioumprisSoftware & Systems Engineer
Commented:
Maybe you have some corruption and you are getting different results because....
Open the BE and do a C & R and report back
My favorite way to get the connection strings for the tables is to open VBE and in the immediate pane type:
? CurrentDb.TablesDefs("TABLENAME").Connect

Open in new window

This gives you the connection string, including path to backend file. If there is no connection string it is a local table.

Compare this value between the databases.

Make sure the table has a primary key defined.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Mark EdwardsChief Technology Officer

Commented:
Before going any further, you need to make a quick check of your linked tables in all 3 front-end files with the MSysObjects table to verify that you are linking to the correct databases and tables.  The following Access query SQL should give you the info you need.
You'll want to make sure the correct back-end database path and file are in the [Database] field, the correct source table is in the [ForeignName] field, the linked table name is in the [Name] field, and the Type for linked Access tables is "6".
If you find anything that is not right, then you need to correct it.  (Not in the MSysObjects table, but by relinking the linked table correctly) If everything is just as it should be, then let us know that too.
Be sure to open the linked tables in the Navigation Pane and verify that they look as they should.
Don't do what others have done and "assume" that it is correct without actually checking because you believe that it "must be".  Too many times we experts have spent many days going round-and-round with the author only for the author to finally give in and actually check, only to find out things were not as they had assumed.
SELECT MSysObjects.Database, MSysObjects.ForeignName, MSysObjects.Name, MSysObjects.Type
FROM MSysObjects
WHERE (((MSysObjects.Type)=6))
ORDER BY MSysObjects.Database, MSysObjects.Name;

Open in new window

Biggles1Founder/CEO

Author

Commented:
Thanks guys,

I will try to answer each comment and suggestion:

Daniel Pinaul said:
On each PC, use the Linked Table Manager to examine what back-end file is being used.  The only way the data can differ is if they are pointing to different back-end files, or if the have different filters applied to their forms...
You could also examine the tables to see if the table data is the same, in which case this would clearly indicate a query/form/VBA issue in the way the data is being presented.

John Tsioumpris Said
Maybe you have some corruption and you are getting different results because....
Open the BE and do a C & R and report back

Mark and Anders said pretty much the same and gave me some valuable insight.


In fact I found the solution to the problem by sort of following your advice:  
Originally I did have a single back end accessed by the user interfaces on all three computers.  That goes without saying.  What was happening was that the back end was returning DIFFERENT data for each computer!!!  How it did that I'll never know because I dont want to waste my time.  I tried creating a NEW back end with the data from the old one.  Same thing.  I ended up populating the new front end almost a few records at a time instead of dumping ALL the data all at once.  I also renamed the new front end and corrected my code accordingly.  IT WORKED.  I'm back to the original state of affairs.
Mark EdwardsChief Technology Officer

Commented:
Interesting.  You said you had a "back-end" (which indicates linked Access tables to an Access app front-end), and now you are "populating the new front end" a few records at a time, which sounds like your pumping records from an external Access file into local tables located in your "front-end", thus replacing the "back-end" completely with local tables located in the "front-end".

If that doesn't describe what you have done, please let us know what we are missing....just what are you doing?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial