Migrate MS Access Database to SQL Server by 6 Easy Ways

Before diving into the main context, let’s go through the related terms. As we know Microsoft Access and SQL Server are well-known database management systems. By using these mediums, you can easily customize your database to store and manage data successfully. MS Access is an integral part of Microsoft Office Suite.  On the other hand, Microsoft SQL Server is a server-based database management system (DBMS), especially used by corporations or enterprises.

About MS Access

Access is owned by Microsoft and is a DBMS. Practically, has more usage in small-scale applications. It has an intuitive and elegant user interface, software development tools, and a relational database system. Further, it is very useful for data entry, web development, and software development fields. Moreover, the latest version of MS Access provides a comprehensive set of tools that simplify the user experience with the databases.

About MS SQL

Microsoft SQL (Structured Query Language) Server is a robust database engine that enables users to manage large amounts of data with good scalability and high availability options. In addition, this utility has a built-in visual basic programming language by which users can create stored procedures and functions for custom applications. Furthermore, it has its own set of data types, security features, and query optimization tools. Thus, users can seamlessly use MS SQL Server in their workflow to address distributed queries, transactions, and replication.

Comparison Table on MS Access and SQL Server

To get an in-depth analysis of the above two powerful concepts, you can see the difference table shown below:

Feature	Microsoft Access	Microsoft SQL Server
Primary Database Model	Relational Database	Relational Database
Initial Release	1992	1989
Server Operating Systems	Windows	Windows, Linux
Supported Programming Languages	VBA (Visual Basic for Applications)	T-SQL (Transact-SQL), .NET languages, Java, Python, etc.
Usability	User-friendly with GUI	Primarily managed through SQL Server Management Studio (SSMS)
Users	Typically used by small to medium-sized teams or individual users	Suitable for large enterprise-level teams and applications
Performance	Can be slower for large datasets	Optimized for performance in large datasets
Development Language	Supports Visual Basic for Applications (VBA)	Uses T-SQL and other languages for development
Data Storage	Single file (MDB or ACCDB) for storage	Uses databases stored in separate files (.mdf and .ldf)
Cost	Generally more affordable	Licensing costs may be higher, especially for large-scale deployments

 

Manual Methods to Migrate MS Access 2016 Database to SQL Server

In the following processes, we will be focussing on transferring the data such as schema structures, forms, queries, reports, and other objects using the basic approach. Now, make sure to meticulously execute each step to migrate the Access file to SQL Server without any data loss.

Convert Access Database to SQL Server Using SSMA

Microsoft SQL Server Migration Assistant (SSMA) for Access is installed on the system using the Windows Installer-based wizard. In this section, we will provide the complete installation and conversion guide using the latest version of SSMA.

Prerequisites

Ensure that your system has the following requirements, before installing the SSMA application:

• Make sure the machine has Windows 7 or recent versions. Also, use Windows Server 2008 or the latest version and Microsoft Windows Installer 3.1 or above versions.
• For .NET Framework, you can visit https://learn.microsoft.com/en-us/dotnet/framework/
• Check for Access permissions on the computer that hosts the targeted SQL Server or Azure SQL Database where you are migrating the database objects.
• Microsoft Data Access Object (DAO) provider version 12.0 or 14.0, available in Microsoft Office 2010/2007 or for download.
• 4 GB RAM is recommended.

Install SSMA

To download the up-to-date version of SSMA, look for the SQL Server Migration Assistant download page – https://www.microsoft.com/en-us/download/details.aspx?id=54255.

Note: It is recommended to uninstall all the previous versions of SSMA for Access before installing the new version.

Steps to install SSMA:

Open the SSMAforAccess_n.msi. Here, n is the build number.

In the introductory windows, press Next.

However, if you don’t have the prerequisites installed on your system, a message will pop up showing that you must install the required components first. After fulfilling the earlier components, run the program again.

Go through the End-User License Agreement. Then, accept the agreement and click on the Next button.

Under the Setup Type page, pick Typical.

Next, on the Ready to Install page, you will have the option to enable or disable telemetry and automatic update checks every time the tool launches. At the end, tap on the install tab to initiate the installation process.

By default, the installation location of this program is set to →” C:\Program Files\Microsoft SQL Server Migration Assistant for Access”.

Pre-migration Assessment

Open SSMA for Access.

Create a new project: Navigate to “File“→ “New Project“.

Enter the project name and save the project to the desired location.

Next, from the dropdown list, choose the SQL Server migration option. Then, click on OK.

Include the Access databases you want to assess.

Right-click the desired database in Access Metadata Explorer and choose Create Report. Alternatively, access the “Create Report” tab in the upper-right corner.

Examine the HTML report for conversion stats, errors, and warnings. You can also open it in Excel to view an object inventory and schema conversion efforts. By default, the report is stored in the SSMAProjects folder.

Validate Data Types

Choose Project Settings from the Tools menu.

Navigate to the Type Mapping tab.

Review and adjust default data type mappings as needed.

Convert Database Objects

Connect to SQL Server using SSMS.

Convert database schema by right-clicking on the database in Access Metadata Explorer or using the Convert Schema tab.

Check for differences between converted and original objects and address any issues.

Review the converted Transact-SQL text against the original code.

Optionally, convert individual objects by right-clicking and selecting Convert Schema.

Review results in the Error List pane for any errors.

Store the project locally via File → Save Project for offline schema remediation exercises.

Post-migration Validation

Validate the migration data records by reviewing the data and schema.

Migrate Access Database to SQL Server using SSMS

SQL Server Management Studio (SSMS) is primarily used for managing SQL Server instances, databases, and related objects. It is unlike the above tools like SQL Server Migration Assistant (SSMA) or SQL Server Integration Services (SSIS) for access. As it is solely built for administrative tasks still it can perform data migration such as migrating the data from Access database to SQL Server.

Step 1. Check the Working of SQL Server Management Studio and Microsoft Access

Ensure you have the SQL Server installed and running on your machine. Further, check the administrative access to both SQL Server Management Studio (SSMS) and Microsoft Access.

Step 2. Export Data from Access

• Open your Access database.
• Select the table(s) you want to migrate to SQL Server.
• Go to the “External Data” tab.
• Click on “ODBC Database” in the “Export” group.
• Follow the wizard to create a new data source for your SQL Server database.
• Select the appropriate SQL Server driver and provide connection details.
• Choose the destination tables in SQL Server for each Access table.
• Complete the export process.

Step 3. Import Data into SQL Server using SSMS

Once you are done with the export process from MS Access. Now, you can use the SQL Server Management Studio (SSMS) to import all the databases to SQL Server.

• Launch SQL Server Management Studio (SSMS).
• Next, connect to your SQL Server instance with all the necessary credentials.
• In the Object Explorer menu, right-click on the target database that you wish to import the data into from MS Access.
• After that, go to “Tasks” → “Import Data“. This will open a new wizard with SQL Server Import and Export option.
• In this wizard, choose the Access data source. Then, pick the right Access database file where you want to export.
• Here, select the tables or queries you want to export from Access.

In the “Choose a Destination” window:

• Pick the “SQL Server Native Client” or appropriate OLE DB provider as the destination.
• Henceforth, go for the “Copy data from one or more tables or views” if you want to import entire tables.
• If you want to modify or change the column mappings or data types, click on the “Edit Mappings” option. But before that ensure that the source and destination columns are correctly mapped.
• Lastly, tap on “Next” to start the import process.
• After the completion of the import process, SSMS will display the resultant errors if any encountered.
• Once migration is done, review and verify the results correctly.

Migrate Access to SQL Server through SSIS

SQL Server Integration Services (SSIS) is a part of the SQL Server suite. Mainly, it is used for ETL (Extract, Transform, Load) operations without Access software. Additionally, you can employ this tool to create a package that can extract the data from Access database and export it into SQL Server. Most often, it requires some basic tech skills to run this application, if you are not familiar with the interface.

Step 1. Download the SQL Server Data Tools (SSDT) tool from the official Microsoft site. Then, Open it. It is a specialized development environment dedicated to SQL Server.

Step 2. Click on Create to unfold a new SSIS project.

Step 3. Within the SSIS project manager, add a Data Flow Task.

Step 4. Next, configure the parameters in the Data Flow Task i.e. source component for Access and destination component for SQL Server.

Step 5. After that, Map columns from the Access source to the destination SQL Server columns respectively.

Step 6. Finally, carrying out the SSIS package for Access migration to SQL Server database.

Convert Access Database to SQL Server by Upsizing Wizard (Microsoft Access)

Besides the other approaches above, you can use the built-in feature of MS Access to migrate Access to SQL Server. Access has an Upsizing wizard which can be effectively used for the export process. Now, follow the instructions below to operate:

Step 1. Open the MS Access application on your system.

Step 2. Next, go to the “Database Tools” tab.

Step 3. After that, under the “Move Data” group, select “SQL Server.”

Step 4. Enter the information about the destination SQL Server like server name and authentication in the Upsizing Wizard.

Step 5. Here, choose the database objects such as tables, queries, etc. that you want to export to the SQL Server.

Don’t forget to review the data after migrating from Access database to SQL Server.

Also Read: How to Export Access to Excel file format without any data loss?

Migrate Access to SQL Server via VBA Code

This technique is one of the easiest ways to import data from Microsoft Access to SQL Server using VBA code. VBA stands for Visual Basic for Applications. Follow the guide to accomplish the export process.

Step 1. Make sure your SQL Server is live and has the necessary permissions enabled to import databases and tables.

Step 2. In the Access database, ensure that you have the integration of “Microsoft ActiveX Data Objects x.x Library.” To set this reference follow the process below:

Press Alt + F11 together to open the VBA editor. Then, go to Tools → References. After that, select the suitable version of “Microsoft ActiveX Data Objects x.x Library.”

Sub MigrateDataToSQLServer()

Dim conn As Object

Set conn = CreateObject(“ADODB.Connection”)

‘ Connect to SQL Server

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

conn.Open

‘ Export data from Access to SQL Server

Dim strSQL As String

strSQL = “SELECT * INTO YourTableName IN ‘ODBC;DRIVER=SQL Server;SERVER=YourServerName;DATABASE=YourDatabaseName;Trusted_Connection=Yes’ FROM YourAccessTableName;”

CurrentDb.Execute strSQL, dbFailOnError

‘ Close connection

conn.Close

Set conn = Nothing

End Sub

Keep in mind to replace the placeholder values with their actual names like YourServerName, YourDatabaseName, YourUsername, and YourPassword.

Important Notes:

• Before starting the process, ensure that your SQL Server has enabled the remote connections option.
• Test the code in a demo environment before running the same on your system.
• The above code uses the SQL Server authentication services. However, if you are using Windows authentication, you can set your connection string accordingly.

Also Read: Different Ways to Convert MDB to CSV File Format.

Shortcomings in the Easily Accessible Solution to Transfer Access to SQL Server

Here are some of the drawbacks i.e. associated with the process of migration from Access to SQL Server database:

• Time-consuming and Labour-intensive task
• Risk of Errors, Data loss, and Data alteration
• Requires technical knowledge
• At times, migration stops due to unexpected error
• Limits automation and challenges to schema mapping
• An inevitable system or electricity shutdown can result in repeating the procedure

Migrate Access Database to SQL Server – Expert Guide

In lieu of manual techniques, where the process is long, cumbersome, requires technical expertise, and involves risk of data loss & data corruption. Thankfully, we have the expert’s verified Top Access converter tool, where you can swiftly perform the migration without any problem.

Further, by using this application, you can not only convert Access file to SQL Server database but also to other file formats such as Excel, CSV, ACCDB, MDB, and more. Also, it can recover the deleted database objects from the .accdb/.mdb files.

Download Now Purchase Now

With this information in mind, let’s check the migration process without any errors or data loss. Even if you land any error, you can contact customer services 24×7.

Step 1. Download and Open the Access migration to SQL Server tool on your computer.

Step 2. Hit on the Browse tab to locate the Access (.accdb/.mdb) file in your storage.

Step 3. Tap on the Export button. Then, select the SQL Server from the dropdown Select Export Options.

Step 4. Next, enter the SQL Server details like Server Name, Authentication, and more.

Step 5. At the end, tap on the Export/Save tab to begin the conversion with all the parameters selected as shown in the figure below.

Final Takeaway

In this write-up, we have learned about the easily accessible and professional solutions to encounter the problem of “How to migrate Access to SQL Server? ” No doubt that the manual methods are easy to find but there are some serious limitations to them. As a result, we suggest you rely on the migration tool Access to SQL Server.