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