CR9 dies when adding 2 ODBC Connections to a report

[mbutch]

I'm using CR9 on a winXP machine. I have two Visual FoxPro DSNs, ODBC1 and ODBC2.

I add a table from both connections and when I click the OK button or the Links tab, CR9 closes and I'm asked to send an error report. Is there a way to get this to work?

Thanks

 

[synapsevampire]

Hmmm, apply the hotfixes 1st.

And see if there's an update for your Foxpro ODBC driver, you might even try to turn off the fetch data in background option.

-k

 

[craigber]

What version of the ODBC driver are you using? What happens if you use the OLE DB provider instead? BTW, OLE DB is the recommended connection technology for FoxPro as the ODBC driver is no longer being updated nor is it shipping with the latest version of Visual FoxPro (8.0)

Craig Berntson
MCSD, Visual FoxPro MVP,

http://www.craigberntson.com

Author, CrysDev: A Developer's Guide to Integrating Crystal Reports"

 

[mbutch]

Thanks for the suggestions. Installing the service pack and hotfixes was the first thing I did, but it didn't solve the issue. I have the latest VFPro ODBC Driver.

I was unable to find an OLE DB provider for Visual FoxPro, where can I get that at?

I wound up calling CD tech support. The problem was that CR9 was confused as to which Common folder to get the dlls from since I have Visual Studio .NET, which comes with CR .NET. I had to rename the 1.0 and 1.1 folders and register a dll and I was back in business.

It doesn't work very well. It takes over 30 min. to return 47,000 records.

 

[craigber]

That's definately way too long a query. Do you have control over the VFP tables? What indeces exist? Do the tables have lots of deleted records? How long does the query take in VFP? What does the SELECT statement look like?


The Visual FoxPro OLE DB provider is only available by purchasing VFP. However, Microsoft currently plans to have the OLE DB provider available as a download and has promised it in the next two to three weeks.

Craig Berntson
MCSD, Visual FoxPro MVP,

http://www.craigberntson.com

Author, CrysDev: A Developer's Guide to Integrating Crystal Reports"

 

[mbutch]

I don't have much control over the table because it is part of a purchased vfp app. Both fields I'm selecting on are indexed. There are no deleted records. It takes about 20 - 30 seconds in VFPro. The select statement looks like:

SELECT a.number, b.number as old_number;
FROM location1\stock a left outer join location2\stock b on a.number = b.number

I'm trying to see which numbers exist today that didn't exist yesterday by determining which b.numbers are null.

I own VFPro 6 Professional, will that get me OLE DB provider?

 

[craigber]

The OLE DB Provider did not ship until VFP 7.

The query you showed should work and should only be slightly slower through ODBC. However, I'm curious to see if a different query works better.

SELECT Number FROM location1\stock where not exist (SELECT Number from location2\stock)

Craig Berntson
MCSD, Visual FoxPro MVP,

http://www.craigberntson.com

Author, CrysDev: A Developer's Guide to Integrating Crystal Reports"

 

[mbutch]

With a little modification, that query works great in VFP, but getting it to work in CR9 is another story. I can't create that query in Crystal SQL Designer because it won't work with 2 connections, it keeps giving me a link error.

I'm just going to use CR8.5 and forget about it.

Thanks for all of your help,

Mike