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 开发者_如何学Cpossible to do? What steps need to be taken?

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

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

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

6. Right click and choose SQL View

7. Copy the MS Access SQL into SQL Management Studio

8. 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

9. When a Query uses nested queries we need to convert them to Stored Procedures and load the data in Temporary tables. eg

This SQL uses 3 nested queries:

• qryTopStocks
• qryTopStocksBuys
• qryTopStocksSells​

We cannot make the queries Functions that return Tables because Functions dont 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:

Then in our DataSets we execute the Stored Procs into Temporary tables that we can join:

10. 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:

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.

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

UPDATE: I just found this, its 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/