MS Access and MySQL

This post is a quick comparison between MySQL and Access databases. I will not spend my time reviewing either database management application (DBMA), nor will I really try to compare their “features” or “merits”. This is really just an informational post to point out some equivalencies and differences between the two.

I began delving into Access somewhat blindfolded. Here at work, we have a fairly bare-bones server, running VBScript—not VB.Net— with only Access databases—not MS SQL Server.


Let me begin by giving you a little comparison of the MySQL field-types versus the Access field-types. A table like this would have helped me dramatically when I started out. It’s too bad I didn’t search for anything like this when I started.

Access Data Type MySQL Equivalent Type Limits
Auto-Number – Long Integer Int with auto-increment enabled -2147483648 to 2147483647 normal
0 to 4294967295 UNSIGNED
Auto-Number – ReplicationID CHAR(36)1 N/A
Text VARCHAR or TINYTEXT 255 characters
Memo Text 65535 characters
Number – Byte TinyInt -128 to 127 normal
0 to 255 UNSIGNED.
Number – Integer SMALLINT -32768 to 32767 normal
0 to 65535 UNSIGNED.
Number – Long Integer INT -2147483648 to 2147483647 normal
0 to 4294967295 UNSIGNED
Number – Single FLOAT? 7-digits
Number – Double DOUBLE 15 digits
Number – Replication ID CHAR(36)1 N/A
Number – Decimal DECIMAL ?
Date/Time DATETIME N/A
Currency N/A ?
Yes/No N/A2 0 or 1
OLE Object BLOB ?3
Hyperlink N/A4 ?

Some notes about the table above:

  1. VARCHAR(36) is not exactly equivalent to the proprietary Replication ID (or UUID or GUID) format. A Replication ID is actually a 128-bit, 36-character unique identifier. CHAR(36) is the closest you can currently get with MySQL.
  2. There is no MySQL equivalent for the Access “Yes/No” data type, unfortunately. The closest you can get is to set a TINYINT(1) data type and use your backend scripting language to restrict it to strictly 0 and 1.
  3. The BLOB, MEDIUMBLOB and LONGBLOB data types in MySQL are much more efficient and versatile than the OLE Object data type in Access. There’s some interesting information contained within this article.
  4. There is no MySQL equivalent to the Access “Hyperlink” data type. I don’t really understand why we would need one, either. The closest you can get is the VARCHAR data type.
  5. Very Important – As you can see in the table above, the “Text” data type has a limit of 255 characters. It is not equivalent to the “TEXT” data type in MySQL.
    When I started working with Access, I naturally assumed that that meant I could not easily store text larger than 255 characters within Access. I searched the Web and came across no solutions. Finally, I wrote some functions to split my text into 255-character pieces, then store each one in its own row of a separate table.
    However, I later found out about the “Memo” field, which is equivalent to the “TEXT” data type in MySQL. There is no Access equivalent to the MySQL “LONGTEXT” data type.
  6. Overall, there is a two (2) gigabyte limit on Access databases.

Some other notes
Please be aware, if you are brand new to working with Access, that there are some major differences in the use of SQL statements, as well.

  • In MySQL, you should always wrap your database entity names (table name, column name, etc.) in accent marks (`). In Access, as long as there are no spaces in the entity name, no wrappers are required. However, if there is a space in the name of your entity (yes, that’s allowed in Access), then it needs to be wrapped by square brackets ([Your Name]).
  • In MySQL, all data can be wrapped with single quotes, no matter its type. In Access, only strings can be wrapped in single quotes. Numbers need to be sent through with no wrappers, and dates must be wrapped by pound/number symbols (#).
  • I got some of the information for this article from a helpful little article about Access data types.
  • I also got some of my information from a helpful article on MySQL data types.

Conclusion
I hope this post helps you make the transition from MySQL to Access a little easier. In my next article, I’ll be discussing some of the nuances of using VBScript to interact with Access.

Tech Tags: HTMLCenter dbma data+types

3 Responses

  • James Denholm-Price

    Useful — thanks! (I’m just contemplating an Access DB for a colleague but I’m most used to MySQL).

    Re. There is no MySQL equivalent for the Access “Yes/No” data type, unfortunately. The closest you can get is to set a TINYINT(1) data type and use your backend scripting language to restrict it to strictly 0 and 1. what about ENUM(‘No’,’Yes’)? Admittedly INSERT ... 1 and INSERT ... 'No' are equivalent (not 0), as are 2 and ‘Yes’, but it allows Yes/No values to be used and stored efficiently.

    Re. In MySQL, you should always wrap your database entity names (table name, column name, etc.) in accent marks (`)… I think it’s closer to Access, in that spaces in names requires use of “ but otherwise “ are optional.

    Cheers — James

  • hmza

    Do the dissimilarities between MYSQL and Microsoft Access cause results to differ?

  • I keep coming across people saying MySql doesn’t have a direct equivalent to Access Yes/No data type. Access stores the so called Yes / No (really True / False ) as -1 / 0 respectfully. This is accomplished in MySql by setting the field to tinyint with a length of 1 and ‘leaving the signed / unsigned flag cleared indicating it’s a signed value. This is exactly, not close, exactly what Access requires / provides.