Can you Set DataSource in Crystal XI Report to Union Query in Access 2007?

[Former Member]

Hi all,

I have been able to install Office 2007 OLE DB Driver with Crystal Reports which allows me to link my Crystal XI Report to my Access 2007 Db. Unfortunately, my Union Query is not available in my drop down list when I set my datasource. I can see my two tables and two other queries. Just can't see the Union query. Does Crystal XI recognize and am I able to use a Union query as a data source?

[Former Member]

I finally resolved this issue. Thank you for your help.

[former_member292966]

Hi Karen,

Open Crystal and go to File | Options.

In the Database tab, put a check in Views and Stored Procedures.

Close Crystal and reopen it. See if you can find your query now.

If the query is parameterized, Crystal considers it a Stored Procedure to pass parameters. This should get you going.

Good luck,

Brian

[Former Member]

Hi Brian,

Thank you so much, but in the 7 hrs I spent on this today, I bumped into that solution going through all of the documentation I could find on this site. I don't get it, tables, views and simple procedures is checked. I also recreated the query as this simple db (two tables converted to queries and one union query) was created in Access 2007, saved down to 2003 version, then upgraded to 2007 to test this feature. I also checked the db to ensure the server the db was on was a trusted location and then moved it to my c drive to see if that worked. Nothing is working. Any other ideas?

Edited by: Karen Arena on Jun 22, 2009 11:28 PM - also receive an error when I select the 2007 db in the listing which states Some tables could not be replaced, as no match was found in the new data source. Please specifiy the table required for any modified tables. I assume this is because Crystal is looking for that table?

[former_member292966]

Hi Karen,

Is your query parameterized?

When you connect to the Access database, are you using ODBC or going directly to the Access MDB?

Queries can only be used through ODBC.

Also, the query must return records. I'm assuming your's does because it's a Union. So Update queries cannot be used in Crystal because no records are returned in the Select.

Good luck,

Brian

[Former Member]

No I don't think it is parameterized, I am creating a query taking the sql from both individual queries, insert the word UNION between the sql code to create the union. The union query does produce records.

I followed the directions for Setting up a System DSN and selecting it in the Crystal Reports ODBC connection wizard and Selecting Microsoft Office 12.0 Access Database Engine OLE DB Provider as noted in the directions I found in the library in a document entitled "Creating Reports with Microsoft Office 2007 Data Files".

I also followed the directions in the article entitled "Troubleshooting Database Connectivity for Crystal Reports" - Missing tables, views, or stored procedures and also followed the directions for How to ensure your client computer is able to retrieve all available tables, views, and stored procedures using the NTablesMax setting.

Edited by: Karen Arena on Jun 23, 2009 12:07 PM - PS. Thank you for helping me, this is all very new to me.

Edited by: Karen Arena on Jun 23, 2009 1:28 PM - So excited...I figured it out! I had to create an odbc rdo, not an OLE DB ADO! Now I just need to figure out how to export to Excel 2007 to take advantage of the increased rows.