In This Chapter
How Microsoft Access Is Structured
How You Can Customize Microsoft Access
How Microsoft Access Resolves Conflicts

This chapter describes the structure of Microsoft Access 97. It explains how the various components of Access work together, and how the application handles changes that users make to those components. An understanding of the architecture of Access can help you take advantage of its features and may help you troubleshoot problems.

See Also

• For information about installing Microsoft Access or other Microsoft Office applications, see Chapter 4, "Installing Microsoft Office."

• For information about customizing Access to meet the specific needs of your workgroup, see Chapter 7, "Customizing and Optimizing Microsoft Office."

Microsoft Access documents are called databases. An Access database is a collection of database objects: tables, queries, forms, reports, macros, and modules. You can design new objects or open existing ones to work with your database. Unlike many database programs, an Access database can contain all of the objects that make up a database application in a single file with the .mdb file name extension. For this reason, an Access database file is sometimes called a database container.

You can store general-purpose Microsoft Visual Basic for Applications procedures in a library database. An Access database application can call procedures from a library database after establishing a link to the database called a reference. You can create or purchase tools called add-ins to add custom features to Access. Access user-level security account information is stored in a database called a workgroup information file.

Tables

In Microsoft Access, you store data in tables. Tables organize data into columns and rows. Each row in a table is called a record. Each column in a record is called a field. For example, each record in a Customers table contains information about one customer. Each customer's record contains fields named for each piece of information you want to store about the customer, such as LastName, FirstName, Address, City, State/Province, PostalCode, and Phone. Users can enter and modify data in tables directly or through queries and data­entry forms, which are described later in this section.

Storing Related Data in Separate Tables

You can create a separate table for each topic of data, such as customers, employees, or products. Using a separate table for each topic means that you store that data only once, which makes your database more efficient and reduces data­entry errors. For example, if you store both customer and order information in one table, whenever a customer places an additional order, you have to reenter information about that customer. Not only is this inefficient, but errors can occur when information is reentered. If you keep separate tables for customers and orders, each customer has only one record — in the Customers table. If you need to update or correct information about a customer, you need to change the data in only one record. This method eliminates redundant entries and reduces data­entry errors.

The process of eliminating redundancy by dividing data into separate related tables is called normalization. Access provides a wizard called the Table Analyzer Wizard to help you normalize your database. For information about running the Table Analyzer Wizard, see "Optimizing Microsoft Access" in Chapter 7, "Customizing and Optimizing Microsoft Office."

Defining Relationships

To bring the data from multiple tables together for a query, form, or report, you define relationships between the tables based on a common piece of information stored in both tables. For example, a Customers table typically contains a CustomerID field whose value uniquely identifies each customer. If the Orders table also has a CustomerID field, you can define a relationship between the two tables, so that when you enter orders, you can enter a customer's ID number to relate information from the Customers table to that order. Instead of actually typing the CustomerID, you can use a lookup list box, as shown in the Orders form from the Northwind sample database.

Although the BillTo list displays only the customer's name, picking a customer from the list stores that customer's CustomerID number in the Orders table. The customer's address information that is displayed below the customer's name is not stored in the Orders table. This information is looked up from the Customers table and displayed based on the relationship defined between the CustomerID field in the Customers table and the CustomerID field in the Orders table.

If you need to enter an order for a customer that is not available in the BillTo list, first you open the Customers form and enter a new record for that customer. Then Access automatically makes the new customer available in the BillTo lookup list box on the Orders form.

Queries

You use queries to view, change, and analyze data in different ways. You can also use them as the source of records for forms and reports. You can create a query either by using a wizard or from scratch in query Design view. In Design view, you specify the data you want to work with by adding the tables or queries that contain the data, and then specifying criteria and other information in the query design grid. In Microsoft Access, you can create many types of queries: select queries, parameter queries, crosstab queries, action queries, and SQL queries. This section describes the types of queries you can use in Access.

Select Queries

A select query is the most common type of query. A select query retrieves data from one or more tables based on criteria you specify at design time such as category, range, and Boolean (logical yes/no or true/false) operators. Access displays the results on a datasheet where you can update the records (with some restrictions). You can also use a select query to group records and to calculate sums, counts, averages, and other types of totals.

Parameter Queries

When you run a parameter query, it displays a dialog box prompting you for information, such as criteria 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.

Crosstab Queries

A crosstab query displays summarized values (sums, counts, and averages) from one field in a table and groups them by one set of facts listed down the left side of the datasheet and another set of facts listed across the top of the datasheet. You can create a crosstab query either by using the Crosstab Query Wizard or from scratch. You can display crosstab data without creating a crosstab query in your database by using the PivotTable Wizard instead. With a PivotTable, you can change row and column headings on demand to analyze data in different ways.

Action Queries

An action query makes changes to many records in one operation. There are four types of action queries: delete, update, append, and make­table.

Delete Queries

A delete query deletes a group of records from one or more tables. For example, you can 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 new customers and a database that contains a table of information about those customers. To avoid typing all this information in, you can append it to the Customers table. Append queries are also helpful for:

• Appending fields based on criteria.

  For example, you may want to append only the names and addresses of customers with outstanding orders.

• Appending records when some of the fields in one table do not exist in the other table.

  For example, in the Northwind sample database, the Customers table has 11 fields. Suppose that you want to append records from another table that has fields that match 9 of the 11 fields in the Customers table. An append query appends the data in the matching fields and ignores the others.

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 Access databases.

  For example, you may want to create a table that contains several fields from the Employees table, and then export that table to a database used by the personnel department.

• Creating reports that display data from a specified point in time.

  For example, suppose you want to print a report on 15­May­97 that displays the first quarter's sales totals based on the data that was in the underlying tables as of 9:00 A.M. on 1­Apr­97. A report based on a query or an SQL statement extracts the most up­to­date data from the tables (the data as of 15­May­97), rather than the records as of a specific date and time. To preserve the data exactly as it was at 9:00 A.M. on 1­Apr­97, create and run a make­table query at that point in time to retrieve the records you need, and store them in a new table. Then use this table, rather than a query, as the basis for the reports.

• Making a backup copy of a table.

• Creating a history table that contains old records.

  For example, you can create a table that stores all the old orders before deleting them from the current Orders table.

• Improving performance of forms and reports based on multiple­table queries or SQL statements.

  For example, suppose you want to print multiple reports that are based on a five­table query that includes totals. You may be able to speed things up by first creating a make­table query that retrieves the records you need and stores them in one table. Then you can base the reports on this table or specify the table in an SQL statement as the record source for a form or report, so that you do not have to rerun the query for each report. However, the data in the table is frozen at the time you run the make­table query.

SQL Queries

An SQL query is a query you create by using an SQL statement. Examples of SQL­specific queries are union queries, pass­through queries, data­definition queries, and subqueries.

Union Queries

A union query combines fields from one or more tables or queries into one field in the query's results. For example, if you have six vendors who send new inventory lists each month, you can combine these lists into one result set by using a union query, and then create a make­table query based on the union query to make a new table.

Pass­through Queries

A pass­through query sends commands directly to Open Database Connectivity (ODBC) data sources, such as Microsoft SQL Server databases, by using commands that are accepted by the server. For example, you can use a pass­through query to retrieve records or change data. You can also use pass­through queries to run stored procedures (SQL programs that are stored on the server) and to perform operations that are only available by using server­specific commands.

Data­Definition Queries

A data­definition query uses SQL Data Definition Language (DDL) statements to create, delete, or alter tables and to create indexes. DDL statements can be used only with Microsoft Jet format (MDB) databases. To work with SQL Server tables and indexes, use pass­through queries instead. To work with other non­Microsoft Jet format tables supported by Access, use the Data Access Objects (DAO) Create methods in Microsoft Visual Basic for Applications.

Subqueries

A subquery consists of an SQL SELECT statement inside another select query or action query. You can enter these statements in the Field row of the query design grid to define a new field, or in the Criteria row to define criteria for a field. You can use subqueries to:

• Test for the existence of some result from the subquery (by using the EXISTS or NOT EXISTS reserved words).

• Find any values in the main query that are equal to, greater than, or less than values returned by the subquery (by using the ANY, IN, or ALL reserved words).

• Create subqueries within subqueries (nested subqueries).

Forms

Forms give users a way of entering data into databases, displaying data on the screen, and printing data. For example, database developers can create forms that act as switchboards, using buttons or hyperlinks to navigate to the various objects in a database. Developers can also create forms that are custom dialog boxes that prompt users for the information required to complete operations. Forms can contain text, graphics, data, color, and ActiveX controls (formerly called OLE controls or custom controls). For more information about ActiveX controls, see "Office Support for ActiveX" in Chapter 34, "Microsoft Office Architecture."

To display data on a form from an underlying record source, such as a table or query, set the RecordSource property to the name of the table or query. You create a link between a form and its record source by using graphical objects called controls. For more information about controls, see "Controls" later in this chapter. The default appearance of a form is controlled by a form template. For more information, see "Form and Report Templates" later in this chapter.

Reports

A report is an effective way to present data from your Microsoft Access database in a printed format. Because you have control over the size and appearance of everything in a report, you can display the information the way you want to see it. The data in a report comes from an underlying table, query, or SQL statement. Other information in the report is stored in the report's design. You create a link between a report and its record source by using graphical objects called controls. For more information about controls, see "Controls" later in this chapter. The default appearance of a report is controlled by a report template. For more information, see "Form and Report Templates" later in this chapter.

Macros

A macro is a set of one or more actions that perform a particular operation, such as opening a form or printing a report. You can use macros to automate common tasks. You can run macros directly from the Macros tab of the Database window, from another macro or event procedure, or in response to an event that occurs on a form, report, or control.

Modules

Visual Basic code provides another way to automate tasks in a Microsoft Access database application. Visual Basic code is stored in modules.

A database can contain two types of modules:

• Standard modules

  Use standard modules to store code you may want to run from anywhere in the application. You can call public procedures in standard modules from expressions, macros, event procedures, or procedures in other standard modules.

• Class modules

  Use class modules to create your own custom objects. The Sub and Function procedures that you define in a class module become methods of the custom object. The properties you define with the Property Get, Property Let, and Property Set statements become properties of the custom object.

Each form and report in your database can contain an associated form module or report module. Form and report modules are a type of class module, but you cannot save them separately from the form or report that they belong to.

Use a form or report module to contain procedures associated with event properties of the form or report. For example, code associated with a form's OnOpen property runs when the form is opened. Procedures­associated event properties are called event procedures. A form or report module can also contain procedures that are not triggered by events.

A form or report module is part of the form or report's design. Thus, if you copy a form or report to another database, its module is copied with it; if you delete a form or report, its module is deleted as well. Access creates the form or report module automatically when you first add Visual Basic code to the form or report.

Controls

In addition to the database objects discussed in the previous sections, the forms and reports in a Microsoft Access database contain an additional set of objects called controls. All the information on a form or report is contained in controls. Controls are objects on a form or report that display data, perform actions, or decorate the form or report. For example, you can use a text box control on a form or report to display data, a command button control on a form to open another form or report, or a line or rectangle control to separate and group other controls to make them more readable.

Access includes built­in controls and ActiveX controls, which are all accessible through the toolbox in form Design view or report Design view. Access includes the following types of built­in controls: text box, label, option group, option button, check box, toggle button, combo box, list box, command button, image control, bound object frame, unbound object frame, subform/subreport, page break, line, rectangle, and tab control. For information about these controls, see Microsoft online Help.

Controls can be bound, unbound, or calculated. A bound control is tied to a field in an underlying table or query. You use bound controls to display, enter, and update values from fields in your database. A calculated control uses an expression as its source of data. An expression can use data from a field in an underlying table or query of a form or report, or from another control on the form or report. An unbound control does not have a data source. You can use unbound controls to display information, lines, rectangles, and pictures.

ActiveX Controls You can use ActiveX controls to add custom functionality to forms. Microsoft Access 97 includes two ActiveX controls: the Calendar control, and the WebBrowser control. The Calendar control makes it easy to display and update a monthly calendar on a form. You can use the WebBrowser control to display Web pages and other documents in an Access form. Before you can add the WebBrowser control to a form, you must have Microsoft Internet Explorer version 3.0 or later installed. For more information about the WebBrowser control, see Chapter 25, "Web Support in Microsoft Office Applications." Additional ActiveX controls are available in Microsoft Office 97, Developer Edition, and from independent software vendors.

Form and Report Templates

When users create a form or report without using a wizard, Microsoft Access uses a template to define the default characteristics of the form or report. The template determines which sections a form or report has and defines each section's dimensions. The template also contains all the default property settings for the form.

The default templates for forms and reports are named Normal. However, you can use any existing form or report as a template. You specify which template you want to use on the Forms/Reports tab in the Options dialog box (Tools menu). If you specify a template other than Normal, this setting is stored in the Windows registry in the HKEY_CURRENT_USER\Software\Microsoft\Office\8.0\Access\Settings key and applies to all new forms or reports that the current user creates. You can import templates (that is, forms or reports to use as templates) to use in your Access database, and you can export them to use them in other databases.

Form and report templates define:

• Whether to include a form or report header and footer.

• Whether to include a page header and footer.

• The dimensions (height and width) of the sections.

• Which default properties to use for controls. However, templates do not create controls on new forms or reports.

To create and specify a form or report template

1. Create a form or report that includes the formatting and controls you want to use as the basis of your template.

2. On the File menu, click Save.

3. In the Save As dialog box, type a name for your form or report, and click OK.

4. On the Tools menu, click Options, and then click the Forms/Reports tab.

5. To specify a form template, type the name of the form in the Form Template box.

   – or –

   To specify a report template, enter the name of the report in the Report Template box.

Note   If you want to use the template in another database, select the form or report in the Database window, and then click Save As/Export (File menu) to export the form or report. Then open the other database and repeat Step 4.

Library Databases

A library database is a collection of procedures and database objects that you can call from any application. You can use libraries to store routines that you use often, so that you do not have to write the same routine for each application you create. You can also use libraries to distribute new features to users.

To use a library database from your Microsoft Access application, you must first establish a link, called a reference, from your application to the library. You can establish a reference in the References dialog box (Tools menu). Library database files have an .mda file name extension.

For more information about creating and using library databases, see Chapter 12, "Using Library Databases and Dynamic­Link Libraries," in Building Applications with Microsoft Access 97.

Add­ins

Add­ins are tools written within the Microsoft Access environment that extend the functionality of the basic product. These tools make difficult tasks easier, automate repetitive operations, and add new features. Add­ins can increase productivity by focusing on a single task or function. You can create them to use yourself, to use within your organization, or to distribute along with a database application. You can also purchase add­ins created by independent software developers.

Access has three kinds of add­ins: wizards, builders, and menu add­ins. Each type has its own advantages and uses. A wizard helps a user create a new table, query, form, report, or control. A builder helps a user set properties in Design view. A menu add­in is not context­specific and can be used anywhere in Access. Wizards and builders are available to the user through the same interface that supports the Access wizards and builders. By default, menu add­ins are available through the Add­Ins submenu (Tools menu). The interface through which an add­in is available is determined by how the add­in is registered when it is installed.

Add­in files are saved like library database files, with the .mda file name extension. Add­in files can also be saved as MDE files. An MDE file has Visual Basic source code removed, and the design of any forms, reports, and modules is secured. MDE files have an .mde file name extension. For more information about MDE files, see Chapter 29, "Workgroup Features in Microsoft Access." Users can add or remove add­ins from Access by pointing to Add­Ins (Tools menu) and then clicking Add­In Manager.

For information about creating add­ins in Access, see Chapter 17, "Creating Wizards, Builders, and Menu Add­ins," in Building Applications with Microsoft Access 97.

Wizards

A wizard handles complex operations. It usually consists of a series of dialog boxes that provide a step­by­step interface to guide the user through the process of creating an object. Wizards usually use forms, graphics, and helpful text to shield the user from the technical intricacies of an operation. Access Form and Report Wizards are examples of this type of add­in. These add­ins guide you through creating forms and reports.

Microsoft Access provides direct support for several types of wizards. As a result, the wizards you create are available through the same user interface as the Access wizards. For example, if you create or install a wizard to design a specific type of form, it appears in the same list as the Access Form Wizards. The types of wizards that Access supports include:

• Control Wizards

• Form Wizards

• Query Wizards

• Report Wizards

• Table Wizards

Builders

A builder is a simpler tool than is a wizard. A builder usually consists of a single dialog box or form that guides the user through the process of setting a property, such as constructing an expression. The Access Expression Builder is an example of this type of add­in.

As with wizards, Access provides direct support for many types of builders. You can create or install builders for any property, even those that Access does not provide builders for. Also, you can install more than one builder for the same property. Additional builders are available to users through the standard builder interface. For example, if one or more builders are already installed for a property, your builder is available from a list of builders. The types of builders that Access supports include:

• Property Builders

• Expression Builders

Menu Add­ins

A menu add­in is a general­purpose tool that accomplishes a task that does not fit into the wizard or builder categories. A menu add­in typically operates on multiple objects or on the Access application itself. The Add­In Manager is an example of a menu add­in.

Menu add­ins are supported by Access through the Add­Ins submenu (Tools menu). When you install a menu add­in, the command to run it is added to the Add­Ins submenu. It is important to understand that menu add­ins are available to the user whenever the Tools menu is available. After a menu add­in is installed, you can also add a button or command to run the menu add­in from any toolbar by using the Customize dialog box (View menu, Toolbars submenu). This means that menu add­ins are not context­sensitive like wizards and builders. A wizard is designed to aid the user within a specific context, such as form or query design. A menu add­in is designed to perform a general function that may not fit within the context of the user's current operation.

Several add­ins are included with Access. Not all of these add-ins are installed when you choose the Typical installation during Setup. To install the add-ins you need, rerun Setup and click Add/Remove; then select the Wizards or Advanced Wizards option, as described in the following tables. The functionality of an add-in is available to all databases opened with a particular installation of Access.

The following table describes the add­ins that are always installed, regardless of the installation option you choose during Setup.

Add­in	File name	Description
Color Builder	An add­in in previous versions; now part of Msaccess.exe	Displays a palette for setting the color values for controls and sections in form and report Design view. Also used to create color property values for customized colors.
Expression Builder	Utility.mda	Creates expressions for macros, queries, and property sheets.
Query Builder	An add­in in previous versions; now part of Msaccess.exe	Creates the correct syntax for a query.
Subform/Subreport Field Linker	An add­in in previous versions; now part of Msaccess.exe	Links fields in a main form and a subform, or in a main report and a subreport.

The following table describes the add­ins included in a Typical installation (or when you select the Wizards option under the Microsoft Access option) during Setup.

Add­in	File name	Description
AutoForm	Wzmain80.mde	Creates a simple form that displays all fields and records in the selected table or query. Each field appears on a separate line with a label to its left.
AutoReport	Wzmain80.mde	Creates a simple report that displays all fields and records in the selected table or query.
Combo Box Wizard	Wzmain80.mde	Creates a combo box control on a form.
Command Button Wizard	Wzmain80.mde	Creates a command button control on a form.
Crosstab Query Wizard	Wzmain80.mde	Creates a query that summarizes data in a compact, spreadsheet­like format.
Database Wizard	Wzmain80.mde	Creates an entirely new database for a variety of uses based on 1 of 22 models.
Export Text Wizard	Wzlib80.mde	Exports data to a text file.
Field Builder	Wzmain80.mde	Sets the properties of a new field by selecting from a list of sample field definitions.
Form Wizard	Wzmain80.mde	Creates a new form.
Import HTML Wizard	Wzlib80.mde	Imports HTML tables and lists from the Internet or an intranet into a Microsoft Access table.
Import Spreadsheet Wizard	Wzlib80.mde	Imports a Microsoft Excel or other spreadsheet into an Access table.
Import Text Wizard	Wzlib80.mde	Imports a text file into an Access table.
Label Wizard	Wzmain80.mde	Creates mailing labels in standard and custom sizes.
Link HTML Wizard	Wzlib80.mde	Links an HTML table or list on the Internet or an intranet to an Access table.
Link Spreadsheet Wizard	Wzlib80.mde	Links spreadsheet data to an Access table.
Link Text Wizard	Wzlib80.mde	Links a text file to an Access table.
List Box Wizard	Wzmain80.mde	Creates a list box control on a form.
Lookup Wizard	Wzmain80.mde	Creates a lookup column in a table, which displays a list of values the user can choose from.
Microsoft Word Mail Merge Wizard	Wzmain80.mde	Manages mail merge operations by using letters stored in Microsoft Word and addresses stored in Access.
Picture Builder	Wzmain80.mde	Creates bitmap images for forms and reports.
PivotTable Wizard	Wzmain80.mde	Places a Microsoft Excel PivotTable on an Access form.
Publish to the Web Wizard	Wzmain80.mde	Creates static and/or dynamic HTML documents from your Access application to be placed on the Internet or an intranet.
Report Wizard	Wzmain80.mde	Creates a report based on a table or query.
Simple Query Wizard	Wzmain80.mde	Creates a select query from the fields you pick.
Switchboard Manager	Wzmain80.mde	Creates and manages switchboard forms for applications.
Table Wizard	Wzmain80.mde	Creates a new table by selecting from a list of sample table and field definitions.
Web Publishing Wizard	Wpwiz.exe (Installed by running WebPost.exe in the ValuPack\WebPost folder on the Office CD)	Posts HTML documents to an Internet or intranet server. Can be used in conjunction with the Publish to the Web Wizard.

The following table describes the add­ins included in a Custom installation when you select the Advanced Wizards option under the Microsoft Access option during Setup.

Add­in	File name	Description
Add­In Manager	Wztool80.mde	Installs and uninstalls wizards, builders, and add­ins.
Chart Wizard	Wztool80.mde	Adds a chart to a form or report based on the data in a table or query.
Conflict Resolver	Wzcnf80.mde	Resolves conflicts between replicated databases during synchronization.
Database Splitter Wizard	Wztool80.mde	Splits databases into data and interface portions, so that one or more users can have local copies of the interface connected to the data on a server.
Documenter	Wztool80.mde	Prints a report that documents all of the objects in a database.
Find Duplicates Query Wizard	Wztool80.mde	Creates a query that finds records with duplicate field values in a single table or query.
Find Unmatched Query Wizard	Wztool80.mde	Creates a query that finds records in one table that have no related records in another table.
Input Mask Wizard	Wztool80.mde	Creates an input mask for a field you choose in a table.
Linked Table Manager	Wztool80.mde	Manages links to tables in other databases.
Macro to Module Converter	Wztool80.mde	Converts macros to Visual Basic event procedures or modules that perform equivalent actions using Visual Basic code.
ODBC Connection String Builder	Wztool80.mde	Creates the correct syntax for a connection to an ODBC database.
Option Group Wizard	Wztool80.mde	Creates a group of option buttons on a form.
Performance Analyzer	Wztool80.mde	Analyzes the efficiency of a database and produces a list of suggestions for improving its performance.
Subform/Subreport Wizard	Wztool80.mde	Creates a new subform or subreport on a form or report.
Table Analyzer Wizard	Wztool80.mde	Takes a table with much duplicate data and splits it into related tables for more efficient storage.
User­Level Security Wizard	Wztool80.mde	Creates a new, encrypted database, with regulated user access, from an existing database.

Workgroup Information Files

A workgroup information file is a file that Microsoft Access reads when starting up; it contains information about the users in a workgroup. If user­level security is being used, this information includes users' account names, their passwords, and the groups that they belong to.

Even when user­level security is not being explicitly used, Access requires the workgroup information file in order to start up. This is because user­level security is always activated before Access starts running to eliminate the possibility of a security back door. Before user­level security is explicitly established, all users are automatically logged on by using the default Admin user account. Once user­level security is established, a user must log on by using a particular account. Each user account can have a password defined that is required when logging on with the account. The default name for the workgroup information file for Microsoft Access 97 is System.mdw.

Note   In previous versions of Microsoft Access, user-preference settings specified in the Options dialog box (Tools menu) are stored in the workgroup information file. In Microsoft Access 97, these settings are stored in the Windows registry in the HKEY_CURRENT_USERSoftware\Microsoft\Office\8.0\Access\Settings key.

For users to share data in a secured workgroup, they must use the Workgroup Administrator to specify a workgroup information file that defines the user and group accounts for the workgroup before they start Access. Alternatively, users can specify a workgroup information file by using the /wrkgrp command­line option when they start Microsoft Access.

The Workgroup Administrator is a separate application named Wrkgadm.exe that is installed in the Windows\System folder (for Windows 95) or the Windows\System32 folder (for Windows NT Workstation 3.51 and 4.0). Running the Workgroup Administrator specifies the workgroup information file in the Windows registry as the setting for the SystemDB value in the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\8.0\Access\Jet\3.5 \Engines key. Using the /wrkgrp command­line option to specify a workgroup information file overrides the value stored in the registry during that program session, but does not change it.

The workgroup information file that a user specifies is the file that Access uses every time it starts up, until the user specifies some other file. If a user does not belong to a secure workgroup and has not specified a workgroup information file, Access uses the default System.mdw file created when Access was installed. The default System.mdw file is located in the Windows\System folder (Windows 95) or the Windows\System32 folder (Windows NT Workstation 3.51 and 4.0).

After users have specified a workgroup information file, they should back up their System.mdw file. If the file somehow becomes corrupted, the user must restore the backup copy, get a new copy from a workgroup administrator (if that is who originally provided the file), or re­create it. Users must have a valid workgroup information file to run Access.

For more information about the Access user­level security model, see "Security Features in Microsoft Access" in Chapter 29, "Workgroup Features in Microsoft Access."

Microsoft Access and the Microsoft Jet Database Engine

Together, Microsoft Access and the Microsoft Jet database engine form a complete database management system (DBMS). Microsoft Access is responsible for the user interface and all the ways that users view, edit, and manipulate data through forms, queries, reports, and so forth. Microsoft Jet — the data manager component of the DBMS — retrieves data from and stores data in user and system databases.

Microsoft Jet is a relational database engine that handles all database processing for Access. Microsoft Jet can also provide data to ODBC client applications.

Microsoft Jet is made up of a set of dynamic­link libraries (DLLs):

• Microsoft Jet DLL (Msjet35.dll)

  Msjet35.dll is the main program that evaluates and carries out requests for data. If the request is for native data — data stored in the Microsoft Access Database (MDB) format — Msjet35.dll also handles the reading and writing of the data. If the request involves non­native data, then Msjet35.dll makes calls to either the ODBC Driver Manager DLL (Odbc32.dll) or one of the external installable ISAM DLLs, as explained later in this section.

• Data Access Objects DLL (Dao350.dll)

  Dao350.dll is the Microsoft Jet component that provides a developer interface to Microsoft Jet. Data Access Objects (DAO) includes a rich, high­level set of objects that insulates developers from the physical details of reading and writing records.

• Installable ISAM DLLs

  Microsoft Jet provides access to several external Indexed Sequential Access Method (ISAM) format files by using a series of installable DLL files referred to as installable ISAMs. Microsoft Jet supports the external ISAM formats shown in the following table.

  ISAM format	Supported by DLL
  Xbase (dBASE and FoxPro)	Msxbse35.dll
  Paradox	Mspdox35.dll
  Lotus	Msltus35.dll
  Microsoft Excel	Msexcl35.dll
  Microsoft Exchange/Outlook	Msexch35.dll
  Text and HTML	Mstext35.dll

  These DLLs handle the reading and writing of data stored in dBASE, FoxPro, Paradox, Lotus, Microsoft Excel, Microsoft Exchange, Microsoft Outlook, fixed­width text, delimited text, and HTML files.

Linked Tables

In general, you store data locally in tables in the database. You can also create links to tables in other Access databases, to data in other file formats (such as Microsoft Excel, dBASE, and Paradox), and to ODBC data sources, such as Microsoft SQL Server. These links are stored in the database container and act like native tables. You can use a linked table just as you would use any other table in your Access database. For example, you can create forms, reports, and queries that use the external table. Any changes to the table are reflected in the linked database. An icon that represents the linked table appears in the Database window along with icons for the local tables in the database, so you can open the linked table whenever you want to. For more information about linked tables, see "Using External Data in Microsoft Access" in Chapter 17, "Switching to Microsoft Access."

Direct Table Opening

You cannot use the Access user interface to open an external table directly. However, you can open external tables directly by using DAO code in Visual Basic. A developer may use this method to get a value quickly from a table that does not need to be accessed very often in the application.

ODBC Connectivity

With Microsoft Jet, users can retrieve data from ODBC data sources. The ODBC standard is typically used to connect to server­based database systems. Access includes ODBC drivers for Microsoft SQL Server. The ODBC standard can also be used to connect to nonserver databases and spreadsheets.

As with installable ISAM data, Access can connect to ODBC data sources by linking or opening tables. Access also supports SQL pass­through queries with ODBC data sources, as described earlier in this chapter. The disadvantage of applications that use pass­through queries is that they are not portable. For example, an application written for Microsoft SQL Server fails if it attempts to gain access to an Oracle server, because it contains server­specific commands. Users create SQL pass­through queries by clicking SQL Specific on the Query menu and then clicking Pass­Through.

For an in­depth discussion of Microsoft Jet and its architecture, see the Microsoft Jet Database Engine Programmer's Guide, which is published by Microsoft Press and available wherever computer books are sold. For more information about Microsoft Press books, see Appendix E, "Other Support Resources."

Separating the Application from the Data If you are distributing an application to a number of users, or if the data is located on a server, consider using a back­end database to hold the tables that contain the data and a front­end database to hold the application's other objects. You can then base all objects in the application on linked tables from the back­end database. For an example of this type of architecture, see the Orders sample application (Orders.mdb), installed in the Program Files\Microsoft Office\Office\Samples folder when you choose a Custom installation during Setup. The forms, queries, reports, and other objects are contained in Orders.mdb. The database tables, however, are in the Northwind database (Northwind.mdb). When you separate the application's data from its objects, you can distribute upgraded queries, forms, reports, macros, and modules in the new version of the front­end database without disturbing the application's data. Similarly, you can exchange one set of data for another or back up the data on the back­end database without affecting the objects in the front­end database. If the data is located on a server, you can reduce the network load and improve performance by having users run the front­end database on their workstations rather than from the server. An additional advantage of separating the data from the rest of the application is that you can upgrade the custom application to future versions of Microsoft Access independently of the shared database tables. This makes it easier for users with different versions of Microsoft Access to use the same data.

Microsoft Access default settings and paths are established when you run the Office Setup program. After Setup, you can customize these settings for a single user, a workgroup, or across an entire organization.

There are several ways to customize the Access application:

• Through the Options dialog box

  You can customize Access in the Options dialog box (Tools menu). The settings you make here are stored in the Windows registry.

• Through the Customize dialog box

  In the Customize dialog box (View menu, Toolbars submenu), you can customize the toolbars, menu bars, and shortcut menus included with Access, or you can create your own.

• With an add­in

  For more information about add­ins, see "Add-ins" earlier in this chapter.

These methods of customizing Microsoft Access are described in the following sections. Some of these customizations are stored in the Windows registry, and some are stored in a separate file on the user's system.

User­Defined Options

The settings specified in the Options dialog box (Tools menu) are saved to the Windows registry, which Microsoft Access reads when it starts up. When two or more users open a shared database on a network, Access applies the separate preferences to each user's view of the shared database. For information about Access entries in the Windows registry, see Appendix C, "Registry Keys and Values."

User\Access 97\Tools_Options

For more information, see "Using Windows System Policies to Customize Office" in Chapter 7, "Customizing and Optimizing Microsoft Office."

Custom Toolbar, Menu Bar, and Shortcut Menu Settings

In Microsoft Access, you can organize the commands on toolbars, menu bars, and shortcut menus the way you want so that you can find and use them quickly. In previous versions of Access, toolbars contained only buttons. In Microsoft Access 97, toolbars can contain buttons, menus, or both. This means that menu bars and shortcut menus are now different types of toolbars; therefore, you can customize all three the same way. For example, although the built­in menu bar still appears by default at the top of the screen and contains standard menus such as File, Edit, and View, you can customize it by adding or removing buttons and menus or by moving it to a different location.

In addition to customizing the built­in toolbars, menu bars, and shortcut menus, you can create your own custom toolbars, menu bars, and shortcut menus. To create and customize toolbars, menu bars, and shortcut menus, and to set properties that affect how they look and behave, use the Customize dialog box (View menu, Toolbars submenu). By customizing toolbars, menu bars, and shortcut menus, you can change Access to better suit the needs of your workgroup. For example, you can add frequently used commands and dialog box options to toolbars and remove items that users rarely use.

New custom toolbars, menu bars, and shortcut menus are stored in the database file and are available to all users who share the database. If you want to copy custom toolbars and menus from another Access database, you can import them. You cannot import a single toolbar, menu bar, or shortcut menu; you must import all the toolbars, menu bars, and shortcut menus in the database file.

Note   Access does not import a toolbar, menu bar, or shortcut menu if it has the same name as one in the database you are importing to.

To import all custom toolbars, menu bars, and shortcut menus from another database

1. Open the database into which you want to import the custom toolbars, menu bars, and shortcut menus.

2. On the File menu, point to Get External Data, and then click Import.

3. In the Files of type box, make sure Microsoft Access (*.mdb;*.mdw;*.mda) is selected.

4. In the Look in box, select the drive and folder for the Access database you want to import from, and then double­click the database.

5. In the Import Objects dialog box, click Options.

6. Under Import, select the Menus and Toolbars check box, and then click OK.

Customizations made to built­in toolbars, menu bars, and shortcut menus by individual users are stored in the in the Windows registry in the HKEY_CURRENT_USER\Software\Microsoft\Office\8.0\Access\Settings\CommandBars key. These customizations cannot be copied to other users.

You can also use Visual Basic code to customize toolbars and menus with the CommandBar object model. To view a form that demonstrates working with the CommandBar object model, open the CommandBarsForm in the Developer Solutions sample application (Solutions.mdb). The Developer Solutions sample application is installed in the Program Files\Microsoft Office\Office\Samples folder when you choose a Custom installation during Setup. For more information about customizing toolbars and menus with Visual Basic, see the Microsoft Office 97/Visual Basic Programmer's Guide, which is published by Microsoft Press and available wherever computer books are sold. For more information about Microsoft Press books, see Appendix E, "Other Support Resources."

Conflicts can occur in Microsoft Access when a database is being replicated or when more than one user attempts to edit the same record in a shared database.

Replication Conflicts

During replication setup — for example, when a user drags a database to the Windows 95 Briefcase — the Jet database engine requests globally unique identifiers (GUIDs) from the operating system that are associated with each row of data in the Design Master. These GUIDs are then copied into any replica databases. If a row changes in the Design Master or a replica, a counter is incremented for the row. This makes it easy for Microsoft Access to compare the values of rows, detect that a change has been made, and replicate the changed data in other databases. If the same row is changed in more than one database simultaneously, Access selects between them based on the following rules:

• Access chooses the database that has the highest value for its counter. The row that has changed most often has a higher counter, because the counter is incremented every time a row is changed.

• If both rows have been changed the same number of times (counters are the same on both rows in both databases), Access chooses a database randomly, as the replication logic cannot reasonably know which of the two rows has the correct data.

Regardless of how the data is selected, users who submit the data that is not chosen are told by the Conflict Resolver in Access that their data was rejected. Users can then resubmit their change or accept the other data.

Locking Conflicts

If record locking is set to No Locks, there may be locking conflicts when more than one user attempts to edit or save the same record in a shared database. Users can set record locking on the Advanced tab of the Options dialog box (Tools menu).

The No Locks setting allows more than one user to edit a record simultaneously, but the record is locked during the instant when it is being saved. If two users attempt to save changes to the same record, Microsoft Access displays a message to the second user who tries to save the record. This user can then discard the record, copy the record to the Clipboard, or replace the changes made by the other user. By using this last option, it is possible for users to write over one another's changes.

For more information about record locking, see "Multiuser Applications and Locking" in Chapter 29, "Workgroup Features in Microsoft Access."