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.
Using the File, Open dialog box, navigate to the .accdb, .mdb, or .adp database that contains the reports you want to import. (You’ll need to ensure that users aren’t using the Access database during this process.) VS then launches Access behind the scenes, opens the database file or project, and imports all of the reports it contains. You don’t get to choose which reports in the file to import, so if you already have reports with the same name, you’ll be asked to confirm that you want to overwrite reports one at a time—even if there are 17,259 of them. When the Access Report Import Wizard is done, your project Reports folder is populated with RDL versions of the Access reports, as shown in Figure 2.
If there are any problems during the conversion, you’ll get a bit of information about what went wrong in the Output window, shown in Figure 3, and in the Task window.
Note that the warning messages tell us that some reports weren’t imported correctly because they have modules or event-handlers implemented.
Importing a Sample Access Report
To illustrate how a simple Access report is imported, I created a new Access project that references a couple tables from the SQL Server AdventureWorks2008 sample database. I imported the data using Access and let Report Wizard build the report. The query extracts rows from the Production.Products table (with an input parameter). I asked the Report Wizard to group on ProductLine, Class, and Style. The only modification I made to the report was to change the background color of the Style header to orange, as shown in Figure 4.
After running the Access Report Import Wizard, an RDL version of the report was added to my VS project. When I previewed it, I saw that the report had drastically changed, as Figure 5 shows.
Notice that there are the following significant differences between the Access report and the RDL report:
- Although there are indentations, there aren’t any defined groups constructed in the RDL.
- The "green" bar (actually yellow) row coloring doesn’t show up in the RDL file.
- The background color has shifted from the Style line to the ProductLine.
- The group headings are now located above the report name.
- The Cost, Profit, and ListPrice columns values have been reformatted, and now appear as floating-point values instead of a Money values.
When I was testing the Report Import Wizard, I also made the mistake of creating an Access report that accessed data via ODBC. When this report was imported, all I got was named references to the cell values as if they were parameters. I was reminded by a gentleman on the Microsoft campus that non-OLE DB data sources can’t be used to generate a data set usable by the RDL Report Processor. The result is a report that doesn’t have a data source. Of course, if you build an OLE DB data source for the Access report and manually rebind it you might be able to import the report.
Converting Access Reports to RDL
Well folks, as you can see, there are some reports that can’t be imported at all, some that can be partially imported, and another subset that can be imported into SSRS without significant problems. For those few (simple) reports that can be transmogrified, the Report Import Wizard might be of some use. The lesson to be taken away from this article is that you shouldn’t depend on importing Access reports into SSRS as an overall strategy because I expect most reports fall into the first category. I think it would help if Office adopted the RDL report generation technology used by SQL Server, so there would be fewer problems importing Access reports into other scaled-up solutions. However, I wouldn’t hold my breath; the Office team is pretty independent. I expect they want SSRS to adopt the Access report format.
