Parts

Tuesday, September 22, 2026

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

In the Parts Frontend, open the Tools menu.
Click MySQL Scripts.
Select your target output folder and click OK.
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

Open MySQL Workbench and log into your MySQL Database.
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`).
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.
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

In the Parts Frontend Select Tools, then click Export CSV.
Select the export to folder when prompted.
Parts Exports each Table to a pipe-delimited (`|`), quote-qualified (`"`) text file.

Step 4: Configure Access Frontend MySQL Connection String

In the Parts Frontend, navigate to Configuration.
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:
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;
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;
Click Apply Configuration to link the Parts Frontend to the MySQL Backend Database.

Step 5: Execute Bulk Data Import into MySQL

In the Parts Frontend select Tools, then click MySQL Load Data.
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.

Connecting Altium and MS Office to MySQL on AWS

This workflow outlines how to set up a 64-bit MySQL environment on AWS RDS for direct access by remote 64-bit client applications, including Altium and Microsoft Access or Excel.

Step 1: 64-Bit MySQL ODBC Driver Setup

System Requirements & Prerequisites

Operating System: 64-bit Windows (10/11 or Windows Server).
Application Bitness: Both MS Access (Office) and Altium Designer must be 64-bit installations.
Core Dependency: Microsoft Visual C++ 2015–2022 Redistributable (x64) (vc_redist.x64.exe). Must be Installed !

Installation & Verification Workflow

Install VC++ Redistributable: Confirm Microsoft Visual C++ 2015–2022 Redistributable (x64) is installed under Windows Settings > Apps > Installed apps.
Download Driver: Download the 64-bit MSI package (e.g., mysql-connector-odbc-8.4.x-winx64.msi) from the official MySQL download page under Windows (x86, 64-bit). Recommended ODBC Drivers are versions 8.0.33 thru 8.4.which fully support caching_sha2_password .
Run Setup: Select Typical or Complete installation to install the MySQL ODBC Driver.
Verify Driver Registration: Press Win + S, search for ODBC Data Sources (64-bit), select the Drivers tab, and confirm MySQL ODBC 8.4 UNICODE Driver is listed.

Step 2: AWS RDS MySQL Database Provisioning

Configuration Settings

Engine: MySQL 8.0.14+ on AWS RDS.
DB Instance Identifier: parts-db-instance
Master Username: username
Public Access: Yes (enables remote client connectivity without a VPN).
Security Group Rule: Inbound TCP rule on Port 3306 set to Source 0.0.0.0/0 (Anywhere-IPv4).

Verification

Run the following command in Windows PowerShell to verify port connectivity:

Test-NetConnection -ComputerName parts-db-instance.c123456789.us-west-2.rds.amazonaws.com -Port 3306

No EC2 Instance is Required

AWS RDS is a fully managed, standalone database service. Because you set Public access to Yes and added an inbound security group rule on port 3306 for 0.0.0.0/0, your remote applications (MS Access, Altium Designer, and MySQL Workbench) connect directly to the RDS endpoint over the public internet.

You only need an EC2 instance if you choose to place the RDS database in a private subnet and route traffic through an SSH jump box / bastion host rather than exposing port 3306 directly. Since you are using direct DSN-less connections over SSL with a complex password, no EC2 server is necessary.

Step 3: MySQL Workbench Installation & AWS Handshake

Installation

Download the MySQL Workbench (64-Bit) MSI installer for Windows.
Execute setup and complete the installation wizard.

Connecting to AWS RDS

Open MySQL Workbench and click the + icon to add a new connection.
Set Connection Method to Standard (TCP/IP).
Set Hostname to your AWS RDS Endpoint URL and Port to 3306.
Enter your Username and click Store in Vault... to enter your password.
Under the SSL tab, set Use SSL to If available or Require (leave CA file paths blank).
Click Test Connection and save.

To verify the active session encryption status, run this query in Workbench:

SHOW STATUS LIKE 'Ssl_cipher';

Standard DSN-Less Connection String

Use this single DSN-less connection string format for 64-bit Parts Frontend and Altium Designer (.DbLib):

DRIVER={MySQL ODBC 8.4 UNICODE Driver};SERVER=your-db.c123456789.us-west-2.rds.amazonaws.com;PORT=3306;DATABASE=parts;USER=username;PWD=Your#Complex_P@ssw0rd!2026;OPTION=3;PERSIST SECURITY INFO=True;