Union query of Foxpro tables from MS Access returns inconsistent results
Posted on 2007-04-03
Current System Configuration:
Windows 2000 sp3
MS Access 2000 (9.0.4402 SR-1)
Visual Foxpro 9.0 detached tables on local drive
MS VFP Driver 6.01.6830.01 06/19/2003
For example, a simple query like:
select Account, Drawing, ActualHours from ddwg1
select Account, Drawing, ActualHours from ddwg2
The query retreives a few thousand records but does not return the same result from one execution to the next. I sum ActualHours and get a different result each time. Got the correct answer 60 times out of 400 tries.
If I first import the same tables then run the union query on the imported tables, results are ok.
I've tried on different tables using different fields, numeric or not, with similar inconsistent results. I ran the query on an XP machine (Access 2000 also) without much success.
Importing the tables is not desirable in this situation.
Has anybody heard of this problem before?
This seems to be definitely an ODBC issue as I do not think Access have a look to the data before results are returned from the ODBC driver or am I wrong?
I've done this type of query from Oracle or Sybase data sources in the past with no problem.