Home » Blogs » migration » How to Migrate ACCDB to SQL Server? – An Exclusive Version

How to Migrate ACCDB to SQL Server? – An Exclusive Version

author
Published By Deepmala Pandey
Published On March 20th, 2024
Reading Time 9 Minutes Reading
Category migration

Highlight: In this write-up, we will discuss the different ways to migrate ACCDB to SQL Server including the SQL Server Migration Assistant (SSMA) for Access (.accdb) and SQL Server Management Studio (SSMS). Besides this, we will also have insights related to an advanced database conversion tool that can assist you in easily migrating the Access database to Live SQL Server. 

Table of Contents Hide

Why Companies Convert ACCDB to SQL Server?

When it comes to managing the organization-level data, MS Access doesn’t perform up to mark due to several reasons. In simple words, MS Access is losing its popularity despite having an intuitive and powerful interface. In fact, it’s been 30 years since its inception. Moreover, in Nov 2017, Microsoft announced the retirement of MS Access from the online suite but later reversed its decision with the release of Office 365 in Sept 2020. 

Even though MS Access is simpler to learn, it provides great flexibility in creating relational databases and the opportunity to use VBA. Still, many companies are starting to migrate Access to SQL Server due to SQL’s several advantages. One such advantage is it offers related database systems. Apart from this, Microsoft Access is an outdated model in comparison to modern systems like FoxPro, COBOL, and SQLBase.

To learn more, check out the comparison table between MS Access and SQL Server given below.

Ms Access vs SQL Server

Method #1: Migrate ACCDB to SQL Server using SSMA for Access

SQL Server Migration Assistant for Access is a part of the SQL Server Migration Assistant (SSMA) i.e. founded by Microsoft. This tool is proficient to automate and streamline the data migration process. Besides this, it allows you to perform migration from any version of Access ranging from 97 to 2010. 

Through this method, you can easily import ACCDB to SQL Server 2019 or other versions. Also, it enables seamless migration to Azure SQL database objects. You can free install the latest version 9.3.

To use the tool, make sure you fulfill the prerequisites:

Microsoft .NET Framework version 4.7.2 or higher

Microsoft Data Access Object (DAO) provider version equal to or greater than 12.0

Note: You should have the credentials of the SQL Server or Azure SQL Database instance for a viable connection.

Installation of SSMA for Access

The installation process is straight and simple. Visit the Microsoft SQL Server Migration Assistant official download page to get the tool. On this page – scroll down and click on the download button to run the installation (a file with a .msi extension), and follow the steps below:

download

Select the installer version you require and click “Next.”

click on next

Next, Double-click on the downloaded file.

download page

In the new SSMA for Access Setup Window, press on “Next“.

Check the “I accept the agreement” radio button text. Then, hit on “Next.”

I accept the agreement

Note: A message box could appear if you are missing some prerequisites. In such a case, exit from the installation guide, install the missing prerequisites, and launch the setup again. 

After that, in the Choose Setup Type window, Select “Typical” as the setup type. Click on “Next.”

choose typical

Check both options i.e. “send telemetry to help improve the quality, reliability, and performance”, and “Automatically check for newer versions.” Henceforth, hit on Install to begin the installation. 

check options and click on install

Once the installation is completed, click “Finish.”

tap on finish

Using SSMA for Access to Migrate ACCDB to SQL Server

After the installation of SSMA for Access, you will find it on the Windows menu.

windows menu

Next, a wizard will appear after you open the tool showing all the steps you need to perform.

opening window of SSMA

Enter the project name and its location to create a project. Also, you need to select the version of SQL Server from the drop-down list where you want to Migrate To. After this, press the Next button.

enter details

Then, press on Add Databases to specify the Access database or you can use the search them by clicking on the Find database button. Next, press the Next button.

click on add databases

On the left side of the Explorer window, you can preview the Access database objects (queries and tables) in tree structure format. In addition, for more information, you can see the list of fields, indexes, and keys. 

access database structure

Likewise, in the screenshot given below, you can view the Access database with the name of Campaign_Template. Further, it contains three fields and a primary key. Now, press on Next

Fill in the information to establish the connection with the SQL Server instance. Then, tap on the Next button. 

enter login details

However, if you don’t have the database pre-existed, it will be created. To do this, tap on Yes.

click on yes

If you desire to use the existing Access applications with SQL Server, you can choose to link the Access tables to the migrated SQL Server tables. After that, hit on Next.

press on Next

The migration process begins.

migration process starts

Here, verify the migration status – the results will show that the test table (Campaign_Table) is not available in the SQL Server database. Click on OK

check the results.

The Access objects migration process is completed. Then, hit on Close

migration completed

Now, the process to migrate ACCDB to SQL Server finishes using the SSMA for Access method. 

After the completion of the migration process, please verify the details that are transferred from Access database to SQL Server database. Here, you can utilize the main window to compare the read and written information.

comparison tool window

Method #2: Convert Access to SQL Server using SSMS

In lieu of other methods, you can use the SQL Server Management Studio (SSMS) to migrate ACCDB to SQL Server. 

Run the Object Explorer, and navigate to the database. Then, right-click on the same. 

After that, Go to Tasks Import Data

Click on Tasks

In the Origin Data, Choose Microsoft Access [Microsoft Jet Database Engine] from the drop-down. And also, enter the name of the Access Database (.accdb/.mdb) file.

choose Microsoft Access option

Next, select the target format. Here, you can use the SQL Server Native client 10.0 or 11.0 to import the data file to the SQL Server database. 

After establishing the connection to the target SQL Server database, opt for the tables you want to migrate. 

At this segment, you can preview the data to import ACCDB to SQL Server. Additionally, you can select the correct mapping of the columns between the tables.

At the end, hit the Finish button to initiate the migration process.

press on finish

Method #3: Migrate ACCDB to SQL Server Using VBA Code

Comparatively, this above method is way easier in migrating data from an Access database (.accdb/.mbd) to SQL Server using VBA Code. Before using the code, ensure that you have already set up an SQL Server and Access database with the necessary permissions allowed. Follow the code to employ the import process in Access. 

Sub MigrateToSQLServer()

    Dim conAccess As Object, conSQLServer As Object, rsAccess As Object, rsSQLServer As Object

    Dim strSQL As String

        ‘ Access database connection

    Set conAccess = CreateObject(“ADODB.Connection”)

    conAccess.Open “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Path\To\Your\Database.accdb;Persist Security Info=False;”

    ‘ SQL Server database connection

    Set conSQLServer = CreateObject(“ADODB.Connection”)

    conSQLServer.Open “Provider=SQLOLEDB;Data Source=YourServerName;Initial Catalog=YourDatabaseName;User ID=YourUsername;Password=YourPassword;”

    ‘ Access recordset

    Set rsAccess = CreateObject(“ADODB.Recordset”)

    rsAccess.Open “SELECT * FROM YourAccessTableName;”, conAccess, adOpenForwardOnly, adLockReadOnly

    ‘ SQL Server recordset

    Set rsSQLServer = CreateObject(“ADODB.Recordset”)

    rsSQLServer.Open “SELECT * FROM YourSQLServerTableName;”, conSQLServer, adOpenDynamic, adLockOptimistic

    ‘ Transfer data

    Do Until rsAccess.EOF

        rsSQLServer.AddNew

        rsSQLServer.Fields.Append rsAccess.Fields.ItemArray

        rsSQLServer.Update

        rsAccess.MoveNext

    Loop

    ‘ Clean up

    rsAccess.Close: Set rsAccess = Nothing

    rsSQLServer.Close: Set rsSQLServer = Nothing

    conAccess.Close: Set conAccess = Nothing

    conSQLServer.Close: Set conSQLServer = Nothing

    MsgBox “Data migration completed!”, vbInformation

End Sub

Remember to replace the placeholders such as YourServerName, YourDatabaseName, YourUsername, YourPassword, YourAccessTableName, C:\Path\To\Your\Database.accdb, and YourSQLServerTableName with their actual names and address. 

Also, do not forget to keep a backup of your database before performing the migration process. 

Also Read: Different Ways to Convert Access to Excel file.

Method #4: A Quick Solution to Convert ACCDB to SQL Server

In contrast to the cumbersome manual method or the risky VBA code method or any free methods, all pose threats to data loss and corruption. However, thankfully, in this regard, there is an instant and reliable solution to migrate ACCDB to SQL Server i.e. ACCDB to SQL Server migration tool.

Download Now Purchase Now

This tool is not only proficient in importing data from Access to live SQL Server but also allows conversion in different formats such as Excel, CSV, ACCDB, MDB, and more. Further, with its self-explanatory interface, you can easily navigate and do the desired operation. 

Follow the outlined steps given below to seamlessly complete the conversion process without any data loss. 

Step 1. Install and open the Access database to SQL Server migration software on your system.

tool main window

Step 2. Click on the “Browse” button to locate the ACCDB/MDB file.

hit on the Browse button

Step 3. Press the “Export” tab to initiate the conversion from MS Access to SQL Server.

press on the Export button

Step 4. Choose the SQL Server version from the dropdown list in the Select “Export Option.”

select SQL server from the export options

Step 5. For reference, please refer to the screenshot below or review and select options such as Schema & Data, Export Deleted Record, and other parameters. Finally, click on the “Export/Save” tab to commence the import.

finally, click on Export/Save Option

Final Takeaway

In this piece of information, we have discussed the basic and advanced methods to migrate ACCDB to SQL Server. Though there are several manual approaches such as SQL Server Migration Assistant for Access, SQL Server Management Studio (SSMS), VBA Code, and more. But none of them can match the effectiveness and rich capabilities of the database converter utility to convert the Access database to SQL Server (.mdf) format.

We are the global leader in End-to-End IT Managed Services, Server Management & Security, and Datacenter Services discipline.

© 2024 MSOutlookTools All Rights Reserved