Access Query and SQL Tables Combined for Reporting

[CTekGirl]

Because of the complex nature of the data I had to combine several queries in MSAccess to get one result query that has everything that I need. I am using this query in combination with some tables from SQL Server. When I first ran this report a month ago it worked fine. Now when I try to refresh it with new data I am getting an ODBC error. Error Detected by Database DLL. I thought it was possible to use 2 different database drivers in one report. It worked once, but now it doesn't. I even tried closing everything and opening a report with the same type of join that worked before and it gets the same error.

I tried removing the Access database QUERY and adding it back; now I am seeing this error:
Database Warning - More than one database driver has been used in this report. If you want to change the database drivers use Database/Set Location. Also, please make sure that no SQL Expression is added and no server-side group by is performed.

Any assistance would be appreciated.

If I cannot get past this my only recourse is to build each one of these views direcly in SQL Server in order to use the same database driver. There are mulitiple views that make up the one view that I need for this report.

 

[CTekGirl]

Update. I created a table from the results of the Access Query ( a function within Access) and with some lag time it seems to work now. I included my original posting just to document the steps I took to get to this point. The Access Query is based on tables within the same SQL Server Database. I am still unclear why this would work with another table but not a query. The query like the other SQL tables require a login into SQL Server. Is this a limitation of CR, that it doesn't know how to process two separate logins? I would like to avoid making a table, because the underlying data may change and the Access Table would no longer be current.

 

[synapsevampire]

The cleanest approach wouyld be to link (not import) the SQL Server tables into the Access database and create a query in Access for the Crystal Report to use.

Using 2 drivers within Crystal is slow as it does not have a very efficient database negine, which is whaat you're trying to get it to act as.

Access may not be the fastest, but it'll beat Crystaal everytime.

-k

 

[CTekGirl]

My original query before I did the workaround table was based on linked tables. I am doing a report on organizational data and because so many users have the ability to change it I decided to make the query based on linked tables(in Access). But this is where the problems lies. If I use this query along with some SQL base tables. The error that I stated in the first posting occurs.