Using Microsoft Access for ETL

Challenge: Taking information out of MS SQL Server database and putting it into MySQL database – de-normalized for reporting (without spending a fortune).

Tentative Solution: Use Microsoft Access to bridge the divide with linked tables, pass-through queries, and append queries.

1. Create a simple “make-table” query in MS Access to query SQL Server and make a local table. This query will later be changed to an “update-table” query.
   Hint: add a constraint (or add a “top x”) to limit your data – you only need enough rows to build the structure and see it.
2. Use MySQL Migration Toolkit (or MySQL Workbench) using the Access database as source to build the scripts (create and insert) that would recreate the table in MySQL. Save output as scripts – if you allow the Migration Toolkit to build the table directly then you will need to edit the table. By saving to scripts you can add your edits to the script.
3. Edit the MySQL script (combining the create and insert scripts) and adding additional fields, keys, and indexes if needed.
4. Run the edited MySQL script against your MySQL database.
5. In Access add the new MySQL table using Link Table Manager.

   

   Linked Tables in Microsoft Access

6. Create a a pass-through query to “truncate” the linked MySQL table. This will empty the table when run.
   Note: this is only needed if you are rebuilding the data each time. Otherwise, if just appending yesterday’s data all you need is an append query.
7. Edit the query created in step 1 to be an “append” query and select the linked MySQL table. Remove additional constraints or limits added when building the MySQL table.

   

   Pass-through and append queries in Microsoft Access

8. Create a macro to update the MySQL table by first truncating it then updating it using the two queries.
   

Advertisement