?In this article, we will talk about migrating a database in MDB to SQL with of Migration Assistant!
The great advantage of SQL is that you can store the database on a server, allowing everyone connected to that instance to have the same components, pages and settings. The changes will be effective for everyone. Another advantage of SQL is loading the tables faster, being more efficient than the MDB files.
?To do the migration, we will have to use the SQL server Migration Assistant for Acess. You can download it clicking here.
?You just need to download the version according your computer, like 32 or 64 bits.
To find the correct version to download, you need to check your office package installed in you computer. To find it out, open a new word document, go to “Archive” > Account > About word.
?After this, download the proper version
?After download it, open the directory of the file and run the installer.
Click next until you reach this screen. Select the “complete” option, to avoid that some necessary file in the installation not to be installed. Click install.
Allow the file to make modifications and click finish after the installation is finished.
This icon will be created in your desktop
Open the software. This is the program that we will use to do the migration of the tables in MDB to SQL.
Click next, this window will appear to create a project.
Here, you will inform the name of the migration project. In my case, I will leave the default name. Then, the location of the new SQL database will be saved, and finally, to which SQL server you want to migrate. In my case, I have SQL Server 2014 installed, so I will select it.
If you do not know which one you have installed on your computer, you can search in Windows for “SQL Server” and it will appear which one you have. If you do not have any installed and would like to download and configure it, you can follow the steps in this video/article.
In this window, you need to select the MDB database that you want to migrate.
To choose it, click in add database. A window will appear to the user make the bank note. In this case, I will be converting a software base from G-Electrical in MDB into SQL server.
After adding the database, click on next.
A window will appear, you should check the Tables box (to convert the database tables), and after this click on next.
A window to configure the database instance in SQL Server wil appear.
To do the next steps you need the SQL Management installed and an existing instance.
Fill the data required. You can just copy from the SQL Management Studio.
Note that in Database, it is recommended that we create a new database to store these tables without compromising the other databases. In this example, we name it as “New SQL Base.”.
The message below will appear, click “yes” to create the new database.
This message only refers to the system not finding a database with the given name, but will be created by clicking “Yes”..
After that, the assistant window will appear to perform the migration. We can only give next
The assistant will start the convertion of the tables to SQL. After that, you can check the tables correctly converted.
Now you need to charge and migrate the objects to the new database. To do it just click ok in the previus screen. After the migration, you just close the assistant window.
If any “warning” or “informational messages” appear at any stage, do not worry, it is normal and should not interfer with the migration process.
The last procedure to do is to enter in your SQL management studio and verify that the database was created and the converted tables are there.
