|C H A P T E R 35||Part 6 Microsoft Office Architecture||Microsoft Office Resource Kit|
|Microsoft Access Architecture|
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.
|Note Access runs on Windows 95 and Windows NT Workstation version 3.51 or later only.|
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 dataentry 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 dataentry 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 dataentry 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."
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.
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.
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.
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.
An action query makes changes to many records in one operation. There are four types of action queries: delete, update, append, and maketable.
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.
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.
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:
For example, you may want to append only the names and addresses of customers with outstanding orders.
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.
A maketable query creates a new table from all or part of the data in one or more tables. Maketable queries are helpful for:
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.
For example, suppose you want to print a report on 15May97 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 1Apr97. A report based on a query or an SQL statement extracts the most uptodate data from the tables (the data as of 15May97), 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 1Apr97, create and run a maketable 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.
For example, you can create a table that stores all the old orders before deleting them from the current Orders table.
For example, suppose you want to print multiple reports that are based on a fivetable query that includes totals. You may be able to speed things up by first creating a maketable 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 maketable query.
An SQL query is a query you create by using an SQL statement. Examples of SQLspecific queries are union queries, passthrough queries, datadefinition queries, and subqueries.
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 maketable query based on the union query to make a new table.
A passthrough 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 passthrough query to retrieve records or change data. You can also use passthrough queries to run stored procedures (SQL programs that are stored on the server) and to perform operations that are only available by using serverspecific commands.
A datadefinition 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 passthrough queries instead. To work with other nonMicrosoft 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:
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.
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. Proceduresassociated 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 builtin 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 builtin 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.
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.
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
– 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.
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 DynamicLink Libraries," in Building Applications with Microsoft Access 97.
Addins 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. Addins 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 addins created by independent software developers.
Access has three kinds of addins: wizards, builders, and menu addins. 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 addin is not contextspecific 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 addins are available through the AddIns submenu (Tools menu). The interface through which an addin is available is determined by how the addin is registered when it is installed.
Addin files are saved like library database files, with the .mda file name extension. Addin 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 addins from Access by pointing to AddIns (Tools menu) and then clicking AddIn Manager.
For information about creating addins in Access, see Chapter 17, "Creating Wizards, Builders, and Menu Addins," in Building Applications with Microsoft Access 97.
A wizard handles complex operations. It usually consists of a series of dialog boxes that provide a stepbystep 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 addin. These addins 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 addin.
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:
A menu addin is a generalpurpose tool that accomplishes a task that does not fit into the wizard or builder categories. A menu addin typically operates on multiple objects or on the Access application itself. The AddIn Manager is an example of a menu addin.
Menu addins are supported by Access through the AddIns submenu (Tools menu). When you install a menu addin, the command to run it is added to the AddIns submenu. It is important to understand that menu addins are available to the user whenever the Tools menu is available. After a menu addin is installed, you can also add a button or command to run the menu addin from any toolbar by using the Customize dialog box (View menu, Toolbars submenu). This means that menu addins are not contextsensitive like wizards and builders. A wizard is designed to aid the user within a specific context, such as form or query design. A menu addin is designed to perform a general function that may not fit within the context of the user's current operation.
Several addins 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 addins that are always installed, regardless of the installation option you choose during Setup.
The following table describes the addins included in a Typical installation (or when you select the Wizards option under the Microsoft Access option) during Setup.
The following table describes the addins included in a Custom installation when you select the Advanced Wizards option under the Microsoft Access option during Setup.
A workgroup information file is a file that Microsoft Access reads when starting up; it contains information about the users in a workgroup. If userlevel security is being used, this information includes users' account names, their passwords, and the groups that they belong to.
Even when userlevel security is not being explicitly used, Access requires the workgroup information file in order to start up. This is because userlevel security is always activated before Access starts running to eliminate the possibility of a security back door. Before userlevel security is explicitly established, all users are automatically logged on by using the default Admin user account. Once userlevel 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 commandline 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 commandline 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 recreate it. Users must have a valid workgroup information file to run Access.
For more information about the Access userlevel 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 dynamiclink libraries (DLLs):
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 nonnative 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.
Dao350.dll is the Microsoft Jet component that provides a developer interface to Microsoft Jet. Data Access Objects (DAO) includes a rich, highlevel set of objects that insulates developers from the physical details of reading and writing records.
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.
These DLLs handle the reading and writing of data stored in dBASE, FoxPro, Paradox, Lotus, Microsoft Excel, Microsoft Exchange, Microsoft Outlook, fixedwidth text, delimited text, and HTML files.
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.
With Microsoft Jet, users can retrieve data from ODBC data sources. The ODBC standard is typically used to connect to serverbased 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 passthrough queries with ODBC data sources, as described earlier in this chapter. The disadvantage of applications that use passthrough 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 serverspecific commands. Users create SQL passthrough queries by clicking SQL Specific on the Query menu and then clicking PassThrough.
For an indepth 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."
|ISAM format||Supported by DLL|
|Xbase (dBASE and FoxPro)||Msxbse35.dll|
|Text and HTML||Mstext35.dll|
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 backend database to hold the tables that contain the data and a frontend database to hold the application's other objects. You can then base all objects in the application on linked tables from the backend 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 frontend database without disturbing the
application's data. Similarly, you can exchange one set of data for
another or back up the data on the backend database without
affecting the objects in the frontend database. If the data is
located on a server, you can reduce the network load and improve
performance by having users run the frontend 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.
|Color Builder||An addin 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 addin in previous versions; now part of Msaccess.exe||Creates the correct syntax for a query.|
|Subform/Subreport Field Linker||An addin 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.|
|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, spreadsheetlike 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.|
|AddIn Manager||Wztool80.mde||Installs and uninstalls wizards, builders, and addins.|
|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.|
|UserLevel Security Wizard||Wztool80.mde||Creates a new, encrypted database, with regulated user access, from an existing database.|
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:
You can customize Access in the Options dialog box (Tools menu). The settings you make here are stored in the Windows registry.
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.
For more information about addins, 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.
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:|
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 builtin 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 builtin 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
Customizations made to builtin 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.
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:
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.
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