Access 2002 Programming by Example

Bob Villareal has been working with microcomputers since 1981 and has nearly 10 years experience with VBA programming in both Access and Excel. Bob has written many tracking and management applications and has been developing databases for a large insurance firm for well over a decade. Bob also does freelance programming and instruction. He is a contributor to the monthly publication and Web journal, Inside Microsoft Access, as well as a Microsoft Access instructor at Tulsa Community College in Tulsa, Oklahoma. Bob earned his Bachelor of Science degree in Communications from the University of Tulsa.

(NOTE: Each chapter concludes with a What's Next? section.)

Introduction.


Why By Example? What This Book Should Accomplish. What I Assume You Know. What I Assume You Dont Know. Source Files and How to Access Them. Conventions Used Throughout the Book.

I. TABLES AND QUERIES.

1. Planning and Designing Your Access Database.


Consider Your Data Before Designing Your Database. Why Relational Tables? Database Design and Normalization. Normalizing in Access.

2. Joins and Cascades.


Cascades.

3. The Table—The Heart of Any Database.


Fields—The Building Blocks of Tables. Access 2002 Data Types. Testing and Using Custom Formats. Building an Access 2002 Table. Entering Data. Explanation of Limitations. The Table Is the Center.

4. Unleashing the Power of Queries.


Queries Ask Questions. Queries Access Records. The Query Grid. Understanding Query Types. Introduction to SQL. Understanding “Ands” and “Ors” in the Query Grid. Understanding Wildcards. Understanding Grouping and Sorting. Understanding Dynasets and Their Underlying Tables. Dynasets Versus Snapshots for Query Optimization.

II. FORMS FOR INPUT AND REPORTS FOR OUTPUT.

5. Exploring Forms and Controls.


Forms Versus Reports. Defining Events. Using a Wizard to Help Design Your Form. Examining Your Form's Properties. Changing the Form's Color. Introduction to Controls. How to Control a Control. Creating a Calculated Control. Another Way to Create Calculated Controls. Creating a Command Button. Dealing with the Underlying Records. Designing a Relational Form.

6. Exploring Reports.


Reports Versus Spreadsheets. Report Basics. Creating a Report from Scratch. Taking Charge of Report Controls. Setting Up Spreadsheet-like Calculations in Report Controls. Taking Charge of Grouping and Sorting. Understanding Sections. The On Open Event. Working with the Record Source Property. Designing a Relational Report Using a Wizard. Conditional Formatting. Creating a Group of Controls.

III. AUTOMATE YOUR ACCESS DATABASE USING CODE.

7. Exploring Objects.


Defining Objects. Defining Object Properties. Object Models. Setting a Reference in the Reference Library. The Power of Controls Revisited. Three Sources. Lookup List.

8. Writing Your Own Visual Basic for Applications Code.


Function Procedures, Sub Procedures, and Modules. Access String Functions. The Access Immediate Window. Using Arrays with String Functions.

9. Macros, Modules, and Messages.


Visual Basic Versus Macros. Printing the Current Record Using a Macro.

10. Using Procedures to Customize Your Database.


Exploring Procedures. Functions Versus Subs. What Is Scope? Creating Procedures. More Than One Way to Loop. More than One Way to Handle Conditions. Using Functions to Fulfill Your Wish List of Features. Access Forgot. Using Functions to Customize Your Applications. Using the Immediate Window to Test Your Functions. Using Functions for Pattern Matching in Queries.

11. Handling Access Error Codes.


What To Do When Errors Occur. Determining which Errors Occur. Error-Checking Sub Routines. Fixing Errors After Trapping Them. Resume Statements. The Err.Raise Method.

12. Access Visual Basic Tools, Tips, and Techniques.


Comparing DAO and ADO. Using VBA to Run Queries. Manipulating Recordsets Using Collections Versus Manipulating Recordsets Using Queries. DAO Collections and Objects. ADO Collections and Objects. Cursors and Cursor Types. How to Use DAO and ADO to Manipulate Data. How to Use DAO and ADO to Enumerate Objects. Creating a List of Objects. Creating a List of Fields.

IV. TAKING ADVANTAGE OF THE LATEST ACCESS FEATURE.

13. Publishing Your Access Database.


Converting Objects to Web Pages. Introduction to Data Access Pages. Creating a Data Access Page. Active Server Pages. Hyperlinks in Access Objects.

14. SQL Server Integration.


MSDE Upgraded. Comparing MSSQL Server 2000 Desktop Engine to Jet. Installing and Starting the Server. Installing a Sample Northwind Application. Exploring Your Northwind Project.

15. User-Friendly Enhancements.


The Paradox of Databases. Compact Improvements. Converting from Previous Versions of Access. Sharing and Integrating with Other Applications.

V. OVERCOMING ACCESS DEVELOPMENT OBSTACLES.

16. Overcoming the Limitations of Queries.


Query Challenges. Using Query by Table Example for If Then Scenarios. Using Subqueries to Unleash the Power Of Queries. Using Functions with Queries, Revisited.

17. Getting the Most from Your Queries.


Making Your Database More Efficient. Query Optimization. Queries Versus Filters. Special SQL Specific Queries.

18. Working with Data from External Sources.


Using Automation to Meet Performance Demands. Importing and Exporting Data. Scalability.

19. Bringing Together and Separating Data.


The Art of Concatenation. The Art of Parsing.

20. Conquering Big Problems that Come In Small Packages.


Using Quotes and Pounds. Testing Quotes and Pounds. Controlling Nulls. Conquering Dates.

21. Taking Charge of Forms.


Using a Parameterized Query As a Form's Record Source. Pop-Up Forms Go Beyond the Message Box. How to Tell Whether a Form Is Open. Go Multidimensional with PivotTables. Using Forms to Control Editing and Save Records. Using Forms to Optimize Large Tables. Tab Control Benefits.

22. Taking Charge of Reports.


Handling Grouping and Sorting Programmatically. Using Table Relationships to Include or Preclude Entire Sections of Report Data. What to Do with Pesky Blank Lines. Multicolumn Reports. Print Extra Copies.

23. Using Replication.


Database Replication. How Replication Works.

Index.