Running Microsoft Access 2000

John L. Viescas is a database expert who provides consulting and training services to companies around the globe. He is the author of numerous books, including Running Microsoft Access 97, Running Microsoft Access 2000, Microsoft Office Access 2003 Inside Out, Building Microsoft Access Applications, and Microsoft Office Access 2007 Inside Out. John has been recognized as a Microsoft MVP since 1993. Home Page: Viescas Consulting, Inc.

Table of Contents Acknowledgments xxiii Using the Companion CD xxv Introduction xxvii Part I Understanding Microsoft Access 1 Chapter 1 Microsoft Access Is a Database and More 3 What Is a Database? 4 Relational Databases 4 Database Capabilities 6 Microsoft Access as an RDBMS 6 Data Definition and Storage 6 Data Manipulation 9 Data Control 12 Microsoft Access as Something More 13 Developing Application Logic 13 Deciding to Move to Database Software 15 Chapter 2 The Uses of Microsoft Access 19 In a Small Business 20 In Contract Work 22 As a Sales and Marketing Tool 23 In a Large Corporation 24 Workgroup Applications 27 Information Processing Systems 27 As a Personal RDBMS 28 Chapter 3 Touring Microsoft Access 31 Windows Features 31 The Architecture of Microsoft Access 34 Exploring the Entertainment Scheduling Database 37 Tables 40 Table Window in Design View 42 Table Window in Datasheet View 45 Queries 47 Query Window in Design View 49 Query Window in Datasheet View 50 Forms 51 Form Window in Design View 54 Form Window in Form View 56 Reports 57 Report Window in Design View 60 Report Window in Print Preview 62 Data Access Pages 63 Data Access Page Window in Design View 65 Data Access Page Window in Page View 68 Macros 69 Modules 72 Organizing Your Objects 74 Part II Building a Database 77 Chapter 4 Designing Your Database Application 79 Application Design Fundamentals 80 Step 1: Identifying Tasks 80 Step 2: Charting Task Flow 81 Step 3: Identifying Data Elements 81 Step 4: Organizing the Data 81 Step 5: Designing a Prototype and a User Interface 81 Step 6: Constructing the Application 82 Step 7: Testing, Reviewing, and Refining 82 An Application Design Strategy 83 Analyzing the Tasks 85 Data or Information? 86 Selecting the Data 87 Organizing Tasks 89 Data Analysis 90 Choosing the Database Subjects 90 Mapping Subjects to Your Database 92 Database Design Concepts 92 Waste Is the Problem 93 Normalization Is the Solution 95 Efficient Relationships Are the Result 101 When to Break the Rules 103 Improving Performance of Critical Tasks 103 Capturing Point-In-Time Data 104 Report Snapshot Data 105 Chapter 5 Building Your Database in Microsoft Access 107 Creating a New Database 108 Using the Database Wizard 108 Creating a New Empty Database 112 Using Microsoft Access Help 114 Creating Your First Simple Table 120 Using the Table Wizard 123 Creating a Table in Design View 127 Defining Fields 128 Field Data Types 130 Field Properties 133 Completing the Fields in the Orders Table 138 Defining Simple Field Validation Rules 140 Defining Input Masks 142 Defining a Primary Key 146 Defining a Table Validation Rule 147 Defining Relationships 149 Defining Your First Relationship 150 Adding Indexes 153 Single Field Indexes 153 Multiple Field Indexes 154 Printing a Table Definition 156 Database Limitations 157 Chapter 6 Modifying Your Database Design 159 Before You Get Started 160 Making a Backup Copy 161 Deleting Tables 162 Renaming Tables 164 Changing Field Names 165 Inserting Fields 167 Copying Fields 169 Deleting Fields 170 Moving Fields 171 Changing Data Attributes 173 Changing Data Types 173 Changing Data Length 177 Conversion Errors 177 Reversing Changes 178 Using the Table Analyzer Wizard 179 Taking Advantage of Lookup Properties 185 Changing the Primary Key 189 Compacting Your Database 191 Part III Working with Data 193 Chapter 7 Using Datasheets 195 Viewing Data 196 Moving Around 196 Keyboard Shortcuts 197 Modifying the Datasheet Format 198 Changing Row Height and Column Width 199 Arranging Columns 200 Hiding and Showing Columns 201 Freezing Columns 202 Removing Gridlines 202 Selecting Fonts 204 Saving Datasheet Formatting 205 Working with Subdatasheets 206 Changing Data 208 Record Indicators 208 Adding a New Record 209 Selecting and Changing Data 210 Replacing Data 212 Copying and Pasting Data 213 Deleting Rows 214 Working with Hyperlinks 215 Activating a Hyperlink 216 Inserting a New Hyperlink 217 Editing an Existing Hyperlink 218 Sorting and Searching for Data 219 Sorting Data 219 Searching For and Filtering Data 220 Printing a Datasheet 225 Print Preview 226 Page Setup 228 Printing 228 Chapter 8 Adding Power with Select Queries 231 Selecting Data from a Single Table 233 Specifying Fields 235 Setting Field Properties 236 Entering Selection Criteria 238 Calculating Values 243 Using the Expression Builder 246 Specifying Field Names 251 Sorting Data 252 Total Queries 253 Using Query Parameters 257 Crosstab Queries 259 Searching Multiple Tables 263 Outer Joins 265 Building a Query on a Query 267 Using Multiple Tables in Total Queries 269 Using a Query Wizard 270 Limitations on Using Select Queries to Update Data 273 Customizing Query Properties 274 Controlling Query Output 275 Working with Unique Records and Values 275 Other Query Properties 278 Chapter 9 Modifying Data with Action Queries 281 Updating Groups of Rows 281 Testing with a Select Query 282 Converting a Select Query to an Update Query 283 Running an Update Query 284 Updating Multiple Fields 286 Creating a New Table 286 Creating a Make-Table Query 287 Running a Make-Table Query 288 Inserting Data from Another Table 290 Creating an Append Query 290 Running an Append Query 293 Example 1: Using an Append Query to Archive Data 294 Troubleshooting Action Queries 298 Common Action Query Errors and Problems 298 An Error Example 299 Deleting Groups of Rows 300 Testing with a Select Query and Parameters 300 Using a Delete Query 302 Deleting Inactive Data 302 Example 2: Updating/Appending Rows from Imported Data 304 Chapter 10 Importing, Linking, and Exporting Data 309 A Word About Open Database Connectivity 309 Importing vs. Linking Database Files 311 Importing Data and Databases 312 Importing dBASE Files 313 Importing Paradox Files 315 Importing SQL Tables 316 Importing Access Objects 321 Importing Spreadsheet Data 323 Preparing a Spreadsheet 323 Importing a Spreadsheet 324 Fixing Errors 328 Importing Text Files 330 Preparing a Text File 330 Importing a Text File 332 Fixing Errors 336 Modifying Imported Tables 338 Linking Files 338 Security Considerations 339 Performance Considerations 339 Linking Access Tables 341 Linking dBASE and Paradox Files 342 Linking Text and Spreadsheet Files 344 Linking SQL Tables 345 Modifying Linked Tables 346 Unlinking Linked Tables 347 Using the Linked Table Manager 347 Exporting Data 348 Exporting to Another Access Database 348 Exporting to a Spreadsheet or to a dBASE or Paradox File 350 Quick Export to Microsoft Excel 350 Exporting to a Text File 351 Exporting to a Mail Merge Document in Microsoft Word 352 Exporting to an SQL Table 354 Chapter 11 Advanced Query Design SQL 357 A Brief History of SQL 358 SQL Syntax Conventions 359 SQL SELECT Syntax in Microsoft Access 360 Column-Name 361 Expression 362 FROM Clause 364 GROUP BY Clause 366 HAVING Clause 367 IN Clause 369 JOIN Operation 371 ORDER BY Clause 374 PARAMETERS Declaration 376 Predicate: BETWEEN 377 Predicate: Comparison 378 Predicate: EXISTS 379 Predicate: IN 380 Predicate: LIKE 381 Predicate: NULL 382 Predicate: Quantified 383 Search-Condition 384 SELECT Statement 386 Subquery 398 Total Function: AVG 402 Total Function: COUNT 403 Total Function: MAX 404 Total Function: MIN 406 Total Functions: STDEV, STDEVP 408 Total Function: SUM 409 Total Functions: VAR, VARP 410 TRANSFORM Statement 411 UNION Query Operator 412 WHERE Clause 414 SQL Action Queries 415 DELETE Statement 415 INSERT Statement (Append Query) 416 SELECT ... INTO Statement (Make-Table Query) 418 UPDATE Statement 422 Complex Query Examples 423 Example 1: Returning the Date of Every Monday 424 Example 2: Generating a Week-by-Week Booking List 426 Example 3: Showing Weeks in Which There Are No Bookings 429 Part IV Using Forms 431 Chapter 12 Form Basics 433 Uses of Forms 433 A Tour of Forms 435 Headers, Detail Sections, and Footers 435 Multiple-Page Forms 436 Continuous Forms 436 Subforms 438 Pop-Up Forms 439 Modal Forms 440 Special Controls 440 Moving Around on Forms and Working with Data 447 Viewing Data 447 Adding Records and Changing Data 451 Searching for and Sorting Data 457 Performing a Simple Search 457 Performing a Quick Sort on a Form Field 457 Adding a Filter to a Form 457 Printing Forms 459 Chapter 13 Building Forms 463 Forms and Object-Oriented Programming 463 Starting from Scratch A Simple Input Form 466 Building a New Form with Design Tools 466 Building a Simple Input Form for the tblClubs Table 477 Checking Your Design Results 485 Working with Form Wizards 487 Creating the Basic Orders Form with a Form Wizard 487 Modifying the Orders Form 490 Simplifying Data Input with a Form 492 Combo Boxes and List Boxes 492 Toggle Buttons, Check Boxes, and Option Buttons 497 Chapter 14 Customizing Forms 499 Aligning and Sizing Controls 499 Sizing Controls to Fit Content 501 Adjusting Control Layout 502 "Snapping" Controls to the Grid 503 Lining Up Controls 505 Adjusting Control Width 507 Enhancing the Look of a Form 509 Lines and Rectangles 509 Colors and Special Effects 512 Fonts 513 Setting Control Properties 515 Formatting Properties 515 Adding a Scroll Bar 524 Enabling and Locking Controls 525 Setting Form Properties 526 The Default View and Views Allowed Properties 526 Setting the Tab Order 526 The Record Selectors, Scroll Bars, and Navigation Buttons Properties 527 The Pop Up and Modal Properties 528 Controlling Editing and Filtering 529 The Control Box and Min Max Buttons Properties 530 Setting the Border Style 531 Setting Form and Control Defaults 532 Working with AutoFormat 533 Chapter 15 Advanced Form Design 535 Basing a Form on a Multiple-Table Query 536 A Many-to-One Form 536 Creating and Embedding Subforms 541 Specifying the Subform Source 542 Designing the Subform 543 Specifying the Main Form Source 546 Creating the Main Form 548 Embedding a Subform 548 Using Conditional Formatting 551 Introducing ActiveX Controls The Tab Control 553 Creating Multiple-Page Forms 557 Linking Forms with a Command Button 560 Adding a Command Button 560 Part V Publishing Information 565 Chapter 16 Report Basics 567 Uses of Reports 567 A Tour of Reports 568 Print Preview A First Look 569 Headers, Detail Sections, Footers, and Groups 570 Subreports 572 Objects in Reports 575 Printing Reports 575 Print Setup 575 Chapter 17 Constructing a Report 581 Starting from Scratch A Simple Report 581 Building the Report Query 582 Designing the Report 582 Sorting and Grouping Information 584 Completing the Report 586 Using a Report Wizard 589 Selecting a Report Type 589 Specifying Wizard Options 591 Viewing the Result 596 Chapter 18 Advanced Report Design 599 Building a Query for a Complex Report 600 Defining Sorting and Grouping Criteria 602 Setting Group Ranges by Data Type 603 Creating the Basic Group Contract Status Report 604 Setting Section and Report Properties 608 Section Properties 608 Report Properties 610 Using Calculated Values 615 Adding the Print Date and Page Numbers 615 Performing Calculations 617 Concatenating Text Strings and Hiding Redundant Values 620 Calculating Percentages 623 Using Running Sum 624 Taking Advantage of Conditional Formatting 626 Creating and Embedding a Subreport 627 Building the Subreport Queries 628 Designing the Subreport 632 Embedding the Subreport 633 Viewing the Embedded Result 635 Creating a "Spreadsheet" Report 636 Building a Summary Query 636 Designing a "Summaries Only" Report 637 Chapter 19 Publishing Data on the Web 643 Working with the Web 644 Delivering Static Web Pages 644 Delivering Dynamic Query Results 646 Processing "Live Data" with HTML Forms 648 Discovering the Possibilities 651 Using Data Access Pages 653 Saving Your Data as a Web Page 655 Creating a Static HTML Document 655 Creating a Dynamic Active Server Page 657 Working with Data Access Pages 660 Usefulness and Limitations 661 Types of Pages You Can Create 661 Creating Pages Using the Wizards 662 Modifying Data Access Pages 670 Opening and Saving Data Access Pages 675 Enhancing Your Page with FrontPage 676 Uploading Your Pages to a Web Server 682 Copying Pages Using the Office Web Pages Facility 682 Publishing Pages Using File Transfer 684 Publishing an Entire Web with FrontPage 685 Part VI Creating an Application 687 Chapter 20 Adding Power with Macros 689 Uses of Macros 690 Creating a Simple Macro 692 The Macro Window 692 Saving Your Macro 694 Testing Your Macro 694 Defining Multiple Actions 696 Grouping Macros 698 Conditional Expressions 701 Summary of Macro Actions 703 Opening and Closing Access Objects 703 Printing Data 705 Executing a Query 705 Testing Conditions and Controlling Action Flow 706 Setting Values 707 Searching for Data 708 Building a Custom Menu and Executing Menu Commands 708 Controlling Display and Focus 709 Informing the User of Actions 710 Renaming, Copying, Deleting, Saving, Importing, and Exporting Objects 711 Running Another Application 712 Summary of Form and Report Events 712 Opening and Closing Forms and Reports 713 Changing Data 713 Detecting Focus Changes 716 Detecting Filters Applied to Forms 718 Trapping Keyboard and Mouse Events 718 Printing 720 Activating a Custom Form, Report, or Application Menu 721 Trapping Errors 722 Detecting Timer Expiration 722 Chapter 21 Automating Your Application with Macros 723 Referencing Form and Report Objects 723 Rules for Referencing Forms and Reports 724 Rules for Referencing Form and Report Properties 724 Rules for Referencing Form and Report Controls and Their Properties 725 Rules for Referencing Subforms and Subreports 725 Opening a Secondary Form 726 Creating the SeeCityInformation Macro 726 Synchronizing Two Related Forms 729 Creating the SyncWeddingAndCity Macro 730 Validating Data and Presetting Values 733 Validating Data 734 Passing Status Information Between Linked Forms 739 Presetting Values 741 Converting Your Macros to Visual Basic 745 Chapter 22 Visual Basic Fundamentals 747 Using Visual Basic Instead of Macros 747 When to Use Macros 748 When to Use Visual Basic 748 The Visual Basic Development Environment 749 Modules 749 The Visual Basic Editor Window 751 Working with Visual Basic Debugging Tools 757 Variables and Constants 765 Data Types 766 Variable and Constant Scope 767 Declaring Variables 770 Const Statement 770 Dim Statement 770 Enum Statement 773 Event Statement 774 Private Statement 776 Public Statement 777 ReDim Statement 779 Static Statement 780 Type Statement 781 Collections, Objects, Properties, and Methods 782 The Access Application Architecture 783 The Data Access Objects (DAO) Architecture 785 The ActiveX Data Objects (ADO) Architecture 787 Referencing Collections, Objects, and Properties 789 Assigning an Object Variable Set Statement 792 Object Methods 794 Functions and Subroutines 804 Function Statement 805 Sub Statement 806 Controlling the Flow of Statements 808 Call Statement 808 Do Loop Statement 809 For Next Statement 810 For Each Next Statement 811 GoTo Statement 811 If Then Else Statement 812 Select Case Statement 813 RaiseEvent Statement 815 Stop Statement 815 While Wend Statement 816 With Statement 816 Running Macro Actions and Menu Commands 817 DoCmd Object 817 Actions with Visual Basic Equivalents 818 Trapping Errors 819 On Error Statement 819 Some Complex Visual Basic Examples 820 A Procedure to Randomly "Sell" Books 820 A Procedure to Examine All Error Codes 829 Chapter 23 Automating Your Application with Visual Basic 837 Assisting Data Entry 837 Filling In Related Data 838 Setting a Custom Input Mask 841 Handling the "NotInList" Event 843 Controlling Tabbing on a Multiple-Page Form 848 Automating Data Selection 850 Working with a Multiple-Selection List Box 850 Providing a Custom Query By Form 854 Selecting from a Summary List 864 Linking to Related Data in Another Form or Report 865 Linking Forms Using a Filter 865 Linking to a Report Using a Filter 867 Synchronizing Two Forms Using a Class Event 869 Creating Meaningful Messages and Help 872 Creating Informative MsgBox Messages 873 Using the Office Assistant and Custom Balloon Help 874 Automating Complex Tasks 878 Triggering a Data Task from a Related Form 879 Linking to a Related Task 882 Validating Complex Data 887 Using the Calendar Custom Control 889 Chapter 24 The Finishing Touches 895 Creating a Custom Form Toolbar 895 Defining a New Toolbar 896 Customizing Your New Toolbar 901 Creating a Custom Form Menu Bar 904 Defining a New Menu Bar 904 Fine-Tuning with the Performance Analyzer Wizard 909 Disabling Form Design View 911 Defining the Main Switchboard Form 912 Designing a Switchboard Form 912 Using the Switchboard Manager 914 Setting Startup Properties for Your Database 917 Performing a Final Visual Basic Compile 918 Chapter 25 After Completing Your Application 921 Taking Advantage of Replication 921 Replication Considerations 922 Creating a Replica 923 Synchronizing Replicas 926 Securing Your Database 928 Access Security Architecture 928 Users, Groups, and Permissions 929 Using the Security Wizard 934 Setting Up Your Secured Database 946 Sharing Your Application with Multiple Users 952 Sharing an MDB File 952 "Upsizing" Your Application to Microsoft SQL Server 954 Creating an Execute-Only Database 976 Creating an Application Shortcut 977 Appendix Installing Microsoft Office 981 Installing the Microsoft Data Engine (MSDE) 986 Managing ODBC Connections 988 Converting from a Previous Release 996 Index 999 This is Black This is Color