Access 2003 Bible

by Prague, Cary N.; Irwin, Michael R.; Reardon, Jennifer

• ISBN13: 9780764539862
• ISBN10: 0764539868
• Edition: 1st
• Format: Paperback
• Copyright: 2003-10-17
• Publisher: Wiley

Summary

The most comprehensive reference on this popular database management tool, fully updated with the new features of Access "X" including increased use of XML and Web services Explores the new, tighter integration with SharePoint and BizTalk in Office "X" that enables greater flexibility for gathering and manipulating data Written by an international bestselling author team with several books to their credit, including previous editions of Access Bible Gets Access beginners started with hundreds of examples, tips, and techniques for getting the most from Access Offers advanced programming information for serious professionals CD-ROM includes all templates and worksheets used in the book, as well as sample chapters from all Wiley Office "X" related Bibles and useful third party software, including John Walkenbach's Power Utility Pak

Author Biography

<b>Cary N. Prague</b> is an internationally best-selling author and lecturer in the database industry. He owns Database Creations, Inc., the world&#8217;s largest Microsoft Access add-on company. Their products include a line of financial software; Business! For Microsoft Office, a mid-range accounting system, POSitively Business! Point of Sale software, the Inventory Barcode manager for mobile data collection, and the Check Writer and General Ledger. Database Creations also produces a line of developer tools including the appBuilder, an application generator for Microsoft Access, the EZ Access Developer Tools for building great user interfaces, appWatcher for maintaining code bases among several developers, and Surgical Strike, the only Patch Manager for Microsoft Access.<br> Cary also owns Database Creations Consulting, LLC., a successful consulting firm specializing in Microsoft Access and SQL Server applications. Local and national clients include many Fortune 100 companies including manufacturers, defense contractors, insurance, health-care, and software industry companies. His client list includes Microsoft, United Technologies, ABB, Smith &amp; Wesson Firearms, Pratt and Whitney Aircraft, ProHealth, OfficeMax, Continental Airlines, and other Fortune 500 companies. <p> <b>Michael R. Irwin</b> is considered one of the leading authorities on automated database and Internet management systems today. He is a noted worldwide lecturer, a winner of national and international awards, best-selling author, and developer of client/server, Internet, Intranet, and PC-based database management systems.<br> Michael has extensive database knowledge, gained by working with the Metropolitan Police Department in Washington, D.C. as a developer and analyst for the Information Systems Division for over 20 years and assorted Federal Agencies of the United States Government. Since retiring in June 1992, he runs his own consulting firm, named The Irwin Group, and is principal partner in the company - IT in Asia, LLC, specializing in Internet database integration and emphasizing Client/Server and net solutions. With consulting offices in Cincinnati, Ohio, Bangkok, Thailand, and Manila, Philippines, his companies offer training and development of Internet and database applications. His company has the distinction of being one of the first Microsoft Solution&#8217;s Providers (in 1992). His local, national, and international clients include many software companies, manufacturers, government agencies, and international companies. <p> <b>Jennifer Reardon</b> is considered a leading developer of custom database applications. She has over ten years&#8217; experience developing client/server and PC-based applications. She has accumulated much of her application development experience working as lead developer for Database Creations. She has partnered with Cary Prague developing applications for many Fortune 500 companies.<br> Her most significant projects include a spare parts inventory control system for Pratt &amp; Whitney&#8217;s F22 program, an engineering specifications system for ABBCombustion Engineering, and an emergency event tracking system for the State of Connecticut. She was also the lead developer of many of the Database Creations add-on software products including Business, Yes! I Can Run My Business, Check Writer, and the User Interface Construction Kit.

Table of Contents

Preface	xi
Acknowledgments	xxxi
Part I Creating Desktop Applications	1	(298)
Section I Working with Data Tables and Queries	1	(298)
Chapter 1: Understanding Data	3	(32)
The Database Terminology of Access	4	(3)
What is a database?	4	(1)
Databases, tables, records, fields, and values	5	(2)
Using More Than One Table	7	(2)
Working with multiple tables	8	(1)
Why you should create multiple tables	8	(1)
Access Database Objects and Views	9	(3)
Datasheets	9	(1)
Queries and dynasets	10	(1)
Data-entry and display forms	10	(1)
Reports	11	(1)
Designing the system's objects	11	(1)
The Seven-Step Design Method	12	(23)
Step 1: The overall design-From concept to reality	13	(2)
Step 2: Report design-Placing your fields	15	(2)
Step 3: Data design-What fields do you have?	17	(7)
Step 4: Table design and relationships	24	(3)
Step 5: Field design data-entry rules and validation	27	(3)
Step 6: Form design-Input	30	(2)
Step 7: Automation design-Menus	32	(3)
Chapter 2: Creating and Building Tables	35	(50)
Creating Database Tables	35	(1)
Creating a Database	35	(6)
Templates Section	37	(2)
Blank database	39	(2)
The Database Window	41	(5)
Objects menu bar	42	(1)
Groups menu bar	43	(1)
The Database window toolbar buttons	44	(1)
The Access window toolbar	44	(2)
Creating a New Table	46	(5)
The table design process	46	(1)
The New Table dialog box	47	(2)
Creating a new table with a Datasheet View	49	(2)
The Table Design Window	51	(5)
Using the Table Design window toolbar	52	(1)
Working with fields	52	(4)
Creating the tblContacts Table	56	(3)
AutoNumber fields and Access	56	(1)
Completing the tblContacts Table	57	(2)
Changing a Table Design	59	(3)
Inserting a new field	59	(1)
Deleting a field	59	(1)
Changing a field location	60	(1)
Changing a field name	60	(1)
Changing a field size	60	(1)
Changing a field data type	61	(1)
Understanding Field Properties	62	(14)
Entering field-size properties	64	(1)
Using formats	65	(4)
Entering formats	69	(1)
Entering input masks	70	(2)
The Input Mask Wizard	72	(1)
Entering decimal places	73	(1)
Creating a caption	73	(1)
Setting a default value	73	(1)
Working with validation	73	(3)
Understanding the Lookup Property window	76	(1)
Determining the Primary Key	76	(4)
Creating a unique key	77	(1)
Creating the primary key	78	(1)
The Indexes window	78	(1)
The Table Properties window	79	(1)
Printing a Table Design	80	(1)
Saving the Completed Table	81	(1)
Manipulating Tables in a Database Window	81	(2)
Renaming tables	81	(1)
Deleting tables	82	(1)
Copying tables in a database	82	(1)
Copying a table to another database	83	(1)
Adding Records to a Database Table	83	(2)
Chapter 3: Entering Data into Tables and Forms	85	(38)
Understanding Datasheets	85	(1)
The Datasheet Window	86	(6)
Moving within a datasheet	88	(1)
The Navigation buttons	88	(1)
The Datasheet toolbar	89	(3)
Opening a Datasheet	92	(1)
Entering New Data	92	(6)
Saving the record	94	(1)
Understanding automatic data-type validation	95	(1)
Understanding how properties affect data entry	96	(2)
Navigating Records in a Datasheet	98	(4)
Moving between records	98	(1)
Finding a specific value	99	(3)
Changing Values in a Datasheet,	102	(2)
Replacing an existing value manually	102	(1)
Changing an existing value	103	(1)
Fields that you can't edit	104	(1)
Using the Undo Feature	104	(1)
Copying and Pasting Values	105	(1)
Replacing Values	105	(1)
Adding New Records	106	(1)
Deleting Records	107	(1)
Adding, Changing, and Deleting Columns	107	(1)
Deleting a column from a datasheet	108	(1)
Adding a column to a datasheet	108	(1)
Changing a field name (column header)	108	(1)
Displaying Records	108	(7)
Changing the field order	108	(2)
Changing the field display width	110	(1)
Changing the record display height	111	(1)
Displaying cell gridlines	112	(1)
Changing display fonts	113	(1)
Hiding and unhiding columns	114	(1)
Freezing columns	115	(1)
Saving the changed layout	115	(1)
Saving a record	115	(1)
Sorting and Filtering Records in a Datasheet	115	(3)
Using the QuickSort feature	116	(1)
Using Filter By Selection	116	(2)
Using Filter By Form	118	(1)
Printing Records	118	(5)
Printing the datasheet	119	(1)
Using the Print Preview window	119	(4)
Chapter 4: Creating and Understanding Relationships	123	(30)
Tables Used in the Access Auto Auctions Database	123	(3)
Understanding Keys	126	(6)
Deciding on a primary key	128	(1)
Benefits of a primary key	129	(1)
Creating a primary key	130	(2)
Understanding foreign keys	132	(1)
Understanding Relations between Tables	132	(4)
A review of relationships	133	(1)
Understanding the four types of table relationships	133	(3)
Understanding Referential Integrity	136	(1)
Creating Relationships	136	(12)
Using the Relationships window	137	(2)
Creating relationships between tables	139	(1)
Specifying relationship options in the Edit Relationships dialog box	140	(4)
Finishing the relationships between the tables of the Access Auto Auctions system	144	(2)
Saving the relationships between tables	146	(1)
Adding another relationship	146	(1)
Deleting an existing relationship	147	(1)
Join lines in the Relationships window	147	(1)
Printing a report of the relationships	147	(1)
Using Subdatasheets	148	(5)
Setting up sub-datasheets	149	(4)
Chapter 5: Displaying Selected Data with Queries	153	(50)
Understanding Queries	153	(5)
What is a query?	154	(2)
Types of queries	156	(1)
Query capabilities	157	(1)
How dynasets work	158	(1)
Creating a Query	158	(5)
Selecting a table	159	(2)
Using the Query window	161	(1)
Navigating the Query Design window	161	(1)
Using the Query Design toolbar	162	(1)
Using the QBE pane of the Query Design window	163	(1)
Selecting Fields	163	(4)
Adding a single field	163	(2)
Adding multiple fields	165	(1)
Adding all table fields	166	(1)
Displaying the Dynaset	167	(1)
Working with the datasheet	168	(1)
Changing data in the query datasheet	168	(1)
Returning to the query design	168	(1)
Working with Fields	168	(5)
Selecting a field	168	(2)
Changing field order	170	(1)
Resizing columns in design mode	170	(1)
Removing a field	171	(1)
Inserting a field	171	(1)
Changing the field display name	171	(1)
Showing table names	172	(1)
Showing a field	173	(1)
Changing the Sort Order	173	(2)
Specifying a sort	174	(1)
Displaying Only Selected Records	175	(3)
Understanding record criteria	175	(1)
Entering simple character criteria	176	(1)
Entering other simple criteria	177	(1)
Printing a Query Dynaset	178	(1)
Saving a Query	179	(1)
Adding More than One Table to a Query	180	(1)
Working with the Table/Query Pane	181	(4)
The join line	181	(1)
Resizing the Table/Query pane	182	(2)
Manipulating the Field List window	184	(1)
Moving a table	184	(1)
Removing a table	184	(1)
Adding more tables	185	(1)
Resizing a Field List window	185	(1)
Adding Fields from More than One Table	185	(3)
Adding a single field	185	(1)
Viewing the table names	186	(1)
Adding multiple fields at the same time	186	(1)
Adding all table fields	187	(1)
Understanding the Limitations of Multiple-Table Queries	188	(3)
Updating limitations	188	(2)
Overcoming query limitations	190	(1)
Creating and Working with Query Joins	191	(4)
Joining tables	192	(1)
Specify the type of join	193	(1)
Deleting joins	194	(1)
Understanding Types of Table Joins	195	(8)
Inner joins (Equi-joins)	195	(1)
Changing join properties	196	(2)
Inner and outer joins	198	(3)
Creating a Cartesian product	201	(2)
Chapter 6: Using Operators and Expressions in Multi-table Select Queries	203	(44)
What Are Operators?	203	(16)
Types of operators	204	(1)
When are operators used?	204	(1)
Mathematical operators	204	(4)
Relational operators	208	(2)
String operators	210	(3)
Boolean (logical) operators	213	(3)
Miscellaneous operators	216	(1)
Operator precedence	217	(2)
Moving beyond Simple Queries	219	(7)
Using query comparison operators	220	(2)
Understanding complex criteria selection	222	(3)
Using functions in select queries	225	(1)
Referencing fields in select queries	226	(1)
Entering Single-Value Field Criteria	226	(9)
Entering character (Text or Memo) criteria	227	(1)
The Like operator and wildcards	228	(4)
Specifying non-matching values	232	(1)
Entering numeric (Number, Currency, or Counter) criteria	233	(1)
Entering Yes/No (logic) criteria	234	(1)
Entering a criterion for an OLE object	235	(1)
Entering Multiple Criteria in One Field	235	(6)
Understanding an Or operation	236	(1)
Specifying multiple values for a field using the Or operator	236	(1)
Using the Or: cell of the QBE pane	237	(1)
Using a list of values with the In operator	238	(1)
Understanding an And query	238	(1)
Specifying a range using the And operator	239	(1)
Using the Between...And operator	239	(1)
Searching for Null data	240	(1)
Entering Criteria in Multiple Fields	241	(5)
Using And and Or across fields in a query	241	(1)
Specifying And criteria across fields of a query	242	(1)
Specifying Or criteria across fields of a query	243	(1)
Using And and Or together in different fields	244	(1)
A complex query on different lines	245	(1)
Creating a New Calculated Field in a Query	246	(1)
Chapter 7: Working with External Data	247	(52)
Access and External Data	247	(5)
Types of external data	248	(1)
Methods of working with external data	248	(1)
Should you link to or import data?	249	(3)
Linking External Data	252	(17)
Types of database management systems	252	(3)
Linking to other Access database tables	255	(2)
Linking to dBASE databases (tables)	257	(2)
Linking to Paradox tables	259	(2)
Linking to non-database tables	261	(6)
Splitting an Access database into two linked databases	267	(2)
Working with Linked Tables	269	(6)
Setting view properties	270	(1)
Setting relationships	270	(1)
Setting links between external tables	271	(1)
Using external tables in queries	271	(2)
Renaming tables	273	(1)
Optimizing linked tables	273	(1)
Deleting a linked table reference	274	(1)
Viewing or changing information for linked tables	274	(1)
Importing External Data	275	(20)
Importing other Access objects	276	(1)
Importing non-Access PC-based database tables	277	(2)
Importing spreadsheet data	279	(3)
Importing from word-processing files	282	(1)
Importing text file data	282	(11)
Importing HTML tables	293	(1)
Modifying imported table elements	293	(1)
Troubleshooting import errors	294	(1)
Exporting to External Formats	295	(6)
Exporting objects to other Access databases	296	(1)
Exporting objects to other external databases or to Excel, HTML, or text files	296	(3)
Part I: Creating Desktop Applications	299	(354)
Section II: Building Forms and Reports	299	(354)
Chapter 8: Understanding the Many Uses of Forms and Controls	301	(20)
Understanding Forms	301	(1)
What are the basic types of forms?	302	(3)
How do forms differ from datasheets?	305	(1)
Creating a form with AutoForm	306	(3)
Understanding Form Controls	309	(1)
What Is a Control?	309	(3)
The different control types	310	(2)
Standards for Using Controls	312	(9)
Label controls	313	(1)
Text box controls	314	(1)
Toggle buttons, option buttons, and check boxes	314	(2)
Option groups	316	(1)
List boxes	317	(1)
Combo boxes	318	(1)
Tab controls	318	(3)
Chapter 9: Building and Manipulating Forms and Controls	321	(28)
Creating a Form with Form Wizards	321	(6)
Creating a new form	321	(1)
Selecting the New Form type and data source	322	(1)
Choosing the fields	323	(1)
Choosing the form layout	324	(1)
Choosing the style of the form	325	(1)
Creating a form title	326	(1)
Completing the form	327	(1)
Changing the Design	327	(2)
Using the Form Window	329	(2)
The Form toolbar	329	(1)
Navigating between fields	329	(1)
Moving between records in a form	330	(1)
Displaying Your Data with a Form	331	(2)
Working with pictures and OLE objects	331	(1)
Memo field data entry	332	(1)
Switching to a datasheet	332	(1)
Saving a Record and the Form	333	(1)
Printing a Form	333	(2)
Using the Print Preview window	334	(1)
Creating New Controls	335	(5)
Resizing the form area	336	(1)
The two ways to add a control	336	(1)
Dragging a field name from the Field List window	337	(1)
Creating unbound controls with the toolbox	338	(2)
Selecting Controls	340	(2)
Deselecting selected controls	341	(1)
Selecting a single control	341	(1)
Selecting multiple controls	341	(1)
Manipulating Controls	342	(7)
Resizing a control	342	(1)
Moving a control	343	(1)
Aligning controls	344	(1)
Sizing controls	345	(1)
Grouping controls	346	(1)
Deleting a control	347	(1)
Attaching a label to a control	347	(1)
Copying a control	348	(1)
Chapter 10: Creating Bound Forms and Placing Controls	349	(42)
Creating a Data-Entry Form without a Wizard	349	(23)
Creating a new blank form	350	(1)
Resizing the form's workspace	350	(3)
Understanding the design windows	353	(2)
Creating a bound form	355	(1)
Saving the form	356	(1)
Working with control properties	357	(5)
Working with form properties	362	(10)
Placing Bound Fields on a Form	372	(3)
Displaying the field list	373	(1)
Selecting the fields for your form	373	(2)
Adding a Form Header or Footer	375	(1)
Working with Label Controls and Text Box Controls	376	(8)
Creating unattached labels	376	(1)
Modifying the text in a label or text control	377	(1)
Modifying the format of text in a control	378	(1)
Sizing a text box control or label control	379	(2)
Deleting a control	381	(1)
Moving label and text controls	381	(1)
Modifying the appearance of multiple controls	382	(1)
Changing the control type	383	(1)
Setting the Tab Order	384	(2)
Using Multiple-Line Text Box Controls for Memo Fields	386	(1)
Working With Bound Object Frames on a Form	387	(1)
Creating a Calculated Field	387	(1)
Fixing a Picture's Display	388	(1)
Printing a Form	389	(1)
Converting a Form to a Report	390	(1)
Chapter 11: Adding Data-Validation Features to Forms	391	(30)
Creating Data-Validation Expressions	392	(3)
Creating status line messages	393	(1)
Entering table level validation expressions	394	(1)
Entering validation expressions	394	(1)
Creating Choices with Option Groups and Buttons	395	(8)
Creating option groups	397	(6)
Creating Yes/No Options	403	(2)
Crating check boxes	403	(2)
Creating Visual Selections with Toggle Buttons	405	(1)
Adding a bitmapped image to the toggle button	405	(1)
Working with List Boxes and Combo Boxes	406	(1)
The Difference between list boxes and combo boxes	406	(1)
Settling real-estate issues	407	(1)
Creating and Using Combo Boxes	407	(14)
Creating a single-column combo box	408	(3)
Understanding combo box properties	411	(1)
Creating a multiple-column combo box	412	(9)
Chapter 12: Creating Professional-Looking Forms and Reports	421	(22)
Making a Good Form Look Great	421	(6)
Understanding visual design	422	(1)
Using the formatting windows and toolbar	423	(2)
Creating special effects	425	(2)
Changing the forms background color	427	(1)
Enhancing Text Based Controls	427	(6)
Enhancing label and text box controls	427	(1)
Creating a text shadow	428	(1)
Changing text to a reverse video display and coloring it	429	(1)
Displaying label or text box control properties	430	(3)
Displaying Images in Forms	433	(3)
Working with Lines and Rectangles	436	(1)
Emphasing Areas of the Form	436	(3)
Adding a shadow to a control	438	(1)
Raising a group of controls	438	(1)
Changing the header dividing line	439	(1)
Adding a Background Bitmap	439	(1)
Using AutoFormat	440	(3)
Customizing and adding new AutoFormats	441	(1)
Copying individual formats between controls	442	(1)
Chapter 13: Understanding and Creating Reports	443	(60)
Understanding Reports	443	(4)
What types of reports can you create?	443	(3)
The difference between reports and forms	446	(1)
The process of creating a report	447	(11)
Creating a Report with Report Wizards	448	(1)
Creating a new report	449	(1)
Choosing the data source	450	(1)
Choosing the fields	450	(1)
Selecting the grouping levels	451	(1)
Defining the group data	452	(1)
Selecting the sort order	453	(1)
Selecting summary options	453	(1)
Selecting the layout	454	(1)
Choosing the style	454	(1)
Opening the report design	455	(1)
Using the Print Preview window	456	(1)
Viewing the Report Design window	457	(1)
Printing a Report	458	(1)
Saving the Report	458	(1)
Starting with a Blank Form	459	(2)
The Design Window toolbar	460	(1)
Banded Report Writer Concepts	461	(6)
How sections process data	462	(2)
The Report Writer sections	464	(3)
Creating a New Report	467	(27)
Creating a new report and binding it to a query	468	(1)
Defining the report page size and layout	469	(3)
Placing fields on the report	472	(2)
Resizing a section	474	(1)
Working with unattached label controls and text	475	(2)
Working with text boxes and their attached label controls	477	(7)
Changing label and text box control properties	484	(2)
Growing and shrinking text box controls	486	(1)
Sorting and grouping data	487	(6)
Adding page breaks	493	(1)
Making the Report Presentation Quality	494	(1)
Adjusting the Page Header	495	(2)
Creating an expression in the Group Header	497	(1)
Changing the picture properties and the Detail section	497	(1)
Creating a standard page footer	498	(2)
Saving your report	500	(3)
Chapter 14: Working with Subforms	503	(36)
What Is a Subform?	503	(4)
Understand the data for the sales example	505	(2)
Creating Subforms with the Form Wizard	507	(10)
Creating the form and selecting the Form Wizard	508	(1)
Choosing the fields for the main form	508	(1)
Selecting the table or query that will be the subform	508	(2)
Choosing the fields for the subform	510	(1)
Selecting the form data layout	510	(1)
Selecting the subform layout	511	(1)
Selecting the form style	512	(1)
Selecting the form title	512	(1)
Displaying the form	512	(2)
Displaying the main form design	514	(1)
Linking a form and subform	515	(1)
Displaying the subform design	516	(1)
Creating the Sales Invoice Form	517	(7)
Creating a combo box that retrieves data	518	(2)
Displaying data from another table in a form	520	(4)
Creating a Subform Without Wizards	524	(15)
Working with Continuous Form subforms	525	(6)
Adding the subform to the main form	531	(2)
Linking the form and subform	533	(1)
Referencing controls in subforms	534	(3)
Creating a simple calculated control	537	(2)
Chapter 15: Creating Calculations and Summaries in Reports	539	(36)
Designing a Full-Page Report with Embedded Subforms and Totals	539	(13)
Designing and creating the query for the report	540	(1)
Designing test data	541	(2)
Examining the Invoice report design	543	(2)
Adding an unbound picture to the report	545	(2)
Adding the Payments subform	547	(3)
Creating a subreport reference to a summary control	550	(2)
Creating a Multilevel Grouping Report with Totals	552	(23)
Creating a total query	553	(4)
Creating a query that uses a query	557	(1)
Creating a new columnar report	558	(5)
Changing the report design	563	(7)
Changing the report margins and page setup	570	(1)
Calculating percentages using totals	571	(1)
Calculating running sums	572	(1)
Creating a title page in a report header	573	(2)
Chapter 16: Presenting Data with Special Report Types	575	(28)
Creating Mailing Labels Using the Label Wizard	575	(10)
Selecting the label size	576	(2)
Selecting the font and color	578	(1)
Creating the mailing label text and fields	578	(2)
Sorting the mailing labels	580	(1)
Displaying the labels in the Print Preview window	580	(2)
Modifying the label design in the Report Design window	582	(2)
Printing labels	584	(1)
Creating Snaked-Column Reports	585	(4)
Creating the report	586	(1)
Defining the page setup	587	(1)
Printing the snaked-column report	588	(1)
Creating Mail Merge Reports	589	(8)
Assembling data for a mail merge report	589	(3)
Creating a mail merge report	592	(1)
Creating the page header area	592	(2)
Working with embedded fields in text	594	(3)
Printing the mail merge report	597	(1)
Using the Access Mail Merge Wizard for Microsoft Word	597	(6)
Chapter 17: Using OLE Objects, Graphs, Pivot Tables/Charts, and ActiveX Controls	603	(50)
Understanding Objects	603	(4)
Types of objects	604	(1)
Using bound and unbound objects	604	(1)
Linking and embedding	605	(2)
Embedding Objects	607	(7)
Embedding an unbound object	607	(4)
Embedding bound objects	611	(1)
Adding a bound OLE object	612	(1)
Adding a picture to a bound object frame	613	(1)
Editing an embedded object	613	(1)
Linking Objects	614	(3)
Linking a bound object	615	(2)
Creating a Graph or Chart	617	(1)
The different ways to create a graph	617	(1)
Customizing the Toolbox	618	(1)
Embedding a Graph in a Form	618	(8)
Assembling the data	619	(1)
Adding the graph to the form	619	(7)
Customizing a Graph	626	(12)
Understanding the Graph window	627	(1)
Working with attached text	628	(3)
Changing the graph type	631	(2)
Changing axis labels	633	(1)
Changing a bar color, pattern, and shape	633	(1)
Modifying gridlines	634	(1)
Manipulating three-dimensional graphs	635	(3)
Integration with Microsoft Office	638	(11)
Checking the spelling of one or more fields and records	638	(1)
Correcting your typing automatically when entering data	639	(1)
Using OLE automation with Office 2003	640	(1)
Creating an Excel type PivotTable	640	(6)
Creating a PivotChart	646	(3)
Using the Calendar ActiveX Control	649	(4)
Part I: Creating Desktop Applications	653	(230)
Section III: Automating Your Applications	653	(230)
Chapter 18: Understanding Visual Basic and the VBA Editor	655	(24)
Migrating from Macros to Visual Basic	655	(7)
When to use macros and when to use Visual Basic procedures	656	(1)
Converting existing macros to Visual Basic	657	(3)
Using the Command Button Wizard to create Visual Basic code	660	(2)
Creating Programs in Visual Basic for Applications	662	(17)
Understanding events and event procedures	663	(2)
Understanding modules	665	(2)
Creating a new module	667	(12)
Chapter 19: Introduction to Programming and Events	679	(34)
Programming Events	679	(2)
How do events trigger actions?	680	(1)
Where to trigger event procedures	681	(1)
Form Event Procedures	681	(4)
Control Event Procedures	685	(8)
Opening a form with an event procedure	686	(2)
Running an event procedure when closing a form	688	(1)
Using an event procedure to confirm a delete	689	(4)
Report Event Procedures	693	(2)
Running an event procedure when a report opens	694	(1)
Report Section Event Procedures	695	(2)
Using On Format	695	(2)
Using Variables	697	(3)
Naming variables	697	(1)
Declaring variables	698	(2)
Working with Data Types	700	(3)
Understanding Visual Basic Logical Constructs	703	(10)
Conditional processing	703	(4)
Repetitive looping	707	(6)
Chapter 20: Working with Expressions and Functions	713	(24)
What Are Expressions?	713	(9)
The parts of an expression	715	(1)
Creating an expression	716	(2)
Special identifier operators and expressions	718	(3)
Special keywords and properties	721	(1)
What Are Functions?	722	(15)
Using functions in Access	722	(1)
Types of functions	723	(14)
Chapter 21: Working with SQL, Recordsets, and ADO	737	(20)
Understanding SQL	737	(7)
Viewing SQL statements in queries	738	(1)
An SQL primer	738	(6)
Creating Programs to Update a Table	744	(13)
Updating fields in a record using ADO	745	(3)
Updating a calculated field for a record	748	(4)
Adding a new record	752	(1)
Deleting a record	753	(1)
Deleting related records in multiple tables	753	(4)
Chapter 22: Automating, Searches, Filters, and Query Parameters	757	(14)
Adding an Unbound Combo Box to Select One or More Records	757	(3)
Using the FindRecord Command to Locate a Record	760	(2)
Using the Bookmark to Locate a Record	762	(2)
Filtering a Form Using Code	764	(2)
Using a Query to Filter a Form Interactively	766	(5)
Creating a parameter query	766	(1)
Creating an interactive dialog box	767	(2)
Linking the dialog box to another form	769	(2)
Chapter 23: Calling Subprocedures and Functions	771	(12)
Understanding the Difference Between a Subprocedure and a Function	771	(4)
Understanding where to create a procedure	772	(1)
Calling procedures and functions	772	(1)
Creating a procedure	773	(2)
Creating Functions	775	(5)
Handling passed parameters	777	(1)
Calling a function and passing parameters	778	(2)
Creating a Function to Calculate Taxes	780	(3)
Chapter 24: Effective Debugging and Error Handling in VBA	783	(22)
Testing and Debugging Your Applications	784	(1)
VBA Assistance: Auto Quick Info and Auto List Members	785	(2)
Syntax checking - The first step	786	(1)
Compiling Procedures	787	(2)
Handling Runtime Errors	789	(2)
Using the Immediate, Locals, and Watches Windows	791	(1)
Creating a Breakpoint	792	(3)
Errors	795	(7)
Types of errors	795	(1)
The elements of error handling	796	(1)
VBA error statements	796	(6)
Logging Errors	802	(3)
Chapter 25: Creating Switchboards, Command Bars, Menus, Toolbars, and Dialog Boxes	805	(60)
Switchboards and Command Buttons	806	(20)
Using a switchboard	807	(1)
Creating the basic form for a switchboard	807	(2)
Working with command buttons	809	(2)
Creating command buttons	811	(7)
Linking a command button to a macro	818	(6)
Adding a picture to a command button	824	(2)
Working with the Switchboard Manager	826	(9)
Creating the Report Switchboard	827	(8)
Creating Custom Menu Bars, Toolbars, and Shortcut Menus	835	(22)
Understanding command bars	836	(1)
Creating custom menu bars with command bars	837	(1)
Changing existing menus and toolbars	838	(1)
Creating a new menu bar	838	(8)
Attaching the menu bar to a form	846	(1)
Creating shortcut menus	847	(4)
Creating and using custom toolbars	851	(2)
Attaching the toolbar to a form	853	(2)
Adding control tips to any form control	855	(1)
Starting the switchboard automatically when you open the database	855	(2)
Creating a Print Report Dialog Box Form in Visual Basic	857	(8)
Creating a form for printing products	857	(2)
Creating the option group	859	(1)
Creating two text boxes on the print report form	859	(1)
Creating command buttons	860	(1)
Creating the Print event procedures	861	(4)
Chapter 26: Programming Continuous Forms, Tab Dialogs, and Command Buttons	865	(18)
Working with Tab Controls	866	(6)
Creating a tab control	867	(3)
Changing the tab control page properties	870	(1)
Copying controls from a Detail section to a tab control page	871	(1)
Programming Continuous Forms	872	(8)
Embedding a subform in a tab control	873	(3)
Creating code to delete a record from a continuous form	876	(2)
Creating code to reposition the record pointer between tabs	878	(1)
Creating code to sort data columns using labels	879	(1)
Common Code for Common Functions	880	(3)
Part II: Creating Enterprise Applications	883	(62)
Section IV: Upsizing to SQL Server and MSDE 2000	883	(62)
Chapter 27: Upsizing Data to a SQL Server Database	885	(18)
Understanding MSDE 2000	885	(4)
Comparing MSDE 2000 and Jet	886	(1)
Choosing the right database engine	886	(3)
Installing MSDE 2000	889	(3)
Hardware requirements	889	(1)
Software requirements	889	(1)
Running the SQL Server 2000 Desktop Engine Installation Program	889	(2)
Customizing the installation of SQL Server 2000 Desktop Engine	891	(1)
Starting the SQL Server 2000 Desktop Engine	892	(1)
Using the Upsizing Wizard	893	(10)
Before upsizing an application	894	(1)
Starting the Upsizing Wizard	895	(8)
Chapter 28: Working with Access Projects	903	(18)
Upsizing to an Access Project	904	(6)
Starting the Upsizing Wizard	904	(2)
Using the Upsizing Wizard to create a Client/Server Application	906	(4)
Using Unbound Forms	910	(11)
Working with unbound forms	911	(1)
Creating an unbound form	912	(1)
Displaying data on the form	913	(3)
Updating data	916	(3)
Finding a record	919	(2)
Chapter 29: Working with Access Projects and SQL Server Tables and Queries	921	(24)
Determining a Project's Database Name	922	(1)
Working with Tables	923	(10)
Working with fields in the Table Design window	923	(2)
The Table Properties window	925	(8)
Understanding Project Queries	933	(14)
Creating views	933	(2)
Creating stored procedures	935	(3)
Creating user-defined functions	938	(4)
Using triggers to automatically update data	942	(3)
Part III: Creating Web Applications	945	(136)
Section V: Creating Data Access Pages and Using XML and InfoPath	945	(136)
Chapter 30: Using and Creating Access Objects for Intranets and the Internet	947	(34)
Types of Web Pages That Access Can Create	948	(6)
Data access pages	948	(2)
Working with dynamic and static views of Web-based data	950	(4)
Exporting Tables, Queries, Forms, and Reports to Web Pages	954	(17)
Exporting an Access table to static HTML format	954	(2)
Exporting an Access query datasheet to static HTML format	956	(2)
Exporting an Access form datasheet to static HTML format	958	(3)
Changing Page Setup properties for datasheets	961	(1)
Exporting a datasheet to dynamic HTML format	961	(2)
Exporting a form to dynamic HTML format	963	(1)
Processing an IDC/HTX file on the Web server	964	(1)
Processing ASP files on the Web server	965	(1)
Exporting a report to static HTML format	966	(2)
HTML template files	968	(3)
Importing and Linking (Read-Only) HTML Tables and Lists	971	(3)
Importing an HTML table	972	(2)
Linking to an HTML table	974	(1)
Using Hyperlinks to Connect Your Application to the Internet	974	(7)
Using the Hyperlink data type	975	(1)
Adding a hyperlink to a form, report, or datasheet	976	(2)
Creating a label using the Insert Hyperlink button	978	(3)
Chapter 31: Building and Working with Data Access Pages	981	(56)
Working with the Data Access Pages	982	(55)
What is a data access page?	982	(3)
Creating a single table data access page	985	(11)
Working with multiple tables and grouped pages	996	(18)
Changing some key properties on data access pages	1014	(11)
Saving other Access objects as data access pages	1025	(12)
Chapter 32: XML, Access, and InfoPath	1037	(46)
XML Data and Access	1037	(1)
Understanding XML	1038	(5)
What is XML?	1040	(1)
What are XML schemas?	1041	(1)
Using XSL to display XML data	1041	(1)
Using XSLT with XML data	1042	(1)
Creating Your Own XML Documents	1043	(7)
The process of creating a simple XML document	1043	(1)
Deciding on the tags for an XML document	1044	(1)
Deciding on the data for an XML document	1045	(1)
Putting the tags and data together	1046	(2)
Creating a multi-table XML document	1048	(2)
Displaying XML Documents in Internet Explorer	1050	(2)
Exporting to XML	1052	(7)
Exporting a table or query to XML	1053	(2)
Exporting linked tables	1055	(1)
Exporting a form to XML	1055	(3)
Exporting a report to XML	1058	(1)
Importing XML Data	1059	(3)
Importing simple, single table XML data	1059	(2)
Importing a single table with OLE Object from an XML document	1061	(1)
Importing multi-table XML data	1061	(1)
InfoPath and Access	1062	(22)
Quick overview of InfoPath	1063	(2)
Creating an XML-Based InfoPath form	1065	(5)
Creating an InfoPath form attached to a database	1070	(5)
Working with an InfoPath form	1075	(3)
Importing an InfoPath XML document into Access	1078	(3)
Part IV: Advanced Access Database Topics	1081	(250)
Chapter 33: Exchanging Data with Office Applications	1083	(18)
Using Automation to Integrate with Office	1084	(7)
Creating Automation references	1084	(3)
Creating an instance of an Automation object	1087	(1)
Getting an existing object instance	1088	(2)
Working with Automation objects	1090	(1)
Closing an instance of an Automation object	1090	(1)
An Automation Example Using Word	1091	(10)
Creating an instance of a Word object	1095	(1)
Making the instance of Word visible	1095	(1)
Creating a new document based on an existing template	1096	(1)
Using Bookmarks to insert data	1096	(1)
Activating the instance of Word	1096	(1)
Moving the cursor in Word	1097	(1)
Closing the instance of the Word object	1097	(1)
Inserting pictures by using Bookmarks	1097	(1)
Using Office's Macro Recorder	1098	(3)
Chapter 34: Adding Security to Applications	1101	(42)
Understanding Jet Security	1101	(3)
Understanding workgroup files	1102	(1)
Understanding permissions	1102	(1)
Understanding security limitations	1103	(1)
Choosing a Security Level to Implement	1104	(1)
Creating a Database Password	1104	(3)
Using the /runtime Option	1107	(3)
Using a Database's Startup Options	1110	(1)
Using the Jet User-Level Security Model	1111	(18)
Enabling security	1111	(1)
Working with workgroups	1112	(3)
Working with users	1115	(4)
Working with groups	1119	(4)
Securing objects by using permissions	1123	(6)
Using the Access Security Wizard	1129	(7)
Encrypting a Database	1136	(1)
Decrypting a Database	1137	(1)
Protecting Visual Basic Code	1137	(2)
Preventing Virus Infections	1139	(4)
Enabling sandbox mode	1139	(4)
Chapter 35: Creating Help Systems	1143	(36)
Understanding the Windows Help Structure	1143	(4)
The Help Viewer	1144	(1)
The Contents tab	1145	(1)
The Topic pane	1146	(1)
Creating a Windows Help System	1147	(20)
Creating Help topics	1148	(1)
Creating a Help project file	1149	(9)
Creating a table of contents	1158	(4)
Creating a Help Index	1162	(5)
Integrating a Help File with Your Application	1167	(12)
Displaying form-level Help	1167	(1)
Displaying control-level Help	1168	(1)
Mapping a Help Context ID to a Help topic	1169	(3)
Testing the HTMLHe1p API	1172	(1)
Testing Help in Access	1173	(1)
Displaying the Table of Contents	1174	(5)
Chapter 36: Working with Advanced Select Queries and Other Query Topics	1179	(50)
Using Calculated Fields	1180	(7)
Calculated fields and the Expression Builder	1182	(3)
Creating complex calculated fields	1185	(2)
Finding the Number of Records in a Table or Query	1187	(2)
Finding the Top (n) Records in a Query	1189	(2)
How Queries Save Field Selections	1191	(4)
Hiding (not showing) fields	1191	(1)
Renaming fields in queries	1192	(1)
Hiding and unhiding columns in the QBE pane	1193	(2)
Query Design Options	1195	(2)
Setting Query Properties	1197	(2)
Creating Queries That Calculate Totals	1199	(17)
Showing and hiding the Total: row in the QBE pane	1200	(1)
The Total: row options	1201	(4)
Performing totals on all records	1205	(1)
Performing totals on groups of records	1206	(4)
Specifying criteria for a total query	1210	(3)
Creating expressions for totals	1213	(3)
Creating Crosstab Queries	1216	(13)
Understanding the crosstab query	1216	(1)
Creating the crosstab query	1217	(2)
Entering multiple-field row headings	1219	(2)
Specifying criteria for a crosstab query	1221	(3)
Specifying fixed column headings	1224	(2)
The Crosstab Query wizard	1226	(3)
Chapter 37: Working with Action and SQL Queries	1229	(36)
About Action Queries	1229	(4)
Types of action queries	1230	(1)
Uses of action queries	1230	(1)
The process of action queries	1231	(1)
Viewing the results of an action query	1231	(1)
Reversing action queries	1232	(1)
Creating Action Queries	1233	(26)
Creating an update action query to change values	1233	(5)
Creating a new table using a make-table query	1238	(4)
Creating queries to append records	1242	(8)
Creating a query to delete records	1250	(5)
Creating other queries using the Query wizards	1255	(3)
Saving an action query	1258	(1)
Running an action query	1258	(1)
Troubleshooting Action Queries	1259	(1)
Data-type errors in appending and updating	1259	(1)
Key violations in action queries	1259	(1)
Record-locked fields in multi-user environments	1259	(1)
Text fields	1259	(1)
SQL-Specific Queries	1260	(5)
Creating union queries	1260	(1)
Creating pass-through queries	1261	(1)
Creating data definition queries	1262	(1)
Creating SQL subqueries in an Access query	1263	(2)
Chapter 38: Increasing the Speed of an Application	1265	(44)
Understanding Module Load on Demand	1265	(3)
Organizing your modules	1266	(1)
Access 2003 prunes the call tree	1266	(2)
Using the Access 2002-2003 Database File Format	1268	(2)
Distributing .MDE Files	1270	(2)
Understanding the Compiled State	1272	(7)
Putting your application's code into a compiled state	1273	(1)
Losing the compiled state	1274	(1)
Distributing applications in a compiled or uncompiled state	1275	(4)
Improving Absolute Speed	1279	(14)
Tuning your system	1281	(1)
Getting the most from your tables	1281	(3)
Getting the most from your queries	1284	(1)
Getting the most from your forms and reports	1285	(3)
Getting the most from your modules	1288	(5)
Increasing Network Performance	1293	(1)
Improving Perceived Speed	1293	(7)
Using a splash screen	1293	(1)
Loading and keeping forms hidden	1294	(1)
Using the hourglass	1295	(1)
Using the built-in progress meter	1295	(2)
Creating a progress meter with a pop-up form	1297	(2)
Speeding up the progress meter display	1299	(1)
Working with Large Program Databases in Access 2003	1300	(9)
How databases grow in size	1300	(1)
Simply compiling and compacting may not be enough	1301	(1)
Rebooting gives you a clean memory map	1302	(1)
Repair does nothing if the database is not corrupt	1302	(1)
You can fix a single corrupt form by removing the record source	1302	(1)
Create a new database and import all of the objects	1302	(1)
The decompile option in Access 2003	1303	(1)
Summary-six steps to large database success	1304	(1)
An interface for detecting an uncompiled database and automatically recompiling	1305	(2)
Making small changes to large databases-export	1307	(2)
Chapter 39: Preparing Your Application for Distribution	1309	(22)
Defining the Startup Parameters of the Application	1310	(4)
Application Title	1310	(1)
Application Icon	1310	(1)
Menu Bar	1311	(1)
Allow Full Menus	1311	(1)
Allow Default Shortcut Menus	1311	(1)
Display Form/Page	1311	(1)
Display Database Window	1311	(1)
Display Status Bar	1311	(1)
Shortcut Menu Bar	1312	(1)
Allow Built-in Toolbars	1312	(1)
Allow Toolbar/Menu Changes	1312	(1)
Use Access Special Keys	1312	(1)
Testing the application before distribution	1313	(1)
Polishing Your Application	1314	(5)
Giving your application a consistent look and feel	1314	(2)
Adding common professional components	1316	(3)
Creating comprehensive and intuitive menus and toolbars	1319	(1)
Bulletproofing an Application	1319	(2)
Using error trapping on all Visual Basic procedures	1319	(1)
Separating the code objects from the tables in the application	1320	(1)
Documenting the application	1320	(1)
Creating a help system	1321	(1)
Implementing a security structure	1321	(1)
The Access 2003 Developer Extensions	1321	(10)
Using the Package Wizard	1322	(8)
Working with the Property Scanner Add-In	1330	(1)
Part V: Appendixes and Reference Material	1331	(32)
Appendix A: Access 2003 Specifications	1333	(6)
Appendix B: Access Auto Auction Tables	1339	(6)
Appendix C: Using the CD-ROM Included with the Book	1345	(10)
Appendix D: Using Standard Naming Conventions	1355	(8)
Index	1363