Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

Converting MS Access Reports to SQL Server Reporting Services Reports


By:   |   Read Comments (2)   |   Related Tips: > Reporting Services Development

Problem

Reports are part of almost any IT ecosystem that generates, gathers and analyzes data. Typically when the scope of the budget approved for the IT expenditure is low, businesses start with low cost data management and reporting alternatives likes MS Access. As the volume of data keeps growing, so does the volume and variety of reports. In this journey of organic growth, eventually a time kicks in when the IT Department feels the inevitable need to migrate the data along with the reports to a more capable database management system like SQL Server and SSRS.

Considerable investment goes into building reports, and redeveloping all the reports from scratch poses a sizeable challenge in terms of cost and schedule. This situation demands an automated way to migrate reports from MS Access to SSRS to reduce partial efforts of rebuilding the report layout and associated details from scratch.

Solution

One of the features of SSDT is the ability to import reports. In order to understand how we can use this import reports feature of SSDT to import reports from MS Access, lets go step-by-step to create a sample MS Access database with reports. For the purpose of this exercise, I am using MS Access 2013 and SQL Server 2016 CTP along with SQL Server Data Tools. This feature should even work with SQL Server 2014.

Creating a Sample MS Access Database with Reports

We assume that MS Access 2013 is already installed on the machine where this exercise is being performed. Northwind is a sample MS Access database that is available from Microsoft, that contains samples tables along with data, and few reports with it. In order to install a Northwind MS Access template, just follow the steps mentioned below and in a couple of minutes you can install the Northwind template.

  1. Open MS Access 2013
  2. Type "Northwind" in the "Search for Online Templates" box at the top of the screen
  3. Click on Northwind 2007 Sample in the result screen
  4. Provide a filename for your Northwind database in the File Name textbox.
  5. Click the Create button and it would create your sample MS Access Database using the Northwind Template.
Northwind Template

Open the created sample database, and you should be able to find reports under the "Reports" section, as shown in the below image.

MS Access Reports

Importing MS Access Reports using SSDT

Create a new blank SSRS project using SSDT. Click on the "Import Reports" sub-menu, under the "Project" menu, and you should be able to see the option of Microsoft Access as shown in the below image.

Import Reports Dialog

After opening the MS Access dialog, select the Northwind sample database that we just created in the above steps, and click Open. This would import the reports from the MS Access database into the SSRS project as shown in the below image.

Imported Reports

One thing to note here is that these reports would not be totally error-free and ready for execution, as shown in the below image for the Invoice report. There will probably be errors due to features that aren't compatible after migration from MS Access to SSRS. There may also be missing data sources and datasets obviously, we the report developer would have to fix these issues.

Errors

Summary

This raises the question that in this case why should one use this feature at all and whether it is worth investing time in fixing a broken imported report or developing a new report from scratch. The answer to this question is - it depends on the complexity of the report. The report developer / analyst needs to make a judgment call whether it would be quicker to develop the entire layout and parameters of the entire bunch of reports hosted in MS Access or would it be just quicker to develop it from scratch in SSRS.

Generally speaking, highly complex reports having a large number of data fields and parameters are a good candidate for this method of migration as this would save considerable time in re-designing the same report by continuously comparing the new report with the older one. For reasonably smaller or simpler reports that can be developed using the SSRS Report Wizard, it would be quicker to develop such reports from scratch.

Given a scenario that if one has to migrate 50+ reports, it would make sense to import all the reports from the MS Access databases using this feature to bring it under a single SSRS solution. Once the reports are imported, then one can selectively make a call on which ones make sense to fix and which ones can be totally discarded and developed from scratch, depending upon the effort required to get the report up and running.

Next Steps
  • Try to fix some of the issues on the Invoice report imported from MS Access, to get an idea how the tasks can be done after the reports are imported.
  • Read this article to get an idea about MS Access Reports Features supported by SSRS.
  • Check out all of the SQL Server Reporting Services tips.
  • Check out all of the Microsoft Access tips.


Last Update:






About the author
MSSQLTips author Siddharth Mehta Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.

View all my tips


 



More SQL Server Solutions




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Monday, February 29, 2016 - 4:56:59 PM - Adrian Hernandez Back To Top

Hello,

Nice article. Can you clarify if this brings in any VBA code? I suspect not, which is too bad as VBA is also a Microsoft product, at least some of the code should be ported. Thanks. 

 


Thursday, February 25, 2016 - 8:18:40 AM - Jeremy Kadlec Back To Top

Siddharth,

Great to have you back on the team.  We look forward to your upcoming tips!

Welcome back...

Thank you,
Jeremy Kadlec
MSSQLTips.com Co-Founder


Learn more about SQL Server tools