While building a software application, the question arises where will you store your data and how? The traditional approach is a simple text file or CSV file which is not a good choice in today’s big data world. The solution is Database Management System(DBMS) which allows different user application programs to concurrently access the same Database. A Database Management system(DBMS) is a simpler, easier, reliable, faster, secure, and powerful software tool for Storing, Managing, and Retrieving data.

In this article, you will gain information about migrating databases from MS Access to SQL Server. You will also gain a holistic understanding of Microsoft Access, Microsoft SQL Server, their key features, and the steps for migrating a database from Microsoft Access to SQL Server. Read along to find out in-depth information about migrating databases from Microsoft Access to SQL Server.

Solve your data replication problems with Hevo’s reliable, no-code, automated pipelines with 150+ connectors.
Get your free trial right away!

What is Microsoft Access?

MS Access to SQL Server: MS Access
Image Source

Microsoft Access is a Database Management System (DBMS) by Microsoft that integrates a graphical user interface with the relational Access Database Engine (ACE) and software development tools. It’s part of the Microsoft 365 suite of apps, and it’s available as part of the Professional and above editions or independently.

Microsoft Access uses the Access Database Engine to store data in its own format (formerly Jet Database Engine). It can also immediately import or link data from other programs and databases.

Key Features of Microsoft Access

Some of the key features of Microsoft Access are as follows:

1) Ideal for individual users and smaller teams

Microsoft Access is a straightforward desktop application that does not require any special hardware or software to run. As a result, it is ideal for individual users and smaller teams who do not require larger and more complex databases at an additional cost.

Where the need for a database is not very high, Microsoft Access offers numerous advantages over database solutions such as SQL Server and Oracle. With the help of Microsoft Access, many smaller teams can now avoid purchasing massive resources for their basic and simple needs. Furthermore, unlike traditional client-server databases, they do not require anyone to administer or monitor Access in MS Access.

2) Easier than a client-server database to understand & use

Many applications that were previously relegated to a client-server database have been taken over by personal computer applications such as Microsoft Access. Access users do not need to undergo any special training to learn how to use the application. Using a readily available, well-organized set of templates, creating and administering a database is quite simple and easy.

Users of Microsoft Excel will find Access easier to learn because it is similar to Excel, even though the fundamental concepts of databases are vastly different.

3) Allows developers to create custom solutions using VBA code

Visual Basic for Applications (VBA) is a programming language that can be used with Microsoft Access. Access developers can use VBA code to create custom solutions for their databases. VBA code is a powerful programming language that consists of a code/command for specific programs.

When the program is run, the instructions will cause actions to be taken automatically. This powerful feature enables developers to transform simple custom end-user solutions into professional solutions by incorporating advanced automation, data validation, error trapping, and multi-user support into their databases.

4) Allows building & publishing Web Databases easily

Microsoft Access users can either design their own database or use a readily available template to create a database that meets their needs. Those who are tech-savvy and familiar with Web Databases would ideally design their own database by creating a blank database and then creating the tables that their database would require in MS Access.

Those who require assistance or are unsure of which tables their project will necessitate can make use of the templates provided. Microsoft Access templates include a large collection of databases that are commonly used by users.

5) Allows output Reports in PDF format

Through the EXTERNAL DATA tab on the ribbon, MS Access users can create reports in electronic image files. Because the PDF file can be opened in Adobe Reader, these reports can be viewed even by users who do not have Access installed on their system.

Access applications have been significantly improved as a result of the reports and databases being shared in PDF format. Users appreciate this output type, which is a welcome addition because it makes it convenient and simple to distribute reports via email, saving paper, and contributing to green computing.

What is SQL Server?

MS Access to SQL Server: SQL Server logo
Image Source

SQL Server is a Relational Database Management System(RDBMS). It is also known as Microsoft SQL Server or sometimes as MSSQL. SQL Server is developed by Microsoft and was initially released on April 24, 1989. Its stable release is SQL Server 2019 released on 4 November 2019. MSSQL is written in programming languages like C and C++.

SQL Server is based on the relational model introduced by E. F. Codd. In RDBMS data is stored in tables and the relationships among the data are maintained between tables. In tables, data is organized in rows and columns format. Each column represents a particular field or feature and each row in the table represents an entry or a record.

Microsoft SQL Server offers a variety of editions with different functionally: Express, Enterprise, Standard, Web, Developer. Express edition is freeware for small and entry-level applications while others are licensed-based editions used for bigger projects on commercial levels based on the service required. SQL Server supports XML data type format and dynamic management views.

To install SQL Server click here.

Key Features of SQL Server

MS Access to SQL Server: SQL Server architecture
Image Source

The main features of SQL Server that make it highly usable are:

1) Cloud Database Support

Microsoft SQL Server offers editions that can be integrated with Microsoft cloud or Azure SQL with built-in security and manageability. Cloud database support makes SQL Server highly available, fast failover with minimum uptime.

2) Ease of Management

Microsoft SQL Servers can easily be used with Windows and Linux systems. SQL Server deployment and its management is easy with Kubernetes.

3) High Security

Data in SQL Server is encrypted and highly secured. Schemas and Tables can be password protected easily and can be accessed with permissions only.

4) End-to-End Business Data Solutions

SQL Server mainly focuses on commercial needs so it provides end-to-end business data solutions. Microsoft SQL Server offers tools for Data Administration,  ETL solutions, Online Analytical Processing(OLAP), and Data Mining purposes. It additionally provides Option Reporting, Interactive Analysis, and Visual Data Exploration using SQL Server BI tools.

Simplify your Data Analysis with Hevo’s No-code Data Pipeline

A fully managed No-code Data Pipeline platform like Hevo Data helps you integrate and load data from 100+ different sources (including 40+ free sources) such as MS SQL Server to a Data Warehouse or Destination of your choice in real-time in an effortless manner. Hevo with its minimal learning curve can be set up in just a few minutes allowing the users to load data without having to compromise performance. Its strong integration with umpteenth sources allows users to bring in data of different kinds in a smooth fashion without having to code a single line. 

Its completely automated pipeline offers data to be delivered in real-time without any loss from source to destination. Its fault-tolerant and scalable architecture ensure that the data is handled in a secure, consistent manner with zero data loss and supports different forms of data. The solutions provided are consistent and work with different Business Intelligence (BI) tools as well.

Get Started with Hevo for Free

Check out why Hevo is the Best:

  • Secure: Hevo has a fault-tolerant architecture that ensures that the data is handled in a secure, consistent manner with zero data loss.
  • Schema Management: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data and maps it to the destination schema.
  • Minimal Learning: Hevo, with its simple and interactive UI, is extremely simple for new customers to work on and perform operations.
  • Hevo Is Built To Scale: As the number of sources and the volume of your data grows, Hevo scales horizontally, handling millions of records per minute with very little latency.
  • Incremental Data Load: Hevo allows the transfer of data that has been modified in real-time. This ensures efficient utilization of bandwidth on both ends.
  • Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.
  • Live Monitoring: Hevo allows you to monitor the data flow and check where your data is at a particular point in time.
Sign up here for a 14-Day Free Trial!

Steps For Migrating Database from MS Access to SQL Server

Because Microsoft Access ODBC drivers are only available on Windows, Microsoft Windows is necessary. The destination MySQL server can be on the same system or on another computer in your network.

The steps involved in migrating a database from MS Access to SQL Server are as follows:

1) Preparing a Microsoft Access Database for Migration

MSysRelationships is an internal table in Microsoft Access that stores relationship/foreign key information. Even the Admin user is denied “read” access to that table, therefore attempting to migrate without granting it permission will result in an error.

The following are the steps to grant “read” access to the Admin role in Microsoft Access 2007:

  • Step 1: Open Microsoft Access and create a database.
  • Step 2: To open the Visual Basic (VB) console, click the Visual Basic button on the Database Tools ribbon. This button’s location in the Macro area is depicted in the diagram below.
MS Access to SQL Server: Database Tools
Image Source
  • Step 3: To make sure you’re logged in as the “Admin” user, go to the Immediate panel and type “? CurrentUser” and hit the Enter button. This should produce “Admin” under “? CurrentUser” in the panel.
  • Step 4: To give access, type the following command in the Immediate panel:
CurrentProject.Connection."GRANT SELECT ON MSysRelationships TO Admin" should be run.
MS Access to SQL Server: Immediate panel
Image Source
  • Step 5: Close the Microsoft Access application.

2) Start the Migration Wizard for MySQL Workbench

The Migration Wizard can be launched from the main MySQL Workbench screen by clicking the “Database-migration launcher ( )” in the Workbench side panel or by selecting “Database“, then “Migration Wizard” from the main menu.

The Overview page of the Migration Wizard is displayed in a new tab, as seen in the following figure.

MS Access to SQL Server: Migration Wizard Welcome
Image Source

3) Set the ODBC Driver Configuration

  • Step 1: Open the system ODBC tool by clicking Open ODBC Administrator from the MySQL Workbench migration overview page to verify that the ODBC driver is installed.
  • Now, go to the Drivers tab as given in the image below.
MS Access to SQL Server: Drivers tab
Image Source
  • Step 3: To create a DSN for your database file, go to the User DSN page and click the “Add” button. For the northwind database sample, a new data source was generated, as shown in the next picture.
MS Access to SQL Server: UserDSN Tab
Image Source

4) Configure Source Parameters

  • Step 1: To proceed to the Source Selection page, click Start Migration from the Overview page. The information regarding the Access database you’re migrating from, the ODBC driver to use, and the parameters for the Access connection must all be entered here.
  • Step 2: To see a list of supported RDBMSes, open the Database System combo box and select “Microsoft Access” from the list.
  • Step 3: Below there is another combination box labeled Stored Connection. It contains a list of RDBMS connection settings that have been saved. You can store connections by checking the box at the bottom of the page and giving the connection a name.
  • Step 4: The Connection Method is selected in the next combo box. This time, you’ll choose “ODBC Data Source” from the drop-down menu. This allows you to choose from a list of pre-configured DSNs in your system.
  • Step 5: All DSNs you’ve defined in your system will appear in the DSN drop-down list. From the list, select the one you generated for the Access database being transferred.
  • Step 6: You can choose your database’s character set in the Default Character Set column (see the figure that follows).
  • Step 7: You can leave the default cp1252 if your Access version has western/Latin characters. If you use a localized version of Access, such as Japanese, you must input the correct character set for your Microsoft Office edition; otherwise, the data will be corrupted.
MS Access to SQL Server: Source RDBMS Connection Parameters
Image Source

Step 8: Finally, select Test Connection to see if you can make an ODBC connection. You should receive a message indicating a successful connection attempt if you entered the correct values.

5) Create a Set of Target Parameters

  • Step 1: Initially, by describing the parameters that link to your MySQL Server instance, set up the target (MySQL) database parameters.
  • Step 2: When you’re done, click the “Test Connection” option to make sure the connection is set up correctly. The Parameters tab is depicted in the diagram below.
MS Access to SQL Server: Setting parameters
Image Source

6) Choose the Objects You Want to Migrate

The reverse engineering of the specified database objects should then proceed. The migration wizard is now retrieving essential information about the database objects in question (such as table names, table columns, primary and foreign keys, indexes, triggers, views, and more). As indicated in the next image, you will be taken to a page that displays the progress.

MS Access to SQL Server: Information retrieval
Image Source

Step 1: Wait for it to finish and then double-check that everything went smoothly. The Source Objects section then shows a list of the objects that have been retrieved and are ready for migration. It should resemble the illustration below.

MS Access to SQL Server:  List of objects
Image Source

The migration wizard detected the table and viewed objects for the source database in the previous example. By default, just the table objects are selected for migration.

Note: You can also choose view objects, but you’ll need to provide their MySQL counterpart code later (no automatic migration is available for them), so it is left unchecked in the example. Stored procedures, functions, and triggers are all the same.

Step 2: As shown in the next image, click the “Show Selection” button to specify which objects you wish to transfer.

MS Access to SQL Server: Show selection button
Image Source
  • Step 3: The right-hand objects will be transferred. The list can be filtered using the filter box (wildcards are allowed, as demonstrated above). You can filter out the things you don’t want to migrate by using the arrow buttons.
  • Step 4: Clear the filter text box before continuing to see the entire list of selected objects. Our example migrates all table objects, thus they’re all listed in the Objects to Migrate list, and the Migrate Table Objects check box is selected.

7) Review the Proposed Migration

At this point, the migration wizard translates the selected objects into their target MySQL server equivalents, as well as the MySQL code required to construct them. It’s possible that you’ll have to wait for the Manual Editing phase to display the first page, as illustrated in the next picture.

MS Access to SQL Server: Manual editing phase
Image Source

The View drop-down menu affects how the transferred database items are displayed (see the figure that follows).

  • Step 1: To inspect and edit the produced MySQL code that corresponds to the selected item, click the “Show Code” option.
  • Step 2: You can also alter the object name by double-clicking on a row in the object tree, or change the database row’s name by double-clicking on it.
MS Access to SQL Server: Migration messages
Image Source
  • Step 3: A Column Mappings option is also available in the View combo box. It reveals the table columns. Using it, you can evaluate and fix the mapping of column types, default values, and other attributes, as shown in the accompanying diagram.
MS Access to SQL Server: Column Mappings
Image Source

8) Create the Database Objects

Next is the Target Creation Options page, as shown in the following figure.

MS Access to SQL Server: Target Creation Options page
Image Source
  • Step 1: You can either execute the created code in the target RDBMS (your MySQL instance from the second step) or dump it to a SQL script file using target-creation options.
  • Step 2: Continue to the following page, leaving it as shown in the previous figure. On the destination MySQL server, the migrated SQL code will be run. The Create Schemas page, as shown in the next picture, allows you to track its progress.
MS Access to SQL Server: Create Schemas Page
Image Source
  • Step 3: After the schemas and objects have been created, you can proceed to the Create Target Results page. It shows a list of created objects, as well as any mistakes that occurred during the creation process. It will resemble the illustration below.
MS Access to SQL Server: Create Target Results Page
Image Source
  • Step 4: You can update the migration code in the code box to the right, and then click the “Apply” button to save your changes. If changes were made, you’ll need to recreate the objects using the new code to make the changes. Recreate Objects is the button you use for this.

9) Transfer the Data to the MySQL Database

The next step is to transfer data from the source Access database to the target MySQL database you just built. This process can be configured on the Data Transfer Setup page.

MS Access to SQL Server: Data Transfer Setup Page
Image Source

There are two possibilities available here.

  • You can send data in real-time and/or dump it into a batch file that you may run later.
  • You can change this process using the other set of options.

As seen in the previous picture, it uses the default values for the options on this page. The data is then transferred. The tasks being completed are confirmed on the related progress page at this point as given in the following image.

MS Access to SQL Server: Progress page
Image Source
  • Step 1: Go to the next page once it’s finished. You will be given a report page that summarizes the entire procedure.
  • Step 2: Review everything and then click the “Finish” button to close the wizard.

10) Undergo Verification

You may now see the results of the successful migration of the northwind database. Query the northwind database using a SQL Editor associated with your MySQL server instance.

Execute a query like, SELECT * FROM northwind.customers, for example, to populate the result grid as seen in the following figure.

MS Access to SQL Server: Query Execution
Image Source

Conclusion

In this article, you have learned about Microsoft Access, Microsoft SQL Server, and their key features. This article also provided information on the steps for migrating a database from Microsoft Access to SQL Server in detail. For further information on MongoDB vs SQL Server, A Guide to SQL Server Migration, Setting up Airflow SQL Server Integration, you can visit the following links.

Hevo Data, a No-code Data Pipeline provides you with a consistent and reliable solution to manage data transfer between a variety of sources and a wide variety of Desired Destinations with a few clicks.

Visit our Website to Explore Hevo

Hevo Data with its strong integration with 100+ data sources (including 40+ Free Sources) such as MS SQL Server allows you to not only export data from your desired data sources & load it to the destination of your choice but also transform & enrich your data to make it analysis-ready. Hevo also allows integrating data from non-native sources using Hevo’s in-built Webhooks Connector. You can then focus on your key business needs and perform insightful analysis using BI tools. 

Want to give Hevo a try?

Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You may also have a look at the amazing price, which will assist you in selecting the best plan for your requirements.

Share your experience of understanding Apache Airflow Redshift Operators in the comment section below! We would love to hear your thoughts.

No-code Data Pipeline for MS SQL Server