Solved

Cached Data in Access Tables?

Posted on 2014-01-07
8
1,061 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
Industry Leaders: 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!

 

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 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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

685 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