• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1566
  • Last Modified:

Cached Data in Access Tables?

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
eklin
Asked:
eklin
  • 5
  • 3
1 Solution
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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
 
eklinAuthor Commented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
eklinAuthor Commented:
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
 
eklinAuthor Commented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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
 
eklinAuthor Commented:
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
 
eklinAuthor Commented:
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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now