?
Solved

Union query of Foxpro tables from MS Access returns inconsistent results

Posted on 2007-04-03
9
Medium Priority
?
785 Views
Last Modified: 2008-07-16
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.
0
Comment
Question by:catudalg
  • 6
  • 2
9 Comments
 
LVL 33

Expert Comment

by:CarlWarner
ID: 18844157
Before we go any further, please download the very latest OLE DB Provider for VFP and use it in place of whatever older version you have in place right now.  It has numerous bug fixes and a few enhancements especially in the way of performance.

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
0
 
LVL 33

Expert Comment

by:CarlWarner
ID: 18844182
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.
0
 
LVL 33

Expert Comment

by:CarlWarner
ID: 18844300
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
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 27

Expert Comment

by:CaptainCyril
ID: 18844679
Are you getting 2 different results or totally different when you say inconsistent?
0
 

Author Comment

by:catudalg
ID: 18845809
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.
0
 
LVL 33

Accepted Solution

by:
CarlWarner earned 2000 total points
ID: 18845870
Regardless of results you had with the VFP ODBC driver, step up to the proper driver for VFP9 which is the OLE DB Provider for VFP as I posted above.
0
 

Author Comment

by:catudalg
ID: 18846014
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.


0
 
LVL 33

Expert Comment

by:CarlWarner
ID: 18846564
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.
0
 
LVL 33

Expert Comment

by:CarlWarner
ID: 18859375
Thanks.  I assume you got better resilts with the OLE DB Provider.  Rock on.
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Audit trails are very important in any system to hold people responsible for certain transactions and hold them to take ownership of their actions. This article is dedicated to all novice "Microsoft Access" developers.
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

599 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question