List of Figures

Chapter 1: Data Access Models: Part I

Figure 1-1: The References dialog box is used to add ADO libraries to an application.
Figure 1-2: The ADODB object library.
Figure 1-3: The Immediate window output from the OpenSchemaConstraints procedure.
Figure 1.4: The Immediate window output from the EasyLoop2a procedure.
Figure 1.5: The Immediate window output from the EasyLoop2b procedure.
Figure 1.6: The XML contents for the file saved by the SaveAccessDBRecordSource procedure, as seen from Internet Explorer.
Figure 1.7: Program output from the SortedTableTypes procedure, showing a list of tables sorted by table name within table type from the Northwind Access database file.
Figure 1.8: The Object Browser showing a selection of data type constants for defining fields.
Figure 1.9: An excerpt from the report.txt file showing grouping and aggregation by quarter.

Chapter 2: Data Access Models: Part II

Figure 2.1: Representative output from running the PrintFolderContents procedure.
Figure 2.2: Representative output from running the OpenRecordWithRecordsetSource procedure.
Figure 2.3: Representative output from running the CallListSiteFilesAndFolders and ListSiteFilesAndFolders procedures.
Figure 2.4: Output from running the SaveToAndRecoverFromStream procedure.

Chapter 3: The ADOX Library and Tables

Figure 3.1: The ADOX object library.
Figure 3.2: Output from the CallListTablesRaw and ListTablesRaw procedures for a clean backup copy of the Northwind database.
Figure 3.3: Output from the CallTablesDetail and TablesDetail procedures contrasted with the Details view from the Database window for the same target database.
Figure 3.4: Output from the TablesDetailFromAllTables procedure. Contrast with the output in Figure 3-2 to appreciate the differences between the AllTables collection and the Tables collection.
Figure 3.5: The first several rows of the Orders table from the Northwind database in a text file named Orders.txt and viewed in Notepad.

Chapter 4: Jet SQL, the ADOX Library, and Queries

Figure 4.1: Output from the SelectAllFields procedure used with the PreviewRecordsetSource procedure.
Figure 4.2: The output on the left comes from the SelectNotDistinctOrderDetails procedure. The output on the right without repeated values is produced by the SelectDistinctOrderDetails procedure.
Figure 4.3: Output of the SelectGroupBy procedure demonstrates the effect of a GROUP BY clause on the result set from a select query's SQL statement.
Figure 4.4: A schematic contrasting equijoins, left outer joins, and right outer joins.
Figure 4.5: Output of the SelectSortedUnion procedure.
Figure 4.6: Output from the CallAndDemoResetCounter procedure that confirms programmatic changes to start and step AutoNumber values.

Chapter 5: Forms, Controls, and Data

Figure 5.1: A sample splash screen.
Figure 5.2: Two forms that reveal the ability of the Timer event to requery the record source behind a form to show the most recent data automatically.
Figure 5.3: The frmPublishShippersTable form in Design view (top) and Form view (bottom).
Figure 5.4: A simple hyperlink form navigation system.
Figure 5.5: The Insert Hyperlink dialog box for the label on the frmGoHere form.
Figure 5.6: These forms use code behind button Click events to perform navigation.
Figure 5.7: A sample On Click event setting that lets a hyperlink execute a function procedure even though the form on which the hyperlink resides has no module behind it.
Figure 5.8: A form based on a single record source (the Order Details table) created by the AutoForm Wizard.
Figure 5.9: Subdatasheets for the Order Details table within the Orders table.
Figure 5.10: A main/subform that has a subform within a subform that appears as a subdatasheet.
Figure 5.11: An excerpt of output from the SyntaxForSubDataSheetOnSubForm procedure.
Figure 5.12: The frmMyOrdersSubtotal form presents the total extended price for the line items appearing on its subform.
Figure 5.13: Conditional formatting controls the appearance of the Extended Price and Discount fields on this form.
Figure 5.14: The Conditional Formatting dialog box.
Figure 5.15: Conditional formatting controls the appearance of the Discount control values on this form, and event procedures conditionally disable the OrderID control on the main form and the OrderID and ProductID controls on the subform.
Figure 5.16: The expressions for the Discount control values in Figure5-13.
Figure 5.17: A form that performs a simple lookup operation.
Figure 5.18: A Combo Box lookup form.
Figure 5.19: The form at the top displays its result set in message boxes so that users can view the result set but not alter its underlying data.
Figure 5.20: These forms let the user select and view a customer's record.
Figure 5.21: A form to display customer orders in one or more message boxes.
Figure 5.22: A form to display customer orders in the List Box control.
Figure 5.23: These forms let the user see monthly sales data for a selected year.
Figure 5.24: The complete list of Access form control types from the Object Browser.

Chapter 6: Reports, Controls, and Data

Figure 6.1: A report created programmatically based on the CreateReport and CreateReportControl methods.
Figure 6.2: The first Report Wizard dialog box for creating the report shown in Figure6.3.
Figure 6.3: A sample report generated by the Report Wizard in the style of the programmatically created report in Figure6.1 .
Figure 6.4: The Chart Wizard chart type page with a 3-D bar chart selected.
Figure 6.5: A sample chart created with the Chart Wizard for a query that selects the OrderDate and Quantity fields from the join of the Orders and Order Details tables in the Northwind database.
Figure 6.6: A wizard page depicting the layout of a mailing label just before adding the PostalCode field.
Figure 6.7: A sample report created by the Label Wizard for the Suppliers table.
Figure 6.8: A Customer Phone List report prepared manually in Design view.
Figure 6.9: Two excerpts from a Customer Phone List report generated in Design view. The top panel shows the layout for the top of the first page; the bottom panel presents the layout for the bottom of the page.
Figure 6.10: A setting in the Sorting And Grouping dialog box that alters the default order of the report.
Figure 6.11: An excerpt from a report showing the customers phone list sorted in ascending order on the ContactName field for its record source.
Figure 6.12: A report's Design view with its Sorting And Grouping dialog box that demonstrates how to group on the first letter of the ContactName field in the Customers table and sort the records within each group on the ContactName field values.
Figure 6.13: An excerpt from the report specified by the settings in
Figure 6-12.
Figure 6.14: An excerpt from a report that displays the result set from the qryOrdersByMonth query.
Figure 6.15: An excerpt from a report that groups the result set from the qryOrdersByMonth query by quarter.
Figure 6.16: An excerpt from a report that groups and subtotals by quarter the result set from the qryOrdersByMonth query. Unlike the report shown in Figure 6-14, this report sorts the monthly totals by date within a quarter.
Figure 6.17: An excerpt from a single report that represents a hierarchical relationship between customers and product sales.
Figure 6.18: A report's Design view that displays a parent-child relationship between customers and their product sales rows based on the qryCustomerProductSales query.
Figure 6.19: An excerpt from a main/subreport that represents a hierarchical relationship between categories and product sales.
Figure 6.20: A report's Design view that displays a parent-child relationship between categories and their product sale rows based on the qryProductSalesWithinCategory query.
Figure 6.21: An excerpt from a main/subreport with a chart that depicts a hierarchical relationship between categories and product sales by plotting the product sales for each category immediately below the table showing product sales within a category.
Figure 6.22: Creating a snapshot file manually.
Figure 6.23: An Access chart-based report viewed from Internet Explorer 6.
Figure 6.24: One Print event procedure for this report draws rectangles around the Report Header and Page Footer sections. Another Print event procedure selectively draws ovals around the monthly order totals.
Figure 6.25: An excerpt from a report that uses FormatCondition objects to control the color of row values.
Figure 6.26: This report uses two event procedures and a text box to compute a page sum in its Page Footer section. A third event procedure lets the developer hide the text box that assists in computing the running sum.
Figure 6.27: In the form on the left, users can designate a record source and a corresponding title for the report on the right.

Chapter 7: Designing and Using PivotTables and PivotCharts

Figure 7.1: A sample PivotTable based on a copy of the Orders table from the Northwind database.
Figure 7.2: The PivotTable Field List dialog box for the PivotTable shown in Figure 7-1.
Figure 7.3: You can drag fields from the PivotTable Field List dialog box to drop areas on the PivotTable, as this example for the Products table shows.
Figure 7.4: A PivotChart automatically generated by the construction of the PivotTable shown in Figure 7-1.
Figure 7.5: A diagram depicting the relationships between tables specified by the Invoices query.
Figure 7.6: An excerpt from the PivotTable created by the CreateAFormWithPivotTableOnInvoices sample.
Figure 7.7: An updated excerpt from our sample PivotTable that shows summary data for the Quantity and ExtendedPrice fields.
Figure 7.8: The PivotChart view created by the CreateChart procedure.
Figure 7.9: The display that results from running the Format3DBarChart procedure.
Figure 7.10: The PivotChart that results from running the CreateTimeBasedLineChart procedure.
Figure 7.11: The display that results from running the UpdateTimeBasedLineChart procedure.

Chapter 8: Microsoft Office Objects

Figure 8.1: Design view of an Autoexec macro for a custom startup.
Figure 8.2: The FileSearch object is one of the shared Office objects.
Figure 8.3: A form for adding members to the FileTypes collection of a FileSearch object. The form also enables executing the search.
Figure 8.4: You use the members of the MsoCondition enumeration group to specify conditions for advanced criteria in the PropertyTests collection of the FileSearch object.
Figure 8.5: The Access11Files Properties dialog box with the settings to implement read-only Simple File Sharing with Windows XP.
Figure 8.6: You use the CommandBar object model to customize built-in command bars and create custom command bars.
Figure 8.7: A custom popup menu bar with a combo box control. You click anywhere on the form to open the custom menu bar.

Chapter 9: Integrating Access with Other Office Applications

Figure 9.1: A References dialog box in Access showing references to Excel, Outlook, and Word.
Figure 9.2: An Excel spreadsheet with a named range, Gas, extending over cells A1 through H45.
Figure 9.3: An excerpt from the output of the OpenXLComputePrint procedure. Notice the identical computational results from Access and Excel.
Figure 9.4: An excerpt from the output of the ComputeOnGas procedure. Notice the new column of data and the resized columns.
Figure 9.5: A display of Excel data in concurrent Excel and Access applications.
Figure 9.6: An Access form used to browse filtered Outlook Inbox items.
Figure 9.7: This form invokes the fromAccessToWordTable procedure.
Figure 9.8: An excerpt from a Word document for printing mailing labels on Avery 5260 laser forms.
Figure 9.9: Excerpts from a Word document for printing form letters.

Chapter 10: Database Security

Figure 10.1: You use the Protection tab of the Project Properties dialog box to set password security for the modules in a project.
Figure 10.2: A graphical depiction of the interaction of .mdb and .mdw files in user-level security.
Figure 10.3: Confirmation screen for a newly created workgroup information file.
Figure 10.4: The User And Group Accounts dialog box for creating a workgroup administrator named Chap10Admin.
Figure 10.5: The Users group permissions for new tables and queries with the default permission settings.
Figure 10.6: The relationship of the User and Group objects to the Catalog object and the Users and Groups collections.
Figure 10.7: A group membership report from the ListGroupsInCat procedure.
Figure 10.8: Output to the Immediate window from the AssignUpdatePermissionsToNewUser2 procedure.
Figure 10.9: Form1 from Chapter10.mdb illustrates operational features of the Jet SandBox.
Figure 10.10: Selfcert.exe dialog box for creating a digital certificate for Rick Dobson.
Figure 10.11: A VBA procedure and the Digital Signature dialog box.
Figure 10.12: The Security Warning dialog box.

Chapter 11: Using Access to Build SQL Server Solutions: Part I

Figure 11.1: The Data Link Properties dialog box and the Database window for an Access project.
Figure 11.2: The Database window for the NorthwindCS Access project.
Figure 11.3: The File New Database dialog box in an Access window immediately after a click on the Project Using Existing Data link on the Getting Started task pane.
Figure 11.4: The Data Link Properties dialog box for connecting a new Access project to the Northwind database on the cab2000 server.
Figure 11.5: The initial dialog box of the Microsoft SQL Server Database Wizard.
Figure 11.6: A graphical view of the Sales by Year stored procedure in the NorthwindCS database.
Figure 11.7: The Data Link Properties dialog box and Database window for an Access project connected to the pubs database.
Figure 11.8: A database diagram for the pubs database.
Figure 11.9: An adapted T-SQL sample (along with its return values) from Books Online for the pubs database in an Access project.
Figure 11.10: A view of the data_types table in the new Access 2003 Table Designer.
Figure 11.11: The output generated by the ReportDataTypes procedure.
Figure 11.12: The Properties dialog box for the OrderID column that shows the setting for the Identity, Identity Seed, and Identity Increment.
Figure 11.13: This database diagram shows the graphical link between the EmployeeID field from the MyExtensions table and the EmployeeID field in the Orders_linked_to_MyExtensions table after the Create Relationship dialog box opens but before the establishment of a relationship.
Figure 11.14: The three panes for the Access project Query Designer with a view that sorts orders by Freight column values in descending order.
Figure 11.15: An excerpt from the result set for Orders_in_1996_1.
Figure 11.16: The output from the date conversion sample.
Figure 11.17: The output from the time conversion sample.
Figure 11.18: The Access 2003 visual designer for stored procedures.
Figure 11.19: The Access 2003 text-based designer for stored procedures.
Figure 11.20: A prompt for a stored procedure parameter value.
Figure 11.21: A comparison of two Design views that sort records: one for a view, and one for a stored procedure.
Figure 11.22: The output to the Immediate window for the InOutReturnDemo VBA procedure.
Figure 11.23: An error message generated by the Shippers_Trigger1 trigger.
Figure 11.24: A message box generated by the ComputePercentChangeBetweenYears stored procedure when it's run from the Database window.
Figure 11.25: The visual designer for in-line functions lets you base a function on tables, views, or other table-returning functions.
Figure 11.26: A visual designer view of an in-line function that has another user-defined function nested within it.

Chapter 12: Using Access to Build SQL Server Solutions: Part II

Figure 12.1: A form created with one click for the Order Details table in a SQL Server database.
Figure 12.2: A main/subform generated by the AutoForm Wizard for the Categories table.
Figure 12.3: A main/subform generated by the AutoForm Wizard for the Employees table.
Figure 12.4: A main/subform based on the Employees table with two unbound text boxes that display subform content on the main form.
Figure 12.5: A form containing employee photo images that correspond to the photo filenames in the Employees table.
Figure 12.6: Importing a .jpg file into an Image control on a form.
Figure 12.7: Collapsing the first column to hide it with a combo box list, even while the combo box binds to the column value after a selection.
Figure 12.8: A combo box created with the Combo Box Wizard against a SQL Server database that shows items from the CategoryName table but saves the corresponding CategoryID.
Figure 12.9: A combo box that's conditional upon another and opens a second form.
Figure 12.10: Design view of the frmTwoCboOpenForm form.
Figure 12.11: Column values containing hyperlink data from the CreateLinksTable procedure.
Figure 12.12: The frmLinks form that displays values from the Links table.
Figure 12.13: A sample form in the chapter's Access project that demonstrates how to program the ServerFilter property.
Figure 12.14: An excerpt from the rptvwEmployeesOrderDetails report. It shows the sort of OrderID and ProductID within EmployeeID.
Figure 12.15: An excerpt from the rptvwEmployeesOrderDetails report after sorting by the preceding code sample.
Figure 12.16: The Data tab for a report's Properties dialog box reveals the syntax for assigning parameter values to the Employee Sales by Country stored procedure.

Chapter 13: Using Access to Build SQL Server Solutions: Part III

Figure 13.1: The Data Link Properties dialog box for an Access project ready to connect to the Northwind database with an sa login that has a password.
Figure 13.2: An excerpt from the SQL-DMO object model.
Figure 13.3: An Access form that lets a user browse selected database objects on the local SQL server.
Figure 13.4: The Immediate window showing the users in the Northwind database after adding the Access11User and then removing the user.

Chapter 14: Access Does the Web

Figure 14.1: The Web Site Templates dialog box for creating a new Empty Web Site with FrontPage 2003.
Figure 14.2: Creating a hyperlink on a page by using FrontPage.
Figure 14.3: Latch controls on the Alignment And Sizing toolbar speed up the process of making new settings to controls.
Figure 14.4: By using the navigation bar, end users can navigate, update, add, and delete records as well as sort and filter the data behind a form.
Figure 14.5: Use the ServerFilter property of a hyperlink to control the data that a form displays.
Figure 14.6: Hyperlinks can transmit a ServerFilter property assignment after the URL as a parameter.
Figure 14.7: An unbound combo box added to a data access page.
Figure 14.8: A Script Editor view that contrasts with the Design view of the data access page shown in Figure 14-7 .
Figure 14.9: A Document Outline window with the onchange event for the cboCategoryID control selected.
Figure 14.10: A collection of .jpg files in the images folder of a Web site for use as images on a data access page.
Figure 14.11: The Relationship Wizard for dynamically building relationships for the tables behind a form on a data access page.
Figure 14.12: A data access page featuring an image control that updates the picture it shows as users navigate through Employees.
Figure 14.13: A two-level interactive report (dapExpandCategoryIDForProducts.htm) with an expanded group for the category ID of 6.
Figure 14.14: A three-level interactive report with an expanded group showing line items within OrderID groups, within CustomerID groups.
Figure 14.15: A pair of Data Link Properties tabs for setting the data source of a data access page to the NorthwindCS database.
Figure 14.16: With calculated fields, you can dramatically improve the ease of interpreting an interactive report.
Figure 14.17: You can improve readability by implementing conditional formatting with an event procedure for the data source control in a data access page.
Figure 14.18: Automatically generated data access pages can have the same look and feel as Access forms.
Figure 14.19: An Access report with a nested relationship for showing how the OutputTo method can generate a data access page with an interactive report style.
Figure 14.20: A slightly edited interactive report based on the Access report in Figure 14-19.
Figure 14.21: The spreadsheet component on this data access page uses sales from the database for 1997 to project sales through 2002 in a browser.
Figure 14.22: The Design view of the data access page from Figure 14-21 with the Commands And Options dialog box for its spreadsheet component.
Figure 14.23: The Office 2003 Chart component on a data access page accepts values from a Spreadsheet component that changes its sales projections when a user moves off the current record.
Figure 14.24: The initial Commands And Options dialog box for specifying the data source for a Chart component.
Figure 14.25: You can populate PivotTable list controls on data access pages as you would within normal Access applications.
Figure 14.26: A pair of different browser windows illustrates some of the variety that users can generate from the same PivotTable list control.
Figure 14.27: A pivot chart tied to a PivotTable list control.
Figure 14.28: A programmed PivotTable list control with its Field List open.

Chapter 15: XML and Access

Figure 15.1: The MyShippersWithElements.xml and MyShippersWithElements.xml documents in a browser.
Figure 15.2: Either escape sequences or CDATA sections can be used to represent special markup characters in an XML document.
Figure 15.3: The Access UI creates XML documents that use attributes in the root tag for an XML document to refer to XML schemas.
Figure 15.4: The hello.xml file rendered before and after transformation by an XSLT file.
Figure 15.5: An Access form used to collect values for writing an XML document.
Figure 15.6: A browser displaying the XML document generated by the click event procedure for the button on the form in Figure 15-5.
Figure 15.7: The Export XML dialog box for creating XML files based on Access database objects, such as tables, queries, forms, and reports.
Figure 15.8: The Import XML dialog box for importing the XML document created in the "Generating XML Files" section.
Figure 15.9: The Shippers.htm file generated by the Access File, Export command in an Internet Explorer 6.0 browser.
Figure 15.10: The frmCustomersOrders form used to demonstrate the process for creating an XML document based on the data behind a form.
Figure 15.11: The Shippersp1.xml tag values printed as sets of column values to the Immediate window.
Figure 15.12: The table that appears when a browser opens Shippersp21.htm.