Migrating the MS Access "Parts" Database to MySQL

Migrating the MS Access "Parts" Database to MySQL

This procedure details how to generate table creation scripts, execute DDL and configure server settings in MySQL Workbench, export data using pipe-delimited text files, configure the Access frontend connection string, and stream data via high-speed bulk loading.

---

Step 1: Generate MySQL Table Creation Scripts

1. In the Parts Frontend, open the Tools menu[cite: 6].
2. Click MySQL Scripts[cite: 6].
3. Select your target output folder and click OK.
4. The utility scans the Access backend tables and outputs individual MySQL 8.0 DDL script files (`.sql`) for each table[cite: 7, 8]:
   • MySQL table script for mfr_links.sql[cite: 7, 8]
   • MySQL table script for parts.sql[cite: 7, 8, 11]
   • MySQL table script for supplier_links.sql[cite: 7, 8]

---

Step 2: Execute DDL Scripts & Enable Local Infile in MySQL Workbench

1. Open MySQL Workbench and log into your AWS RDS MySQL instance.
2. Select File > Open SQL Script from the main toolbar and browse to open each generated `.sql` file (`mfr_links.sql`, `parts.sql`, `supplier_links.sql`)[cite: 7, 8, 11].
3. Click the lightning bolt icon (⚡ Execute the selected portion of the script or everything) on the query editor toolbar to execute the DDL statements and create the `parts` schema and tables on AWS RDS[cite: 11].
4. Verify and enable loading data from local CSV files:
   • Run the following query to check the current setting:
     SHOW VARIABLES LIKE "local_infile";
   • If the value returns OFF, execute:
     SET GLOBAL local_infile = 1;

---

Step 3: Export Data to Pipe-Delimited CSV Files

1. In the MS Access Tools dialog, click Export CSV[cite: 12].
2. Select your working export folder when prompted.
3. The macro exports each table into a pipe-delimited (`|`), quote-qualified (`"`) text file[cite: 13]. This formatting prevents commas inside component values, descriptions, or parameters from disrupting column boundaries during import[cite: 13].

---

Step 4: Configure Access Frontend MySQL Connection String

1. In the Parts Frontend, navigate to Configuration.
2. In the Select a Parts Database, i.e. Access *.accdb file. Or enter a MySQL ODBC Connection String input field, enter your DSN-less MySQL ODBC connection string:
   ODBC;DRIVER={MySQL ODBC 8.4 UNICODE Driver};SERVER=parts-db-instance.c123456789.us-west-2.rds.amazonaws.com;PORT=3306;DATABASE=parts;USER=username;PWD=YourPassword;OPTION=3;
3. Click Apply Configuration. The frontend will re-link TableDefs and connect directly to the remote MySQL backend tables.

---

Step 5: Execute Bulk Data Import into MySQL

1. In the MS Access Tools dialog, click MySQL Load Data (or Import Excel)[cite: 6, 12].
2. Select the folder containing the exported pipe-delimited files when prompted[cite: 13].
3. The macro streams the records into the remote MySQL tables using high-speed bulk transfer.
4. Verify row counts in MySQL Workbench:
   SELECT COUNT(*) FROM parts.parts;