MS Access 2003 linked table to .DBF file not being refreshed??

[Randy Y]

Hello all!
I'm experiencing a frustrating problem. I have an older MDB application which has linked tables to dbf files. Those linked tables appear to be retaining old data if accessed in a qry, but will display the correct data if opened explicitly. I don't know enough about MS Access to know how to automatically 'refresh' the index for these tables, but I believe that's the problem, because after I build a *new* table over those same old dbf files, choosing to replace the inf file, my qrys will work.
What am I overlooking?
Thanks!
RY

[MMcCarthy]

Have you run the linked table manager to refresh the links?

Mary

[Randy YYY]

Mary,
Yes, I have refreshed them from the linked table manager...sorry, I neglected to mention that.
Well, let's say that I selected all of the linked tables and clicked 'OK' and waited for an appropriate period of time. I'm confident I had exclusive use of the MDB at the time, but probably did not have exclusive use of the DBFs that the tables are linked to. I assume Access would let me know if he wasn't able to refresh at that point?

[MMcCarthy]

Can you post a sample query just to see if there is something there.

[Randy YYY]

Mary,
Sorry, but I'm a noob here...I may not have the proper format for displaying code, but here it is:

SELECT inv.LOCATION, inv.VENDITEM, [GDI inv].ITEM, IIf(IsNull([102ware].[min]),IIf([loc2]<[gdi inv]![min],[gdi inv]![max]-[loc2],0),IIf([loc2]<[102ware].[min],[102ware].[max]-[loc1],0)) AS amt, inv.DESC, inv.ONHAND, [GDI warehous].LOC2 AS store, inv.UNIT
FROM (inv INNER JOIN ([GDI inv] INNER JOIN [GDI warehous] ON [GDI inv].ITEM = [GDI warehous].ITEM) ON inv.ITEM = [GDI inv].VENDITEM) LEFT JOIN 102ware ON [GDI inv].ITEM = [102ware].ITEM
WHERE (((IIf(IsNull([102ware].[min]),IIf([loc2]<[gdi inv]![min],[gdi inv]![max]-[loc2],0),IIf([loc2]<[102ware].[min],[102ware].[max]-[loc1],0)))>0) AND ((inv.ONHAND)>0) AND (([GDI inv].ONHAND)<1));
 

This is a selection query for an inventory re-order report. It, and many other queries in this five-year-old MDB, has worked well for all that time, so I'm confident of the logic and I'm confident of the contents of the DBFs and the associated Linked Tables, but when the query ran early yesterday (before I forcibly refreshed the index by creating a new linked table), it returned incorrect data...as in generating twenty pages of items which I clearly had onhand. My first attempt to correct it was to select all tables from the Linked Table Manager and refresh them, which took an appropriate time (I felt) for twenty tables with 75K records. That didn't correct the problem. Then I selected only the tables in use from the LTM and refreshed them...still didn't work. THEN I got the idea to add some new linked tables just for the sake of replacing the associated INF files (That *is* what it does, right?)...and then it worked.
SO...the problem is corrected, but I'm not sure what caused the problem...and that's what I want to avoid.
Any thoughts?
RY

[MMcCarthy]

The query looks fine. The only thing I can assume is that it has something to do with the way the tables were originally linked. Creating new links is probably the safest way to go.

Mary