catudalg
asked on
Union query of Foxpro tables from MS Access returns inconsistent results
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
union all
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.
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
union all
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.
I forgot to mention, since you show you are using ODBC, be aware that the VFP ODBC driver is only as current as VFP6 and doesn't truly support VFP7 through VFP9 databases and tables. You need the OLE DB Provider for VFP from the link I posted above. You will have much better luck with the proper driver.
Tidbits of information to speed you along with using your new connectivity software:
Connection info: OLE DB Provider for Visual FoxPro
http://www.carlprothman.net/Default.aspx?tabid=87#OLEDBProviderForVisualFoxPro
OLE DB Provider for Visual FoxPro
http://msdn2.microsoft.com/en-us/library/0xzsac67(VS.80).aspx
How to: Access Visual FoxPro Data in Visual Studio
http://msdn2.microsoft.com/en-us/library/3haz2895(VS.80).aspx
How to: Access Visual FoxPro Data in Microsoft Office
http://msdn2.microsoft.com/en-us/library/5e5x014a(VS.80).aspx
(From Trevor Hancock at MS)
To quickly get to a list of OLE DB Providers, right click on the Windows Desktop and choose...
Do this:
Right-click the desktop.
Select New>Text Document
Change the name to LookatOLEDB.UDL (note the extension).
Tell Windows "HELL YES!" when it warns about changing the file extension.
Now, DblClick that puppy and check the Providers tab.
You can use this to make an OLE DB connection string that you can just read out of LookatOLEDB.UDL with NotePad, or to browse providers, etc.
Another method of doing the same thing, and a few more details on the UDL files, is at the following URL:
Creating and Configuring Universal Data Link (.udl) Files
http://msdn2.microsoft.com/en-us/library/e38h511e(vs.71).aspx
Connection info: OLE DB Provider for Visual FoxPro
http://www.carlprothman.net/Default.aspx?tabid=87#OLEDBProviderForVisualFoxPro
OLE DB Provider for Visual FoxPro
http://msdn2.microsoft.com/en-us/library/0xzsac67(VS.80).aspx
How to: Access Visual FoxPro Data in Visual Studio
http://msdn2.microsoft.com/en-us/library/3haz2895(VS.80).aspx
How to: Access Visual FoxPro Data in Microsoft Office
http://msdn2.microsoft.com/en-us/library/5e5x014a(VS.80).aspx
(From Trevor Hancock at MS)
To quickly get to a list of OLE DB Providers, right click on the Windows Desktop and choose...
Do this:
Right-click the desktop.
Select New>Text Document
Change the name to LookatOLEDB.UDL (note the extension).
Tell Windows "HELL YES!" when it warns about changing the file extension.
Now, DblClick that puppy and check the Providers tab.
You can use this to make an OLE DB connection string that you can just read out of LookatOLEDB.UDL with NotePad, or to browse providers, etc.
Another method of doing the same thing, and a few more details on the UDL files, is at the following URL:
Creating and Configuring Universal Data Link (.udl) Files
http://msdn2.microsoft.com/en-us/library/e38h511e(vs.71).aspx
Are you getting 2 different results or totally different when you say inconsistent?
ASKER
By inconsistent results, I mean the following:
On 2000 records say, the value of a field might be incorrect on a few records, typically a handful, and on the next run there are incorrect values on a different set of records.
The values that are incorrect do exist on other records.
On 2000 records say, the value of a field might be incorrect on a few records, typically a handful, and on the next run there are incorrect values on a different set of records.
The values that are incorrect do exist on other records.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I agree with you but a question deserve an answer.
The MSDN doc says that OLE DB Provider for VFP is only supported on Office XP. We're still on 2000.
The whole process I'm implementing is producing summary reports in Crystal querying FoxPro tables from several folders.
I'm actually looking into doing the unions in a FoxPro .dbc then querying directly from Crystal via the OLE DB Provider.
The MSDN doc says that OLE DB Provider for VFP is only supported on Office XP. We're still on 2000.
The whole process I'm implementing is producing summary reports in Crystal querying FoxPro tables from several folders.
I'm actually looking into doing the unions in a FoxPro .dbc then querying directly from Crystal via the OLE DB Provider.
The results from an improper driver are meaningless. Too bad it just didn't say outright that this driver is impotent and that you sgould use another. Unfortunately, MS error messages and sometimes bad results necer tell you the whole story and end up having all of us waste lots of hours trying to make something work that never will.
Disregard the docs on that Office limitation. They will never fix that comment and you are not the first to point it out and actually believe it is true. Windows 2000 is NT technology just as much as Windows XP is and the OLE DB Provider for VFP runs fine on W2K and WinXP, as does the appropriate versions of Office on either. The connectivity driver really has nothing directly to do with Office anyhow.
Disregard the docs on that Office limitation. They will never fix that comment and you are not the first to point it out and actually believe it is true. Windows 2000 is NT technology just as much as Windows XP is and the OLE DB Provider for VFP runs fine on W2K and WinXP, as does the appropriate versions of Office on either. The connectivity driver really has nothing directly to do with Office anyhow.
Thanks. I assume you got better resilts with the OLE DB Provider. Rock on.
Microsoft OLE DB Provider for Visual FoxPro through 9.0
http://www.microsoft.com/downloads/details.aspx?FamilyId=E1A87D8F-2D58-491F-A0FA-95A3289C5FD4&displaylang=en