Migrating the MS Access "Parts" Database to MySQL

This procedure details how to generate MySQL schema and table scripts, configure server settings in MySQL Workbench, export data using pipe-delimited text files, configure the Parts Frontend connection string, and Load Data via high-speed bulk loading.

---

Step 1: Generate MySQL Table Creation Scripts

1. In the Parts Frontend, open the Tools menu.
2. Click MySQL Scripts.
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:
   • MySQL table script for mfr_links.sql
   • MySQL table script for parts.sql
   • MySQL table script for supplier_links.sql

---

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

1. Open MySQL Workbench and log into your MySQL Database.
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`).
3. Click the lightning bolt icon⚡ for each script to create schemas and tables in MySQL. Refresh the Schemas in the Navigator Panel to display the Schemas and Tables.
4. Verify and enable LOAD DATA from local CSV files in the MySQL Workbench:
   • 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 Parts Frontend Select Tools, then click Export CSV.
2. Select the export to folder when prompted.
3. Parts Exports each Table to a pipe-delimited (`|`), quote-qualified (`"`) text file.

---

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:
3. Example for AWS:
   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;
4. Example for Localhost:
   DRIVER={MySQL ODBC 8.4 UNICODE Driver};SERVER=localhost;OPTION=3;PORT=3306;DATABASE=parts;USER=librarian;PWD=Password1;PERSIST SECURITY INFO=True;
5. Click Apply Configuration to link the Parts Frontend to the MySQL Backend Database.

---

Step 5: Execute Bulk Data Import into MySQL

1. In the Parts Frontend select Tools, then click MySQL Load Data.
2. Close Parts Tools dialog and select Show All in the Parts Frontend to View the Records.

Contact Parts for Technical Support or an Online Demonstration.