How to Migrate ACCDB to SQL Server? – An Exclusive Version
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.
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.
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:
Select the installer version you require and click “Next.”
Next, Double-click on the downloaded file.
In the new SSMA for Access Setup Window, press on “Next“.
Check the “I accept the agreement” radio button text. Then, hit on “Next.”
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.”
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.
Once the installation is completed, click “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.
Next, a wizard will appear after you open the tool showing all the steps you need to perform.
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.
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.
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.
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.
However, if you don’t have the database pre-existed, it will be created. To do this, tap 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.
The migration process begins.
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.
The Access objects migration process is completed. Then, hit on Close.
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.
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.
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.
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.
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.
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.
Step 2. Click on the “Browse” button to locate the ACCDB/MDB file.
Step 3. Press the “Export” tab to initiate the conversion from MS Access to SQL Server.
Step 4. Choose the SQL Server version from the dropdown list in the Select “Export Option.”
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.
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.