Interesting Problem For Database Gurus

[ErnestM]

1. I have a legacy FoxPro (character based) application (no sniggering at the back) on a SCO Unix OpenServer platform. I can access the FoxPro tables via AFPS on the SCO box using Microsoft Access 2003 (in Access 2000 mode) via an ODBC Visual Foxpro link (just a free table link as SCO Foxpro is 2.6 and supports free table connections only)

2. Question: Why do I have to quit my Access application and restart it before any changes in the tables (that have been done on the Unix system) show up in Access? When I used to use the ISAM, any changes would show up immediately upon triggering a query, etc.

Answers on a postcard - and free beer my next trip over for the person that can answer this (Oh - and I have already tried refreshing the links through the linked table manager both interactively and through a function call, so that doesn't work)

I have gone back to using the dbase 5.0 ISAM for the time being (no FoxPro ISAM since Access 2000) and it is doing a relatively good (albeit somewhat slower) job...

Cheers

ErnestM

[catretriever]

Earnest,

I've never ODBC'ed to Foxpro before, but are you sure you are setting the connection up as a Link and not just as an Import?

[ErnestM]

Yep as a link. File->Get External Data->Link Tables->ODBC()
(I have to link as a don't want to update the live data interactively through Access. I just use this to run some custom reporting, etc)

ErnestM

[plasticpig]

Have you got fetch data in background checked on the ODBC setup?

[ThatPhilBrettGuy]

Must be some snapshot setting somewhere in the ODBC driver? Never used the Foxpro driver but I'd look for something in that area.

[ErnestM]

plasticpig said:

Have you got fetch data in background checked on the ODBC setup?

Tried it both ways - same result...

ErnestM

Edited to add: I am thinking that there is some sort of "cache" on the client machine somewhere that needs either editing (registry) or configuring.

>> Edited by ErnestM on Tuesday 7th March 11:20

[Plotloss]

Now I dont know much about Access or Unix but it sounds like there's commitment control boundaries defined over the entire dataset.

Is Access performing a commit on exit I wonder?

[ErnestM]

It has to be ODBC related somehow - or possibly the foxpro odbc connector. Otherwise when I use the dbase 5 ISAM, it would do the same thing, but it doesn't. When I use the ISAM, I can change some data on the Unix side, re-run a report (which triggers the underlying query that the report is based on) and the data changes are reflected. When I do it with the ODBC Visual FoxPro connector (which is the MS suggested way of reaching your FP data tables in Access 2003) the data change is not reflected. However, when I quit Access and restart and THEN run the report, the data has changed. The ODBC connector is faster, but I think it has some sort of cache. I've found some references to SMB caching at the operating system level, but I think that is a red herring so far.

Like I said, an interesting problem that I would like to get solved but the critical thing is that the reporting is running OK right now with the ISAM.

ErnestM

[plasticpig]

I have just tried linking to a Fox 2.6 table from Access and updating the table in Fox 2.6 and I see the changes in Access as soon as I open the table or requery it. What version of the ODBC driver are you using?

[ErnestM]

ODBC is 3.525.1117.0
Visual FoxPro ODBC Driver is 6.01.8629.01
JET engine is the one that came with XP SP2 (the latest)

ErnestM