How To Convert or Migrate MS Access database to SQL Server.

With the custom software development, data migration from MS Access to MS SQL Server could be a critical point of the entire process.

The client can see their data in the application and it is easier for them to relate to their own data, as opposed to some made up data. This is part the process which the end results will lead to Convert MS Access To Web.

Our MS Access developers helped us to crack down the Microsoft Access database which was badly designed and to understand the business process.

Part of the process is to reverse engineer MS Access database since in most cases the developers of the original Application are nowhere to be found.

In addition, our company has MS SQL consultants who can design and optimize the new Microsoft SQL Server database after the migration process has been completed.

When you out grown MS Access database:

You might be a small or medium-sized business which uses the Microsoft Access database as your central system. In the recent years, your company has grown, and so has your database size. Now you are rapidly approaching to the 2 GB size limit of the MS Access database.

You are probably also aware that you have outgrown MS Access database and you need to look in the other direction. There might be nothing wrong with your system, but you know that there is a lot of room for improvement from the functionality, security and user-experience perspective. In this point you may ask yourself the question of How Do I Convert or Migrate MS Access database to SQL Server?

In such situations, migration to Microsoft SQL Server stands out as the logical way to go. That migration is usually coupled with the development of back end services, as well as front end which enables users to interact with the database model. However, there is a way to keep the Microsoft Access as the front end which interacts with the SQL Server database.

In this article, we will cover the technicalities of moving from Microsoft Access to Microsoft SQL Server database.

Thankfully, Microsoft developed a special tool for that purpose named Microsoft SQL Server Migration Assistant for Access (SSMA). We have split the process into two steps: Preparation and SSMA.

Steps to Convert MS Access Database to MS SQL Database Engine:

1. Preparing the Microsoft Access database:

Before starting the migration process, you want to ensure that your Access database is in good shape. There are couple of key principles which need to be followed here:

• Access table needs to have an index and a primary key.
• You need to check the primary/foreign key relationships because SQL Server does not support joining columns on different data types and sizes in constraints.
• The Attachment column needs to be removed. In Microsoft Access, the tables can contain the column of datatype Attachment.
• You may ask yourself how do I Convert or Migrate MS Access database to SQL Server? Simply follow the steps below:

2. Things you need to do with MS Access Database:

• Close the Access database.
• No other users may be connected to the database.
• If the database is in the mdb file format, user-level security needs to be removed.
• Back up your Access database, just in case if something goes wrong.

3. Microsoft SQL Server Migration Assistant for MS Access - SSMA:

Migration, with Microsoft SQL Server Migration Assistant makes the process seamless. The objects that are migrated are tables and select queries with no parameters. Naturally, forms, reports, macros and VBA modules can’t be converted.

4. Below are the Steps to migrate to MS SQL from MS Access while using SSMA:

• Download the SSMA tool from here: https://www.microsoft.com/en-us/download/details.aspx?id=54255
• Open the SSMA from the PC, preferably the machine which contains the Access database.
• The Migration Wizard will be your guide. First you will need to set up a new project, pick the version of the SQL Server, and save the project on your local hard drive.
• In the next step, you will need to add the Access Database.
• After that, you will be prompted to pick the Tables and Queries you want to migrate.
• In the next step, you will need to set up the connection to the SQL Server.
• If you want to use your existing Access applications with SQL Server, you can link your original Access tables to the migrated SQL Server tables. This is as simple as clicking a checkbox “Link Tables”.
• When you hit “Next”, the conversion will start and the tool will convert, load and migrate the data to the SQL Server database.
• After the conversion is complete, the summary screen will appear. If there were any errors, you can click on the “Report” button to try and identify the potential causes.
• You hit “Close”, save the project and you can exist the SSMA program.

5. Check and see if all all tables were migrated successfully to MS SQL database:

Now you can go to your SQL Server database and check whether all the objects have been migrated. You might have decided to keep the Access as your front end. However, if you would want to implement a well-rounded, robust, secure and scalable multi-user web-based system, you can contact us and we will happily guide you through the process.

6. Automation to improve the Data Migration:

When we develop a new application, there can be a short period when you work in parallel in the old (Microsoft Access system) and in the new application.

For such scenarios, our software developers or the MS SQL consultants will create special SQL stored procedures which transfer the data from the conversed MS Access database to the new designed SQL database.

That process was taking place right after MS Access database was fully up-sized. In reality when developing a new application in most cases the design of the MS SQL database would be different from the old MS Access.

If that's the case then it easier to complete the migration from one SQL database to another.

That process can be automated and it can be run on schedule, so the transition to the new system is much smoother for the client.

Conclusions - Conversion from Ms Access to MS SQL Database:

There are multiple steps to consider when migrating into SQL database.

In most cases this process could be part of the conversion from access to a web application.

We take even some additional steps and create Stored Procedure to cleanse the data from the converted MS Access database into the new designed SQL database.

In this point the client can view their data via the web bases solution and continue to test with the app with the data they are familiar with.

In other cases when the client still wants to stick to their MS Access application. In this point MS SQL Database will function as the back end for the MS Access application.

The 2GB limit for MS Access will not be a problem anymore.

For additional info of the development process above please contact us or call us via this phone: 732-536-4765.