Microsoft Access can fulfill your small-scale database needs, but you may encounter limitations related to scalability, reliability, security, and performance as the data grows. For instance, state and local government entities often employ Microsoft Access for tasks like inventory management and timesheet maintenance. As data volume expands, you might consider transitioning to a more robust database management system such as Amazon Relational Database Service (Amazon RDS), which offers numerous advantages, including scalability, enhanced security, cross-service compatibility, collaborative access, and improved performance.
In this post, we provide guidance on how to migrate tables from Microsoft Access to Amazon RDS for MySQL for the purposes of backup, migration, and data retention.
Solution overview
This post provides a comprehensive guide that outlines the step-by-step process of migrating tables from Microsoft Access to Amazon RDS for MySQL.
Microsoft Access Forms allows you to interact with the data stored in Microsoft Access databases. Let’s look at the high-level steps to integrate Microsoft Access Forms with Amazon RDS for MySQL:
Create an RDS for MySQL database.
Connect MySQL Workbench to the database.
Create an Open Database Connectivity (ODBC) connection to the database.
Connect Microsoft Access to ODBC and migrate tables.
Validate using MySQL Workbench.
Connect Microsoft Access Forms to an RDS for MySQL instance.
Prerequisites
The following prerequisites are needed for this solution:
MySQL Workbench
64 bit ODBC Connector
MySQL driver for ODBC connector
Create an RDS for MySQL database
For instructions to create your database, refer to Creating an Amazon RDS DB instance.
When you are creating an instance, you have an option to make the instance publicly accessible or keep that option disabled. With a publicly accessible endpoint, you can access the database instance from the internet, such as accessing the public endpoint from your laptop. With the recommended option of public access disabled, you can connect to the database instance from resources such as an Amazon Elastic Compute Cloud (Amazon EC2) instance within the same VPC that has permission to access the database instance. You may choose to upload the Microsoft Access file to an EC2 instance capable of reaching the private endpoint of Amazon RDS.
Use AWS Identity and Access Management (IAM) accounts to control access to Amazon RDS, especially operations that create, modify, or delete Amazon RDS resources. You may choose to create an individual user for each person who manages Amazon RDS resources, including yourself. Do not use AWS root credentials to manage Amazon RDS resources. Grant each user the minimum set of permissions required to perform his or her duties.
To learn how to authenticate to MySQL using IAM database authentication, see IAM database authentication for MariaDB, MySQL, and PostgreSQL.
Network traffic to and from the database can be encrypted using Secure Socket Layer (SSL) or Transport Layer Security (TLS). For more information about using SSL/TLS with Amazon RDS, see Using SSL/TLS to encrypt a connection to a DB instance.
To verify connectivity is enabled, it’s important to review the security group configuration and specify rules in the security group associated with your RDS DB instance to allow access from a specific IP address or a CIDR range, port, or another security group.
After all the settings have been configured, you can create the RDS DB instance. When the DB instance becomes available, you can find the endpoint to connect on the Connectivity & security tab, as shown in the following screenshot.
Note down the endpoint and port information provided on the Connectivity & security tab. We use this information when connecting to this DB instance from MySQL Workbench.
Connect MySQL Workbench to the database
To connect to an RDS for MySQL instance using MySQL Workbench, complete the following steps:
Open MySQL Workbench, and on the Database menu, choose Create new connection.
For Connection Name, enter a name for the connection.
For Hostname, enter the RDS DB instance endpoint information you copied in the previous step.
Choose Test Connection to verify the connection information is correct.
Choose OK.
After you have connected, you can verify that the database instance currently doesn’t contain data.
It’s recommended to create a Non-admin MySQL user for this migration, see Create User and Grant Access.
Create an ODBC connection to the database
In this step, you create an ODBC connection to the RDS for MySQL DB instance from your Windows machine, where Microsoft Access is hosted.
ODBC is a standard interface used to establish connections between applications and various database management systems (DBMS) or data sources. ODBC enables applications to communicate with and access data from databases, regardless of the specific DBMS being used. To create an ODBC connection, complete the following steps:
Choose Start in Windows and open ODBC.
Choose your ODBC driver and choose Configure.
For Data Source Name, enter a name for your connection.
For TCP/IP server, enter your RDS endpoint information.
Enter your login details.
Choose OK.
You will see a new ODBC connection in your ODBC data sources.
Connect Microsoft Access to ODBC and migrate tables
After you set up the ODBC connection, you can connect the existing Microsoft Access database to the RDS for MySQL DB instance to export tables. With this solution, you have an option to filter and choose the tables that need to be migrated. You may choose to backup your Microsoft Access database before starting the migration.
To connect to the ODBC connection and export your tables, complete the following steps:
Open Microsoft Access.
Open the database that contains the tables you want to migrate or back up.
Verify that no data is being written to the database tables you want to migrate.
Choose (right-click) the table that you want to export to Amazon RDS,
choose Export, then choose ODBC Database.
When prompted for the new table name during the export process, you may choose to retain the existing table name or give a new table name.
In the Select Data Source pop-up, choose the Machine Data Source tab.
Choose the new ODBC connection you created.
Choose OK.
Confirm the export using ODBC and choose Close.
After the migration has been initiated, you will be able to locate the tables in your RDS DB instance. The time varies depending on the size of the tables and Internet speed. In our test, we used a table of 29 rows over 40 Mbps, and it took less than 2 seconds to complete the export.
Validate using MySQL Workbench
After the tables are exported to Amazon RDS for MySQL, check the new tables using MySQL Workbench.
Connect Microsoft Access Forms to RDS for MySQL instance
Microsoft Access Forms are user interface components used in the Microsoft Access database management system. They serve as a visual means for users to interact with and manipulate data stored in Microsoft Access databases.
You may opt to continue using Microsoft Access Forms as your data entry and retrieval interface, and integrate it with your RDS for MySQL DB instance.
To point a Microsoft Access Form to a table you migrated to Amazon RDS, complete the following steps:
Open Microsoft Access and on the menu, choose External Data.
On the New Data Source menu, choose From Other Sources, then choose ODBC Database.
Select Link to the data source by creating a linked table.
Choose OK.
On the Machine Data Source tab, choose the connected RDS database.
Choose OK.
New tables are listed as Unassigned objects in the navigation pane.
You can now use these tables from the RDS instance as linked objects that can seamlessly integrate with other Microsoft Access components like Forms, Queries, Reports, and Macros.
Choose the form you want to point to the RDS table.
Choose (right-click) the form and choose Design View.
In the Property Sheet pane, on the Record Source drop-down menu, choose the new table that you created in Amazon RDS.
Data entered into this form will now be recorded in the RDS table.
Amazon RDS offers features to help support your data resiliency and backup needs. To explore Replication, Failover, backup and restore, refer to Resilience in Amazon RDS
Clean up
To clean up your resources, delete the RDS DB instance. This will delete your data present in the RDS DB instance. For instructions, refer to Deleting a DB instance.
Delete the ODBC data source:
Choose Start in Windows and open ODBC.
Choose your ODBC driver and choose Remove.
Conclusion
In this post, you learned how to migrate Microsoft Access tables to Amazon RDS for MySQL. Follow these steps to export your Microsoft Access database to Amazon RDS for MySQL and unlock the advantages of a cloud-based database, such as improved performance, scalability, security, cross-service compatibility, and collaborative access.
To learn more about features of RDS, see Amazon RDS features.
About the Author
Anant Mittal is an AWS Solutions Architect in Virginia. He works with K12 and state and local customers to craft highly scalable and resilient solutions. He likes working on innovative solutions and is a passionate advocate for cloud computing technologies, promoting the benefits of scalability, flexibility and cost-efficiency that the cloud offers to businesses and organizations.
Read MoreAWS Database Blog
