Data mapping
[ Home ] [ MySQL2Access ] [ Access2MySql ] [ Download ] [ Order ] [ Data mapping ] [ Support ] [ Similar products ]

Data type mapping from Ms Access to MySql

The following table illustrates how Access2MySQL Converter maps Ms-Access field types to MySQL column types .

Ms Access

 (Max) Size

MySQL
Boolean 1-bit TinyInt

N Byte

 1-Byte

TinyInt Unsigned
N Integer 2-Byte SmallInt

N Long Integer

 4-Byte

Integer
N Single 4-Byte Float

N Double

 8-Byte

Double
Currency 8-Byte Decimal (20, 4)
N Decimal 12-Byte Decimal (M, D)
Text 256 B VarChar (Size)

Date/Time

 8-Byte

DateTime

OLE

 1 G

MediumBlob

Memo

 64 KB

Text

Autoincrement

 4-Byte

Integer with autoincrement property

Note: 

  • The converter does not support these Ms Access data types: GUID, Hyper link Please change these data types before converting. 
  • By default, Memo fields of Ms Access will be converted to Text type of MySql. If you want MediumText or LargeText, you may modify the SQL scripts before executing.

 

Data type mapping from MySql to Access

The following table illustrates how MySQL2Access Converter maps MySQL data types to Ms-Access data  types. 

MySQL

Ms Access

 Size

Date : 3B
Time : 3B
DateTime : 8B
TimeStamp : 4B

Date/Time

 8B
Char : L255
VarChar : L255
 		Text /

Float : 4B

N Single

 4B
Double/Real  : 8B
 		N Double 8B

TinyInt : 1B
Year : 1B

N Byte

 1B

SmallInt : 2B

N Integer

 2B
Integer/Int : 4B
MediumInt : 3B 
 		N Long Integer 4B

TinyText : L255
Text : L64KB
MediumText : L16MB
LongText : L4GB

Memo

 /
TinyBlob : L255
Blob : L64KB
MediumBlob : L16MB
 Longblob : L4GB
 		OLE object /
Decimal/Numeric (M,D)
 		N Decimal (M,D) 8B
All Integer fields with auto_increment property
 		Increment 4B
BigInt : 8B Text 20B

* The following MySQL data types are not fully supported because MS Access does not support these data types, please change them before converting in order to have your databases and applications portable.

Data type Comment
Char/VarChar Binary If a column marked BINARY is used in an expression, the whole expression is compared as a BINARY value, resulting in case-sensitive comparison You may use "LIKE BINARY" statement or stored procedure to implement case-sensitive comparison with Char/VarChar data without BINARY property.
Set Not supported in MS Access. An exception will be raised while copying table structures. You may alter the SQL datable to change the data type to Char type.
Enum Not supported in MS Access. An exception will be raised. You may alter the SQL table to change the data type to Char type.
IntegerType Unsigned Not supported in MS Access. These integer fields will be converted without the "unsigned" property, so it is better to have your application not to use unsigned data type, and handle "unsigned" constraints in the program. The only unsigned data type in MS Access is "Byte".
BigInt Not supported in MS Access. An BigInt like 9223372036854775807 will be converted to Double (8-byte) type and  value will become 9.22337203685478E+18. If you really need BigInt data, you might not want to export / port the database to MS Access that does not support 8-byte integer. 
TimeStamp After setting the default value of a DateTime field of MS Access as function "now()", the value in a row will be the time of inserting. However, when you modify the row later, the value will remain the same. There is no way for the JET Engine to support TimeStamp. So you may have to write codes to simulate TimeStamp effects.
Decimal The decimal type in Ms Access is not exactly equivalent with the decimal type in MySQL.