C H A P T E R    35 Part 6  Microsoft Office Architecture Microsoft Office Resource Kit
Microsoft Access Architecture Previous



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

   Note   Access runs on Windows 95 and Windows NT Workstation version 3.51 or later only.


How Microsoft Access Is Structured

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.


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.


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:

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:

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.


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:


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.


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.


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.


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:

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.


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:

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


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:


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:

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.

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.

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.

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 security-enhanced 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):

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

How You Can Customize Microsoft Access

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."

   Tip   In Windows 95 and Windows NT Workstation 4.0, you can use a system policy to define most settings in the Options dialog box (Tools menu) for all Access users in your workgroup. In the System Policy Editor, set the following policy:

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."


How Microsoft Access Resolves Conflicts

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."

Contents | << Previous | Next >> | Index

© 1998 Microsoft Corporation
All rights reserved. Terms of Use.