MS Access and MySQL

by Curtiss   |   last updated April 11, 2012   |   programming ·

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.

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.