Creating Databases, Data Access Pages, and Projects

3 4

When you create an Access database, you have the choice of creating a new database, a data access page, or a project. A database is the right choice when you’re working with data on your own computer: An Access database stores data in its own tables, or it links to other Access databases, as well as to Excel worksheets and text files. A data access page is the best choice when you want to work with data in Access or SQL Server databases via the Internet or an intranet, using Microsoft Internet Explorer 5.0 or later. A project is the correct choice if you need to create a large-scale client-server application to work with data in SQL Server tables. Projects store their data in SQL Server tables and use Access forms and reports as an interface (or data access pages when the data is not stored locally).

Projects in Access

Access uses the term project in two very different ways. If you choose a project selection when creating a new database, you create an ADP file that contains Access interface objects connected to data stored in a SQL Server database. Chapter 19, "Creating Access Projects," gives more details about this topic.

The other type of project is a Microsoft Visual Basic for Applications (VBA) project. Every Access database (MDB file) has its own VBA project, which can be viewed in the Project Explorer pane of the Visual Basic Editor (VBE) window. An Access VBA project contains the code attached to forms and reports (in the Microsoft Access Class Objects folder), any standard modules in the database (in the Modules folder), and possibly other objects. The database’s project has the same name as the database, as shown in Figure 2-1.

Figure 2-1. An Access database’s project has the same name as the database itself.

If you have Microsoft Office Developer (MOD), you can also create new VBA projects and compile them into COM add-ins, as described in Chapter 21, "Creating Your Own Add-Ins."

An Access database contains a variety of objects in which you can store and manipulate data, such as tables, forms, queries, reports, macros, and modules. (A database won’t necessarily contain all these objects.) An Access project, on the other hand, contains only interface objects; the data is stored in tables in an external database, usually a SQL Server database.

The Database Window

When you open (or create) a database in the Access window, a Database window appears within the main Access window. The title bar of the Database window contains the database’s name and version. On the left side of the Database window is an Objects bar, from which you can select the type of database objects you want to view. The Database window has its own toolbar, which includes buttons that you can click to perform actions on the selected database object or to change the appearance of the Database window itself. Figure 2-2 shows the Database window, with some of its main features labeled.

Figure 2-2. The Access Database window allows you to select the types of data objects you want to view.

note

---

The Northwind and the Crafts databases are used throughout this chapter to illustrate database features. Both databases are available on the companion CD.

As an example of how the main database components are interrelated, the diagram in Figure 2-3 illustrates how you might use a form to enter data—in this example, you’re entering information about crafts-related books and videos into tables and then filtering that data by queries and printing it in various reports. After you’ve entered all your information, you can also access the various database components that you have created—reports, queries, and so on—via the database’s main menu.

Figure 2-3. After you enter data in a form, you can filter and sort the data by using queries and print the results in reports.

Tables

You can use Access database tables to store data internally or to link to external data. Local tables store data in the same database as interface elements (forms and reports). Linked tables display data stored in external data sources, such as other Access databases, Excel worksheets, comma-delimited text files, and dBASE files. Figure 2-4 shows several local and linked tables in an Access database. Linked tables are indicated by arrows, and special icons indicate linked dBASE files, Excel worksheets, and text files.

Figure 2-4. A different icon is used to indicate each linked table type—a plain arrow for linked Access tables, the Notepad icon for linked text files, a dB icon for dBASE files, and the Excel icon for linked Excel worksheets.

Database tables consist of rows and fields. A row (or record) is a set of related data about a particular subject; a field is a category of information. For example, a Customers table would have one row (or record) for each customer and would store information in fields such as Customer ID, Last Name, and Street Address. Different fields can store different types of data, such as numbers, text, and Yes/No values. You create and modify tables in Design view, as shown in Figure 2-5.

Figure 2-5. You can create and modify Access tables in Design view.

You can enter data directly into a table in Datasheet view (see Figure 2-6), but for ease of use and for data security, it’s preferable to use a form as the interface for entering and modifying data. However, it’s sometimes convenient to open a table in Datasheet view for a quick edit or a search-and-replace operation.

Figure 2-6. You can use Datasheet view to change an area code with Find And Replace.

See Chapter 4, "Creating a Database," for more information about tables and fields.

A database typically contains a number of tables related to one another by key fields. The diagram in Figure 2-7 shows the links between tables in an Access database in the Relationships window. You can print the Relationships window by choosing File, Print Relationships.

Figure 2-7. The Relationships window shows links between tables in an Access database.

See Chapter 3, "Introduction to Database Design," for more information about key fields and setting up relationships among tables in a database.

Access projects don’t contain tables; instead, they work with tables located in SQL Server databases. See Chapter 19, "Creating Access Projects," for more information about projects.

Queries, Views, and Stored Procedures

You use queries to sort, filter, add, delete, and modify data in your Access databases. For example, to find out how many CDs in your collection were recorded by Record Artist, you use a query to tell Access which data you want to find. Access then displays only those records that list Record Artist as the artist. You’ll use primarily two types of queries in your work with Access: select queries and action queries. Sorting and filtering are done in select queries, whereas the various types of action queries can be used to modify data in tables and create new tables.

For more information about the different query types, see Part 3, "Queries and Recordsets."

Access projects don’t have queries; instead, they have views and stored procedures, which correspond to various types of queries. Views and stored procedures are stored in the SQL Server database, not in the Access project itself.

Views in Access

The word view is used in the following two ways in Access:

• Views of a database object, such as a table, form, or query, are selected from the View menu. These views will be discussed in the chapters dealing with specific database objects.
• Views in Access projects correspond to select queries (which are used to return a set of records on the basis of criteria you specify) in Access databases.

For more information about views and stored procedures, see Chapter 19, "Creating Access Projects."

Queries are also commonly used to sort data for reports or data access pages. For example, you might want to display data for only a specific range of dates. Figure 2-8 shows a query in Design view, with criteria that limit the query results to orders placed between June 1 and December 31, 1995.

Figure 2-8. This query filters by a date range in Design view.

Figure 2-9 shows the same query in Datasheet view, with only orders in the specified date range displayed.

Figure 2-9. Here’s the same query in Datasheet view.

Forms and Reports

In Access, both databases and projects use forms to enter, edit, and modify data in local or linked tables. Reports are used to preview and print data for distribution. Although you can print an Access form, you’re better off using forms for entering and modifying data and using reports for printing data, even if you have to create two database objects instead of one. The design elements needed for a useful and attractive form make for an ugly and not particularly useful report (and vice versa).

Figure 2-10 shows a typical form displaying data from a single record in a table.

Figure 2-10. This useful form displays a single record’s information.

Figure 2-11 depicts a grouped report in Print Preview, showing data from several records in a table.

Figure 2-11. This useful report displays information from multiple records.

Both forms and reports use controls, which are interface objects that either display data from tables or provide decorative elements. On forms, different types o f controls let users enter data by typing, selecting from a list, or choosing an option from a group of options. Figure 2-12 shows a form in Design view. The Toolbox is used for inserting various types of controls; the field list is used for inserting fields from a table or query; and the properties sheet is used for modifying the properties of a control, a form section, or the entire form.

Figure 2-12. Design view offers many tools that you can use as you design your forms.

Figure 2-13 shows a complex form—in this case, the Crafts Books, Videos, And Kits form, which is the main data entry form for the Crafts sample database (available on the companion CD). This form includes text boxes for entering data directly, combo boxes for selecting items from drop-down lists, check boxes for indicating Yes or No choices, and option groups for selecting one of a number of options. It also has a tab control with several tabs, each with an embedded subform, and several command buttons that run event procedures to perform various actions.

You can use command buttons and embedded subforms on forms to quickly access related data. On the Books And Videos form, for example, you can click the Publisher Data button next to the Publisher box to get complete data about the selected publisher, and you can click the Works By Same Author button under the ID box to display a form listing all books by the selected author.

Figure 2-13. This complex form is shown in Form view.

For more information about using various types of controls on forms, see Chapter 6, "Working with Form Controls."

Reports are used to display (or print) data, not to modify data directly, so interactive controls such as combo boxes aren’t useful on reports. However, reports do have several special features that don’t apply to forms, such as the ability to sum numeric fields both for data groups and for the entire report, which is useful when you’re preparing financial reports that contain breakdowns by month or by quarter. Additionally, you have many options for displaying data in reports, including grouped reports, columnar (datasheet) reports, charts, graphs, and—new to Access 2002—PivotTables and PivotCharts.

For more information about creating various types of reports, see Chapter 7, "Using Reports to Print Data."

Macros and Modules

Macros are collections of actions that enable you to perform database tasks using the keyboard or mouse. As the programming language for Access has become more powerful (first with Access Basic and then with VBA), code has largely replaced macros. However, macros are still useful for automating repetitive database tasks. They’re also useful for two special purposes: creating database startup routines and creating your own custom hot keys. Figure 2-14 illustrates a macro that performs a number of actions necessary for importing and processing data from a mainframe computer; all you need to do to perform these actions in the correct sequence is run the macro.

Figure 2-14. This macro runs a sequence of actions to import data from a mainframe computer.

Modules are collections of VBA procedures used to perform database actions. (See Figure 2-15.) Macros also perform database actions, but they have limitations; VBA code allows you to go beyond the boundaries of macro actions and fully automate a sophisticated database application. You can write procedures in stand-alone modules (that is, standard modules) or in modules attached to forms and reports (that is, code behind forms), and you can also define new database objects in class modules. Some programming tasks, such as error handling, require VBA code and cannot be handled with a macro.

Figure 2-15. The Access Visual Basic Editor (VBE) window shows form and report code modules and a standard module.

For more information about macros, see Chapter 14, "Using Macros." For more information about writing code in modules, see Chapter 20, "Customizing Your Database Using VBA Code."

Data Access Pages

Data access pages are used to work with live data via the Internet or an intranet or with data in the database. When you create a new database, the task pane offers a data access page option that lets you create a stand-alone data access page (one that is not part of a database). However, data access pages can also be components of Access databases, listed in the Pages group of the database’s Objects bar.

Data access pages can display data in much the same way as forms. (See Figure 2-16.) They also offer a mode for working with grouped data that resembles a grouped report more than a traditional Access form. Data access pages offer advantages over reports when you need to distribute data over the Internet or by e-mail—users see current data when they open the message, as opposed to the stationary data displayed in a report snapshot.

Figure 2-16. This data access page was created from a form.

caution

---

Sending a data access page to someone by e-mail is risky. Unless the recipient has a connection to the appropriate database, the data access page won’t be able to display the data.