Affiliate Disclosure: By buying the products we recommend, you help keep the lights on at MakeUseOf. Read more.
There are conflicting ideas about how hard it is to make use of Microsoft Access. One school of thought suggests that it’s difficult, complex, and beyond normal folk. The other is that it’s just as easy to use as Excel.
I’m here to tell you the truth. And it sits somewhere between the two. Access takes more effort to become familiar with than a spreadsheet, but it’s not difficult, and it’s very powerful.
You can start right here. After this introductory Microsoft Access tutorial, I’ll follow up with posts on the various parts that make up Access, and show you how to save time and effort.
A Microsoft Access Tutorial: Starting at the beginning
First up, a little boring theory. I’ll make it as fast as I can, and hopefully it won’t hurt too much.
Microsoft Access is an RDBMS (Relational Database Management System). It’s often called a database, that that term actually refers to the information you keep in it. It’s like the difference between a particular spreadsheet and Excel itself.
As an RDBMS goes, Access is decidedly at the smaller end of the scale. It’s not ideal for databases bigger than about 2GB, and doesn’t cope terribly well with a simultaneous user base of more than around half a dozen.
But within these confines, it’s difficult to find something more comprehensive for the price, and many people already have the product, if they have an applicable version of Microsoft Office.
For those of you who have used Excel, or pretty much any other spreadsheet, there are some fundamental differences to understand, and then take advantage of.
Firstly, data and display are separate in Access. That is, you don’t format the data to look nice, or to be easy to input. Instead, you structure it to make sense, and then design separate forms and reports to output the data.
Secondly, you don’t (generally) store calculations. You store raw data, and perform calculations when you need them.
Third and last for now. It’s very easy in Excel to deal with an exception. For instance, if you have a column that is full of numeric values, it’s quite simple on a single row, to decide that putting the word “˜Jim’ in the cell instead makes more sense. You can’t do that in any sensibly designed Access database, so planning is more important.
To get your head around this, some brief summaries of the various parts of Access. It doesn’t all need to make sense the first time through. I’ll cover each of these in depth with separate posts soon.
There’s an assumption all the way through this series of posts that you have some familiarity with Excel or something similar. If you’ve spent the last few decades under a rock, spend some time getting comfortable with Excel first. You’ll thank me later. I’m also going to assume that you want to create a local database for Windows users, even though Access can also build web databases.
We’ve written some helpful Excel articles before, so feel free to take a wander through them. We can help you with calendars, budgets, charts and printing. I even wrote a short piece on using the fill handle to save time.
These are the basic building blocks of an Access application, and the part that works the most like a spreadsheet.
Planning is required though, because you need to decide how all of the columns are going to work before you start.
Here’s something to work through in your mind before we come back to this in detail later in this Microsoft Access tutorial. Mostly, with Excel, you’re dealing with a single spreadsheet.
In Access, you generally involve a number of tables. Sometimes a significant number. The tables in Access can be linked together, in ways that make sense in terms of the way the information is related. That’s where the “˜R’ in RDBMS comes from, and that’s the power behind Access.
This is the way you ask questions of Access. You can design them on the fly, and save them for later use. In a general sense, the answers are not saved. Just the queries that will provide the answers. That’s great, because the next time you ask the question, if the data has been updated, you’ll get a different answer.
These are the visual components that make inputting and viewing data on-screen easy.
Not everyone is going to be comfortable inputting data straight to the tables you create, and you might want more control over how that input works. Forms are the answer.
You can also set these up for more practical viewing of the data from tables or queries on-screen.
Sometimes you don’t want to see things on-screen. you want to print them, or email them to your boss.
You might want to pass information to someone who cannot use Access themselves. You can easily design and build suitable reports for most situations.
Macros and modules
This is where things get a little more complex. It’s best to have a good understanding of all the components above before you spend any time with these.
Both options are ways to automate tasks in Access, with macros being the easier choice, and VBA modules being the more capable. Both have their uses, and we will come back to them later.
Access is complex, but it’s not difficult. From an Excel perspective, Access tables are like spreadsheets, but more structured. Everything else is about manipulating those tables to question, view, report and automate.
The complexity is worth it, because of the power Access gives you to achieve more. I’ll be back soon with a post all about tables.
In the meantime, find a spreadsheet you might like to replace with a database. Take a look at the sheets, and see if you can find any inconsistencies. You need to tidy those up to use the data in Access.
Do you use Access at all? Have you struggled? What sorts of problems do you have? let me know in the comments.