Video get to know database objects

Try it! Transcript



Access provides six database objects that, when combined, help you take full advantage of your data. Learn the role that each object plays.

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:

Major query types	Use
Select	To retrieve data from a table or make calculations.
Action	Add, change, or delete data. Each task has a specific type of action query. Action queries are not available in Access web apps.

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.

Data isn't very interesting—in fact it can be meaningless—until you do something with it. With Access, you can structure and shape your data to give it the context that makes it not only useful, but downright indispensable. By incorporating some of the six Access components, or OBJECTS, into your database, you can make it easier to enter, find, visualize, and otherwise manage your data.

Here are the six Access objects you can use to work with your data.

TABLES store your data. They're the backbone of your database. The FIELD is the smallest piece of data in any database, and fields are organized into columns in your table. A COLLECTION of fields forms a RECORD, and records are organized into your table's rows. In this way, your data fields and records are neatly organized as a table.

QUERIES are questions you ask of your data. "What were the fourth-quarter sales?" for example. Queries can also filter data and do math such as calculating sums or discounts. The results are arranged in a table. Depending on the type of query, you can add or change data.

FORMS give your database a finished look. They can help you enter data quickly and provide buttons for navigating your database.

REPORTS are how you arrange, distill, or visualize your data to better present or analyze it. When you need a chart or a graph to represent some of your data, you can add one to a report.

MACROS automate your database. They make something happen when you select a button. You can also create macros that carry out repetitive tasks for you.

MODULES also help automate your database in a bigger way beyond macros. You can perform powerful tasks by writing code using Visual Basic for Applications in Modules. With modules, you essentially create a custom add-on for your database.

Now that you've been introduced to the six database objects, let's look again at our example and learn about VIEWS. What you see here is the table object showing in DATASHEET View. Remember that the database object you're working with is always in a view of some kind—either a view for CREATING the object or a view for USING that object.

Here's another example. THIS table is now open in Datasheet View. You can use its tab to easily switch to Design View and see your many options for creating and changing the table.

This form is open in FORM View, which you can use to quickly add data to the underlying table. You can open the form in LAYOUT view to add controls or change the form. This is where you create the form in the first place.

The same is true with queries. You set up the query in Design View. Then when you run the query, the results appear in Datasheet View. You can take it even further and open a query in SQL view and change the actual code if you need to.

Some views do both jobs. For example, you can add data to this table as you'd expect. But you can also change the design by adding fields.

So now you know the six types of Access database objects and the views where you can work with those objects. As you plan your database, think about what you want to do with your data. Your end uses for the data will help you determine which Access objects will help you best realize your goals.