Why is it important to use form in Access than data directly in the table?

Microsoft Access is a relational database program and a typical database includes tables, queries, forms, and reports. With Microsoft Access, you can easily organize, store and retrieve data. There are several benefits or advantages to using Microsoft Access.

Reports also display your data, but on paper. Unlike Forms, Reports don't allow you to edit the data - they are designed to be static. After all, once you've printed your data on paper (or as a PDF) it's going to be pretty static, so Reports reflect that.

Whether it's a product catalogue (as in the screenshot below) or a staff directory, an invoice or a manufacturing docket, if it's data on paper, it's a report.

Queries extract and link the data. Think about Amazon's database. It has (maybe) a million products, a hundred million customers and a billion orders. The odds are that you're not going to want to move this data across the internet very often - it would just take too long, and most of it would be irrelevant to what you're actually wanting to find out. So the job of a query is to extract just the information you need for you to work with. So when you log in to Amazon (other online - and even real! - stores are available) and ask to see your order history, a query will have the job of extracting relevant information about the customer (you), the orders (but only those for the past 60 days, or whatever) and the products (but only those you've ordered in that time frame). It passes that information to the form (to display on screen) or the report (to print out). And queries are dynamic - by which we mean that if you make a change to the data - perhaps correcting a typo in your address - they pass that changed data back to the correct table.

Macros are one way of extending what you can do in Microsoft Access. For example, when you create a form, you can add a button to the form - perhaps to open another, related form or report. When you add a button, Access, by default, will launch a wizard which guides you towards getting the button to do what you wanted. But there's a limit to what you can achieve like this. For example, what if you wanted the button to open both another form and also a report? The wizard won't let you do this - but a Macro will.

You may have used macros elsewhere - perhaps in Word or Excel. But macros in Access are slightly different. When you create a macro in Microsoft Excel, for example, you either record it (essentially telling Excel "Watch what I'm doing and write it down so that you can repeat these actions later") - which is what most people do, at least to begin with - or you write the instructions yourself, using a programming language called Visual Basic for Applications.

But in Microsoft Access, you can't record a macro. So rather than forcing you to learn the programming language, Microsoft have invented a new way of creating such instructions. Instead of having to write the code yourself, macros provide a drop-down list of instructions, and you can select from that list. So, for example, you might select the instruction "OpenForm". This then enables another drop-down list, showing all the available forms. Another instruction would be "OpenReport" which provides a list of all available reports to choose from.

In this way, you could select "OpenForm" twice to open two forms, or "OpenForm" and then "OpenReport" to open both a form and a report, thus achieving what you wanted your button to do, but what you couldn't do by simply using the button's wizard.

Modules are much more akin to the world of Excel macros designed above - they are places where you store VBA code. But as we noted, you can't record a macro in Microsoft Access, so modules are where you'll find all the VBA code you've written yourself, plus, potentially, code written by various wizards within Microsoft Access.

Why would you write your own code from scratch when you have the macro option? Well, remember that macros provide drop-down lists of actions such as "OpenForm" and "OpenReport". This list, while very useful, is limited. Writing your own code is like speaking a language - the limit to what you can say in that language is really the limit of your imagination (and skill!). But a drop-down list can only ever provide a subset of those choices. So, many people start with macros, but eventually progress to writing their own code - in which case, they'll be storing that code in modules.

In conclusion

So that's it for the introduction to Microsoft Access objects.

Do note that it really is just an introduction - there are other types of object that you'll come across, and in one or two places I've simplified the picture a little for ease of explanation and understanding. But this set of descriptions should at least get you started.

In future articles, we're going to take a look at the difference between "normal" tables and "linked" tables, and at what relationships are and why you need them!

Get in touch!

If you've got questions, feel free to get in touch, either through the comments box below, or via our contact page - we'd love to hear from you. Or, if you want to know more, why not talk to us about organising a Microsoft Access course? And in the meantime, enjoy getting to grips with Microsoft Access!

What is the advantage of using form for data entry over using a table?

Why are forms in Access important?

What is the difference between a table and a form in Access?