Solved

Cached Data in Access Tables?

Posted on 2014-01-07
8
1,201 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
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!

 

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

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

631 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