Where did that name come from?
As you work with computers, you come across terms that make you wonder how they got their names. For example, why is the main circuit board of your computer called a “motherboard“? Or why is your computer’s digital storage device called a “hard disk“? I have found its easier to remember the term if you know where the term came from. For example, the motherboard got its name because like a mother, it provides power to – and exchanges information with – all the connected expansion cards, which by the way are called “daughterboards“. The hard disk is so named because the platter that holds the data is hard, not floppy, as were the old 5 1/4” floppy disks of the 1980’s.
The objects in a Microsoft Access Database also have names, like Table and Query, that may cause one to wonder where the name came from. But, as you will see, when you understand how the object is like its name, it will be easier to remember its function.
Tables
A table is a piece of furniture that holds things. For example, your dining room table holds plates and dishes you use to enjoy delicious food while dining with family and friends.
In a database, a table holds things called records and fields. If you kept your data in Microsoft Excel, then the record would be a row, the field would be a column, and the table would be the sheet. For example, you might have a table for people. It could, for example, include fields for record id, first name, last name, phone number, email address or even favorite ice cream flavor.
Queries
A query is a question or inquiry. The purpose of the question might be to ask for information such as “Whose favorite ice cream flavor is Mint Chocolate Chip?” Or the question might be to ask for an action, such as when you say to your child “Will you please do your homework?”
In a database, a query is also a question. When the query is run, the computer will answer the question based on the information in the tables. For example, if your query asks which people prefer Mint Chocolate Chip, the records for Martha Washington and John Adams will be displayed. If your question requested an action, such as erase all phone numbers where the area code is 571, then it would erase George and Martha’s phone numbers.
Forms
A form is a type of document with blank spaces for inserting information that someone will read. For example, the IRS Form 1040 is used to fill in tax information to determine if you should pay more or receive a refund.
A form in an Access database is also used to view and enter information that is or will be stored in a table. The form can be composed of labels, text boxes, drop down menus, check boxes, pictures, buttons, and more.
Reports
A report is a written account or statement regarding something. For example, after a battle, an army officer is required to write and file a report with superiors about what happened in the battle.
A report in an Access database is also created to give a written account of information that is contained in one or more tables. The report can be printed on paper, or it can be viewed on the screen, or sent to a PDF file for later viewing.
Macro
The prefix “macro” means large. For example, macro-economics refers to the economy as a whole.
A macro in an Access database is a large set of computer instructions that can be run all at once. The macros are usually executed by the user clicking a button. For example, you can create a macro that will print the an annual report plus the last 12 monthly reports when the user clicks a button on a form.
In Microsoft Access macro are created by choosing one or more predefined commands. This is different from other Microsoft Office applications, like Word and Excel. In those other applications, macros are created with VBA (the Visual Basic for Applications programming language). Microsoft Access use of VBA is done with modules, as explained below.
Modules
A module is an independent unit that can be attached to a larger object / solution. For example, NASA in the Apollo missions, had a Lunar Module. It was a smaller spacecraft that could be detached and later reattached to the mother ship to help land men safely on the moon.
A module in a Microsoft Access database contains Visual Basic code that can be attached to other parts of the database. The code in a module can be much more powerful than a macro. For example we recently created a module that got input from a user, exported relevant data to Excel, and then created a beautiful dashboard complete with interactive charts and graphs. This modular code saved the company multiple hours each week as now they just enter their parameters, click a button and watch the computer do all the work in just a few minutes. This automation makes the database both very powerful and easy to use.
Summary
In this post we have seen that a Microsoft Access Database is a collection of tables, queries, forms, reports, macros, and modules. The tables hold data, and queries answer question about the data in those tables. The forms allow users to insert, edit and view data in the tables via a friendly natural interface. Reports are used to output the table data to paper. Macros and Modules provide automation features so the end user can work with the database with very little training.
If you would like some help automating your Access database, please let us know.