Performance Issues with Replica databases

[accessgenie]

First a little background...

have a client with a master and three replica databases in Access 2002. The master copy resides on a Windows XP box that acts as a server for their broadband-based network, but the other networked pcs and laptops are using Vista. Each user has his/her replica on their hard drive, whether working on a pc or a laptop.

The original database was designed by another developer who I suspect hadn't worked with relational databases much previously (almost all of the data records were in two VERY large tables with no lookup tables and no normalization). The client wanted to add some new functionality to the database, including recording sales and purchase order details. The changes they wanted required restructuring the database, which I did. I initially split the database forms and reports from the data, but the client wanted the front end replicable as well as the backend, since employees needed to take the application with them out into "the field" and they wanted to be able to sync individually written queries to the master.

So I "rejoined" the database and made the other design changes, plus a few more that were tacked on later, and the client was happy--at least for a moment or two. However, a couple of performance and data issues have crept in:

(1) The replica databases open and/or close *extremely* slowly on an intermittent basis (e.g., sometimes they are fine, sometimes it may take 5 to 10 minutes for the database to open. The client noted that these issues seemed to start after a particular major Windows Update. I was able to alleviate this to a degree by modifying the users' shortcuts to look for the msaccess.exe first prior to opening up the application and making sure that the executable wasn't set up to "run as administrator". However, the client tells me they are still experiencing slower performance.



(2) The other issue is a data issue: The client noticed when running a query off of two tables with a one-to-many relationship (I'll use Sales and Sales Details, since the data is similar) where there were some records that had the data from the primary table (Sales) but the data from secondary table (Details) was missing.

I have not been able to get to their offices to troubleshoot the problem, since I'm still recovering from a bad bout of pneumonia in October and need someone to drive me out there (the company is over an hour's drive from me). However, I had two copies of the master available to look at: one from late September (A), and one from late October (B), which were sent to me by the client via a usb drive they mailed to my house.

In looking at database A, I only found 2 records that didn't have a corresponding record in the Details table, one dated in January, the other in June. However, when I ran the same query in database B, I found not only those two records, but 15 others, half of these were from after I'd received database A, but most of the rest were ones that did have a match in database A (where that database had the corresponding detail records as well), although one record from database B, dated in early April, did not have a corresponding record in database A at all.

My initial thought was that these records were test cases, instances where the application failed before updating tables, instances where the details weren't saved by the application, and/or caused by replication/corrupted data issues). It is also entirely possible that my code behind the entry form for these two tables is faulty. I am going to look at that tomorrow, when I'm a bit fresher and my head is on straight.

Anyway, before I try reinventing the wheel, has anyone else here had similar issues and found the cause/fix for them? Suggestions? Comments? My client is making noises like he may scrap the entire thing and find another developer. I don't foresee getting a lot more work from this client, as the system he has now seems to be what he wants except for these two issues. However, I don't feel good about leaving things this way and would like to resolve the problems, if I can.

Thanks in advance,

Laura

[accessgenie]

I've tested my code and could not recreate a situation where the details were deleted but the primary record remained.

Again, any help you can give me would be appreciated.