CODEWITHSUNDEEP
sql-serversql-server-2008ms-accessreporting-servicesreporting
Mike
Mike

Reputation: 41

Convert MS Access Reports to SQL Server Reporting

I have a bunch of old reports in MS Access that I want to just move over to SQL Server.

Is this possible to do? What steps need to be taken?

Upvotes: 4

Views: 3890

Answers (1)

Jeremy Thompson
Jeremy Thompson

Reputation: 65702

  1. Identify a Report to convert
  2. Open the Report in MS Access in Design mode

enter image description here

  1. Get an old copy of the report or run the report out of MS Access (as the basis of making a SSRS report)
  2. Open the Report Properties and find the Record Source the Report is using: qry_Intermediary_Summary

enter image description here

  1. Goto the Queries tab and right click the Query and choose Design View:

enter image description here

  1. Right click and choose SQL View

enter image description here

  1. Copy the MS Access SQL into SQL Management Studio

enter image description here

  1. Edit the MS Access SQL so it is SQL Server compliant:
  • Escaped column names that are reserved SQL Keywords (eg GROUP)
  • Replace double quotes with single quotes
  • Make sure Table/Views exist
  • Remove Dollar signs
  • Convert Trim(...) to LTrim(RTrim(...)))
  • etc

enter image description here

  1. When a Query uses nested queries we need to convert them to stored procedures and load the data in Temporary tables. eg

enter image description here

This SQL uses 3 nested queries:

  • qryTopStocks
  • qryTopStocksBuys
  • qryTopStocksSells​

Notes:

  • We cannot make the queries Functions that return Tables because Functions don't support ORDER BY
  • We cannot turn the queries into Views because Views do not accept parameters

So we have to convert the queries into stored procedures:

enter image description here

Then in our DataSets we execute the stored procedures into Temporary tables that we can join:

enter image description here

enter image description here

  1. Once you have the Query and it is returning the exact results as MS Access (view the old report to check), then we can create a new report.

I have used the MS Access to SSRS conversion tool. It managed to get the MS Access report designs but couldn't extract data. These SSRS2005 version reports are in directory AAA. Copy the Report you are converting from the AAA folder into the BBB project folder.

Import the old SSRS2005 report into BIDS/SSRS2016:

enter image description here

Select all the controls and copy them onto a new SSRS2016 report. Then delete the SSRS2005 report from the project. You only need it to copy the controls retaining the design, fonts and styles.

  1. In BIDS map all the controls to their field in the DataSet.​

UPDATE: I just found this, it's quite helpful: https://www.databasejournal.com/features/msaccess/article.php/3705151/Converting-Access-Queries-to-SQL-Server.htm

And this is a really good explanation of MS Access queries vs SQL Server queries for linked DBs

https://www.mssqltips.com/sqlservertip/1482/microsoft-access-pass-through-queries-to-sql-server/

Upvotes: 2

Related Questions