Ways to get started if you're using a database for the first time

Working with databases and database objects can be a daunting task when you first get started. The following information should help you become more familiar with the components that make up a Microsoft Access database.

ShowDatabases: What they are and how they work

A database is a collection of information that's related to a particular subject or purpose, such as tracking customer orders or maintaining a music collection. If your database isn't stored on a computer, or only parts of it are, you may be tracking information from a variety of sources that you're having to coordinate and organize yourself.

For example, suppose the phone numbers of your suppliers are stored in various locations: in a card file containing supplier phone numbers, in product information files in a file cabinet, and in a spreadsheet containing order information. If a supplier's phone number changes, you might have to update that information in all three places. In a database, however, you only have to update that information in one place — the supplier's phone number is automatically updated wherever you use it in the database.

ShowAccess database files

Using Microsoft Access, you can manage all your information from a single database file. Within the file, you can use:

  • Tables to store your data.
  • Queries to find and retrieve just the data you want.
  • Forms to view, add, and update data in tables.
  • Reports to analyze or print data in a specific layout.
  • Data access pages to view, update, or analyze the database's data from the Internet or an intranet.

A form, report, query, and data access page displaying data from the same table

Callout 1 Store data once in one table, but view it from multiple locations. When you update the data, it's automatically updated everywhere it appears.

Callout 2 Display data in a query

Callout 3 Display data in a form

Callout 4 Display data in a report

Callout 5 Display data in a data access page

ShowTables and relationships

To store your data, create one table for each type of information that you track. To bring the data from multiple tables together in a query, form, report, or data access page, define relationships between the tables.

Two tables, each with a Customer ID field that relates them

Callout 1 Customer information that once existed in a mailing list now resides in the Customers table.

Callout 2 Order information that once existed in a spreadsheet now resides in the Orders table.

Callout 3 A unique ID, such as a Customer ID, distinguishes one record from another within a table. By adding one table's unique ID field to another table and defining a relationship, Microsoft Access can match related records from both tables so that you can bring them together in a form, report, or query.

ShowQueries

To find and retrieve just the data that meets conditions that you specify, including data from multiple tables, create a query. A query can also update or delete multiple records at the same time, and perform predefined or custom calculations on your data.

A query with fields from two tables

Callout 1 This query accesses separate tables to retrieve the Order ID, Required Date, Company Name, and City information for customers in London whose orders were required in April.

ShowForms

To easily view, enter, and change data directly in a table, create a form. When you open a form, Microsoft Access retrieves the data from one or more tables, and displays it on the screen with the layout you choose in the Form Wizard, or with the layout that you created on your own in Design view (Design view: A view that shows the design of these database objects: tables, queries, forms, reports, and macros. In Design view, you can create new database objects and modify the design of existing objects.).

A form displaying one record of information and a Print Invoice button

Callout 1 A table displays many records at the same time, but you might have to scroll to see all of the data in a single record. Also, when viewing a table, you can't update data from more than one table at the same time.

Callout 2 A form focuses on one record at a time, and it can display fields from more than one table. It can also display pictures and other objects.

Callout 3 A form can contain a button that prints, opens other objects, or otherwise automates tasks.

ShowReports

To analyze your data or present it a certain way in print, create a report. For example, you might print one report that groups data and calculates totals, and another report with different data formatted for printing mailing labels.

Reports that total, display charts, or generate mailing labels

Callout 1 Use a report to create mailing labels.

Callout 2 Use a report to show totals in a chart.

Callout 3 Use a report to calculate totals.

ShowData access pages

To make data available on the Internet or an intranet for interactive reporting, data entry, or data analysis, use a data access page. Microsoft Access retrieves the data from one or more tables and displays it on the screen with the layout that you created on your own in Design view, or with the layout you chose in the Page Wizard.

Grouped data access page with expand buttons and record navigation toolbars

Callout 1 Click the expand indicator ...

Callout 2... to display the data and record navigation toolbar for the next level of detail.

Callout 3 Use the record navigation toolbars to move to, sort, and filter records, and to get Help.

ShowTables: Store and manage your data

A table is a collection of data about a specific topic, such as products or suppliers. Using a separate table for each topic means that you store that data only once. This results in a more efficient database and fewer data-entry errors.

ShowHow data is organized in tables

Tables organize data into columns (called fields) and rows (called records).

Records and fields in tables

For example, each field in a Products table contains the same type of information for every product, such as the product's name. Each record in that table contains all the information about one product, such as the product's name, supplier ID number, units in stock, and so on.

ShowTable Design view

In table Design view, you can create an entire table from scratch, or add, delete, or customize the fields in an existing table.

Different ways to customize a table in Design view

Callout 1 If you want to track additional data in a table, add more fields. If an existing field name isn't descriptive enough, you can rename the field.

Callout 2 Setting a field's data type (field data type: A characteristic of a field that determines what kind of data it can store. For example, a field whose data type is Text can store data consisting of either text or numeric characters, but a Number field can store only numerical data.) defines what kind of values you can enter in a field. For example, if you want a field to store numerical values that you can use in calculations, set its data type to Number or Currency.

Callout 3 You use a unique tag, called a primary key (primary key: One or more fields (columns) whose values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.), to identify each record in your table. A table's primary key is used to refer to related records in other tables.

Callout 4 Field properties are a set of characteristics that provide additional control over how the data in a field is stored, entered, or displayed. Which properties are available depends on a field's data type.

ShowHow to relate two tables

A common field relates two tables so that Microsoft Access can bring together the data from the two tables for viewing, editing, or printing. In one table, the field is a primary key (primary key: One or more fields (columns) whose values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.) that you set in table Design view (Design view: A view that shows the design of these database objects: tables, queries, forms, reports, and macros. In Design view, you can create new database objects and modify the design of existing objects.). That same field also exists in the related table as a foreign key (foreign key: One or more table fields (columns) that refer to the primary key field or fields in another table. A foreign key indicates how the tables are related.).

Common field that relates two tables

Callout 1 In the Suppliers table, you enter a supplier ID, company name, and so on, for each supplier. SupplierID is the primary key that you set in table Design view.

Callout 2 In the Products table, you include the SupplierID field, so that when you enter a new product, you can identify its supplier by entering that supplier's unique ID number. SupplierID is the foreign key in the Products table.

ShowTable Datasheet view

In a table or query, Datasheet view (Datasheet view: A view that displays data from a table, form, query, view, or stored procedure in a row-and-column format. In Datasheet view, you can edit fields, add and delete data, and search for data.) provides the tools you need to work with data.

ShowUsing the Table Datasheet and Query Datasheet toolbars

The Table Datasheet and Query Datasheet toolbars (toolbar: A bar with buttons and options that you use to carry out commands. To display a toolbar, press ALT and then SHIFT+F10.) provide many of the tools you need to find, edit, and print records.

Datasheet toolbar

Callout 1 Print or preview data

Callout 2 Check spelling

Callout 3 Cut, copy, or paste selected text, fields, whole records, or the entire datasheet

Callout 4 Sort records

Callout 5 Filter records, and find or replace values

Callout 6 Add or delete records

ShowWorking with columns, rows, and subdatasheets

You can find tools for working with columns, rows, and subdatasheets (subdatasheet: A datasheet that is nested within another datasheet and that contains data related or joined to the first datasheet.) in the datasheet itself, or by right-clicking a column selector (column selector: The horizontal bar at the top of a column. You can click a column selector to select an entire column in the query design grid or the filter design grid.).

Tools for working with data in Datasheet view

Callout 1 Use the column selector to move, hide, or rename a column.

Callout 2 Resize columns or rows.

Callout 3 Use subdatasheets to view related data.

Callout 4 Freeze the leftmost column so that it is displayed as you scroll to the right.

ShowMoving through records

You can use the navigation toolbar to move through the records in a datasheet.

Record navigation buttons

Callout 1 Go to the first record.

Callout 2 Go to the previous record.

Callout 3 Type a record number to move to.

Callout 4 Go to the next record.

Callout 5 Go to the last record.

Callout 6 Go to a blank (new) record.

ShowQueries: Getting answers to your questions

You use queries to view, change, and analyze data in different ways. You can also use them as a source of records for forms, reports, and data access pages (data access page: A Web page, published from Access, that has a connection to a database. In a data access page, you can view, add to, edit, and manipulate the data stored in the database. A page can also include data from other sources, such as Excel.). There are several types of queries in Microsoft Access.

ShowSelect queries

A select query is the most common type of query. It retrieves data from one or more tables and displays the results in a datasheet where you can update the records (with some restrictions). You can also use a select query to group records and calculate sums, counts, averages, and other types of totals.

ShowParameter queries

A parameter query is a query that when run displays its own dialog box prompting you for information, such as criteria (criteria: Conditions you specify to limit which records are included in the result set of a query or filter.) for retrieving records or a value you want to insert in a field. You can design the query to prompt you for more than one piece of information; for example, you can design it to prompt you for two dates. Access can then retrieve all records that fall between those two dates.

Parameter queries are also handy when used as the basis for forms, reports, and data access pages. For example, you can create a monthly earnings report based on a parameter query. When you print the report, Access displays a dialog box asking for the month that you want the report to cover. You enter a month and Access prints the appropriate report.

ShowCrosstab queries

You use crosstab queries to calculate and restructure data for easier analysis of your data. Crosstab queries calculate a sum, average, count, or other type of total for data that is grouped by two types of information — one down the left side of the datasheet and another across the top.

ShowAction queries

An action query is a query that makes changes to or moves many records in just one operation. There are four types of action queries:

  • Delete Queries     A delete query deletes a group of records from one or more tables. For example, you could use a delete query to remove products that are discontinued or for which there are no orders. With delete queries, you always delete entire records, not just selected fields within records.
  • Update Queries     An update query makes global changes to a group of records in one or more tables. For example, you can raise prices by 10 percent for all dairy products, or you can raise salaries by 5 percent for the people within a certain job category. With an update query, you can change data in existing tables.
  • Append Queries     An append query adds a group of records from one or more tables to the end of one or more tables. For example, suppose that you acquire some new customers and a database containing a table of information on those customers. To avoid typing all this information into your own database, you'd like to append it to your Customers table.
  • Make-Table Queries     A make-table query creates a new table from all or part of the data in one or more tables. Make-table queries are helpful for creating a table to export to other Microsoft Access databases (Microsoft Access database: A collection of data and objects (such as tables, queries, or forms) that is related to a particular topic or purpose.) or a history table that contains old records.

ShowSQL queries

An SQL query is a query you create by using an SQL statement (SQL string/statement: An expression that defines an SQL command, such as SELECT, UPDATE, or DELETE, and includes clauses such as WHERE and ORDER BY. SQL strings/statements are typically used in queries and in aggregate functions.). You can use Structured Query Language (SQL) to query, update, and manage relational databases such as Access.

When you create a query in query Design view (Design view: A view that shows the design of these database objects: tables, queries, forms, reports, and macros. In Design view, you can create new database objects and modify the design of existing objects.), Access constructs the equivalent SQL statements behind the scenes for you. In fact, most query properties in the property sheet in query Design view have equivalent clauses and options available in SQL view (SQL view: An object tab that displays the SQL statement for the current query or that is used to create an SQL-specific query (union, pass-through, or data definition). When you create a query in Design view, the SQL equivalent is constructed in SQL view.). If you want, you can view or edit the SQL statement in SQL view. However, after you make changes to a query in SQL view, the query might not be displayed the way it was previously in Design view.

Some SQL queries, called SQL-specific queries (SQL-specific query: A query that consists of an SQL statement. Subqueries and pass-through, union, and data-definition queries are SQL-specific queries.), can't be created in the design grid (design grid: The grid that you use to design a query or filter in query Design view or in the Advanced Filter/Sort window. For queries, this grid was formerly known as the QBE grid.). For pass-through (pass-through query: An SQL-specific query you use to send commands directly to an ODBC database server. By using pass-through queries, you work directly with the tables on the server instead of the data being processed by the Access database engine.), data-definition (data-definition query: An SQL-specific query that contains data definition language (DDL) statements. These statements allow you to create or alter objects in the database.), and union queries (union query: A query that uses the UNION operator to combine the results of two or more select queries.), you must create the SQL statements directly in SQL view. For subqueries (subquery: An SQL SELECT statement that is inside another select or action query.), you enter the SQL in the Field row or the Criteria row of the query design grid.

ShowForms: Putting a friendly face on your data

A form is a type of a database object that is primarily used to enter or display data in a database. You can also use a form as a switchboard that opens other forms and reports in the database, or as a custom dialog box that accepts user input and carries out an action based on the input.

Data entry form, switchboard form, and dialog box

Callout 1 A data-entry form

Callout 2 A switchboard form

Callout 3 A custom dialog box

Most forms are bound to one or more tables and queries (query: A question about the data stored in your tables, or a request to perform an action on the data. A query can bring together data from multiple tables to serve as the source of data for a form or report.) in the database. A form's record source (record source: The underlying source of data for a form, report, or data access page. In an Access database, it could be a table, query, or SQL statement. In an Access project, it could be a table, view, SQL statement, or stored procedure.) refers to the fields in the underlying tables and queries. A form need not contain all the fields from each of the tables or queries that it is based on.

A bound form stores or retrieves data from its underlying record source. Other information on the form, such as the title, date, and page number, is stored in the form's design.

Types of information displayed on a form: text, data, calculations, and graphics

Callout 1 Graphic elements, such as lines and rectangles, are stored in the form's design.

Callout 2 Data comes from the fields in the underlying record source.

Callout 3 A calculation comes from an expression, which is stored in the form's design.

Callout 4 Descriptive text is stored in the form's design.

You create a link between a form and its record source by using graphical objects called controls (control: A graphical user interface object, such as a text box, check box, scroll bar, or command button, that lets users control the program. You use controls to display data or choices, perform an action, or make the user interface easier to read.). The most common type of control used to display and enter data is a text box.

The relationship between a form and its underlying record source

Callout 1 Labels display descriptive text.

Callout 2 Text boxes display data from and enter data into the Products table.

Callout 3 A text box uses an expression to calculate a total.

You can also open a form in PivotTable view or PivotChart view to analyze data. In these views, you can dynamically change the layout of a form to present data in different ways. You can rearrange row headings, column headings, and filter fields until you achieve the desired layout. Each time you change the layout, the form immediately recalculates the data based on the new arrangement.

Forms in PivotTable view and PivotChart view

Callout 1 In PivotTable view, you can view detail or summarized data by arranging fields in the filter, row, column, and detail areas.

Callout 2 In PivotChart view, you can display data visually by selecting a chart type and viewing data by arranging fields in the filter, series, category, and data areas.

ShowCreating a form

ShowCustomizing a form

ShowIn Design view

You can customize a form in Design view in the following ways:

Record source Change the tables and queries that a form is based on.

Controlling and assisting the user You can set form properties to allow or prevent users from adding, deleting, or editing records displayed in a form. You can also add custom Help to a form to assist your users with using the form.

Form window You can add or remove Maximize and Minimize buttons, short cut menus, and other Form window elements.

Sections You can add, remove, hide, or resize the header, footer, and details sections of a form. You can also set section properties to control the appearance and printing of a form.

Controls You can move, resize, or set the font properties of a control. You can also add controls to display calculated values, totals, current date and time, and other useful information on a form.

ShowIn PivotTable or PivotChart view

You can customize a form in PivotTable or PivotChart view in the following ways:

Add, move, or remove fields     You can add fields to the filter, row, column, and detail areas in PivotTable view, and to the filter, category, series, and data areas in PivotChart view. You can also move fields from one area to another and remove fields from the view.

Filter records     You can filter data displayed in the view by adding or moving a field to the filter area. You can also filter a field in the row and column area.

Sort records     You can sort items in row or column fields in ascending or descending order. You can also sort items in custom order in PivotTable view.

Group records     You can group items in row or column fields on intervals, or create custom groups.

Format elements and change captions     In PivotTable view, you can change the font settings, alignment, background color, and number format of a field. You can also change the captions of fields and custom groups. In PivotChart view, you can change the chart type, format data markers, and more.

ShowReports: Presenting your data to the world

A report is an effective way to present your data in a printed format. Because you have control over the size and appearance of everything on a report, you can display the information the way you want to see it.

Mailing labels, report with grouped records, and report with chart

Callout 1 Create mailing labels.

Callout 2 Show totals in a chart.

Callout 3 Group records into categories.

Callout 4 Calculate totals.

Most reports are bound to one or more table and query (query: A question about the data stored in your tables, or a request to perform an action on the data. A query can bring together data from multiple tables to serve as the source of data for a form or report.) in the database. A report's record source refers to the fields in the underlying tables and queries. A report need not contain all the fields from each of the tables or queries that it is based on.

A bound report gets its data from its underlying record source. Other information on the form, such as the title, date, and page number, is stored in the report's design.

The types of information displayed on a report: text, data, and expressions

Callout 1 The report title and column headings are stored in the report's design.

Callout 2 The date comes from an expression stored in the report's design.

Callout 3 Data comes from fields in the underlying table, query, or SQL statement.

Callout 4 Totals come from expressions, which are stored in the report's design.

You create the link between a report and its record source by using graphical objects called controls (control: A graphical user interface object, such as a text box, check box, scroll bar, or command button, that lets users control the program. You use controls to display data or choices, perform an action, or make the user interface easier to read.). Controls can be text boxes that display names and numbers, labels that display titles, or decorative lines that graphically organize the data and make the report more attractive.

The relationship between a report and its underlying record source

Callout 1 Lines are decorative.

Callout 2 Labels display descriptive text.

Callout 3 Text boxes display data from the Sales by Date query.

Callout 4 A text box uses an expression to calculate a total.

ShowCreating a report

You can create different types of reports quickly by using wizards. Use the Label Wizard to create mailing labels, the Chart Wizard to create charts, or the Report Wizard to create a standard report. The wizard asks you questions and creates a report based on your answers. You can then customize the report the way you want it in Design view (Design view: A view that shows the design of these database objects: tables, queries, forms, reports, and macros. In Design view, you can create new database objects and modify the design of existing objects.).

ShowCustomizing a report

You can customize a report in the following ways:

Record source Change the tables and queries that a report is based on.

Sorting and grouping data You can sort data in ascending or descending order. You can also group records on one or more fields, and display subtotals and grand totals on a report.

Report window You can add or remove Maximize and Minimize buttons, change the title bar text, and other Report window elements.

Sections You can add, remove, hide, or resize the header, footer, and details sections of a report. You can also set section properties to control the appearance and printing of a report.

Controls You can move, resize, or set the font properties of a control. You can also add controls to display calculated values, totals, current date and time, and other useful information on a report.

ShowData access pages: Publishing your data to the Web

A data access page is a special type of Web page designed for viewing and working with data from the Internet or an intranet — data that is stored in a Microsoft Access database (Microsoft Access database: A collection of data and objects (such as tables, queries, or forms) that is related to a particular topic or purpose.) or a Microsoft SQL Server database (Microsoft SQL Server database: A database that consists of tables, views, indexes, stored procedures, functions, and triggers. You can connect your database to SQL Server data by using ODBC or by creating an Access project (*.adp) file.). The data access page may also include data from other sources, such as Microsoft Excel.

ShowA page in Page view or Microsoft Internet Explorer 5.01 with Service Pack 2 (SP2) or later

Using a data access page is similar to using a form: You can view, enter, edit, and delete data in a database. However, you can also use a page outside a Microsoft Access database (Microsoft Access database: A collection of data and objects (such as tables, queries, or forms) that is related to a particular topic or purpose.), so users can update or view data over the Internet or an intranet.

The following illustration shows how the Product data access page, which is used for entering and updating product information, looks in Page view (Page view: An Access window in which you can browse the contents of a data access page. Pages have the same functionality in Page view as in Microsoft Internet Explorer 5.0 or later.) or in Microsoft Internet Explorer 5.01 with Service Pack 2 (SP2) or later.

Data access page in Page view

Callout 1 Enter product information in the text boxes, drop-down list box, and check box.

Callout 2 Use the record navigation toolbar to navigate, add, delete, save, sort, and filter records, and to get Help.

ShowA page in Design view

The following illustration shows the Products data access page in Design view (Design view: A view that shows the design of these database objects: tables, queries, forms, reports, and macros. In Design view, you can create new database objects and modify the design of existing objects.).

Parts of a data access page in Design view

Callout 1 Title in the body of the page

Callout 2 Group header for the Products group level

Callout 3 Record navigation section for the Products group level

ShowParts of a data access page

The body     The body is the basic design surface of a data access page. On a page that supports data entry, you can use it to display informational text, controls bound to data, and sections.

Sections     You use sections to display text, data from a database, and toolbars.

Two types of sections (section: A part of a form, report, or data access page such as a header, footer, or detail section.) are typically used on pages that support data entry: group header and record navigation sections. A page can also have footer and caption sections.

Each group level in a data access page has a record source (record source: The underlying source of data for a form, report, or data access page. In an Access database, it could be a table, query, or SQL statement. In an Access project, it could be a table, view, SQL statement, or stored procedure.). The name of the record source is displayed on the section bar (section bar: The horizontal bar above a data access page section in Design view. The section bar displays the type and name of the section. Use it to access the section's property sheet.) for each section used for a group level.

ShowDesigning different types of data access pages

You design data access pages in Design view (Design view: A view that shows the design of these database objects: tables, queries, forms, reports, and macros. In Design view, you can create new database objects and modify the design of existing objects.) in Microsoft Access. The page is a separate file that is stored outside Access; however, when you create the file, Access automatically adds a shortcut to the file in the Database window. Designing a data access page is similar to designing forms and reports — you use a field list (field list: A window that lists all the fields in the underlying record source or database object, except in data access page Design view. In data access page Design view, it lists all the record sources and their fields in the underlying database.), the toolbox (toolbox: A set of tools that is available in Design view for adding controls to a form or report.), controls (control: A graphical user interface object, such as a text box, check box, scroll bar, or command button, that lets users control the program. You use controls to display data or choices, perform an action, or make the user interface easier to read.), and so on. However, there are some significant differences in the way that you design and interact with data access pages as opposed to forms and reports. How you design the page depends on what it will be used for.

ShowUsing data access pages in Internet Explorer

A data access page is connected directly to a database. When users display the data access page in Internet Explorer, they are viewing their own copy of the page. That means any filtering, sorting, and other changes they make to the way the data is displayed — including changes they make within a PivotTable list (PivotTable list: A Microsoft Office Web Component that is used to analyze data interactively on a Web page. Data displayed in a row and column format can be moved, filtered, sorted, and calculated in ways that are meaningful for your audience.) or spreadsheet (Spreadsheet Component: A Microsoft Office Web Component that provides the interactive functionality of a spreadsheet on a Web page.You can enter data, add formulas and functions, apply filters, change formatting, and recalculate.) — affect only their copy of the data access page. However, changes that they make to the data itself — such as modifying values, and adding or deleting data — are stored in the underlying database, and therefore are available to everyone viewing the data access page.

Users get Help on how to work with the page in Internet Explorer by clicking the Help Button image button on the record navigation toolbar. The Help file that appears is automatically included with any data access page that's published with a record navigation toolbar. If you delete the record navigation toolbar or if you disable its Help button, you should provide instructions for using the page to those who will use the page.

 Note   To view and work with the data access page on the Internet or an intranet, users need Microsoft Internet Explorer 5.01 with Service Pack 2 (SP2) or later.

ShowUsing data access pages in Microsoft Access

You can also work with a data access page in Page view (Page view: An Access window in which you can browse the contents of a data access page. Pages have the same functionality in Page view as in Microsoft Internet Explorer 5.0 or later.) in Access. Data access pages can supplement the forms and reports that you use in your database application. When deciding whether to design a data access page, form, or report, consider the tasks that you want to perform.

You get Help on how to work with the page in Page view by clicking the Help Button image button on the Page View toolbar to display Access Help. Clicking the Help button on a record navigation toolbar on the page displays Help for working with the page in Microsoft Internet Explorer. As mentioned previously, this Help file is automatically included with any data access page that's published with a record navigation toolbar. You can delete the Help button on pages that are used only within Access, or you can modify the button to provide custom Help.

ShowWhere a data access page gets its data

ShowTypes of data sources for a data access page

A data access page gets its data from a Microsoft Access database (Microsoft Access database: A collection of data and objects (such as tables, queries, or forms) that is related to a particular topic or purpose.), or a Microsoft SQL Server database (Microsoft SQL Server database: A database that consists of tables, views, indexes, stored procedures, functions, and triggers. You can connect your database to SQL Server data by using ODBC or by creating an Access project (*.adp) file.) version 6.5 or later. To design a page using data from one of these databases, the page must be connected to the database. If you have already opened an Access database or an Access project (Microsoft Access project: An Access file that connects to a Microsoft SQL Server database and is used to create client/server applications. A project file doesn't contain any data or data-definition-based objects, such as tables and views.) that's connected to a SQL Server database, the data access page you create automatically connects to the current database and stores that path in the ConnectionString property of the data access page. When a user browses to the page in Microsoft Internet Explorer 5.01 with Service Pack 2 (SP2) or later or displays the page in Page view (Page view: An Access window in which you can browse the contents of a data access page. Pages have the same functionality in Page view as in Microsoft Internet Explorer 5.0 or later.), it displays current data from the underlying database by using the path defined in the ConnectionString property. If you design the data access page while the database is on a local drive, Access will use the local path, which means the data won't be accessible to others. For this reason, it's important that you move or copy the database to a network location that's accessible to users of your page. Once the database is on the network share, open the database using the UNC (universal naming convention (UNC): A naming convention for files that provides a machine-independent means of locating the file. Rather than specifying a drive letter and path, a UNC name uses the syntax \\server\share\path\filename.) address. If you move or copy the database after you've already designed the page, you'll have to update the path in the ConnectionString property to point to the new location.

Instead of having to update the ConnectionString property of each page in the database separately, you can choose to create a connection file. A connection file stores the connection information for a data access page, and can be shared between multiple data access pages. When you open a data access page that uses a connection file, the page reads the connection file and connects to the appropriate database. After creating the connection file, if you move or copy the database, all you have to do is edit the connection information in the connection file.

ShowTypes of data sources for Microsoft Office Web Components on a data access page

Although the data access page gets its data from a Microsoft Access database (Microsoft Access database: A collection of data and objects (such as tables, queries, or forms) that is related to a particular topic or purpose.) or Microsoft SQL Server database (Microsoft SQL Server database: A database that consists of tables, views, indexes, stored procedures, functions, and triggers. You can connect your database to SQL Server data by using ODBC or by creating an Access project (*.adp) file.), Microsoft Office Web Component (Microsoft Office Web Components: Interactive components, such as worksheets, charts, and PivotTable lists on Web pages that facilitate data analysis. To use these components, you must have a Microsoft Office license.) controls on the page can display data from either of these databases or from other sources. For example, the page may contain a PivotTable list (PivotTable list: A Microsoft Office Web Component that is used to analyze data interactively on a Web page. Data displayed in a row and column format can be moved, filtered, sorted, and calculated in ways that are meaningful for your audience.), spreadsheet (Spreadsheet Component: A Microsoft Office Web Component that provides the interactive functionality of a spreadsheet on a Web page.You can enter data, add formulas and functions, apply filters, change formatting, and recalculate.), or chart (chart: A graphical representation of data in a form, report, or data access page.) with data from a Microsoft Excel worksheet or a database other than Access or SQL Server. Depending on the data source, the data in these controls may be a snapshot of the original data or it may be live data displayed through a separate connection that the control has with its data source.

To create an Office Web Component with data from a source other than the current database, you might have to use a program other than Access to first publish the data to a Web page. For example, to create a PivotTable list that contains data from an Excel worksheet, you have to publish the data from Excel. Then, in Access, you can open the Web page and turn it into a data access page by adding controls that are bound to an Access or SQL Server database. You can further customize the page by adding labels, pictures, or other features to enhance the page's appearance. From within Access, you can create a PivotTable list that uses data from an external source other than Excel.

ShowMacros: Putting your data to work

Macros are a set of actions that you can create to help you to automate common tasks. By using groups of macros, you can perform several tasks at once.

ShowWhat is a macro?

A macro is a set of one or more actions (action: The basic building block of a macro; a self-contained instruction that can be combined with other actions to automate tasks. This is sometimes called a command in other macro languages.) that each perform a particular operation, such as opening a form or printing a report. Macros can help you to automate common tasks. For example, you can run a macro that prints a report when a user clicks a command button.

Macros contain actions and arguments

Callout 1 When you create a macro, you enter the actions you want to carry out in this portion of the Macro window (Macro window: The window in which you create and modify macros.).

Callout 2 You can specify arguments for an action in this portion of the window.

A macro can be one macro composed of a sequence of actions, or it can be a macro group (macro group: A collection of related macros that are stored together under a single macro name. The collection is often referred to simply as a macro.). You can also use a conditional expression (conditional expression: An expression evaluated and compared to a value; for example, If...Then and Select Case statements. If the condition is met, one or more operations are performed. If it isn't met, the operation is skipped.) to determine whether in some cases an action will be carried out when a macro runs.

The following macro is composed of a series of actions. Microsoft Access carries out these actions each time the macro runs. To run this macro, you refer to the macro name Review Products.

A macro containing several actions

ShowWhat is a macro group?

If you have numerous macros, grouping related macros in macro groups (macro group: A collection of related macros that are stored together under a single macro name. The collection is often referred to simply as a macro.) can help you to manage your database more easily.

For example, the following macro group, named Buttons, is made up of three related macros: Employees, Products, and Reps. Each macro carries out the OpenForm action, and the Products macro also carries out the MoveSize action.

Example of a macro group

The name in the Macro Name column identifies each macro. When you run a macro in a macro group, Microsoft Access carries out the action in the action column and any actions that immediately follow with a blank Macro Name column.

You can run a macro in a macro group in an event or event procedure (event procedure: A procedure that is automatically executed in response to an event initiated by the user or program code, or that is triggered by the system.) by typing the macro group name followed by a period and then the macro name. In the preceding example, to refer to the Employees macro in the Buttons macro group, you would type Buttons.Employees.

ShowConditional actions

In some cases, you may want to carry out an action or series of actions in a macro only if a particular condition (condition: Part of the criteria that a field must meet for searching or filtering. Some conditions must be used with a value; for example, the field Author with the condition equals with the value Jane.) is true. For example, if you're using a macro to validate data in a form, you might want to display one message in response to one set of values entered in a record and another message in response to a different set of values. In cases like these, you can use conditions to control the flow of the macro.

A condition is a logical expression (expression: Any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. Expressions can perform calculations, manipulate characters, or test data.) that evaluates to True/False or Yes/No. The macro follows different paths depending on whether the condition is true or false.

When you run the macro, Microsoft Access evaluates the first conditional expression (conditional expression: An expression evaluated and compared to a value; for example, If...Then and Select Case statements. If the condition is met, one or more operations are performed. If it isn't met, the operation is skipped.). If the condition is true, Microsoft Access carries out the action in that row and any of the immediately following actions that are preceded by an ellipsis (...) in the Condition column.

Microsoft Access then runs any additional actions in the macro that have a blank Condition column until it reaches another expression, a macro name, or the end of the macro.

If the condition is false, Microsoft Access ignores the action and any immediately following actions that are preceded by an ellipsis in the Condition column and moves to the next action row (action row: A row in the upper part of the Macro object tab in which you enter macro names, actions, arguments, and comments associated with a particular macro or macro group.) that contains another condition or a blank Condition column.

The following macro runs the MsgBox and the StopMacro actions only when the expression in the Condition column is true (when there is a Null (Null: A value you can enter in a field or use in expressions or queries to indicate missing or unknown data. In Visual Basic, the Null keyword indicates a Null value. Some fields, such as primary key fields, can't contain a Null value.) value in the SupplierID field).

Example of a conditional action in a macro

ShowAction arguments

Action arguments are additional information required by some macro actions — for example, the object affected by the action or special conditions under which the action is carried out. After you add an action to a macro, you set the arguments for the action in the lower portion of the Macro window (Macro window: The window in which you create and modify macros.). These arguments give Microsoft Access additional information on how to carry out the action.

Tips for setting action arguments:

ShowModules: Automate routine tasks and create business solutions

A module is essentially a collection of declarations, statements, and procedures stored together as one named unit to organize your Microsoft Visual Basic (Microsoft Visual Basic: A high-level, visual-programming version of Basic. Visual Basic was developed by Microsoft for building Windows-based applications.) code. Microsoft Access has two types of modules: standard modules (standard module: A Visual Basic for Applications (VBA) module in which you can place Sub and Function procedures that you want to be available to other procedures throughout your database.) and class modules (class module: A module that can contain the definition for a new object. Each instance of a class creates a new object. Procedures defined in the module become properties and methods of the object. Class modules can exist alone or with forms and reports.).

ShowWhat is a module?

A module is a collection of Visual Basic declarations and procedures that are stored together as a unit.

Parts of a module

Callout 1 Object box

Callout 2 Procedure box

Callout 3 Declarations

Callout 4 Procedures

Callout 5 Procedure View button

Callout 6 Full Module View button

ShowClass modules

Form (form module: A module that includes Visual Basic for Applications (VBA) code for all event procedures triggered by events occurring on a specific form or its controls.) and report modules (report module: A module that includes Visual Basic for Applications (VBA) code for all event procedures triggered by events occurring on a specific report or its controls.) are class modules that are associated with a particular form or report. Form and report modules often contain event procedures (event procedure: A procedure that is automatically executed in response to an event initiated by the user or program code, or that is triggered by the system.) that run in response to an event on the form or report. You can use event procedures to control the behavior of your forms and reports, and their response to user actions, such as clicking a command button.

When you create the first event procedure for a form or report, Microsoft Access automatically creates an associated form or report module.

Choose an object and an event to select a procedure

Callout 1 To view or add a form procedure, choose one of the form's objects from the Object box ...

Callout 2... and then choose an event from the Procedure box. Names of events that already have procedures are bold.

Procedures in your form and report modules can call procedures you have added to standard modules.

In Access 97 or later, class modules can also exist independent of a form or report, and this type of class module is listed in the Database window (Database window: In Access 2003 and earlier, the window that appears when a database or project is opened. It displays shortcuts for creating new database objects and opening existing objects. In later versions, it is replaced by the Navigation Pane.). You can use a class module to create a definition for a custom object. In Access 95, class modules exist in association only with a form or report.

ShowStandard modules

Standard modules contain general procedures that aren't associated with any other object, and frequently used procedures that can be run from anywhere within your database. The major difference between a standard module and a class module that is not associated with a particular object is one of scope and lifetime. The value of any variables or constants that are declared or exist in a class module without an associated object are available for use only while that code is running and only from that object.

Standard modules contain procedures that are available from anywhere in the database

Callout 1 Function name and arguments

Callout 2 Visual Basic declarations and code statements

Standard modules are listed in Modules under Objects in the Database window (Database window: In Access 2003 and earlier, the window that appears when a database or project is opened. It displays shortcuts for creating new database objects and opening existing objects. In later versions, it is replaced by the Navigation Pane.). Form, report, and standard modules are also listed in the Object Browser (Object Browser: A dialog box that displays information about objects, properties, methods, and constants in the current project and in referenced object libraries, and that you use to search for an element, get help on it, or paste it into a module.).

ShowRelationships in a database

After you've set up different tables for each subject in your Microsoft Access database (Microsoft Access database: A collection of data and objects (such as tables, queries, or forms) that is related to a particular topic or purpose.), you need a way of telling Microsoft Access how to bring that information back together again. The first step in this process is to define relationships (relationship: An association that is established between common fields (columns) in two tables. A relationship can be one-to-one, one-to-many, or many-to-many.) between your tables. After you've done that, you can create queries, forms, and reports to display information from several tables at once. For example, this form includes information from four tables:

Orders form displaying related information from five tables at once

Callout 1 The Customers table

Callout 2 The Orders table

Callout 3 The Products table

Callout 4 The Order Details table

ShowHow relationships work

In the previous example, the fields in four tables must be coordinated so that they show information about the same order. This coordination is accomplished with relationships between tables. A relationship works by matching data in key fields — usually a field with the same name in both tables. In most cases, these matching fields are the primary key (primary key: One or more fields (columns) whose values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.) from one table, which provides a unique identifier for each record, and a foreign key (foreign key: One or more table fields (columns) that refer to the primary key field or fields in another table. A foreign key indicates how the tables are related.) in the other table. For example, employees can be associated with orders they're responsible for by creating a relationship between the EmployeeID fields.

EmployeeID used as primary key in Employees table and foreign key in Orders table.

Callout 1 EmployeeID appears in both tables—-as a primary key ...

Callout 2... and as a foreign key.

ShowA one-to-many relationship

A one-to-many relationship is the most common type of relationship. In a one-to-many relationship, a record in Table A can have many matching records in Table B, but a record in Table B has only one matching record in Table A.

A record in Suppliers table with more than one related record in Products table

Callout 1 One supplier ...

Callout 2... can supply more than one product ...

Callout 3... but each product has only one supplier.

ShowA many-to-many relationship

In a many-to-many relationship, a record in Table A can have many matching records in Table B, and a record in Table B can have many matching records in Table A. This type of relationship is only possible by defining a third table (called a junction table) whose primary key (primary key: One or more fields (columns) whose values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.) consists of two fields — the foreign keys (foreign key: One or more table fields (columns) that refer to the primary key field or fields in another table. A foreign key indicates how the tables are related.) from both Tables A and B. A many-to-many relationship is really two one-to-many relationships with a third table. For example, the Orders table and the Products table have a many-to-many relationship that's defined by creating two one-to-many relationships to the Order Details table. One order can have many products, and each product can appear on many orders.

Foreign keys in Order Details table and matching primary keys in Orders table and Products table

Callout 1 Primary key from the Orders table

Callout 2 Primary key from the Products table

Callout 3 One order can have many products ...

Callout 4... and each product can appear on many orders.

ShowA one-to-one relationship

In a one-to-one relationship, each record in Table A can have only one matching record in Table B, and each record in Table B can have only one matching record in Table A. This type of relationship is not common, because most information related in this way would be in one table. You might use a one-to-one relationship to divide a table with many fields, to isolate part of a table for security reasons, or to store information that applies only to a subset of the main table. For example, you might want to create a table to track employees participating in a fundraising soccer game. Each soccer player in the Soccer Players table has one matching record in the Employees table.

Every record in the Employees table can have only one matching record in the Soccer Players table.

Callout 1 Each soccer player has one matching record in the Employees table.

Callout 2 This set of values is a subset of the EmployeeID field and the Employees table.

ShowAbout defining relationships

The kind of relationship that Microsoft Access creates depends on how the related fields are defined:

You can also create a relationship between a table and itself. This is useful in situations where you need to perform a Lookup within the same table. In the Employees table, for example, you can define a relationship between the EmployeeID and ReportsTo fields, so that the ReportsTo field can display employee data from a matching EmployeeID.

 Note   If you drag a field that isn't a primary key and doesn't have a unique index to another field that isn't a primary key and doesn't have a unique index, an indeterminate relationship is created. In queries containing tables with an indeterminate relationship, Microsoft Access displays a default join (join: An association between a field in one table or query and a field of the same data type in another table or query. Joins tell the program how data is related. Records that don't match may be included or excluded, depending on the type of join.) line between the tables, but referential integrity (referential integrity: Rules that you follow to preserve the defined relationships between tables when you add, update, or delete records.) won't be enforced, and there's no guarantee that records are unique in either table.

ShowReferential integrity

Referential integrity is a system of rules that Microsoft Access uses to ensure that relationships between records in related tables are valid, and that you don't accidentally delete or change related data. You can set referential integrity when all of the following conditions are met:

The following rules apply when you use referential integrity:

ShowCascading updates and deletes

For relationships in which referential integrity (referential integrity: Rules that you follow to preserve the defined relationships between tables when you add, update, or delete records.) is enforced, you can specify whether you want Microsoft Access to automatically cascade update (cascading update: For relationships that enforce referential integrity between tables, the updating of all related records in the related table or tables when a record in the primary table is changed.) and cascade delete (cascading delete: For relationships that enforce referential integrity between tables, the deletion of all related records in the related table or tables when a record in the primary table is deleted.) related records. If you set these options, delete and update operations that would normally be prevented by referential integrity rules are allowed. When you delete records or change primary key (primary key: One or more fields (columns) whose values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.) values in a primary table (primary table: The "one" side of two related tables in a one-to-many relationship. A primary table should have a primary key and each record should be unique.), Microsoft Access makes necessary changes to related tables to preserve referential integrity.

If you select the Cascade Update Related Fields check box when defining a relationship, any time you change the primary key of a record in the primary table, Microsoft Access automatically updates the primary key to the new value in all related records. For example, if you change a customer's ID in the Customers table, the CustomerID field in the Orders table is automatically updated for every one of that customer's orders so that the relationship isn't broken. Microsoft Access cascades updates without displaying any message.

 Note   If the primary key in the primary table is an AutoNumber (AutoNumber data type: In a Microsoft Access database, a field data type that automatically stores a unique number for each record as it's added to a table. Three kinds of numbers can be generated: sequential, random, and Replication ID.) field, setting the Cascade Update Related Fields check box will have no effect, because you can't change the value in an AutoNumber field.

If you select the Cascade Delete Related Records check box when defining a relationship, any time you delete records in the primary table, Microsoft Access automatically deletes related records in the related table. For example, if you delete a customer record from the Customers table, all the customer's orders are automatically deleted from the Orders table (this includes records in the Order Details table related to the Orders records). When you delete records from a form or datasheet with the Cascade Delete Related Records check box selected, Microsoft Access warns you that related records may also be deleted. However, when you delete records using a delete query (delete query: A query (SQL statement) that removes rows matching the criteria that you specify from one or more tables.), Microsoft Access automatically deletes the records in related tables without displaying a warning.

 
 
Applies to:
Access 2003