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
| Db | Purpose |
|---|---|
| Access | Home or small businesses |
| SQL Server | Medium to large businesses |
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?
| Db | Db Size |
|---|---|
| Access | 2 GB |
| SQL Server | 524,272,000 GB |
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.
| Db | Memory strategy |
|---|---|
| Access | Requires compact and repair periodically to reclaim space. |
| SQL Server | Frees memory when records are deleted |
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?
| Db | Restore | Reliability |
|---|---|---|
| Access | Can restore from backup. | Can be prone to data corruption if used in multiple user or networked environment |
| SQL Server | Can restore entire db from backup, or rollback individual transactions or changes made to db | Designed as a true client/server system to prevent stability and corruption issues. |
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:
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.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
| Db | Design | Intended use | Network Traffic |
|---|---|---|---|
| Access | Server sends entire set of data for every request; client does processing | Limited number of users, limited number of individual transaction edits | Lots of overhead since for EVERY request the entire data set is sent |
| SQL Server | Server does processing and sends only relevant information over network to client | 24/7 applications with high number of transactions and high number of users | MUCH lower than Access. Only sends the specific information the client requests |
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.
| Db | Cost | Total Yearly cost |
|---|---|---|
| Access | $20/user/month for Office 365 | $6,000 (25 users * 20/mo*12 mo) |
| SQL Server Standard Edition | ~2k/core using 1 core for standard edition + client access license (CAL)/user * 25 users | $2k + CAL cost(?) = < $8k (?) |
| SQL Server Enterprise Edition | $/core with no client access license required | ~$8,000 (1 core) - ~$13,750 ( 2 core pack) |
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.
Top comments (7)
An usual solution I have seen is work with an open source DB like Mysql or Postgres and Access like frontend
I'd love to see more people using that setup. I miss the good old days of using 'Access Data Projects' they allowed Access forms and reports to be directly created against MSSQL without all the nonsense of connection strings. One simple connection per .ADP file. THAT is how Access should be designed, but the kids who use Access couldn't figure out how to do it. I argued for years. It breaks my heart, but I would MUCH rather live in the tech world from 2000-2012 compared to the tech world of today.
Wow, interesting. I suppose you have to start with what you know and go from there. If nothing else it seems like more "standard" solutions will be easier to get help from the web when you run into issues and easier to maintain in the long run if multiple people are involved. I try to keep an open mind to why the non-standard way might have value but sometimes it is harder than others to find it.
You say that CALs aren't required with Enterprise edition? I don't think that is true.
Also, you say that Jet engine is used with SQL Server Express? I don't know how true that is. The topic is a lot more complex than it was a few years ago. There are many different ways to create SQL Server databases. I think that MS SQL Server Express is the best choice for MOST people.
One combination interesant is use Access in the Front End and MySQL/MariaDB in the Back End using ODBC and Data Access Disconnect :D
Yeah. But open-office and Libre-Office from what I understand, they allow a true Client-Server solution. JUST LIKE WHAT ACCESS DATA PROJECTS USED TO OFFER. It wasn't JUST ADP that was a huge deal, the OTHER side was 'Data Access Pages'. I implemented DAP and ADP all OVER the Seattle area for many of the largest companies in the world.
Office Web Components was from DAP, and that was the COOLEST reporting that I've ever seen. It was great, until OWC stopped being included with SQL Server installation.
I miss the good old days of storing spreadsheets and pivotTables in a SQL Server database. I've worked on systems with 70k distinct reports based on OWC / DAP. It was SUPER popular back in the day. I wish I was still using that combination today.
Here is more about the options in LibreOffice / OpenOffice. From ChatGPT
Yes, in LibreOffice you can use either MySQL or PostgreSQL as the backend database and design your tables and queries directly in those databases. LibreOffice Base allows you to connect to external databases like MySQL or PostgreSQL via JDBC or ODBC drivers. Here's how it works:
Tables and Queries
Forms and Reports
Forms: Forms in LibreOffice Base can be bound to tables or queries in your MySQL/PostgreSQL database. You can create forms using the form wizard or design them manually. The forms will then allow you to input or view data that is stored directly in the backend database.
Reports: LibreOffice includes a report generator (Report Builder) that can be used to create reports from the data in your MySQL/PostgreSQL backend. Reports can be generated based on the data in tables or queries and can include dynamic content.
Things to Keep in Mind
Would you like any guidance on setting up the connection or designing specific components?
It seems the trick with building new tools is balancing capability and interface for people who are at best power users with those who are deeply experienced in the technology. It does take time to learn a technology deeply but it is so rewarding and so much more powerful.
Seems those small to medium businesses are going to be a pile of the easy to use technologies, duct taped together until someone with more expertise comes in and makes sense of it.
Definitely a sad day when "advancements in technology" make the product worse!