Search code examples
mysqlsqldatabasems-accesssqlyog

Importing .sql into MS Access using OBDC

I currently have a database in MySQL, which I'd like to import in MS Access.

Is it possible to do this while keeping all relationships intact (i.e. without exporting to .csv, or by using ODBC)?

I'm a noob in this area so any help is greatly appreciated.

Thanks.

Solution

  • You need to solve two different problems:

    1. Creating an empty MS Access database with a structure that matches the MySQL database structure.

    2. Extracting the data from MySQL and loading it into MS Access.

    This is not easy because different SQL databases offer different structural features, different datatypes, and so on. The more complex your use of MySQL is the more likely you'll run into some show-stopper during the conversion (for instance, Access doesn't support triggers at all). Conversely if you're using MySQL as a simple data store you may find the conversion fairly easy.

    To get an MS Access database with the same structure as your MySQL database, your best bet is to find a database definition / diagramming tool that offers reverse engineering and supports both MySQL and MS Access. Use it to reverse engineer your MySQL database into a database diagram, then change the underlying database to MS Access and use the tool to generate a database.

    Check out Dezign For Databases which (on paper, anyway) offers the features you would need to do this.

    To pump the data across, there are any number of tools. This kind of operation is generically referred to as ETL (Extract, Translate, Load).