MysqlToAccess: Tools, Scripts, and Performance Tips

Tools

• MySQL Workbench Migration Wizard — free, good for medium/simple schemas; needs ODBC and may miss complex Access-specific items.
• DBConvert / Intelligent Converters / ESF Toolkit — commercial, preserves relationships, offers mapping options and reporting.
• ODBC + Access Export/Linked Tables — built-in, useful for phased migrations or using Access as front-end.
• Custom ETL / Scripts (Python pyodbc/sqlalchemy, PowerShell, SSIS/Talend/Pentaho) — maximum control for complex transformations and repeatable pipelines.

Typical Scripts & Approaches

• One-off export (CSV/SQL): Export Access tables to CSV, bulk-load into MySQL with LOAD DATA INFILE or generated INSERT scripts.
• ODBC sync script (Python example):

  Code
  Copy CodeCopied
  # Python (sketch) import pyodbc, mysql.connector src = pyodbc.connect(r”DRIVER={Microsoft Access Driver (*.mdb,.accdb)};DBQ=source.accdb;“) dst = mysql.connector.connect(user=‘u’, password=‘p’, host=‘h’, database=‘db’)
  
   
  read rows from Access, transform types, insert into MySQL using executemany
   
  

• Schema migration: Extract Access schema (including MSysRelationships), map types (TEXT→VARCHAR/TEXT, AUTONUMBER→INT AUTO_INCREMENT, YES/NO→TINYINT(1)), create MySQL DDL, then load data.
• Incremental sync: Use timestamps/PK cursors in scripts or commercial tool sync features; avoid full dumps for large datasets.

Performance & Reliability Tips

• Pre-clean source: Remove orphaned rows, normalize overly-wide text fields, fix invalid dates/strings.
• Batch inserts: Use bulk load (LOAD DATA INFILE) or prepared executemany in batches (e.g., 1k–10k rows) to reduce round-trips.
• Disable indexes/constraints during load: Drop or disable non-essential indexes, load data, then recreate indexes and foreign keys.
• Type and charset consistency: Ensure correct MySQL column types and UTF-8/utf8mb4 charset to avoid truncation or encoding errors.
• Preserve relationships: Extract MSysRelationships and recreate foreign keys in MySQL to maintain referential integrity.
• Transaction handling: Wrap logical batches in transactions to allow safe rollback on errors.
• Test with a pilot: Migrate a representative subset, verify row counts, checksums, and query performance.
• Monitor locks and I/O: Run loads during low-usage windows; monitor disk, CPU, and MySQL buffer pool.
• Handle Access-specific logic: Forms, reports, and VBA must be rewritten (as views, stored procedures, or application code).
• Automate and log: Keep detailed logs of row counts, errors, and elapsed time; add retries for transient failures.

Quick Checklist (migration run)

1. Inventory schema, data sizes, queries, macros, and VBA.
2. Clean and back up Access DB.
3. Choose tool (Workbench for simple, commercial or custom for complex).
4. Map schema and charsets; generate DDL.
5. Pilot migrate subset; validate counts and checksums.
6. Full migration with batched bulk load; rebuild indexes/constraints.
7. Functional testing and performance tuning.
8. Cutover or configure ongoing sync; monitor.

If you want, I can generate a concrete Python migration script template or a mapping table for Access→MySQL types for your specific database—tell me the approximate size and whether you have ACCDB or MDB.