book

Beginning Access 2003 VBA

Name: Beginning Access 2003 VBA
Author: Denise Gosnell
ISBN: 9780764556593

by Denise Gosnell

September 2004

Beginner

544 pages

11h 35m

English

Wrox

Read now

Unlock full access

Copyright
About the Author
Credits
Introduction
Who Should Read This BookWhat This Book CoversHow This Book Is StructuredWhat You Need to Use This BookConventionsHow It WorksSource CodeErratap2p.wrox.com
1. Introduction to Access 2003 VBA
1.1. What Is Access 2003 VBA?1.2. What's New in Access 2003 VBA?1.3. Access 2003 VBA Programming 1011.3.1. Requirements Gathering Phase1.3.2. Design Phase1.3.2.1. Model the Application Design1.3.2.2. Determine Application Architecture1.3.3. Development Phase1.3.3.1. The Visual Basic Editor1.3.4. Testing Phase1.3.4.1. Running and Debugging Code in the Visual Basic Editor1.3.5. Implementation Phase1.4. Summary1.5. Exercises
2. The Basics of Writing and Testing VBA Code
2.1. Creating Modules2.2. Standard Modules versus Class Modules2.2.1.2.2.1.1. How It Works2.3. Creating and Calling Procedures2.3.1. Sub versus Function Procedure2.3.1.1. Create and Call a New Sub Procedure2.3.1.2. How It Works2.3.1.3. How It Works2.3.1.4. How It Works2.3.1.5. Create and Call a New Function Procedure2.3.1.6. How It Works2.3.1.7. Scope and Lifetime of Sub Procedures and Functions2.3.1.8. Using Built-In Functions2.3.2. Upgrading Macros to Procedures2.4. Using Variables and Constants to Store Values2.4.1. Types of Variables2.4.2. Declaring and Using Variables2.4.2.1. Declaring and Using Arrays2.4.3. Constants2.4.3.1. Declaring and Using Constants2.4.3.2. Using Built-in Constants2.4.4. Scope and Lifetime of Variables and Constants2.4.4.1. How It Works2.4.5. Naming Conventions2.5. Controlling Program Flow2.5.1. Decision Making2.5.1.1. If. . . Then2.5.1.2. How It Works2.5.1.3. IIf2.5.1.4. Conditional If2.5.1.5. Select. . . Case2.5.1.6. How It Works2.5.2. Loops2.5.2.1. For. . . Next and For Each. . . Next2.5.2.2. How It Works2.5.2.3. Do. . . Loop2.5.2.4. While. . . Wend2.6. Documenting Your Code2.7. Error Debugging2.7.1. Types of Errors2.7.1.1. Syntax Errors2.7.1.2. Compile Errors2.7.1.3. Runtime Errors2.7.1.4. Logic Errors2.7.2. Debugging Your Code2.7.2.1. Using Breakpoints to Step through Code2.7.2.2. How It Works2.7.2.3. Using the Immediate Window2.7.2.4. Using the Locals Window2.7.2.5. How It Works2.7.2.6. Using the Watch Window2.7.2.7. How It Works2.7.2.8. Using the Call Stack2.8. Error Handling2.8.1. Default Error Messages2.8.2. Handling Errors with an On Error Statement2.8.2.1. On Error Statement2.8.2.2. Resume Statement2.8.2.3. How It Works2.8.3. The Err Object2.8.3.1. Raising an Error2.8.4. Using the Errors Collection2.8.5. Creating a Generic Error Handler2.8.5.1. How It Works2.9. Summary2.10. Exercises
3. Programming Applications Using Objects
3.1. Elements of an Object3.1.1. What Is a Property?3.1.2. What Is a Method?3.1.3. What Is an Event?3.1.3.1. How It Works3.2. Viewing Objects Using the Object Browser3.3. Ways to Refer to Objects3.4. Using Access Collections and Objects3.4.1. The Forms Collection and Form Object3.4.1.1. How It Works3.4.2. The Reports Collection and Report Object3.4.3. The CurrentProject Object3.4.4. The DoCmd Object3.4.5. The Screen Object3.4.5.1. How It Works3.4.6. The Printers Collection and Printer Object3.4.6.1. How It Works3.4.7. Other Objects3.5. Summary3.6. Exercises
4. Creating Your Own Objects
4.1. Using Class Modules to Create Your Own Objects4.1.1. Class Diagrams4.1.2. Creating Properties4.1.2.1. Using Public Variables4.1.2.2. Using Property Let, Property Get, and Property Set4.1.2.3. How It Works4.1.3. Creating Methods4.1.3.1. Using Public Sub Procedures and Functions4.1.3.2. How It Works4.1.4. Creating Events4.1.4.1. Declaring and Raising Events4.1.4.2. How It Works4.1.4.3. Creating the Event Sub Procedure or Function4.1.5. Using the Class4.1.5.1. Instantiating the Class4.1.5.2. Initialize and Terminate Events4.1.5.3. How It Works4.1.6. Advanced Techniques4.1.6.1. Creating Multiple Instances of the Class4.1.6.2. Creating Class Hierarchies4.1.6.3. Working with Enumerated Types4.1.6.4. Inheritance Using Implements Keyword4.2. Create Custom Properties for Existing Objects4.2.1. Using Existing Tag Property4.2.2. Using Public Variables4.2.3. Using Property Let, Property Get, and Property Set Routines4.2.3.1. How It Works4.3. Create Custom Methods for Existing Objects4.3.1. Using Public Sub Procedures and Functions4.3.1.1. How It Works4.4. Summary4.5. Exercises
5. Interacting with Data Using ADO and SQL
5.1. Introduction to Data Access5.2. Using ADODB to Work with Data5.2.1. The ADO Object Model5.2.2. The Connection Object5.2.2.1. Connecting to Access Databases5.2.2.2. Connecting to Other Databases5.2.3. The Command Object5.2.4. The Recordset Object5.2.4.1. Creating a Recordset5.2.4.2. How It Works5.2.4.3. Counting Records in a Recordset5.2.4.4. Navigating through a Recordset5.2.4.5. Adding, Editing, and Deleting Records in a Recordset5.2.4.6. How It Works5.2.4.7. Sorting, Finding, Filtering records in a Recordset5.2.4.8. How It Works5.3. Introduction to SQL5.3.1. Retrieving Data Using SQL Select Statements5.3.1.1. Select Clause5.3.1.2. From Clause5.3.1.3. Where Clause5.3.1.4. Order By Clause5.3.1.5. Subqueries5.3.1.6. Union5.3.1.7. Using SQL and ADO to Populate a Recordset5.3.2. Inserting Data Using SQL Insert Statements5.3.3. Inserting Results of a Select into a table5.3.4. Updating Data Using SQL Statements5.3.5. Deleting Data Using SQL Statements5.3.5.1. How It Works5.4. Using ADOX to Manipulate Data Objects5.4.1. Creating a Table with ADOX5.4.2. Managing Security with ADOX5.5. Summary5.6. Exercises
6. Building Interactive Forms
6.1. Form Navigation and Flow6.1.1.6.1.1.1. How It Works6.2. Working with Controls6.2.1. Combo Boxes versus List Boxes6.2.1.1. How It Works6.2.2. Tab Controls and Subforms6.3. Building User Interaction6.3.1. The MsgBox Function6.3.2. Validating User Input6.3.2.1. How It Works6.4. Adding Polish to Your Forms6.5. Summary6.6. Exercises

7. Importing, Linking, and Exporting Using External Data Sources
7.1. Linking, Importing, and Exporting Overview7.2. Access and Other Databases7.2.1.7.2.1.1. How It Works7.3. Transferring Complete SQL Server Database7.4. Spreadsheets7.5. Text Files7.5.1.7.5.1.1. How It Works7.6. XML Files7.7. E-mails and Outlook7.8. Other Ways to Export Data7.9. Using Data from Web Services7.9.1.7.9.1.1. How It Works7.9.1.2. How It Works7.10. Summary7.11. Exercises
8. Creating Reports and Web-Enabled Output
8.1. Working with Reports from VBA8.1.1. Setting the Report Data Source8.1.2. Creating Reports Programmatically8.1.2.1. Creating an Empty Report8.1.2.2. Adding Controls to the Report8.1.2.3. How It Works8.1.2.4. Using the Printer Object to Designate Printing Options8.1.2.5. Working with Report Events8.2. Exporting Access Data to Web Formats8.2.1. Creating Static HTML Pages8.2.1.1. How It Works8.2.2. Creating ASP Pages8.2.3. Creating Data Access Pages8.2.3.1. Creating Data Access Pages Using the OutputTo Method8.2.3.2. How It Works8.2.3.3. Viewing and Modifying Data Access Pages from Access8.2.3.4. How It Works8.3. Summary8.4. Exercises
9. Building SQL Server Applications with Access Projects
9.1. Access Projects—Using Access as a Front End to SQL server9.2. Setting Up SQL Server9.2.1. SQL Server Versions9.2.2. Obtaining and Installing SQL Server Desktop Engine9.2.2.1. The Benefits of SQL Server Desktop Engine9.2.2.2. Installing SQL Server Desktop Engine9.2.2.3. Understanding What Was Installed9.3. Creating Access Projects (ADPs)9.3.1. Using an Existing SQL Server Database9.3.2. Using a New SQL Server Database9.3.2.1. How It Works9.4. Working with SQL Server Objects from Access9.4.1. SQL Server Tables9.4.1.1. How It Works9.4.2. SQL Server Stored Procedures9.4.2.1. How It Works9.4.3. SQL Server Views9.4.3.1. How It Works9.4.4. SQL Server Functions9.5. Migrating an Existing Access Database to an Access Project9.5.1. Changes to an Existing Database9.5.2. Using the Upsizing Wizard9.6. Summary9.7. Exercises
10. Advanced Access Programming
10.1. Using Dynamic Link Libraries (DLLs)10.1.1. Standard DLLs10.1.1.1. How It Works10.1.2. Using External DLLs10.1.2.1. How It Works10.2. Automation10.2.1. Working with Automation Objects10.2.2. Controlling Microsoft Excel10.2.2.1. How It Works10.2.3. Controlling Microsoft Word10.2.3.1. How It Works10.2.4. Controlling Microsoft Outlook10.2.4.1. How It Works10.3. ActiveX Controls10.4. Libraries10.4.1.10.4.1.1. How It Works10.5. Add-ins10.6. Transactions10.7. Summary10.8. Exercises
11. Finishing the Application
11.1. Multi-User Considerations11.1.1. Record Locking and Update Conflicts11.1.1.1. Record Locking on Bound Forms11.1.1.2. Record Locking on Unbound Forms11.1.2. Multi-User Architecture Considerations11.1.2.1. How It Works11.2. Optimizing Your Applications11.2.1. Improving Actual Performance11.2.1.1. General Design Guidelines11.2.1.2. Optimizing VBA Code11.2.1.3. Improving Data Access11.2.2. Improving Perceived Performance11.2.3. Running the Performance Analyzer11.2.3.1. How It Works11.3. Securing Your Application11.3.1. Adding a Database Password11.3.2. Adding a Password for VBA Code11.3.3. Adding Users, Groups, and Permissions11.3.4. Encrypting a Database11.4. Distributing Your Application11.4.1. Distributing the Application to Users with the Full Version of Access11.4.2. Distributing the Application with the Access Runtime11.4.3. Distributing an Execute-Only Application11.5. Maintaining the Application11.5.1. Compacting and Repairing the Database11.5.2. Making Backup Copies of the Database11.6. Summary11.7. Exercises
12. Case Study 1: Project Tracker Application
12.1. Design Specifications12.2. Building the Database12.2.1.12.2.1.1. How It Works12.3. Building the User Interface12.3.1.12.3.1.1. How It Works12.4. Building the Class Modules for the Objects12.4.1. The Project Class12.4.1.1. How It Works12.4.2. The Contact Class12.4.2.1. How It Works12.5. Building the Standard Modules12.5.1.12.5.1.1. How It Works12.6. Connecting the User Interface to the Code12.6.1. The Projects Form12.6.1.1. How It Works12.6.2. The Contacts Form12.6.2.1. How It Works12.7. Touring the Finished Project Tracker Application12.8. Summary
13. Case Study 2: Customer Service Application
13.1. Design Specifications13.2. Building the Database and Database Objects13.3. Building the User Interface13.4. Building the Class Module for the Objects13.5. Building the Standard Modules13.6. Connecting the User Interface to the Code13.6.1. The Customer Search Form13.6.2. The View/Manage Customer Accounts Form13.7. Touring the Finished Customer Service Application13.8. Summary
A. Exercise Answers
A.1. Chapter 1A.2. Chapter 2A.3. Chapter 3A.4. Chapter 4A.5. Chapter 5A.6. Chapter 6A.7. Chapter 7A.8. Chapter 8A.9. Chapter 9A.10. Chapter 10A.11. Chapter 11

Content preview from Beginning Access 2003 VBA

Chapter 9. Building SQL Server Applications with Access Projects

So far, all the examples in this book have dealt with standard Access database files (MDB files). An Access Project (ADP file) is another type of Access database file that is designed specifically for working with SQL Server databases. In this chapter, you will explore how to create and manage SQL Server databases using Access Projects. You'll learn how to create new Access Projects from scratch and explore the various SQL Server objects that you can create and manage from the Access Project, such as tables, stored procedures, views, and functions. You will also upsize an existing Access database to SQL Server and provide guidelines for resolving any problems that occur during the migration.

This chapter focuses on using Access Projects to work with features that are specific to SQL Server, but it does not cover forms and modules. You still write forms and modules in Access Projects as you have done in prior chapters.

Access Projects—Using Access as a Front End to SQL server

As a quick recap, you are already familiar with standard Access database files: the MDB files. The MDB file is typically used in a standalone manner, meaning that all the tables, forms, modules, macros, and reports for the application are stored within the single file. In Chapter 7, you learned that it is also possible to use a standard Access MDB file in a client/server environment by linking to external database tables such as SQL Server. An example ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 9780764556593Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Beginning Access 2003 VBA

by Denise Gosnell

Chapter 9. Building SQL Server Applications with Access Projects

Access Projects—Using Access as a Front End to SQL server

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.