Articles on all things data...

Is Microsoft Access any good?

 November 10

by Andrew Richards

Let's start with a couple of basic questions. 

Is Microsoft Access any good?

Answer: Yes.

Oh. You wanted more?

Yes, it really is.

Is Microsoft Access being phased out?

Answer: No.

It really isn't.

Separating the myths from the facts about Microsoft Access

I get REALLY fed up with hearing about how Access is rubbish. Access has no security. Access is being replaced, dropped and phased out by Microsoft. Access is for idiots. Access can't handle real data. Access isn't even a real database.

Access smells and it looks funny too. 

And do you know what? 

Most of this is absolute rubbish!

Let me say it again, to be clear:

Most of this is rubbish, spouted by idiots who should know better, but don't.

Too many people spout rubbish about Microsoft Access

But what are you supposed to do? You need to move your data into a database, because your Excel files can't cope, or your existing Lotus Notes system is being phased out, or your data is currently held on paper... or WHATEVER, and you keep coming across people saying that Access is for babies and isn't used by anyone serious.

So let's tackle these questions head on. And I'll TRY to keep my temper, and give you a genuine answer to each of them...

1: Microsoft Access is being phased out by Microsoft.
NOT TRUE.

I have been hearing people claim that Microsoft are dropping Access for AT LEAST 15 years now. And it's not true. Will it be part of Microsoft 2030? Office 2035? Who the hell knows?! Not me! And not even Microsoft, I'll bet.

But what I can also tell you is that Microsoft are still very much actively adding features and functionality to Access - and have recently released Access 2021, with nearly a dozen significant new features. And, there are lots of interviews with the Access programme manager at Microsoft in the past year or so in which he outlines plans for some really useful new features coming in the near future. Take a look here, for example. Or here. Or here.

So no, Access isn't dying, being killed, or even ill.

2: Access isn't a real database system

FALSE.

Huh? What does that even mean?! Sure, there are limitations to what Access can do (more in a bit), but that doesn't mean it's not a "real" database system. Okay, you might not want to use it to hold 50 million rows of data, but that's like saying that a Tesla Model S or a Porsche 911 aren't real cars because they don't have seating for 7 people!

There IS a problem with there being a lot of rubbish Microsoft Access Databases out there, but to stretch the analogy, that's like blaming Porsche for there being a lot of bad drivers on the road!

But you can create proper, relational databases with real power using Microsoft Access.

3. Access has no security

Only partly true.

Well, okay, that may be true, to an extent. But it has the same level of security as Excel, Word and PowerPoint, and no one says that they're inherently rubbish! 

Perhaps what you mean is that there isn't adequate security given that Access is designed to hold your data. But again I'd say that this is much more an issue of understanding what the tool is for, and what it's not for, than an inherent weakness in Access. So, let me state this as clearly as I can:

If security doesn't matter for the particular set of data you're recording, then you can hold the data in Access without any issue whatsoever.

If you want to hold information about the parts required in a manufacturing process, it may well be that as this data relates to a process rather than people, it is not private and does not require security. Access will do the job just fine. Perhaps you want to log calls received by your customer service department, including who took the call, the category of issue, when the call was logged and when it was resolved. If all your staff are allowed to access this data, the data itself is not confidential (no personal details of your customers, for example) and security is not important to you, then Access will be absolutely fine for this.

But Access can still be incredibly useful, even when security is paramount. You see, it is a tool of two parts. One part holds the data, the other creates the user interface. So yes, it can hold the data (and often does), but it doesn't have to. You can use Access to create the user interface, but use something else, such as SQL Azure to hold the data - and now you have class-leading security available. Access connects to the data and is still a critical part of your database tool, but it's not responsible for the data security. Again - use the right tool for the job.

People often use Access because it's cheap - but if you need security then using SQL Azure (SQL Server on the cloud) doesn't have to be expensive - we've got clients who have their mission-critical data sitting on a SQL Azure database costing under £20 per month. And yes, they use Microsoft Access as the front end interface to this highly secure database.

Using Microsoft Access with SQL Server brings the best of both worlds
4. Access is only for small databases

Only partly true.

Well, maybe - it depends what you mean by "small". We have clients who've had over a million rows of data sitting in their tables, with a dozen or more concurrent users, and their database worked absolutely fine for them.

It's true that at that sort of scale, you need to have a well-designed database. If you just throw it together without understanding anything about database structure, normalisation and other design concepts, then yes, you're likely to run into problems. But the only actual size limit for data storage is that an Access database file can be no larger than 2GB - which is quite a lot of data, and plenty for many requirements. Even if you find this isn't enough, you can split a database over multiple files. 

But I refer you to the diagram (and section) above - if you need a large database, by all means use Access for the user interface, but connect it to a SQL database (such as SQL Azure) for the data storage. And yes, even that has a size limit, but if you're hitting THAT limit (which is 524,272 terabytes) then, well, you'll perhaps need to look at Oracle or something else again.... and you can STILL use Access as your user interface tool if you want to!

5. Access can't be used for web databases

That depends what you mean.

It's true that you can't use MS Access to create a database which people will connect to via their web browser, as they can with, say, a Salesforce CRM database or IMDB (the Internet Movie Database).

That's not what Access is for, and those tentative, toe-in-the-water attempts that Microsoft made to allow web-based databases to be created using Access (eg Access Web Services and Data Access Pages) have all been taken away. It is perhaps this removal of existing features which has led to the speculation (see point 1) that Access is being phased out. But rather, I think, this is a case of making it crystal clear exactly what Access is for, and what it isn't for. It's the same reason why they removed the "User level security wizard" - if security is core to your database, use SQL for the data storage, not Access.

However...

If what you want is not necessarily a web-based database, but a database that you can use anywhere - at home, in the office, in your yacht in the Bahamas - then yes, Access can still be a viable tool. How? Because in a well designed Access database, each user should have their own copy of the user interface on their PC - just as they should have their own web browser installed. And just as they then use their web browser to visit websites (which are stored online), so they then use their own database interface (created in Access) to connect to their data (stored online, in SQL Azure).  

Most of our clients now use MS Access user interfaces to connect to SQL Azure databases, giving them access to the database whether they're at home or in the office - or anywhere else.

Why use Microsoft Access? 

Because it has real strengths! 

Here are the top 5 reasons why people use Microsoft Access:

  1. It's sometimes called a RAD platform - with RAD standing for Rapid Application Development. You can create a table, add a query to filter the data, and build a simple interface to view and edit that data in Microsoft Access in under an hour. 
  2. It talks really well with the other tools in Microsoft Office. You can export the data to Excel, or to a Word document with a simple right-click. 
  3. It shares its programming language with the rest of Office, making it easy to re-use skills that you may already have. If you've ever learned how to write macros in Excel (perhaps tweaking macros that you've recorded) then you already know how to code MS Access. You can use this code to bring real power to your database, connecting easily Access to Excel, Word, PowerPoint and Outlook.
  4. It's cheap! You don't need anything other than a copy of Microsoft Access, which is part of many Office versions and many editions of Microsoft 365 and Office 365.
  5. It provides a lot of tools in one place. You can create tables to store your data, queries to filter your data and combine data from multiple tables, forms to create a fully-featured user interface, reports to output your data as a PDF - all using nothing more than Access. 

As with most things in life, however, there's not a single solution to every requirement. Yes, there are times when Access on its own will do the job, but often you want to get the best of all worlds by combining Access with SQL. Let's finish up, then, by looking at likely scenarios for where Access can do the job without help, and at where you'll probably want to combine it with the power of SQL Server.

When should I use Microsoft Access on its own?

If you have a requirement for a database with the following features, then it's likely that Microsoft Access, on its own, is a good option:

  1. You need a simple database, and you're on a tight budget. You're likely to have Microsoft Access already (it's part of many versions of Office) and there's no actual need for much else - no server, no special licenses, no programmers to hire.
  2. You want to create a database to share data between a relatively small number of people in the same location - in one office, or in one department of a bigger organisation perhaps. You can store your data in an Access database on a network drive, and each user gets their own copy of the user interface which connects to that database.
  3. You want to create a prototype ahead of investing in a fully-fledged larger scale database. Perhaps you have plans for a large-scale database which can be accessed by dozens of users, across multiple locations, containing many millions of rows of data. The likelihood is that you're going to need a large-scale database tool such as SQL Server - eventually. But for the planning of which tables you're going to need to hold your data, which screens you'll need in your user interface - just use an Access database. You can plan how they will link together, how the data will flow, the reports you're going to need and so on, just using MS Access. Then, once you're happy that you can get what you need from your new system, you can use your Access prototype as a starting point in building your final system.


When should I use Microsoft Access with SQL Server?

SQL Server is Microsoft's "big" database system. It allows for several key things which Access just can't do:

  1. Massive amounts of data can be stored in SQL Server - far beyond the 2GB limit for an Access file
  2. Enterprise-level security, including user-level security, is baked in to SQL Server so that an organisation can control exactly who can control what
  3. SQL Server allows for hot backups - for example if you're using SQL Azure, you can restore your database to any time to suit your needs over the past several weeks.
  4. SQL Server is a great solution for building a database which will be the back-end for a website. For example, if you want to set up an online store using your own database, Access won't cut it, but SQL would be a perfect choice.
  5. Logging of exactly what's done, when and by whom using triggers

Remember, even though this is a list of where SQL scores over Access, that doesn't take Access out of the equation. 

A SQL Server back end with an Access front end

For very many scenarios, the best solution is to store your data in SQL Server, but use Microsoft Access to provide the user interface to that data. In that way, the benefits of each can be combined:

  1. Get the security of SQL Server as the store for your data, preventing unwanted access, granting access to some objects but not others and so on
  2. Get the scalability of SQL, storing large amounts of data in a highly efficient manner
  3. Use triggers to log who's doing what, and to automate processes
  4. Use SQL Azure to allow access to your database for your staff wherever in the world they may be
  5. Connect this database to an Access front end, giving you a tool to create a user interface quickly and without necessarily specialist programmers and developers
  6. Use the Access tools to build connections to other MS Office tools - creating a follow-up appointment in Outlook every time a new sale is made, or outputting your data to an Excel workbook, adding charts, PivotTables and other Excel analysis.
  7. In due course, if the need arises, you can then look to build a web front end for your customers to gain direct access to the database, using tools such as ASP .Net, Python or whatever else you want to use. 

Where to start?

It's likely that you've already got data. It's likely that this data sits within numerous Excel sheets. And the process of taking that data, making sure that it's consistent and accurate (the process known as "data cleansing"), structuring it ready to be used in a database, and then actually importing it into your database is a task that needs considerable thought, and disciplined planning. 

If this is something you need to do, we've got a course on migrating your data from Excel to a database that will guide you every step along the way. And we're always happy to have a chat to give advice - just get in touch via our contact page to start the process.

share this

Related Posts

Sign up now to be the first to hear about new articles!