Crystal XI and Access 2010 - Issues Retrieving Data

[TechSlinger]

Hello

I have Crystal XI and was able to link to my Access 2003 table using the Access/Excel DAO connection. Now I've upgraded to Access 2010 and I have to use the ODBC connection. I created the ODBC to the database and Access 2010 drivers.

However, if I join more than 2 tables then I get the error message:
"Failed to retrieve data from the database. Details: 42000: [Microsoft][ODBC Microsoft Access Driver] syntax error in From clause. [Database Vendor Code: -3506]"

The resultant SQL is:

 SELECT `Data`.`ChartNo`, `Data`.`Disch Date`, `CMG_Plus_2012`.`CMG ENGLISH SHORT DESC`, `ICD_10_Codes`.`Block`
 FROM   {oj (`Data` `Data` LEFT OUTER JOIN `CMG Plus_2012` `CMG_Plus_2012` ON `Data`.`CMG`=`CMG_Plus_2012`.`CMG`) LEFT OUTER JOIN `ICD 10 Codes` `ICD_10_Codes` ON `Data`.`MrDx`=`ICD_10_Codes`.`DxCode`}

The table connections worked fine when using the DAO connection but not now. Why is that and how can I get around this error? Thanks.

 

[kray4660]

I do not believe you can use and Access 2010 DB with Crystal XI (unless SAP released an update that I am not aware of). True, you should be able to use an ODBC, but from my experience it does not seem to work.

 

[PeterDimitrov]

Do you have MSAccess 2010 installed on the machine where you are running the report? If it is not installed you may need to install ODBC drivers or MSAccess redistributable

http://www.microsoft.com/en-us/download/details.aspx?id=13255

http://www.R-Tag.com

Viewer and Scheduler for Crystal reports, SSRS and Dynamic Dashboards.