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