Relinking queries and reports after upsizing (1 Viewer)

[Shrout1]

I have several MS Access databases that I would like to migrate the back ends into SQL Server. I have tested the upsizing process on some sample MS Access databases and it makes sense to me. I am able to link the upsized table as an external data source and use the MS Access database as a front end.

The snag is this: Is there any means to automatically upsize a table into SQL server and then switch the reports and queries over to the freshly exported SQL and re-imported server table? If not it seems that I will need to go through query by query, report by report and do this manually.

Still new to this concept so I just want to pick some brains!

 

[Angelflower]

No there is not any means to automatically upsize a table into SQL and then switch the reports and queries over. It’s a manual process. The way that I approach a conversion of Access to SQL server is to convert all my non action queries to views and all my action queries (Update, Delete and Insert) into stored procedures. Have fun!

 

[Rx_]

I was out on vacation for 2 1/2 weeks.
My preferred method is to use the free Microsoft Tool
SQL Server Migration Assistant for Access
You can find more information on this site and other places.
Basically, it migrates all of the tables, with data types to SQL Server.
From there, the Linked Tables are exactly the same name as all of your objects.
This tool allows keeping some tables local and linking others.

An example of a local table is one I name SQL_Linked
See attachment of the first 3 of 125 table names to be linked
Basically, this table is used in a custom module to:
1. Destroy all linked tables
2. create DSN_less links using those tables with a Linked checked box using SQL Server Native Client

This removes the need to create an ODBC DSN on each client workstation.

For adding a new table to the Access Project:
Create a local Access Table. Use it with queries, forms. Test it.
Use SQL Server Migration Assistant for Access to migrate the single table up to SQL Server. Add the name of the new table to SQL_Linked. Run the custom module to create a DSN_Less Link from Access. Re-test.

Views vs Queries:
So long as a pure SQL statement is used, the ODBC interpretation to TSQL is extremely efficient. However, it is necessary to avoid Access function such as the IIF in queries. TSQL use of them is extremely inefficient.

For more complex queries with sorts, or for those queries used often as part of another query, I will often create a View in SQL.
In that case, it is attached in the SQL_Linked table exactly the same as a table would be. At that point, the view name (typically V_MyViewName) would need to be re-coded.

However, an Access Query "Select * from MyTable Where CustomerID = [5]"
will be compiled with ODBC and run in TSQL extremely efficiently.
The record set will only bring back the pointers across the network where CustomerID = 5.

The SQL Server Migration Assistant for Access does have a process to modify queries too. I can't speak to that. TSQL is something I enjoy writing or prefer to write and run analysis to get inefficiencies.