Migrate Access database to SQL Server

How to Migrate Access Database to SQL Server

There are lots of benefits to migrate Access Database to SQL Server. The most important two benefits are supporting more users than MS Access which is limited to 255 concurrent users only and exceeding the limit size of 2 GB. Other benefits of migrating MS Access database to SQL Server are:

  1. The SQL Server database usually performs better than MS Access database, especially with a large, terabyte-sized database.
  2. The SQL Server integrates with Windows system security to provide a single integrated access to the network and the database, employing the best of both security systems.
  3. The SQL Server can automatically recover the database to a consistent state in a matter of minutes and with no database administrator intervention which is very important in case of operating system crashes.
  4. Remote users can use the Access front-end database on any desktop.

Steps to migrate Access Database to SQL Server

Step 1:

  • Open Microsoft SQL Server Management Studio.
  • Right click on database and select New . Give it a name, for example: “D-SALES-MGR”.
  • Right click on it and select “Tasks”
  • Select Import Data…
  • Select the MS Access that you already bought.
  • Select all tables and import them to SQL Server.
  • The name of the server (highlighted in red box), we’ll use it later when connecting to the ODBC.

Step 2:

  • From control panel. Open Administrative Tools.
  • Select ODBC Data Sources.
  • When the dialog opens, you have to choices depends on whether your PC is acting like a server or normal PC. If it’s a server, then choose the tab “System DSN”. Otherwise, you’ll have to choose “User DSN”.
  • Click Add, and select SQL Server Native Client 11.0 and click Finish.
  • On the other dialog box, give the new data source a name. Let’s name it “D-SALES-MGR” or any other name.
  • Click on the server. You will find the name of your PC. Just complete it to be exactly like the name we’ve mentioned in the red box above. Something like DESKTOP-YOUR-PC-NAME\SQLEXRPESS
MS Access SQL Server

Note: the server name must be your systems.

  • The other window, just leave it as it is and click Next.
  • Click on “Change the default database to” and select the name appears in step E. for example “D-SALES-MGR”.
  • The other window, don’t change anything. Just click on Finish.
  • Make a test to ensure everything is OK and click finish.

Step 3:

  • Open Microsoft Access, the one you’ve already bought. Press Shift Key Just immediately after you click the program’s icon.
  • From External Data, click on ODBC Database
  • Click Link to Data Source.
  • Click Machine Data Source.
  • There you will find the name “D-SALES-MGR”.
Access SQL Server

The last step, after you link the data source. You might need to rename all of the tables by just removing the  dbo. So as to be exactly like the original. From there, the program will be ready and connected to SQL Server.

Still need more information, please click this link.