Introduction
Microsoft Access and MySQL are both in the
top ten most popular databases by market share and general
popularity scoring. Both have been generally available since
the mid-1990's, and have wide use globally in businesses
of every size. Read on to learn more about each, compare
and contrast to find out which is best suited to your specific
needs.
Key Uses for Each
In the table below you'll see a variety uses and capabilities
that are important in your comparison between MySQL and
MS Access.
| Use |
MS Access |
MySQL |
| Rapid prototyping |
Excellent |
Not well suited |
| User interface design |
Good |
No native user interface
note |
| User Access |
Desktop
note |
Desktop or Browser
note |
| Simultaneous Users |
Usually less than 10
note |
100s to 1,000s
note |
| Security |
Limited |
Sophisticated |
| High-volume and/or complex queries |
Slower |
Faster |
| High-volume updates |
Slower |
Faster |
| Replication
note |
None |
Supported |
The quick summary? Need a quick user interface design
that will be used by a few users over a local network? Microsoft
Access is probably your best bet. Need to support many users
over the internet from any location in the world? MySQL
is recommended. Could you handle the situation with MS Access?
Yes, it's been done many times using a hybrid solution (MS
Access + MySQL) or remote desktop such as Citrix.
Deployment Differences
The way Access and MySQL are deployed is perhaps the most
significant, and for some, most intimidating difference.
Microsoft Access often just appears on your business desktop
with Word, Excel, and PowerPoint. In contrast, MySQL is
a server, which can scare many people from digging deeper.
In the table below we'll cover some of the major elements
of deploying both solutions.
| Use |
MS Access |
MySQL |
| Database Location |
Stored as a file on a desktop or shared file
server machine the same way Word or Excel files
are saved. |
Stored on a MySQL installation. The database
is accessed by way of connections rather than a
file. |
| Installation |
Windows installer (Access-only or part of Office
Professional / Office365) |
Windows, Linux, Unix, or Mac installer |
| Hardware Requirements |
2GB memory, 1Ghz+ CPU, 3GB disk space |
Roughly 500MB memory, 1 CPU (or more) and 100MB
disk space (or more depending on the size of your
database) |
| Administrator Interface |
Microsoft Access itself using the table, form,
report, and query designers. |
Free
MySQL Workbench and open source tools like phpMyAdmin.
A command line interface also exists for scripting
and more advanced administrators. |
| End User Interfaces |
Microsoft Access itself via forms, reports,
and queries you create. Tools like
OpenGate's Designer for MS Access can simplify
the design process. |
Any modern web application development environment
with a MySQL connector, or tools like
OpenGate Vivaldi's MySQL designer interface. |
| "Running" the
database |
Open the Access file with MS Access. |
MySQL runs in the background on the machine
and accepts connections to your database from authorized
users via MySQL Workbench or an application you
create. |
| Hosting Options |
None, really |
Amazon RDS, Google Cloud SQL, Microsoft Azure,
Hostgator, 1&1 Internet Hosting |
Skills Required
Table and Query/View Design
Both MS Access and MySQL require similar skills when it
comes to the design of the database and queries/views
that power the experience. Learning either will
take weeks or months depending on your technical acumen
and previous experience. If you already have
experience with MS Access, creating tables and views in
MySQL won't require a significant learning curve.
And vice versa if you know MySQL and need to work with
MS Access. Beyond some elements of the initial
user interface differences for an administrator, the two
are similar enough to be able to get working fairly
quickly.
Form and Report Design
Creating forms and reports is the most significant skill
difference between MySQL and MS Access. In
Microsoft Access you have a
WYSYWIG
editor to drag and drop fields into forms and reports.
A few hours is usually all it takes to understand how to
create functional forms. For MySQL, there is no
native end user interface. You can either use a
tool like
OpenGate's Vivaldi MYSQL web application designer,
or develop the interface using a web development
language like java, PHP, Python, .NET, etc.
Development "from-scratch" using one of these languages
takes time to understand how to connect to the MySQL
database, and present the necessary entry forms and
reports to your users based on the MySQL data. How
much time is very dependant upon your technical skill,
programming languages you already know, or availability
of training to jump-start your project (or using
consultants).
Comparing Costs
| Cost Element |
MS Access |
MySQL |
| User Licenses |
Roughly $100 for a
standalone copy per user. Your company may
already have licenses included with Office365
professional. End users that don't need to
design forms/tables can also install a free
Runtime Access. |
None |
| Server
License |
None |
Dependent on deployment. Hosted on Amazon
AWS starts at $30/month. If you have a
website host, MySQL is often included. |
Terminology and Concept Translation
| Concept/Term |
MS Access |
MySQL |
| Database |
Database |
Schema (a MySQL Server
can host many schema) |
| Table |
Table |
Table |
| Query |
Query |
View |
| Report |
Report |
Not native to MySQL. Requires
development or 3rd party tools. |
| Form |
Form |
Not native to MySQL. Requires
development or 3rd party tools. |
| Trigger - an action that
is automatically taken when a condition is met (e.g.,
write to an audit table when a record is updated). |
Not native to MS Access. |
Supported |
| Stored Procedure - a function
that can be called on demand that performs an option
(e.g., return the total order revenue based on a
customer ID passed to the procedure). |
Not native to MS Access. |
Supported |
| Users |
Not native to MS Access in 2007+. Requires
that you build your own user management or use a
tool like
OpenGate's UI Builder - Enterprise. |
Supported for database access. But generally
you need to build your own finer-grained user management
for data security, or
use a tool like OpenGate's Vivaldi MySQL Web Application
Designer. |
| Roles |
Not native to MS Access
in 2007+. Requires that you build your own
user management or use a tool like
OpenGate's UI Builder - Enterprise. |
Supported for database
access. But generally you need to build your
own finer-grained user management for data security,
or
use a tool like OpenGate's Vivaldi MySQL Web Application
Designer. |
| Scheduled Events |
Not supported. Requires that you build
your own scheduler, or use a tool like
OpenGate's Workflow Builder. |
Supported |