Now that you are more comfortable with the user interface in Microsoft Office Access 2007, it’s time to dig deeper into exactly what makes up an Access database. This chapter helps you understand the relationships among the main components in Access and shows you how to move around within the database management system.
Access calls anything that can have a name an object. Within an Access database, the main objects are tables, queries, forms, reports, macros, and modules.
If you have worked with other database systems on desktop computers, you might have seen the term database used to refer to only those files in which you store data. In Access, however, a desktop database (.accdb) also includes all the major objects related to the stored data, including objects you define to automate the use of your data. You can also create an Access application using a project file (.adp) that contains the objects that define your application linked to a Microsoft SQL Server database that stores the tables and queries. Here is a summary of the major objects in an Access database:
Table. An object you define and use to store data. Each table contains information about a particular subject, such as customers or orders. Tables contain fields (or columns) that store different kinds of data, such as a name or an address, and records (or rows) that collect all the information about a particular instance of the subject, such as all the information about a department named Housing Administration. You can define a primary key (one or more fields that have a unique value for each record) and one or more indexes on each table to help retrieve your data more quickly.
Query. An object that provides a custom view of data from one or more tables. In Access, you can use the graphical query by example (QBE) facility or you can write SQL statements to create your queries. You can define queries to select, update, insert, or delete data. You can also define queries that create new tables from data in one or more existing tables. When your Access application is a project file connected to an SQL Server database, you can create special types of queries-functions and stored procedures-that can perform complex actions directly on the server.
Form. An object designed primarily for data input or display or for control of application execution. You use forms to customize the presentation of data that your application extracts from queries or tables. You can also print forms. You can design a form to run a macro or a Microsoft Visual Basic procedure in response to any of a number of events-for example, to run a procedure when the value of data changes.
Report. An object designed for formatting, calculating, printing, and summarizing selected data. You can view a report on your screen before you print it.
Macro. An object that is a structured definition of one or more actions that you want Access to perform in response to a defined event. For example, you might design a macro that opens a second form in response to the selection of an item on a main form. You can include simple conditions in macros to specify when one or more actions in the macro should be performed or skipped. You can use macros to open and execute queries, to open tables, or to print or view reports. You can also run other macros or Visual Basic procedures from within a macro.
Module. An object containing custom procedures that you code using Visual Basic. Modules provide a more discrete flow of actions and allow you to trap errors. Modules can be stand-alone objects containing functions that can be called from anywhere in your application, or they can be directly associated with a form or a report to respond to events on the associated form or report.
For a list of events on forms and reports, see Chapter 17, “Understanding Event Processing.”
|Inside Out-What Happened to Data Access Pages?|| |
Office Access 2007 no longer supports designing data access pages (DAPs). Usability studies conducted by Microsoft show that DAPs are not a widely used feature within Access, and Microsoft is focusing more of their efforts on Microsoft Windows SharePoint Services for sharing data in corporate environments. To maintain backward compatibility with previous versions, Office Access 2007 will continue to support existing .mdb applications that contain DAPs, but you cannot create new data access pages or modify existing pages from within Access 2007.
Figure 3–1 shows a conceptual overview of how objects in Access are related. Tables store the data that you can extract with queries and display in reports or that you can display and update in forms. Notice that forms and reports can use data either directly from tables or from a filtered view of the data created by using queries. Queries can use Visual Basic functions to provide customized calculations on data in your database. Access also has many built-in functions that allow you to summarize and format your data in queries.
Figure 3–1: In an Access application, you can design queries to extract data from or update data in tables; you can build forms or reports on tables or queries, and you can write code in macros or modules to automate your application.
Events on forms and reports can trigger either macros or Visual Basic procedures. An event is any change in the state of an Access object. For example, you can write macros or Visual Basic procedures to respond to opening a form, closing a form, entering a new row on a form, or changing data either in the current record or in an individual control (an object on a form or report that contains data). You can even design a macro or a Visual Basic procedure that responds to the user pressing individual keys on the keyboard when entering data!
For more information about using Visual Basic within Access, see Chapter 19, “Understanding Visual Basic Fundamentals,” and Chapter 20, “Automating Your Application with Visual Basic.”
Using macros and modules, you can change the flow of your application; open, filter, and change data in forms and reports; run queries; and build new tables. Using Visual Basic, you can create, modify, and delete any Access object; manipulate data in your database row by row or column by column; and handle exceptional conditions. Using module code you can even call Windows application programming interface (API) routines to extend your application beyond the built-in capabilities of Access.