Importing Access Reports into SQL Server Reporting Services

At a recent speaking event, an attendee asked me if it’s possible to import reports created in Microsoft Access into SQL Server Reporting Services (SSRS). Before giving them an answer, I figured I needed to get the facts straight and see what problems developers could encounter if they walked into a customer site where a half-dozen (or a half-million) Access reports needed to be imported into SSRS. I expect most folks want to leverage the work invested in the Access reports wherever possible, so having a mechanism to transmogrify them into a format that SSRS can manage is very attractive. Let’s look at how to convert your Access reports into RDL.

Getting Started
To import Access reports into SSRS, you need several components. First, get a big black kettle, fill it half-full of swamp water, and start a fire… no, wait, that’s another recipe. You will need

• Access (the version that matches the reports to be imported) installed on your development system. It’s launched when the Access database is referenced by Visual Studio’s (VS’s) Import Report Wizard.
• VS 2008 SP1 or VS 2005. VS 2008 SP1 implements the business intelligence (BI) tools that shipped with VS 2005.
• The Access database file (e.g., .mdb file, accdb file, .adp—project—file).
• One or more reports within the database that gather data from OLE DB data sources. Unfortunately, you won’t be able to import reports that reference ODBC-sourced data (at least not the data sets). Also, imported reports can’t reference Visual Basic code in modules.
• The MSDN help topic(s) that list the objects that can be converted (msdn.microsoft.com/en-us/library/ms156375.aspx and msdn.microsoft.com/en-us/library/ms156508.aspx).
• An ounce (or two) of patience.

Despite my distaste for Access, I manage the Saint Jude Sanctuary Choir membership database in Access. Because I update this database only once or twice a year, I often have to open it with a new version of Microsoft Office, which has to convert the database to the latest version of Access. I can’t tell you how many versions of the database I’ve struggled with over the years. Each time I bring up Access and click the previous year’s Access database, I hum a few bars from a hymn and hope for the best.

I’m fairly familiar with the Access report configuration UI and it’s not that hard to use, so I can see why Access developers want to leverage it as a way to present data. Access is (as I see it), a nicely engineered development suite with its own database engine (JET) and unique SQL and programming requirements. However, I don’t recommend Access for anything more than home or very lightweight business applications. Although an Access application (including reports) can reference Visual Basic code behind the scenes, the way queries and stored procedures (which in Access are just singleton queries stored in the database) are written is different than SQL Server. The reporting mechanism is also very different when compared to the RDL-based reports that are used in SSRS. I haven’t scratched the surface of the reporting power of Access, but I have learned to appreciate Access 2007’s Report Layout mode to reposition the prototype report columns. Before you get too excited about this functionality, you should know that if you have reports with lots of code running behind the scenes, you might as well go back two squares and drop the .MDF files into that black kettle full of swamp water because they won’t convert.

Running the Report Import Wizard
To get started importing Access reports into SSRS, open a VS project and select Business Intelligence Projects. If you don’t see an option for a BI project, you don’t have the right version of VS installed. Then, create a new project and select a target SQL Server database. (You can also open an existing BI project to manage imported Access reports.) Next, right-click Reports and select Import Reports, Microsoft Access, as shown in Figure 1.