MS Access and MySQL/migrating

from HTYP, the free directory anyone can edit if they can prove to me that they're not a spambot
Jump to: navigation, search

Overview

This page is about moving data from MS Access to MySQL, which involves the following issues:

Data Types

ODBC appears to translate data types it doesn't understand into Memo fields. This would be fine, except that you can't sort on Memo fields – so you may have to use types which ODBC can handle better. Fields which can't be sorted on also can't be designated as indexes in the data schema (on MySQL's end), or Access will refuse to import the table.

MySQL type MS Access type Notes
TINYTEXT Memo
VARCHAR(255) Text field size = 255
INT INT(4)

(I'll add to this table as more examples come up.)

Access also has trouble dealing with AUTOINCREMENT fields (usually used for ID) in the table data viewer. If you create a new field but leave the autonumbered ID field blank, on exiting the field Access will show the record as "#deleted". If you close and reopen the table data view, the new data appears. If you enter an ID by hand when you create the record, this problem doesn't happen.