Wednesday, January 04, 2012

How Reporting Services on 64-bit server can print report from MS-Access 2007 accdb

Introduction to the problem
When you develop a report on a 64-bit server in Business Intelligence studio that reports from an accdb database (MS-Access 2007/2010 format), everything will work fine, because it uses 32-bit drivers. It is only when you deploy the ms-access datasource and report to the 64-bit Reporting services site that you run into a problem
Solution 1
Install the Access version 12 oledb drivers - Use SSIS data flow task (which can be made to run in 32-bit) to read the data and send to output to a DataReader destination component. This can be picked up as a datasource by Reporting Services and serve as basis for a report.
Prerequisite
The Jet 4.0 odbc driver will not wortk on the new accdb format. You have to install the MS-Access version 12 drivers first and install them on the 64-bit machine.
Download the driver from Microsoft downloads
Making it work
I have not tried this, because I opted for solution 2, but the concept of using a Datareader destination in SSIS may well be usable in other situations.
Solution 2


The solution came from Daniel Cai's blog
Apparently Microsoft has made 64-bit drivers for accdb files and included them in the MS-Access 2010 redistributable.
Select the 64-bit download from Microsoft downloads here
Note: If you had the Microsoft Access 2007 database engine installed, you will need to uninstall it.
Install it on the 64-bit server and change the connection string in the Reporting services datasource
Provider=Microsoft.Jet.OLEDB.4.0; 
to 
Provider=Microsoft.ACE.OLEDB.12.0;