Microsoft Access 2010 VBA Programming Inside Out

738 Pages·2011·34.908 MB·English

by  Andrew Couch

www.it-ebooks.info
www.it-ebooks.info
Microsoft Access 2010
® ®
VBA Programming
Inside Out
Andrew Couch
www.it-ebooks.info
Published with the authorization of Microsoft Corporation by:
O’Reilly Media, Inc.
1005 Gravenstein Highway North
Sebastopol, California 95472
Copyright © 2011 by Andrew Couch
All rights reserved. No part of the contents of this book may be reproduced or transmitted in
any form or by any means without the written permission of the publisher.
ISBN: 978-0-7356-5987-2
1 2 3 4 5 6 7 8 9 M 6 5 4 3 2 1
Printed and bound in the United States of America.
Microsoft Press books are available through booksellers and distributors worldwide. If you need
support related to this book, email Microsoft Press Book Support at [email protected].
Please tell us what you think of this book at http://www.microsoft.com/learning/booksurvey.
Microsoft and the trademarks listed at http://www.microsoft.com/about/legal/en/us/
IntellectualProperty/Trademarks/EN-US.aspx are trademarks of the Microsoft group of
companies. All other marks are property of their respective owners.
The example companies, organizations, products, domain names, email addresses, logos,
people, places, and events depicted herein are fictitious. No association with any real company,
organization, product, domain name, email address, logo, person, place, or event is intended
or should be inferred.
This book expresses the author’s views and opinions. The information contained in this book
is provided without any express, statutory, or implied warranties. Neither the author, O’Reilly
Media, Inc., Microsoft Corporation, nor its resellers, or distributors will be held liable for any
damages caused or alleged to be caused either directly or indirectly by this book.
Acquisitions and Developmental Editor: Kenyon Brown
Production Editor: Teresa Elsey
Editorial Production: Octal Publishing, Inc.
Technical Reviewer: Alan Cossey
Indexer: Denise Getz
Cover Design: Twist Creative • Seattle
Cover Composition: Karen Montgomery
Illustrator: Robert Romano
www.it-ebooks.info
pour Pamela, ma raison d’être
www.it-ebooks.info
www.it-ebooks.info
Contents at a Glance
Part 1: VBA Environment Part 4: Advanced
and Language Programming with VBA Classes
Chapter 1 Chapter 9
Using the VBA Editor and Debugging Code . . . 3 Adding Functionality with Classes . . . . . . . . .339
Chapter 2 Chapter 10
Understanding the VBA Language Structure 39 Using Classes and Events . . . . . . . . . . . . . . . . .359
Chapter 3 Chapter 11
Understanding the VBA Language Features . .89 Using Classes and Forms . . . . . . . . . . . . . . . . . .381
Part 2: Access Object Model Part 5: External Data and
and Data Access Objects (DAO) Office Integration
Chapter 4 Chapter 12
Applying the Access Object Model . . . . . . . . .127 Linking Access Tables . . . . . . . . . . . . . . . . . . . .395
Chapter 5 Chapter 13
Understanding the Data Access Integrating Microsoft Office . . . . . . . . . . . . . .437
Object Model . . . . . . . . . . . . . . . . . . . . . . . . . . .161
Part 6: SQL Server and SQL Azure
Part 3: Working with Forms
Chapter 14
and Reports
Using SQL Server . . . . . . . . . . . . . . . . . . . . . . . .483
Chapter 6
Chapter 15
Using Forms and Events . . . . . . . . . . . . . . . . . .231
Upsizing Access to SQL Server . . . . . . . . . . . . .543
Chapter 7
Chapter 16
Using Form Controls and Events . . . . . . . . . . .273
Using SQL Azure . . . . . . . . . . . . . . . . . . . . . . . . .589
Chapter 8
Creating Reports and Events . . . . . . . . . . . . . .323 Part 7: Application Design
Chapter 17
Building Applications . . . . . . . . . . . . . . . . . . . .631
Chapter 18
Using ADO and ADOX . . . . . . . . . . . . . . . . . . . .659
v
www.it-ebooks.info
www.it-ebooks.info
Table of Contents
Introduction ...................................................................xix
Part 1: VBA Environment and Language
Chapter 1: Using the VBA Editor and Debugging Code . . . . . . . . . . . . . . . . . . . . . . . . 3
Debugging Code on a Form .......................................................4
Entering the VBA Editor......................................................5
The Application and VBA Code Windows.......................................6
Creating Modules and Procedures..................................................8
Creating a Module ........................................................ 10
Creating a Procedure...................................................... 11
Executing a Subroutine .................................................... 13
Executing a Function ...................................................... 15
Viewing and Searching Code ............................................... 16
Split Window ............................................................. 17
Searching Code........................................................... 19
Debugging Code in a Module.................................................... 20
Debug Commands ........................................................ 23
Breakpointing Code............................................................. 23
Set Next Command........................................................ 25
Breakpoint Step and Run Commands........................................ 26
Displaying Variables in the Locals Window ................................... 29
Tracing Procedures with the Call Stack....................................... 30
Watching Variables and Expressions......................................... 31
Adding Conditional Watch Expressions ...................................... 32
Working with the Immediate Window ....................................... 33
Changing Code On-the-Fly................................................. 34
Using the Object Browser and Help System........................................ 35
Configuring the Help System ............................................... 35
Working with the Object Browser ........................................... 36
Summary...................................................................... 37
What do you think of this book? We want to hear from you!
Microsoft is interested in hearing your feedback so we can continually improve our books and learning
resources for you . To participate in a brief online survey, please visit:
microsoft .com/learning/booksurvey
vii
www.it-ebooks.info
viii Table of Contents
Chapter 2: Understanding the VBA Language Structure . . . . . . . . . . . . . . . . . . . . . . . 39
VBA Language Settings ......................................................... 40
Comments ............................................................... 40
Setting Option Explicit .....................................................41
Selecting Option Compare..................................................43
Compiling Code........................................................... 44
Conditional Compilation ................................................... 45
References ............................................................... 46
Working with Constants and Variables ............................................ 49
Improving Code Quality with Constants...................................... 49
The Enum Keyword........................................................ 51
Variables and Database Field Types.......................................... 52
Handling NULL Values, IsNull and Nz ........................................53
Using Static Variables...................................................... 55
Using Global Variables..................................................... 56
Variable Scope and Lifetime . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
Working with Arrays....................................................... 59
Type Structures ........................................................... 65
Functions and Procedures ....................................................... 66
Managing Code with Subroutines........................................... 67
Defining ByRef and ByValue Parameters ..................................... 70
Private and Public Procedures .............................................. 72
Optional and Named Parameters ........................................... 73
The ParamArray Qualifier .................................................. 75
Organizing Code in Modules and Class Modules.............................. 76
Control Statements and Program Flow ............................................ 77
IF… Then… Else… Statements................................................ 77
IIF Statements ............................................................ 78
Choose Statements........................................................ 79
Select Case Statements..................................................... 80
TypeOf Statements ........................................................ 80
For and For Each Loops .................................................... 81
Do While and Do Until Loops ............................................... 82
Exit Statements ........................................................... 84
The With Statement ....................................................... 85
GoTo and GoSub ..........................................................86
Line Continuation ......................................................... 86
Splitting SQL Over Multiple Lines ........................................... 86
Summary...................................................................... 87
Chapter 3: Understanding the VBA Language Features . . . . . . . . . . . . . . . . . . . . . . . 89
Using Built-In Functions ......................................................... 90
Date and Time Functions................................................... 90
String Functions........................................................... 92
Domain Functions .............................................................. 95
Constructing Where Clauses................................................ 97
SQL and Embedded Quotes...................................................... 98
www.it-ebooks.info