Solved

Union query of Foxpro tables from MS Access returns inconsistent results

Posted on 2007-04-03
9
743 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
 
LVL 27

Expert Comment

by:CaptainCyril
ID: 18844679
Are you getting 2 different results or totally different when you say inconsistent?
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

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 500 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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

746 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now