Many organizations consider migrating their data from Microsoft Access to MySQL to scale the system and take such valuable benefits of MySQL as open-source, cross platforming, concurrent transactions and many others. Although procedure of MS Access to MySQL migration is relatively simple compared to other DBMS, there are some challenges in the process:
- 3rd party libraries (such as ODBC, DAO) may be required to read MS Access databases
- Some types must be mapped into MySQL equivalents properly
3rdParty Libraries
There are multiple ODBC drivers for MS Access and MySQL available on the market. They have different features and performance. If some migration tool required ODBC driver but does not include it in the installation pack, wrong choice of drivers may cause incorrect conversion or generic malfunction of the product.
DAO libraries are solely provided by Microsoft and therefor problem specified above is not applied to this option. However, migration tool may require different versions of DAO depending on version of MS Access database. Early versions of DAO come with the corresponding MS Office or MS Access installation. However, recent version of the libraries to read MS Access 2010 and higher must be installed separately since Office 365 does include DAO libraries anymore. These libraries may be installed as part of Microsoft Access Runtime or Database Engine.
Another issue related to DAO installation is bitness. If you have 32-bit Windows and Office, it is required to install 32-bit version of DAO libraries. Otherwise, it must be 64-bit.
Type Mapping
Although most of MS Access data types have equivalents in MySQL, some of them need to be mapped properly. Boolean type can be converted into tinyint, smallint or int, but not in the same way as ANSI standard. This is because MS Access true (VARIANT_TRUE) value is stored as -1 while ANSI true value is 1.
MS Access date type can include both date and time parts and so must be mapped into MySQL datetime or timestamp. Hyperlink type is mapped into MySQL TEXT as well as MS Access MEMO.
There are multiple dedicated tools automating MS Access to MySQL database migration that help to avoid spending time on the bottlenecks listed above. Kye features of such software are:
- Support for all modern versions of MS Access and MySQL including such forks as MariaDB and Percona and SAAS variations.
- Direct reading MS Access database and writing to MySQL. It is necessary to avoid possible problems with 3rd party libraries.
- Migration of all database entries (schema, data, indexes, constraints, queries)
- Intelligent type mapping with option to customize it on user level
MS Access to MySQL converter provided by Intelligent Converters has all these features. Moreover, it can filter converted data via SELECT-queries, provides option to migrate into MySQL script file (for those cases when MySQL does not allow remote connections) and supports command line to automate database migration process.
Comments are closed.