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.