By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,105 Members | 2,541 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,105 IT Pros & Developers. It's quick & easy.

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

P: 1
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
Oct 13 '10 #1
Share this Question
Share on Google+
5 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Have you run the linked table manager to refresh the links?

Mary
Oct 13 '10 #2

P: n/a
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?
Oct 13 '10 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Can you post a sample query just to see if there is something there.
Oct 13 '10 #4

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

Expand|Select|Wrap|Line Numbers
  1. 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
  2. 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
  3. 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));
  4.  
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
Oct 13 '10 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Oct 13 '10 #6

Post your reply

Sign in to post your reply or Sign up for a free account.