Things to Remember While Converting Microsoft Access Application Into SQL Server Application

When the Access database grows much large and the performance of the application gets worsen, you might need to think of upsizing the Access database into SQL Server database. So that you can split your MS Access front end and SQL Server back end. Microsoft Access comes with a utility called “Upsizing Wizard”. It is located at Tools > Database Utility > Upsizing Wizard. This utility tries its best to convert the whole database including data tables, queries, relationships etc. This wizard is able to convert all tables perfectly but it might convert all queries into stored procedures or functions or views.

Choose the solution between Access application with Linked tables or Access Data Project.

When you are linking a table from a database on the network, the datasource is not updatable. So if the application adds/updates/deletes record, then this solution is not for you. Access Data Project is the best solution for the application which needs updatable recordset. One can import different objects like forms/reports in Access Data Project. Here the tables are not linked but you can directly manipulate the database.

How to Convert Query into Stored Procedure?

When the query is directly used in a form which is using/not using a form variable, convert it into stored procedure. For example, if the query is:

select * from EMP where Name like [forms]![Emp Form]![Name]

You may convert the above query into a stored procedure like:

Create Procedure GetEmployee
(
@Name nvarchar(20)
)
AS
Select * from EMP where Name like @Name

If the query is using another query without any variable, make the first query as a stored procedure and convert second query into view.

If the query is using another query with variable, make the first query as stored procedure with variable and convert second query into a function.
Now you can use this function result as a temporary table.

Use Pass-through query wherever possible, because it reduces network traffic. And last but not least

PAY ATTENTION TO THE BOOLEAN COMPARISON AND NULL VALUE CONCATENATION

Once you upsize the database, MS wizard gives you report of how many tables successfully upsized and how it changed the datatypes of columns. Most of the time, it upsizes it correctly but sometimes it just skips some tables/records. So to check it I have created an Access form which can be run to check the consistency of the records. Open the main form “frmMain” give the appropriate value and press “Check…” button. It will store the results in database. Now you can check the results by viewing the queries.