Get to know database objects

Tables

Access organizes your information into tables: lists of rows and columns reminiscent of an accountant’s pad or a spreadsheet. Each table stores information about a specific subject, so most databases include more than one table.

Each row in the table is called a record, and each column is called a field. A record contains all the specific information for a particular entity, such as a customer or an order. A field is a single item of information about that entity. In the Products table, for instance, each row or record would hold information about one product. Each column or field holds some type of information about that product, such as its name or price. If you aren’t already familiar with them, learn the basics of databases.

Queries

In a well-designed database, the data that you want to present through a form or report is usually located in multiple tables. You use a query to pull that information from various tables and to assemble it for display in a form or report.

A query can be a request for data results from your database, or it can be used to perform an action on the data, or it might be both. Use queries to get answers to simple questions, perform calculations using data, combine data from different tables, and add, change, or delete data from a database.

There are many types of queries, but the two basic types are:

Forms

Forms are like display cases in stores—they make it easier to view or get to the database items you want. Similar to paper forms, Access forms are objects through which you or other users can add, edit, or display the data stored in your Access desktop database. It’s important to design your form with use in mind. For example, if it’s for multiple users, a well-designed form helps to make data entry accurate, fast, and efficient.

Reports

Reports offer a way to view, format, and summarize information from your Access database. For example, create a simple report of phone numbers for all your contacts or a summary report on the total sales across different regions and time periods.

Reports are handy when you want to present the information in your database to:

• Display or distribute a summary of data.

• Archive snapshots of the data.

• Provide details about individual records.

• Create labels.

Macros

A macro is a tool that allows you to automate tasks and add functionality to your forms, reports, and controls. For example, if you add a command button to a form and associate the button's OnClick event to a macro, it then performs a command each time the button is clicked.

Access provides a design environment to help you create macros. Essentially, you use a simplified programming language to build a list of actions to perform. Some actions require additional information, like which field to display fill in. The design environment makes it easier to create macros, as you select from lists of actions and fill in information.

Macros are used to automate a series of actions, make changes to data in a database, and more. Through the Design view, macros provide a subset of the commands that are available in Visual Basic for Applications (VBA). Most people find it easier to build a macro to add functionality to forms, reports, and controls than to write VBA code, but you can always convert the macros to VBA from within the Design view.

Suppose that you want to open a report directly from one of your data entry forms. Add a button to your form and then create a macro that opens the report. The macro can either be a standalone macro (a separate object in the database), which is then bound to the OnClick event of the button, or the macro can be embedded directly into the OnClick event of the button itself. Either way, when you click the button, the macro runs and opens the report.

Modules

Modules are VBA code that you write to automate tasks in your application and to perform higher end functions. You write modules in the VBA programming language. A module is a collection of declarations, statements, and procedures that are stored together as a unit.