Solved

Cached Data in Access Tables?

Posted on 2014-01-07
8
892 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 57
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 57
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
 

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

758 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now