Transcription
Microsoft Access 2010VBA Macro Programming
About the AuthorRichard Shepherd has worked for many years for major banks and corporations in theUnited Kingdom creating Access databases to solve specific problems. These companiesinclude National Grid plc (electricity distribution), Hertz Car Leasing, NatWest plc (retailbanking), Schroders plc (fund management), BNP Paribas (investment banking), Lloyds TSBplc (corporate banking), The Royal Bank of Scotland plc (investment banking), and theBritish National Health ServiceHe has developed advanced databases for budgeting, business planning, trading reporting,and profit and loss reporting. He has qualified as an accountant with the Association ofChartered Certified Accountants and is now a Fellow of the Association. He is also the authorof Excel VBA Macro Programming and Excel 2007 VBA Macro Programming (McGraw-HillProfessional).Richard works as a freelance software developer and can be contacted attollside@yahoo.com.About the Technical EditorAllen G. Taylor is a 30-year veteran of the computer industry and the author of 27 books,including SQL for Dummies, Database Development for Dummies, and Crystal Reports 2008 forDummies. He teaches database development through a leading online education provider, andlectures internationally on astronomy, history, innovation, and entrepreneurship. His blog addressis www.moontube.wordpress.com, and his web site can be found at www.DatabaseCentral.Info.You can contact Allen at allen.taylor@ieee.org.
Microsoft Access 2010VBA Macro Programming Richard ShepherdNew York Chicago San FranciscoLisbon London Madrid Mexico City MilanNew Delhi San Juan Seoul Singapore Sydney Toronto
Copyright 2011 by The McGraw-Hill Companies. All rights reserved. Except as permitted under the United States CopyrightAct of 1976, no part of this publication may be reproduced or distributed in any form or by any means, or stored in a database orretrieval system, without the prior written permission of the publisher.ISBN: 978-0-07-173858-3MHID: 0-07-173858-4The material in this eBook also appears in the print version of this title: ISBN: 978-0-07-173857-6,MHID: 0-07-173857-6.All trademarks are trademarks of their respective owners. Rather than put a trademark symbol after every occurrence of a trademarked name, we use names in an editorial fashion only, and to the benefit of the trademark owner, with no intention of infringement of the trademark. Where such designations appear in this book, they have been printed with initial caps.McGraw-Hill eBooks are available at special quantity discounts to use as premiums and sales promotions, or for use in corporatetraining programs. To contact a representative please e-mail us at bulksales@mcgraw-hill.com.Information has been obtained by McGraw-Hill from sources believed to be reliable. However, because of the possibility ofhuman or mechanical error by our sources, McGraw-Hill, or others, McGraw-Hill does not guarantee the accuracy, adequacy, orcompleteness of any information and is not responsible for any errors or omissions or the results obtained from the use of suchinformation.TERMS OF USEThis is a copyrighted work and The McGraw-Hill Companies, Inc. (“McGrawHill”) and its licensors reserve all rights in and tothe work. Use of this work is subject to these terms. Except as permitted under the Copyright Act of 1976 and the right to store andretrieve one copy of the work, you may not decompile, disassemble, reverse engineer, reproduce, modify, create derivative worksbased upon, transmit, distribute, disseminate, sell, publish or sublicense the work or any part of it without McGraw-Hill’s priorconsent. You may use the work for your own noncommercial and personal use; any other use of the work is strictly prohibited. Yourright to use the work may be terminated if you fail to comply with these terms.THE WORK IS PROVIDED “AS IS.” McGRAW-HILL AND ITS LICENSORS MAKE NO GUARANTEES OR WARRANTIES AS TO THE ACCURACY, ADEQUACY OR COMPLETENESS OF OR RESULTS TO BE OBTAINED FROM USINGTHE WORK, INCLUDING ANY INFORMATION THAT CAN BE ACCESSED THROUGH THE WORK VIA HYPERLINKOR OTHERWISE, AND EXPRESSLY DISCLAIM ANY WARRANTY, EXPRESS OR IMPLIED, INCLUDING BUT NOTLIMITED TO IMPLIED WARRANTIES OF MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE. McGrawHill and its licensors do not warrant or guarantee that the functions contained in the work will meet your requirements or thatits operation will be uninterrupted or error free. Neither McGraw-Hill nor its licensors shall be liable to you or anyone else forany inaccuracy, error or omission, regardless of cause, in the work or for any damages resulting therefrom. McGraw-Hill has noresponsibility for the content of any information accessed through the work. Under no circumstances shall McGraw-Hill and/orits licensors be liable for any indirect, incidental, special, punitive, consequential or similar damages that result from the use of orinability to use the work, even if any of them has been advised of the possibility of such damages. This limitation of liability shallapply to any claim or cause whatsoever whether such claim or cause arises in contract, tort or otherwise.
To my wife, Elaine, and my son, Alexander.
This page intentionally left blank
Contents at a GlancePart IProgramming in Access VBAChapter 1The Basics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3Chapter 2Variables, Arrays, Constants, and Data Types . . . . . . . . . . . . . . .13Chapter 3Modules, Functions, and Subroutines . . . . . . . . . . . . . . . . . . . . .27Chapter 4Programming Basics: Decisions and Looping . . . . . . . . . . . . . . . . .35Chapter 5Strings, Functions, and Message Boxes . . . . . . . . . . . . . . . . . . .45Chapter 6Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .71Chapter 7Debugging . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .81Chapter 8Errors and the Error Function . . . . . . . . . . . . . . . . . . . . . . . . .91Chapter 9Forms and Reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .97Chapter 10Common Dialog Control . . . . . . . . . . . . . . . . . . . . . . . . . . . . 123Chapter 11Working with the Ribbon . . . . . . . . . . . . . . . . . . . . . . . . . . . 129Chapter 12SQL Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 149Chapter 13Table Macros . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165Part IIObject ModelsChapter 14The Access Object Models . . . . . . . . . . . . . . . . . . . . . . . . . . . 175Chapter 15The Main Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 193vii
viiiMicrosoft Access 2010 VBA Macro ProgrammingChapter 16The DoCmd Object . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 213Chapter 17Using Access to Interact with Other Office Programs . . . . . . . . . . . 221Part IIIAdvanced Techniques in Access VBAChapter 18Charts and Graphs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 231Chapter 19Working with External Databases . . . . . . . . . . . . . . . . . . . . . . 239Chapter 20API Calls . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 253Chapter 21Class Modules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 265Chapter 22Animation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 273Part IVAccess VBA in ActionChapter 23Getting the Login ID . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 279Chapter 24Securing Your Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . 283Chapter 25Creating Audit Trails on Tables . . . . . . . . . . . . . . . . . . . . . . . . 289Chapter 26Creating and Editing Queries in VBA . . . . . . . . . . . . . . . . . . . . . 293Chapter 27Search and Replace in Queries . . . . . . . . . . . . . . . . . . . . . . . . 297Chapter 28Using the DateAdd Function . . . . . . . . . . . . . . . . . . . . . . . . . . 301Chapter 29Monitoring Table Statistics . . . . . . . . . . . . . . . . . . . . . . . . . . 305Chapter 30Handling Large Text Files . . . . . . . . . . . . . . . . . . . . . . . . . . . 307Chapter 31Create and Change Table Structures . . . . . . . . . . . . . . . . . . . . . 313Chapter 32Create an Objects InventoryChapter 33Manipulate Chart Colors . . . . . . . . . . . . . . . . . . . . . . . . . . . . 321Chapter 34Drill Down on Charts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 325Chapter 35Use Excel For Output . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 329. . . . . . . . . . . . . . . . . . . . . . . . . 317
Contents at a GlanceChapter 36Use FTP in VBA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 337Chapter 37What Happens When the 2-GB Limit Is Reached . . . . . . . . . . . . . . 343Chapter 38Creating Menu Structures with the Ribbon . . . . . . . . . . . . . . . . . 345Chapter 39Make Controls on Forms Interactive . . . . . . . . . . . . . . . . . . . . . 349Chapter 40Set Up Levels of User Security . . . . . . . . . . . . . . . . . . . . . . . . 355AppendixASCII Character Codes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 359Index. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 365ix
This page intentionally left blank
ContentsAcknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Part IChapter 1Chapter 2xxixxiiiProgramming in Access VBAThe Basics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3Macros and VBA Modules in Access . . . . . . . .Exploring the Visual Basic Editor in Access . . . . .VBA Project Explorer and Code Windows .Your First Access VBA Macro . . . . . . . .More Exploring of the VBA Project WindowSaving Your Code . . . . . . . . . . . . . . . . .34691112Variables, Arrays, Constants, and Data Types . . . . . . . . . . . . . . .13Variables . . . . . . . . . . . . . . . . . .Implicit Declaration . . . . . . . . .Explicit Declaration . . . . . . . . .The Scope and Lifetime of Variables .Local Variables . . . . . . . . . . .Module-Level Variables . . . . . . .Global Variables . . . . . . . . . .Name Conflicts and Shadowing . . .Static Variables . . . . . . . . . . .Data Types . . . . . . . . . . . . . . . . .Variant . . . . . . . . . . . . . . .Date/Time Values Stored in VariantsEmpty Value . . . . . . . . . . . .Null Values . . . . . . . . . . . . .Other Data Types . . . . . . . . . .131414151516171717181819191920.xi
xiiMicrosoft Access 2010 VBA Macro ProgrammingVBA Data Types . . . . . . . .Numeric Types . . . . .String Types . . . . . .Arrays . . . . . . . . . . . . .Multidimensional ArraysDynamic Arrays . . . .User-Defined Types . . . . . . .Constants . . . . . . . . . . .Reserved Words . . . . . . . .Chapter 3Chapter 4Chapter 5.202021222324242526Modules, Functions, and Subroutines . . . . . . . . . . . . . . . . . . . . .27Modules . . . . . . . . . . . . . . . . . . . . .The Difference Between Subroutines and FunctionsWriting a Simple Subroutine . . . . . . . . . . .Writing a Simple Function . . . . . . . . . . . .Public and Private Functions and Subroutines . . .Argument Data Types . . . . . . . . . . . . . . .Optional Arguments . . . . . . . . . . . . . . .Passing Arguments by Value . . . . . . . . . . .2728293032333333Programming Basics: Decisions and Looping . . . . . . . . . . . . . . . . .35Decisions . . . . . . . . . . . . . . .Multiple Conditional StatementsSelect Case Statements . . . .Looping . . . . . . . . . . . . . . .For.Next Loops . . . . . . . .For Each Loops . . . . . . . .Do Until Loops . . . . . . . .While.Wend Loops . . . . . .Early Exit of Loops . . . . . .363839404041424243Strings, Functions, and Message Boxes . . . . . . . . . . . . . . . . . . .45Strings . . . . . . . . . . . . . . . . . .Concatenation . . . . . . . . . .Splitting Strings . . . . . . . . . .Changing the Appearance of StringsSearching Strings . . . . . . . . .Functions . . . . . . . . . . . . . . . . .Len . . . . . . . . . . . . . . .45464647484949. . . . . . . . . . . . . . . . . . . . .
xiiiContentsAbs . . . . . . .Int . . . . . . .Sqr . . . . . . .Asc . . . . . . .Chr . . . . . . .Conversion Functions . .CStr . . . . . .CInt . . . . . .CLng . . . . . .CDbl . . . . . .Val . . . . . . .Format Function . . . .Date and Time FunctionsNow . . . . . .Date . . . . . .Time . . . . . .DateAdd . . . .DateDiff . . . .DatePart . . . .DateSerial . . .DateValue . . .Day . . . . . .Hour . . . . . .Month . . . . .Second . . . . .Minute . . . . .Year . . . . . .Weekday . . . .The SendKeys CommandMessage Boxes . . . . .Chapter 616161626266Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .71Arithmetic Operators* Operator . Operator .– Operator ./ Operator .\ Operator .727272737373.
xivMicrosoft Access 2010 VBA Macro Programming Operator . .Mod OperatorComparison OperatorsConcatenation OperatorLogical Operators . . .And Operator .Not Operator .Or Operator .Xor Operator .Other Operators . . .Is Operator . .Like OperatorChapter 7Chapter 8. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .747474757575767678797979Debugging . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .81Types of Errors . . . . . . . . . . . . . . . . .Compile Errors . . . . . . . . . . . . .Runtime Errors . . . . . . . . . . . . .Logic Errors . . . . . . . . . . . . . .Design Time, Runtime, and Break Mode . . . .Breakpoints . . . . . . . . . . . . . . . . . .Using Stop Statements . . . . . . . . . . . . .Running Selected Parts of Your Code . . . . . .Single Stepping . . . . . . . . . . . . .Procedure Stepping . . . . . . . . . . .Call Stack Dialog . . . . . . . . . . . .The Debug Window . . . . . . . . . . . . . . .Events That Can Cause Problems When DebuggingMouse Down . . . . . . . . . . . . . .Key Down . . . . . . . . . . . . . . .Got Focus / Lost Focus . . . . . . . . .Using Message Boxes in Debugging . . . . . . .Avoiding Bugs . . . . . . . . . . . . . . . . .818182828284848485858586888888898990Errors and the Error Function . . . . . . . . . . . . . . . . . . . . . . . . .91The Resume Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Implications of Error Trapping . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Generating Your Own Errors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .939494.
xvContentsChapter 9Chapter 10Forms and Reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .97Forms . . . . . . . . . . . . . . . . . . .Creating a Simple Form . . . . . . .Using Relational Fields in Your FormSubforms . . . . . . . . . . . . . .Datasheet View . . . . . . . . . . .Making Your Form Look ProfessionalUsing a Custom Ribbon on the FormOther Controls . . . . . . . . . . .Using VBA on Forms . . . . . . . .Using Events . . . . . . . . . . . .Reports . . . . . . . . . . . . . . . . . .Creating a Simple Report . . . . . .Keeping Your Report Tidy . . . . . .Using Formulas on Your Report . . .Using a Custom Ribbon on the ReportUsing VBA on Forms . . . . . . . .Using Events . . . . . . . . . . . .124126126128Working with the Ribbon . . . . . . . . . . . . . . . . . . . . . . . . . . . 129Creating a Ribbon Customization . . . . .Using the Custom UI Editor . . . . . . . .Creating Code for Your Custom Buttons . .Images . . . . . . . . . . . . . . . . . .How Can You Use VBA Code with the RibbonMore on the Ribbon . . . . . . . . . . .Chapter 12.Common Dialog Control . . . . . . . . . . . . . . . . . . . . . . . . . . . . 123The Open File Dialog . . . . .The Save File As DialogThe Color Dialog . . .The Print Dialog . . . . . . .Chapter 11. .130132136138138141SQL Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 149Using the Query Design WindowSelect Query . . . . . . . . . .Union Query . . . . . . . . . .The Delete Query . . . . . . .Make Table Query . . . . . . .150154156157158
xviMicrosoft Access 2010 VBA Macro ProgrammingAppend Query . . . . . . . . . . . .Update Query . . . . . . . . . . . .Pass-Through Query . . . . . . . . .Using Custom Functions within QueriesChapter 13.Table Macros . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165Creating a Table Macro . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Part IIChapter 14The Access Object Models . . . . . . . . . . . . . . . . . . . . . . . . . . . 175.Chapter 18213Using Access to Interact with Other Office Programs . . . . . . . . . . . 221Driving Microsoft Outlook . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Driving Access from Other Office Programs . . . . . . . . . . . . . . . . . . . . . . . . .Part III193193201201205205207208The DoCmd Object . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 213DoCmd Methods . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Chapter 17176178180184186190The Main Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 193Application Object . . . . . . . . . . . . . . .Main Properties, Methods, and CollectionsMe Object . . . . . . . . . . . . . . . . . . .Main Properties, Methods, and CollectionsCurrentDb Object . . . . . . . . . . . . . . . .Main Properties, Methods, and CollectionsRecordset Object . . . . . . . . . . . . . . . .Main Properties, Methods, and CollectionsChapter 16165Object ModelsProperties and Methods Explained . .Manipulating Properties . . .Calling Methods . . . . . . .Using the Object Browser . . . . . . .Communicating with the TablesHierarchy . . . . . . . . . . . . . .Chapter 15159161163163224226Advanced Techniques in Access VBACharts and Graphs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 231Working with the Series Collection Object . . . . . . . . . . . . . . . . . . . . . . . . . .Exporting a Chart as a Picture File . . . . . . . . . . . . . . . . . . . . . . . . . . . . .236237
ContentsChapter 19Working with External Databases . . . . . . . . . . . . . . . . . . . . . . 239Linking to Other Access DatabasesODBC Links and DSNs . . . . . .Using a DSN . . . . . . . . . . .Problems Using Linked Tables . .Using Pass-Through Queries . . .Using ADO . . . . . . . . . . . .Chapter 20.Part IVChapter 23.266266267268270272Access VBA in ActionGetting the Login ID . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 279281Securing Your Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . 283Using an ACCDE File to Protect Your Application . . . . . . . . . . . . . . . . . . . . . . .Using VBA to Lock Your Application Down . . . . . . . . . . . . . . . . . . . . . . . . . .Chapter 25253254254256258263Animation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 273A Simple Use of the User Name . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Chapter 24240240243245246248Class Modules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 265Creating a Data Services Layer . . . . . . . . . . . .Inserting a Class Module . . . . . . . . . . . . . . .Creating an Object . . . . . . . . . . . . . . . . . .Creating a Collection . . . . . . . . . . . . . . . . .Using the PNames Collection . . . . . . . . . . . . .Using the PNames Collection as a Multi-tier ApplicationChapter 22.API Calls . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 253What Is an API Call? . . . . . . . . . . . .Using an API Call . . . . . . . . . . . . . .Getting Disk Space . . . . . . . . .Reading from and Writing to INI FilesReading Keyboard Activity . . . . .Playing Multimedia Sounds . . . . .Chapter 21.283284Creating Audit Trails on Tables . . . . . . . . . . . . . . . . . . . . . . . . 289Who Is the User? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .The Audit Trail on the Table Structure . . . . . . . . . . . . . . . . . . . . . . . . . . . .Using Events to Create the Audit Trail . . . . . . . . . . . . . . . . . . . . . . . .289290290xvii
xviiiMicrosoft Access 2010 VBA Macro ProgrammingChapter 26Creating and Editing Queries in VBA . . . . . . . . . . . . . . . . . . . . . 293Creating a New Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Deleting an Existing Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Updating a SQL Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Chapter 27Search and Replace in Queries . . . . . . . . . . . . . . . . . . . . . . . . 297Searching for a Specific String Within All Queries . . . . . . . . . . . . . . . . . . . . . .Search and Replace in a Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Chapter 28293294294297299Using the DateAdd Function . . . . . . . . . . . . . . . . . . . . . . . . . . 301Using DateAdd to Pause Your Code . . . . . . . . . . . . . . . . . . . . . . . . . . . . .303Chapter 29Monitoring Table Statistics . . . . . . . . . . . . . . . . . . . . . . . . . . 305Chapter 30Handling Large Text Files . . . . . . . . . . . . . . . . . . . . . . . . . . . 307Chapter 31Create and Change Table Structures . . . . . . . . . . . . . . . . . . . . . 313Creating a Table . . . . .Deleting a Table or a FieldEditing Field Properties . .Creating Indexes . . . . .313314315315Chapter 32Create an Objects Inventory. . . . . . . . . . . . . . . . . . . . . . . . . 317Chapter 33Manipulate Chart Colors . . . . . . . . . . . . . . . . . . . . . . . . . . . . 321Chapter 34Drill Down on Charts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 325Chapter 35Use Excel For Output . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 329Using a Recordset to Create a Spreadsheet .Using an Existing Spreadsheet as a TemplateTransferring Individual Numbers to Excel . .Allowing Users to Design Their Excel ReportsChapter 36.329330332333Use FTP in VBA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 337Using VBA Code to Transfer Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Use of Semaphore Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .338340Chapter 37What Happens When the 2-GB Limit Is Reached . . . . . . . . . . . . . . 343Chapter 38Creating Menu Structures with the Ribbon . . . . . . . . . . . . . . . . . 345
ContentsChapter 39Make Controls on Forms Interactive . . . . . . . . . . . . . . . . . . . . . 349Creating a Simple Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Creating a Form for the Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .350351Chapter 40Set Up Levels of User Security . . . . . . . . . . . . . . . . . . . . . . . . 355AppendixASCII Character Codes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 359Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 365xix
This page intentionally left blank
AcknowledgmentsWith grateful thanks to all the people at McGraw-Hill who have made this bookhappen. Special thanks to Joya Anthony and Vipra Fauzdar, who have managedthis project and dealt with the vast numbers of e-mails it has generated. Otherswho deserve special mention for all the help they have given to the project include WendyRinaldi (who asked me to write this book), Allen Taylor, and Melinda Lyttle.Finally, a big thank you to Elaine and Alexander for all their support on this project.xxi
This page intentionally left blank
IntroductionDatabase macro programming has changed enormously over the last 15 years,having gone from Access Basic macros to VBA. Another major change occurredwith Office 95, when macros went to VBA modules in a separate environmentaccessed via the Visual Basic Editor. It used to be fairly basic: code was entered into a specialdesign window. Although the language was rather powerful in its own right, it was not astructured language and could certainly not be described as object-oriented. The number ofcommands was limited, and a fair amount of ingenuity was required to do certain tasks. Themain advantage was that it was fairly easy to learn and understand; many programmers cuttheir teeth by initially writing database macros.If anyone other than the original author examined the code, it could take days to find outexactly how it worked and what it was doing. Commercial companies frequently found thatwhen the author of a complicated
Chapter 16 The DoCmd Object. 213 Chapter 17 Using Access to Interact with Other Office Programs. 221 Part III Advanced Techniques in Access VBA Chapter 18 Charts and Graphs. 231 Chapter 19 Working with External Databases. 239 Chapter 20 API Calls. 253 Chapter 21 Class Modules. 265 Chapter 22 Animation. 273 Part IV Access VBA in Action Chapter 23 Getting the Login ID.
![[MS-VBAL]: VBA Language Specification - Framework](/img/22/ms-vbal.jpg)
