Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Cached Data in Access Tables?

Posted on 2014-01-07
8
Medium Priority
?
1,452 Views
Last Modified: 2014-01-22
Hi,

I am debugging a MS Access database that is composed of a front end and back end data file, both in Access.  The database supports an workflow that creates a new record in a Log table for each step of the workflow as the work item moves along, and tags the current record as Current = Y.  

The problem I'm getting is that all of a sudden, the front end database started showing all the historical records as well (e.g. Current <> Y) for a particular work item.  Looking through a few posts, I was able to determine that there is nothing wrong with the code.  The issue is with the data file.  If I use a different data file (from a previous point in time), it works fine, but I lose all the data that was generated since then.  Also, I tried importing all the current tables to the old version of the file that works but the same thing happens.

Has anyone run into a similar issue before?  I've heard that MS Access can "cache" table values?  If so, how can I clear this cache?  I've also tried compact/repair but it doesn't work either.

Thanks in advance
0
Comment
Question by:eklin
  • 5
  • 3
8 Comments
 
LVL 58
ID: 39763030
The JET engine, which is used by Access, does cache data on the station, but that is cleared and destroyed as soon as Access closes.

I would suggest opening the backend directly and verifying what is or is not in the actual tables.

If the data is correct, then I would re-link the tables and see if the problem disappears.

If the data is no correct, then the BE is probably corrupt.  Make a backup copy, peform a compact and repair, and see what you have.

 Depending on what you have and where the problem is, you may be able to combine your old backup along with the current DB to get all the data.

Jim.
0
 

Author Comment

by:eklin
ID: 39763092
Jim,  thanks for the feedback.  I've tried re-linking multiple times and re-checked the backend database, both to no avail.  Relinking didn't work and the tables look fine in the BE.  I'll try again and let you know.
0
 
LVL 58
ID: 39763108
<<Relinking didn't work and the tables look fine in the BE.  I'll try again and let you know. >>

 I'm assuming you mean that the FE still shows the wrong data, not that the relink itself didn't work.

If the the data looks good in the BE directly, then I would make sure your pointing to the right BE from the FE.

For example, you might have a DSN issue if your linking on a server name.

Jim.
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 

Author Comment

by:eklin
ID: 39763111
I tried re-linking again and didn't work.  I should also mention that I have a "Gold Copy" of the latest version of the database.  During testing, I've started with a fresh copy of the "Gold Copy" multiple times.  Initially, I wouldn't get this error.  However, somewhere in the middle of the testing I started getting this issue, even though I re-performed the same steps with a fresh copy of the database.  It almost feels like a "time bomb" that went off at a certain point...
0
 

Author Comment

by:eklin
ID: 39763117
Jim - our emails must have crossed paths.  You are correct, the FE still shows wrong data.  Relink was successfull.  The data in BE looks ok and there is no DSN.  The BE is in MS Access located in the same folder as the FE.  FE has linked tables to the BE.
0
 
LVL 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 total points
ID: 39763136
Hum....not sure where the problem lies, but I would try this:

1. Take a copy of the BE and put it in a totally new location.
2. rename the BE in the original location (ie. myapp.mdb.save)
3. Take a copy of your golden FE and place it in the same location as the new BE.
4. Delete all the table links in the FE.
5. Create all the table links pointing to the BE in the same location.
6. Re-test.

I think in doing that, somewhere along the way you'll find the problem.

Jim.
0
 

Author Comment

by:eklin
ID: 39763157
Jim - thank you for taking the time to lay out this test plan.  I'll perform these steps and hopefully will find out the root cause along the way.  I'll let you know in either case. Let me go get some coffee before getting started
0
 

Author Comment

by:eklin
ID: 39800307
Jim - thanks for your input.  After following your suggestion and re-testing, I concluded that it is a flaw in the VBA code.  The reason why this was occuring sporadically is because it only occurs if the "backup" role is assigned to the work item.  Since I only used the "backup" role when performing the last few legs of testing, I was repeatedly getting this error.  Once I made the connection I was able to verify that the issue does not occur with the "primary" role.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

963 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question