How To Convert OR Migrate An Access Database To SQL Server

Summary: This blog is written with the main prospective of providing users with easy and simple idea on how to convert or migrate an Access Database to SQL Server. So, from now onwards it becomes too easy for you to optimize performance and allow more robust multi-user Access to the database.

With the passage of time database application grows in size and complexity the same is with the Access database, perhaps you need to consider upsizing it to Microsoft SQL server database  for better optimized performance, availability, security, scalability,  reliability, and recoverability etc..

In such situation all you need is to convert Access To SQL. Luckily, Microsoft provides an upsizing wizard in access 2010 that makes it easy to convert your database.  As, this tutorial discusses the process of converting or migrating Access database to SQL server.

Convert Access Database To SQL Server

Thing You Need To Do Before Converting Access Database To SQL

Before starting to convert Access to SQL, you need to do the following things:

• Make backup of your database.
• Check that if you have plenty of disk space on the device or not; in which you will have to keep your upsize database.
• Allow permissions on the SQL Server database.
• Add unique index to each access table that you have, before upsizing it.

How To Convert Access Database To SQL?

The following below mentioned steps will give an easy idea on how to convert Access Database to SQL. So, follow the steps one by one.

1. Open your database in MS access.
2. Select the Database Tools tab in the Ribbon.

3. Tap to the SQL Server button placed in Move Data As this will opens the Upsizing Wizard.

4. Choose whether you wish to import the data into an existing database or create a new database for the data.

In this tutorial we are creating a new SQL server database using the data in your Access database. So click Next and continue.

5. Give the connection information for the SQL server installation. You will need to assign name to the server. Credentials for an administrator with permission to create a database and the name of the database you want to connect. After providing the information click to the Next option.
6. Make use of the arrow buttons to move the tables you want to transfer to the list labeled Export to SQL Server. After then tap to the Next button to continue.
7. Look the default attribute that needs to be transferred and make if any changes needed. You required to preserve settings for table indexes, validation rules and relationships, among other settings. After completing all this, tap to the Next option to continue.
8. Now it’s up to you whether you choose creating a new Access client/server application that will access the SQL Server Database, changes your existing application to reference the data stored on SQL Server, or copy the data without making any changes to your access database.
9. At last tap to the Finish option and wait for upsizing process to complete. After finishing all this review the upsizing report for important information about the database migration.

How To Migrate Data From MS Access To SQL Server Database?

To migrate Access database to SQL server you just need to make sure that SQL Server or SQL Server Express installed on your PC. If you don’t have such and needs to download SQL Server Express on a personal computer, then be sure to download the version having advanced services. If it is not there, than the database engine won’t be get installed and you’ll have SQL Management Studio without any instance to connect.

After installing the SQL Server properly, start following the below mentioned steps to import Access database into SQL Server.

Transfer Access Database To SQL Server

Open SQL server management studio and connect it to the database server you wish to export Access database to SQL. Under databases, make a right click and choose New Database.  If you are already having a database and you just simply need to import couple of tables from access, then just skip this and go to the import data step mentioned below.

• Make a right click on your current database instead of creating a new one.

• In case you are making a new database, then give it a name and configure the settings if you want to change them from the default.

• After then make a right click on the Test database that you have just created and choose Tasks and then Import Data option.

• From the Choose a Data Source dialog box, choose Microsoft Access (Microsoft Jet Database Engine)  from the drop down box.

• Besides the File Name there is an option of Browse, so navigate the access database that you want to import and click Open.

Note: Database can’t be used in Access 2007 or higher format (ACCDB) Access Database file as SQL Server unable to recognize it.

Do if you are using access 2007 to 2016 databases then first you need to convert it to the 2002-2003 Database format (MDB) by going to the File – Save As.

After then click to the Next option to choose the destination.  As you have right clicked the database you wanted to import the data into, it should already be in the list but if it is not their then select SQL Native Client from the Destination drop down. You see the database instance within the server name and then be able to choose the specific database at the bottom after choosing the method of authentication.

Tap to the next and specify how you want to transfer the data from Access to SQL by selecting the option Copy the data from one or more tables or Write a query to specify the data to transfer.

• In order to copy all the tables or just few tables from the access database without any manipulation, select the first option.
• But if you need to copy only certain rows and columns of data from a table, choose the second option and write a SQL query.

By default, all the tables should be selected and if you tap to the Edit Mappings button, you can see how the fields are get mapped between the two tables. If you  have created a new database to import then it will be same as the original one.

Click Next and you will see Run Package screen where Run Immediately should be checked.

Click to Next option and then at the Finish.  You can now see the progress of the data transfer. After completion of this data transfer you can see the number of rows transferred for each table in the Message column.

Tap to the Close option and you are all done. You can now proceed further and run a SELECT on your tables to check that all the data got imported correctly or not. Now you can enjoy the benefits and power of SQL Server for managing your database.

Conclusion:

Now you must have got a clear idea on how to convert Access database to sql server or import Access database to SQL server. So try these simple steps and enjoy the power of SQL server for easy managing of your vast Access database.