As you can probably guess from the title, we’re doing yet another article in the MySQL versus series. Databases have my interest again and there’s another Database we haven’t talked about yet. The Microsoft Access database. We don’t have to even start digging in and you probably already know how different Access is going to be. But that’s where I thought it would be interesting to dig in more. There are so many obvious differences, but when we dig how many similarities are there? There are also some obvious similarities, but then how many differences are there?
As you can guess, I just found this to be an interesting topic. In my experience through a few classes, we didn’t use Access as a full database. But for one of my girlfriend’s classes, she did. So, let’s dive into the differences between MySQL and Microsoft Access. We’ll focus more on just the differences between the two on this one, and not so much on the positives/negatives. In my perhaps biased opinion, you’ve seen that MySQL is one of my favorite open-source databases to use at home, while I’ve never really mentioned Access. So, today we will only look at the differences between MySQL and Access.
What Is Microsoft Access
First to note is that Access is more than just a Database Management System (DBMS). Access instead incorporates the Jet Database Engine and a graphical user interface (GUI). It also adds software development tools. Traditionally, Access uses a Relational Database Management System (RDBMS). However, Access is also able to run as simply a DBMS.
Although the Jet Database Engine is the default, you can also choose other popular databases supported by Access such as SQL Server, Oracle, DB2, or find support for the Open Database Connectivity (ODBC) standard. Access also allows you to export or import data from spreadsheets, word processing files, or database files. You can also import or even link directly to data that is stored in other databases or applications. Access also can understand a wide variety of data formats.
The point of Access is to, of course, be able to access a variety of sources. This could mean perhaps the files it accesses, or the different data formats it can read. But it also applies to the ways data can be accessed. For example, Access can work with data from other sources, other SQL databases, on popular PC database programs, on servers, minicomputers, mainframe, or even with data on the Internet or intranet web servers. But enough about Access, let’s start comparing some of the differences.
Open-Source vs Not So Much
As we’ve discussed so many times before, MySQL is an open-source RDBMS that is mostly available to use for free. There is only some proprietary code involved with MySQL. Of course, that is the code you cannot just view and the area in which you would pay for. With MySQL, you have a little more debatable. So, Access is one of the apps that come with Microsoft Office. But sure, those aren’t exactly free to use. You must have Office to obtain Access for free there. You can get a free 30-day trial for Microsoft 365 apps, but there are also free runtime versions of Access you can use. Now, there should be ways to use Access for free, but of course, it is not the same as MySQL being open-source, as Access uses a commercial license.
Administration
When thinking of partitioning, we’ve talked about how MySQL uses horizontal partitioning, or sharding using MySQL Cluster or MySQL Fabric. Access, on the other hand, does not support partitioning. The same goes for replication. MySQL supports multi-source or source-replica replication, whereas Access does not support replication.
For user concepts, MySQL uses a fine-grained authorization concept for users. However, Access does not incorporate user concepts, although there was simple user-level security built-in until Access 2003.
Both MySQL and Access are built to be durable. They both support the persistence of data. However, Access does not include files for transaction logging. Both MySQL and Access are also ACID compliant, but again, Access does not have the files for transaction logging.
We should also mention operating systems. MySQL works on Linux, Windows, Solaris, OS X, and FreeBSD. Access, however, operates only on Windows. This is because it is not a real database server. It only acts like one by making use of DLLs (dynamic link libraries). Maybe not quite as important, MySQL was written in C++ and C while Access was written only in C++.
Server-side scripts may also be a consideration, depending on what versions you’re using. For example, MySQL only has server-side scripts available in its proprietary version. With Access, it is only if you use Access 2010 or later with the ACE engine. The same goes with Triggers in Access. They are only available in Access 2010 or later with the ACE engine, while triggers are always available in MySQL.
As far as security goes, Access is much more limited. MySQL, as you’ve probably heard before, has different types of security, and can be configured with SSL support. Access has only username/password support for security.
Now that we’ve looked through the differences, let’s take a quick look at some of the pros and cons of Access.
Pros of Access
Access is easy to navigate, as it uses a classic Microsoft UI (user interface). It also comes as a part of the MS Office Suite.
Access is not only just for the tables. If you’re needing your diagrams all mapped out, no need to do that on paper. Access makes it easy to create entity-relationship diagrams for your tables or mock layouts.
Access also works with standard SQL syntax/scripts, so you don’t have to spend time learning a new language to use it.
Cons of Access
Although support for Access is there, it is not always helpful. As far as tutorials, they can be limited at times on the material covered. This makes the "help info" not always helpful.
Although updates have come along, not too many big changes have been made. That could be good, but also could be bad too. Not too many changes could also mean they can fall behind.
It is difficult to sport unused tables, reports, forms, Marcos, etcetera. Although it may not be very important at first, with limited memory you’ll need to find what objects can be removed without hurting any underlying queries or reports.
Another picky one, but dialog boxes are not always resizable, so long names could get cut off. And that wouldn’t be too much of a problem unless you have similar naming conventions. In which it may be harder to spot which one is which.
Conclusion
In this article, we looked at the difference between MySQL and Microsoft Access. All-in-all, there’s a lot of similarities between the two, but that only followings having standard SQL characteristics. As far as the more technical approach, mainly being the underlying structures, there are many more differences. It was, in my opinion, an interesting look into the databases. I suppose there are more similarities than I gave it credit for, but perhaps the differences are the important deciding factors, such as paid or open-source. In the end, I hope you found this as an interesting dive into MySQL and Access. Until next time, cheers!
Read all my articles for free with my weekly newsletter, thanks!
Want to read all articles on Medium? Become a Medium member today!
Check out some of my recent articles:
How Fast Are SQLAlchemy Relationships?
References:
Microsoft Access vs. MySQL Comparison
