Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Union query of Foxpro tables from MS Access returns inconsistent results

Posted on 2007-04-03
9
Medium Priority
?
762 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

721 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