Multiple Data Sources

[pwomack]

I'm using Crystal Reports 7 and MS Access97 and Access2000 databases.

I'm working on creating a crystal report that will be executed from a Windows application. The application is from a software vendor and was probably developed using Visual Basic. The report connects to the two databases by using two ODBC user dsn. The Access97 database comes with the Windows application and the Access2000 database is a database that I created. The Access97 database (supplied with the Windows application) does not contain all of the information that I need for the report, thus my Access2000 database.

I first developed the report using the CR report designer and all works well (information from both databases is displayed).

I then defined the report and it's parameters using a form provided by the application so that the report can be executed from one of the application’s forms.

This is the point at which I run into problems. The custom report opens Okay from the application, but when I page down to a page that displays information from my Access2000 database I get the following error:

“ODBC error: [Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database engine cannot find the input table or query 'tblCompData'. Make sure it exists and that its name is spelled correctly.”

The tblCompData is the table from my Access2000 database.

Again, the reports works Okay from Crystal Reports designer.

I’ve tried different things to get this to work (like setting the location to UNC and Same as Report under the Set Location menu for my database), but nothing works.

My company doesn’t have a maintenance agreement with the software vendor, so the vendor hasn’t provided any help.

Thanks.

 

[Turkbear]

Hi,
How is the application accessing the database ?

Check to be sure all permissions are set correctly so that the appication has sufficient file system access rights to all the database table locations.




To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[pwomack]

Thanks Turkbear.

I hope that I understand your question.

All I know is that the application is using a user dsn ODBC connection to access the application's Access97 database.

The application does not use the Access2000 database, except indirectly, through Crystal Reports. My company purchased the application; we didn't develop it ourselves.

All files (the CR report, Access2000 database) are located in directories that with the proper permissions set.

 

[Turkbear]

Hi,
In general what I meant was: what user account actually runs the Crystal Report or is used to connect to the Access2000 database?
( if it is the same account that the application is running under, confirm that this account is able to access the database tables..) -







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[pwomack]

Thanks again Turkbear.

The user acount is "admin" with a database level password for both of the databases.

I know the application's database password, so I set my Access2000 database level password the same.

I've been trying different things this afternoon to get the connection working. I just now deleted the user dsn ODBC connection to the Access2000 (my) database and replaced it with a Data File type connection, and the report now works when executed from the application.

I don't know very much about crystal reports and data connections, but I think that I want to use a ODBC user dsn connection for my database, just like the app does.

 

[Turkbear]

Hi,
Try using a SYSTEM dsn instead of a User one.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[pwomack]

Okay. I'll post back with the results, probably tomorrow.

 

[synapsevampire]

Also if you're using MS Access, try the direct connectivity instead of the ODBC type.

I don't think that CR 7 wouldn't naturally connect to Access 2000 as CR 7 was out before Access 2000.

So it may be that your'e using an old ODBC driver.

I think that there was direct connectivity in CR 7 too, might give that a whirl.

-k

 

[pwomack]

Good morning Turkbear
I created a system DSN, but I can't locate it in the data tab of the CR Subreport Expert.

Good morning -k
I made a data connection using the Data File option in the Data tab of the Subreport Expert. It worked (see my previous post.) Is the Data File option what you mean by direct connectivity?

Thanks to both of you.

 

[synapsevampire]

Yeah, they are probably only using direct connections in the app (data file option), so that was causing the error.

-k