Beginning Access 2002 Vba

by Wrox Author Team

• ISBN13: 9781861008213
• ISBN10: 186100821X
• Edition: 1st
• Format: Trade Paper
• Copyright: 2003-01-01
• Publisher: Peer Information

Summary

If you are using Access and want to go to the next level, you need to learn VBA. As part of Visual Basic, VBA allows you to use some tremendously powerful programming techniques. In this book, we show you how VBA gives you complete control over the way your users view and interact with your Access databases. This is the next level of Access skills, presented to you here in a clear tutorial style. This book is a revision of the best-selling "Beginning Access 97 VBA" and "Beginning Access 2000 VBA" books, reworked to provide a rich and comprehensive guide to programming Access 2002 with VBA. Book jacket.

Table of Contents

Introduction	1	(1)
The Structure of this Book	1	(2)
How is Access Changing?	3	(1)
Who is this Book For?	3	(1)
What Does this Book Cover?	3	(1)
What You Need for this Book	4	(1)
The Book's CD	4	(1)
Exercise Solutions	5	(1)
Conventions	5	(1)
Customer Support	6	(5)
Errata	7	(1)
E-mail Support	7	(1)
p2p.wrox.com	8	(1)
Why this System Offers the Best Support	8	(3)
Designing Applications	11	(40)
What Is an Access Application?	12	(1)
The Development Process	13	(10)
The Analysis Phase	14	(1)
Requirements Analysis	14	(2)
Prototyping	16	(1)
Technical Analysis	16	(1)
Data Analysis	17	(1)
Design and Coding	18	(1)
Designing for Change	19	(1)
Coding	20	(1)
Testing	20	(1)
Documentation	21	(1)
Acceptance	22	(1)
Review	22	(1)
Further Reading	23	(1)
The Ice Cream Shop Application	23	(18)
Designing the Ice Cream Shop Database	24	(2)
Typical Dilemmas Regarding Data Storage	26	(3)
Choosing a Storage Engine	29	(1)
Entering and Viewing Data	29	(1)
Designing a Form	29	(6)
Creating Navigation Buttons	35	(4)
The Finished Product	39	(2)
Macros Or VBA?	41	(7)
Why You Should Use VBA	41	(1)
When to Use Macros	42	(1)
Trapping Keystrokes Throughout an Application	43	(1)
Carrying Out Actions when a Database is Opened -- the Autoexec Macro	44	(1)
Moving to VBA	45	(3)
Summary	48	(1)
Exercise	48	(3)
Introduction to Event-Driven Programming	51	(42)
Examples of Events	52	(2)
How Windows Handles Events	54	(3)
Some Definitions	57	(1)
So Many Events	57	(1)
Default Events	58	(2)
One Action, Many Events	60	(1)
Handling Events in Access with VBA	60	(7)
What are Actions and Methods?	67	(1)
Why is the Code in Different Colors?	68	(1)
What were all those Popup Thingies?	69	(2)
What if I Still Get it Wrong?	71	(2)
Compiling Code	73	(1)
Other Events	73	(2)
The VBA IDE	75	(8)
Components of the VBA IDE	76	(1)
Project Explorer	77	(3)
Code Window	80	(2)
The (General) Object	82	(1)
Adding Intelligent Navigation Buttons to the Company Contacts Form	83	(4)
Adding to the User Interface	87	(2)
Summary	89	(1)
Exercises	90	(3)
Creating Code	93	(52)
Procedures	93	(2)
Modules	95	(1)
Subroutines and Functions	95	(1)
Naming Conventions	96	(4)
Keep Names Meaningful	97	(1)
Mixed Case Naming	97	(1)
Prefix All Variables and Constants With Their Type	97	(1)
Prefix Global Variables with their Scope	98	(1)
Naming Conventions for Controls	98	(1)
Naming Conventions for Objects	99	(1)
Naming Conventions for Constants	99	(1)
Naming Conventions Summary	100	(1)
Procedure Declaration	100	(10)
Function Types	101	(3)
Parameters or Arguments	104	(2)
Optional Arguments	106	(2)
Default Values	108	(1)
Named Arguments	109	(1)
Built-In Functions	110	(1)
Variables	110	(23)
Declaring Variables	111	(1)
Manipulating Variables	112	(1)
Variable Types	113	(1)
Boolean	114	(1)
Byte	114	(1)
Currency	115	(1)
Double and Single	115	(1)
Integer and Long	115	(1)
String	115	(1)
Object	116	(1)
Hyperlink	116	(1)
Variant	116	(7)
Date	123	(5)
Formatting Variables	128	(5)
Constants	133	(1)
Intrinsic Constants	134	(1)
Variable Scope and Lifetime	135	(7)
Local Variables	136	(2)
Static Variables	138	(1)
Global Variables	139	(1)
Private Variables	139	(2)
Public Variables	141	(1)
Summary	142	(3)
Controlling the Program	145	(30)
Programming Structures	145	(13)
Expressions	146	(1)
Decision Structures	146	(1)
If...Then	146	(2)
If...Then...Else	148	(1)
Elself	149	(1)
Logical Operators with the If statement	149	(1)
Select Case	150	(5)
IIf	155	(1)
Operator Precedence	156	(2)
Repetition	158	(4)
Loops	158	(1)
For...Next	158	(2)
Do...Loop	160	(1)
Nested Control Structures	161	(1)
Exiting a Control Structure	161	(1)
Arrays	162	(11)
Static Arrays	166	(1)
Upper and Lower Bounds	167	(1)
Dynamic Arrays	168	(4)
Redimensioning Existing Arrays	172	(1)
The Dangers of ReDim	173	(1)
Summary	173	(2)
Using Access Objects	175	(36)
Object Oriented Programming	175	(4)
Objects	176	(1)
Properties	176	(1)
Methods	177	(1)
Events	177	(1)
The Interface	177	(1)
Classes	177	(1)
Inheritance	178	(1)
The Advantages of Object-Orientation	179	(1)
Object Models	179	(2)
Using Object Models in VBA	180	(1)
Access 2002 Objects	181	(3)
New Objects in Access 2002	182	(1)
New Objects	182	(1)
New Object Properties & Methods	183	(1)
The AccessObject Object	184	(12)
Dynamic Listboxes	187	(7)
Changing the Switchboard	194	(2)
Forms and Reports	196	(5)
Control Types	199	(1)
The Err Object	199	(2)
Referring to Objects	201	(1)
Special Objects	202	(4)
The Me Keyword	204	(2)
The Object Browser	206	(2)
Summary	208	(1)
Exercises	209	(2)
Using DAO	211	(58)
DAO vs ADO	211	(7)
A Brief History of DAO	213	(1)
The Future of DAO	214	(1)
OLEDB and ADO	215	(2)
ADO.NET and the Future of Data Access	217	(1)
The DAO Hierarchy	218	(16)
The DBEngine Object	221	(1)
The Workspace Object	221	(1)
The Database Object	222	(2)
The TableDefs Collection	224	(1)
The QueryDefs Collection	225	(1)
The Recordsets Collection	226	(6)
Building Recordsets Dynamically	232	(1)
Requerying Data in Recordsets	233	(1)
Working with Recordsets	234	(32)
Examining Field Values	238	(1)
Moving Through Recordsets	238	(7)
Counting Records in a Recordset	245	(1)
Table-Type Recordsets	245	(1)
Dynasets and Snapshots	245	(1)
AbsolutePosition and PercentPosition	246	(2)
Looking for Specific Records	248	(1)
Finding Records in Table-Type Recordsets	248	(6)
Finding Records in Dynasets and Snapshots	254	(3)
Notes on Formatting Dates and Currency Amounts in VBA	257	(1)
When to Use Find and Seek	258	(2)
Bookmarks	260	(1)
Comparing Bookmarks	261	(1)
Editing Records in Recordsets	262	(4)
When a Recordset Can't be Updated	266	(1)
Summary	266	(1)
Exercises	267	(2)
Data Management Techniques	269	(46)
The Challenge -- Flexibility vs. Manageability	269	(1)
The Requirement -- Ad Hoc Query Functionality	270	(11)
Why Not Use Parameterized Queries?	273	(2)
Building a Query by Form Interface	275	(6)
Creating and Modifying DAO QueryDefs	281	(14)
Working with SQL	286	(9)
Two Approaches to Displaying Results	295	(15)
Modifying the QueryDef	297	(3)
Using a Matching Records Table	300	(9)
Other Considerations	309	(1)
Summary	310	(1)
Exercises	310	(5)
Working with Tables	315	(24)
Working with Tables	315	(13)
The Ten Steps	317	(6)
Modifying the Table	323	(2)
Creating a Table Using an XML Schema	325	(3)
Sub Datasheets	328	(7)
Summary	335	(1)
Exercises	336	(3)
External Data	339	(36)
Other Applications	340	(8)
Databases	340	(5)
Spreadsheets	345	(3)
Text Files	348	(9)
Specifications	350	(7)
XML	357	(9)
Electronic Mail	366	(3)
Using External Data	369	(4)
The Database Splitter	369	(1)
Linked Tables	370	(2)
Differences between Linked and Local Tables	372	(1)
Summary	373	(1)
Exercises	373	(2)
Reports	375	(36)
Starting Off	375	(6)
Page Numbers	381	(1)
IIf	381	(3)
Dates	384	(4)
Testing the ReportHistory Function in the Immediate Window	387	(1)
Summarizing	388	(1)
Expressions	389	(2)
Events	391	(10)
Open	392	(1)
Activate	392	(1)
Deactivate	393	(1)
Close	393	(1)
Error	393	(1)
Format	394	(2)
Conditional Formatting	396	(2)
Cancel and FormatCount	398	(1)
Print	399	(1)
Retreat	400	(1)
NoData	400	(1)
Page	400	(1)
When to Use the Different Events	400	(1)
Format	401	(1)
Print	401	(1)
Retreat	401	(1)
The FormatCount and PrintCount Properties	401	(1)
Filters	401	(6)
Summary	407	(1)
Exercises	408	(3)
Advanced Programming Techniques	411	(44)
Arrays	412	(16)
Multi-dimensional Arrays	412	(1)
Dynamic Multi-dimensional Arrays	413	(1)
Referencing Elements in a Multi-dimensional Array	414	(1)
Memory Considerations	415	(1)
Erasing Arrays	416	(1)
Parameter Arrays	417	(2)
The Array Function	419	(1)
The GetRows() Method	420	(3)
Detecting Arrays	423	(1)
The IsArray() Function	423	(1)
The VarType() Function	424	(3)
The TypeName() Function	427	(1)
Arguments	428	(3)
Passing Arguments By Value	429	(2)
Dynamic-Link Libraries (DLLs)	431	(13)
Declaring a DLL in Code	432	(5)
Enums	437	(1)
Aliases	438	(1)
Using the ByVal Keyword	439	(1)
Passing Strings	439	(1)
Passing Arrays to a DLL	440	(1)
Type Conversion	440	(1)
User-defined Data Types	441	(2)
Null Pointers	443	(1)
The Dangers of Using DLLs	443	(1)
Custom DAO Properties	444	(8)
Database Properties	448	(4)
Summary	452	(1)
Exercises	453	(2)
Finding and Dealing with Errors	455	(62)
Planning for Errors	455	(6)
The Design	456	(1)
User Analysis	456	(1)
Table Design	456	(1)
Queries	456	(1)
Forms and Reports	457	(1)
Modules	457	(1)
Data-Driven Design	458	(1)
Object-Oriented Techniques	458	(1)
Option Explicit	458	(1)
Syntax Checking, Statement Completion, and Code Tips	459	(1)
Comments	460	(1)
Compiling	461	(1)
Testing	462	(2)
Functional Testing	462	(1)
Usability Testing	462	(1)
Destructive Testing	463	(1)
Maintenance	463	(1)
Types of Errors	464	(9)
Syntax Errors	464	(1)
Compile Errors	465	(1)
Run-Time Errors	466	(1)
Semantic (or logic) Errors	467	(4)
Locating Errors	471	(1)
Other Errors	472	(1)
Debugging	473	(18)
Program Execution	473	(1)
Breakpoints	474	(1)
Assertions	475	(1)
The Stop Statement	476	(1)
Continuing Execution	476	(1)
Stopping Execution	476	(1)
Stepping through Code	476	(1)
Re-running Lines of Code	477	(1)
Skipping Lines of Code	478	(1)
Changing Code	479	(1)
Commenting Out Lines	479	(1)
Block Commenting	479	(1)
The Call Stack	480	(1)
The Immediate Window	481	(1)
The Locals Window	481	(1)
The Watch Window	482	(1)
Quick Watches	482	(1)
Hovering	482	(9)
Error Handling	491	(23)
The Err Object	491	(1)
Visual Basic Errors	492	(5)
Using the Code Commenter and Error Handler Add-In	497	(5)
Form Errors	502	(2)
Access and VBA Errors	504	(1)
Data-Access Errors	504	(1)
Data-Access Objects	504	(3)
ActiveX Data Objects	507	(1)
User Defined Errors	508	(1)
The Error Stack	509	(5)
Debugging your Debugging Code	514	(1)
Summary	514	(1)
Exercises	515	(2)
Using Classes	517	(62)
Class Modules and Custom Objects	518	(47)
What Are Objects?	518	(2)
Why Use Objects?	520	(1)
Abstraction	520	(1)
Encapsulation	521	(1)
Polymorphism	522	(1)
Inheritance	522	(1)
The MyRectangle Class	522	(9)
Abstraction	531	(1)
Encapsulation	531	(1)
Polymorphism	532	(3)
Polymorphism through Early Binding	535	(9)
Inheritance	544	(1)
The PaymentStats Class	544	(1)
The Business Need	544	(1)
The Object Model	545	(2)
Building the Interface	547	(3)
Implementing the Logic	550	(8)
Finishing Touches	558	(3)
Benefits of an Object-Oriented Approach	561	(2)
Differences between Classes and Modules	563	(1)
The Class Initialize and Terminate Events	564	(1)
Forms as Class Modules	565	(9)
Creating Custom Properties for Forms	566	(3)
Custom Form Methods	569	(1)
Creating Multiple Instances of Forms	570	(4)
Collections -- Creating a Hierarchy	574	(1)
Getting the Most from Class Modules	575	(1)
Hide Your Data	575	(1)
Don't Overdo It	575	(1)
Avoid Get and Set Methods	576	(1)
Get the Object Model Right	576	(1)
Make it Look Easy	576	(1)
Summary	576	(1)
Exercises	577	(2)
WithEvents and RaiseEvent	579	(24)
Event Review, Some Terms and Facts	581	(1)
What is WithEvents?	582	(2)
Why Use WithEvents?	584	(9)
The Simple Combo Class	585	(4)
The Record Selector Class	589	(4)
RaiseEvent	593	(7)
A Simple Example	594	(6)
Summary	600	(1)
Exercises	600	(3)
Libraries and Add-Ins	603	(46)
Library Databases	603	(10)
Creating a Library Database	604	(8)
Considerations	612	(1)
Class Libraries	613	(10)
An ADO Class Library	613	(10)
Add-Ins	623	(23)
The Language Converter Add-In	624	(14)
Creating the Add-In	638	(6)
The Complete Language Converter Add-In	644	(1)
The Color Schemes Add-In	644	(1)
Creating Add-Ins Summary	645	(1)
Updating your Add-In	646	(1)
Summary	646	(1)
Exercises	647	(2)
Automation	649	(38)
What Is Automation?	649	(2)
Interfaces and Object Models	650	(1)
Object References	651	(1)
Creating Automation Objects	652	(1)
Microsoft Outlook	653	(10)
Microsoft Word	663	(9)
Microsoft Excel	672	(12)
Summary	684	(3)
Multi-User	687	(62)
Multi-Developer Issues	687	(1)
Record Locking in Access 2002	688	(22)
What is Locking Anyway?	688	(1)
Page Locking vs. Record Locking	689	(1)
Record-Level Locking	690	(2)
Optimistic and Pessimistic Locking	692	(2)
Choosing a Locking Strategy	694	(1)
Setting the Default Record Locking Mechanism	695	(3)
Implementing Record Locking on Forms	698	(1)
Recordsets and Record Locking	699	(1)
The Type Argument	699	(1)
The Option Argument	700	(1)
The LockEdits Argument	701	(1)
The LockEdits Property	702	(1)
Handling Record-Locking Errors	702	(1)
Optimistic Locking Errors	702	(4)
Pessimistic Locking Errors	706	(2)
Deadlocks	708	(1)
Other Solutions	709	(1)
Security	710	(27)
Why Security?	710	(2)
Setting a Database Password from VBA	712	(1)
Protecting Your Code	712	(1)
The Access Security Model	713	(1)
Using the User-Level Security Wizard	713	(1)
Manually Making a Workgroup Information File (WIF)	714	(2)
Manipulating Users and Groups	716	(1)
Enumerating Users and Groups	717	(2)
Creating a New User	719	(2)
Creating a New Group	721	(1)
Adding a User to a Group	722	(2)
Changing a Password	724	(6)
Setting Object Permissions with Visual Basic	730	(2)
Analyzing the Output	732	(5)
Workspaces	737	(1)
Compiling	737	(8)
What is Compilation?	738	(1)
How do we Compile Code?	739	(2)
When does Code Decompile?	741	(1)
Using MDE files	742	(1)
Saving a Database as an MDE file	742	(1)
Restrictions on MDE Files	743	(1)
Using MDEs with Add-Ins and Libraries	744	(1)
Encrypting	745	(1)
Summary	745	(1)
Exercises	746	(3)
The Internet	749	(38)
The Internet	749	(2)
Data Access Pages and Access XP	751	(10)
DAP Connection Files	752	(2)
What's New in Access 2002 DAPs	754	(1)
New DAP Events	754	(7)
XML	761	(14)
What is Extensible Markup Language (XML)	761	(2)
Advantages to XML	763	(1)
How to Export a Table as XML	763	(6)
Reports and XML	769	(2)
Active Server Pages	771	(4)
Uploading Data	775	(10)
How to Use Hyperlinks	777	(3)
Hyperlinks in VBA	780	(1)
Custom Hyperlink Form	781	(4)
Summary	785	(1)
Exercises	785	(2)
Optimizing Your Application	787	(52)
Efficiency	787	(2)
Reducing Memory Overhead	789	(5)
Use the Right Data Type	790	(2)
Group Procedures into Modules	792	(1)
Reclaim Memory Where Possible	793	(1)
Don't Load Unnecessary Libraries	793	(1)
Save as an MDE	793	(1)
A Final Recommendation -- Buy More Memory!	794	(1)
Increasing Execution Speed	794	(32)
Timing the Code Samples	795	(4)
Use Constants	799	(1)
Don't be Vague!	800	(1)
Use Specific Object Types (Early Binding)	800	(2)
Use Variables, Not Properties	802	(2)
Avoid Slow Structures	804	(1)
Method A: (Trigonometrical Method)	804	(1)
Method B: (Heron's Formula)	804	(1)
Immediate If (IIf)	805	(1)
Use Integer Arithmetic Where Possible	806	(1)
Use In-Line Code	806	(2)
Use DoEvents Judiciously	808	(2)
Use the Requery Method, not the Requery Action	810	(1)
Use Me	810	(1)
Speed Up Database Operations	810	(1)
Creating Test Data	810	(9)
Generating Random Numbers	819	(2)
Populating the Test Table	821	(1)
Use Indexes	821	(1)
Use Appropriate Recordset Types	822	(2)
Use Bookmarks	824	(1)
DAO or ADO?	824	(2)
Increasing Apparent Speed	826	(5)
Startup Forms and Splash Screens	826	(1)
Using a Start-Up Form	827	(1)
Use Gauges	827	(2)
Remove Code from Form Modules	829	(1)
Pre-Load and Hide Forms	829	(1)
Setting the HasModule Property to False	830	(1)
Cache Data Locally	830	(1)
Network Considerations	831	(1)
Search on Indexed Fields in Attached Tables	831	(1)
Put Non-Table Objects on the Local Machine	832	(1)
Disable AutoExpand	832	(1)
Finishing Touches	832	(3)
Compact the Database	833	(1)
Compile All Modules	834	(1)
Open Databases Exclusively	834	(1)
Summary	835	(1)
Exercises	836	(3)
Moving to Client-Server	839	(54)
What is Client-Server?	840	(1)
Jet vs. SQL Server: Why Move?	840	(3)
Advantages of SQL Server	841	(1)
Disadvantages of SQL Server	842	(1)
Access Data Projects	843	(2)
Database Files	843	(1)
What is an Access Data Project (ADP)?	843	(2)
Creating an Access Data Project	845	(1)
What's New in Access 2002 for ADPs?	845	(1)
Extended Properties	845	(1)
Installing SQL Server Desktop Engine	846	(1)
Running the Upsizing Wizard	847	(6)
Before you Start	847	(5)
What's New in your Database?	852	(1)
Results of Upsizing the IceCream Database	853	(2)
SQL Server Objects	855	(15)
Views	855	(4)
User-Defined Functions (UDFs)	859	(3)
Multi-Line User-Defined Functions	862	(3)
Stored Procedures	865	(1)
What is a Stored Procedure?	865	(5)
Fixing the Queries	870	(7)
qryxSS	870	(1)
Fixing qrySalesSummary	871	(1)
qryxSS	871	(2)
qrySalesSummary	873	(4)
Forms	877	(3)
frmlceCreamPopup	877	(3)
When you have No GUI	880	(10)
SQLDMO	880	(1)
System Stored Procedures	880	(1)
Security Procedures	880	(1)
System Management	881	(1)
To Set the Reference to SQLDMO	881	(3)
SQLDMO and Security	884	(1)
A Note on Security	884	(6)
Summary	890	(3)
ActiveX Data Objects -- ADO	893	(40)
Universal Data Access	893	(5)
Object Linking and Embedding Database (OLE DB)	894	(1)
So, What is OLE DB?	894	(1)
ADO vs. DAO	895	(3)
What is ADO?	898	(33)
Connection	898	(3)
Connection Properties	901	(1)
Errors	902	(2)
Command	904	(1)
Command Properties	905	(1)
CommandType Property	906	(2)
Command Methods	908	(1)
Execute	908	(8)
Recordset	916	(1)
Cursor Type	916	(2)
Locking	918	(4)
Moving Around in Recordsets	922	(1)
Inserting Records	922	(2)
Updating Records	924	(1)
Deleting Records	925	(1)
Finding Records	926	(2)
Fields	928	(1)
Record	928	(1)
Stream	929	(1)
Disconnected Recordsets	929	(2)
Summary	931	(1)
Exercises	931	(2)
Index	933