If MS Access were king

If you had to convince someone their in house tool based on MS Access had a lot of room for improvement could you do it?

My journey to full stack development started in excel, then excel vba, access, SQL server with ASP.net and now SQL Server with NodeJS and Angular JS. Each step felt like a monumental improvement over the previous step and all essentially had the same goal. I wanted to organize and display data more efficiently and more effectively.

Now that I have seen the light and live in the land of more capable tools, I am having a hard time explaining why MS Access isn't a good solution. This post is an attempt to make a better case for my preferred stack.

First of all, what is the intended customers for these tools?

Intended customers

Ok, but would a small business ever exceed the capabilities of MS Access (as a single user writing his own programs, I know I have experienced plenty of frustrations if not the actual limitations). If so, what limitations can we point to?

Size?

Yes that is 524,272 TB for SQL server. Just a little bit more than 2 GB. Even if 2 GB seems reasonable, note about MS Access here. If you delete a record, MS Access does not free up this memory.

This makes the 2 GB of space a bit tighter. But how much space is 2 GB? If you are just storing text, probably plenty. If images are uploaded though, this limit may become a problem.

What about data backup and loss prevention?

Why is access prone to corruption?
MS Access's underlying technology is called JET for Joint Engine Technology. JET is also used in Visual basic and SQL Server Express. The key with JET is that it is based on the concept of file sharing. If multiple users are trying to access the same data, they will both get sent the entire set of data. Two challenges arise from using file sharing:

1. How to make sure the two users don't edit the same data
   Answer to this is by employing record locking. Optimistic locking and pessimistic locking are the two approaches used. Staying out of these details for now, ultimately this can cause conflicts, failure to update data and even data corruption.

2. Since the entire file or table is shared, performance can be slow on the network. Limiting number of transactions, users, or amount of data shared is required to keep performance up.

Performance summary

Database Design Limitations

Ok, so what about just setting up a database? Isn't it much easier to use MS Access's drag and drop capabilities? Is there any design limitations to using MS Access over SQL Server? If you are ok with workarounds, the limitations of Access are not as pronounced as I expected. Two key things:

• If you want to do a full outer join, this is technically impossible in MS Access but you can get around it by using a LEFT JOIN, RIGHT JOIN, and bringing them together with a union.
• Sometimes MS Access performance issues require you to split up a database into a front end and a backend database. I have read of people using even more than two linked databases for performance improvements.

If you are motivated to figure out these workarounds, I would argue you are probably motivated enough to learn SQL and SQL server (or some other Relational Database Management System (RDMS)) . Perhaps that is a better investment in your time.

Cost

This one turns out to be more complicated than anticipated. The assumption here is if you are comparing the price of Access vs SQL server you are probably a small company. Let's answer this for a company with 25 employees assuming that the application being developed is an internal tool only.

As we know by now, SQL Server is designed for many users doing lots of transactions a day. As a result the number of cores is an important factor (and Microsoft has it priced that way $/core vs. $/user). SQL server pricing is quite complex. I wasn't able to figure out the exact price for the client access licenses needed for the standard edition. The enterprise edition pricing does not require the CAL and is therefore independent of the number of users.

For MS Access, the # of cores of the "server" (or machine running the database) isn't relevant because all the processing is done on the client machine.

Let there be no confusion, SQL Server pricing is complicated and probably requires a call with Microsoft to make sure you have the correct license. I think I have distilled it here, but if it were me making a company decision on this, I would call and ask about pricing, then keep googling to make sure I understood all the nuances.

Summary

So if MS Access were king today, being used for anything and everything, what would the world be like? We would probably have a much less world wide web. Since MS Access doesn't support many transactions, things like twitter, facebook, and even instant messaging would not be possible. Even the early chat message program of 1996, Mirabilis's ICQ, had a client server relationship. Trying to write just a simple global chat program like ICQ appears to be an impossibility if your only tool is MS Access.

An Aside

Yes I admit, I am heavily biased against MS Access since I ran into its limits writing programs for myself. I am hesitant to say though, there may be places for the program still today, I just don't want to have any part in developing or maintaining them.