Database Automation using VBA - ucb-access.org

1 Database Automation using VBA UC BERKELEY EXTENSION MICHAEL KREMER, E-mail: Web Site: Copyright 2010 Michael Kremer All rights reserved. This publication, or any part thereof, may not reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording, storing, or otherwise without express written permission. ICON KEY Note Example Warning 5th Edition Database Automation using VBA (Advanced Microsoft access ) Page i TABLE OF CONTENTS 1. Introduction to VBA _____ 6 What is VBA? _____ 6 VBA Core Elements _____ 7 Objects _____ 7 Properties _____ 7 Methods _____ 7 Events _____ 8 Variables _____ 8 VBA Program Structure _____ 9 Sub Procedure _____ 9 Function Procedure: _____ 9 Standard Module _____ 10 Class Modules _____ 11 Anatomy of the VBA Editor _____ 13 Project Explorer _____ 14 Properties Window _____ 14 Code Window _____ 14 Immediate Window _____ 15 VBA Code vs.

2 Macros _____ 16 2. VBA Basics _____ 19 Basics of Variables _____ 19 Usage of Variables _____ 19 Assignment of Variables _____ 22 Naming of Variables _____ 23 Sub Procedure _____ 24 Function Procedures _____ 28 Other VBA Elements _____ 31 Comments _____ 31 Executable Line of Code _____ 32 Constants _____ 34 Symbolic or User-defined Constants: _____ 34 Intrinsic or System-defined Constants _____ 35 3. Executing VBA _____ 36 Control Structures _____ 36 Database Automation using VBA (Advanced Microsoft access ) Page ii Decision Structures _____ 36 If Statement _____ 37 Select Case Statement _____ 42 Loop Structures _____ 45 Do Loop _____ 46 For Next Loop _____ 49 Exiting a Control Structure _____ 51 Message Box Statement/Function _____ 53 Message Box Statement _____ 53 The Message Box function _____ 55 Arrays _____ 56 Overview of Arrays _____ 56 Fixed-Size Arrays _____ 58 Dynamic-Size Arrays _____ 59 Nulls in VBA _____ 60 4.

3 Declaring Variables _____ 62 Overview of Variables _____ 62 The Dim Statement _____ 62 Implicit and Explicit Variable Declaration _____ 63 Scope of Variables _____ 67 Lifetime of Variables _____ 73 5. Fundamental Data Types _____ 76 Working with Strings _____ 76 Comparing Strings _____ 76 String Arrays _____ 81 Split Function _____ 81 Join Function _____ 82 Working with Numbers _____ 84 Integer Data Types _____ 84 Boolean Data Type _____ 84 Floating-Point Data Types _____ 86 Fixed-Point Data Types _____ 86 Conversion, Rounding, Truncating and Formatting _____ 88 Working with Dates and Times _____ 89 Variant Data Type _____ 93 6. Calling Procedures _____ 95 Syntax of Calling Procedures _____ 95 Database Automation using VBA (Advanced Microsoft access ) Page iii Passing a Parameter by Reference or by Value _____ 98 Sending a variable by reference: _____ 98 Sending a variable by value: _____ 99 Named Arguments _____ 99 Optional Arguments _____ 100 7.

4 Working with Objects _____ 101 Overview of Objects _____ 101 Properties _____ 102 Methods _____ 102 Events _____ 102 Objects in MS access _____ 103 The Collection Object _____ 105 Navigating Object Hierarchy _____ 109 Referring to Objects _____ 118 Object Variables _____ 127 Working with Objects in a Collection _____ 130 Object Properties _____ 135 Object Methods _____ 137 using the Object Browser _____ 141 8. Events in MS access _____ 143 Overview of Events _____ 143 Sequence of Events _____ 146 Responding to Events _____ 148 Form Events _____ 154 Data Related Events _____ 163 Control Events _____ 172 Special Events _____ 176 Report Events _____ 178 Canceling an Event _____ 179 9.

5 Error Handling in MS access _____ 181 Overview of Errors _____ 181 Types of Errors _____ 181 Syntax Errors _____ 184 Run-time Errors _____ 187 Database Automation using VBA (Advanced Microsoft access ) Page iv Logic Errors (aka Bugs) _____ 190 Handling VBA Errors _____ 191 Error Handling Routines _____ 192 Getting Information about VBA Errors _____ 197 Handling access Interface and Database Errors _____ 199 AccessError Method _____ 201 OnError Event _____ 202 Debugging Tools _____ 205 Immediate Window _____ 206 Breakpoint and Stepping _____ 207 Call Stack _____ 209 Watch Window and Quick Watch _____ 210 Data Tips and Locals Pane _____ 211 10.

6 Introduction to Data access Technologies _____ 214 Overview of Data access Technologies _____ 214 SQL Programming using VBA _____ 216 11. Data access Objects (DAO) _____ 221 Overview of DAO _____ 221 DAO objects _____ 221 DAO Database Objects _____ 224 DAO Recordset Object _____ 228 Sorting/Filtering of Recordsets _____ 234 Looping through Recordsets _____ 236 12. Updating data using DAO _____ 241 Overview of Updating Data _____ 241 Finding Records _____ 242 Updating Records using DAO _____ 245 Bookmarks and RecordSetClone Objects _____ 252 13. ActiveX Data Objects (ADO) _____ 257 Overview of ADO _____ 257 Programming using ADO _____ 258 14. Examples, Part I _____ 263 Transaction Processing using DAO _____ 263 NotInList Event _____ 266 Database Automation using VBA (Advanced Microsoft access ) Page v Word Mail Merge from access _____ 268 Linked Table Verification Process _____ 271 15.

7 Examples Part II _____ 274 Controlled Close, Save, Undo _____ 274 Multi-Select Listbox _____ 277 Complex Search Form _____ 280 Database Automation using VBA (Advanced Microsoft access ) 1. Introduction to VBA 6 What is VBA? Notes: 1. INTRODUCTION TO VBA BA is a programming language based on Microsoft Visual Basic(VB) language. It is a structured and procedural language. Structured programming essentially means that the programming language and its control structures determine the flow of the program. Structured programming does not use any Goto statements to force the flow of the program (= unstructured programming). A procedural programming language specifies the What and the How, meaning what is the goal and how do you want to reach this goal.

8 Procedural programming languages are also referred to as imperative programming languages (=specifying the steps the program must take to reach the desired state). For example, SQL is a non-procedural language, you only specify the What, but not the How. What is VBA? Visual Basic is a stand-alone language that allows you to build complete applications in a windows environment. Visual Basic for Applications (VBA) is a hosted language, which means it runs within another application. VBA is most commonly used with the common office applications (Word, Excel, Powerpoint, access ), but it is also licensed to third party applications (WordPerfect, Corel Draw). VBA interacts very easily with the host application by pointing to the object library of the host application.

9 An object library is a file that exposes all objects of the host application to VBA. For example, in Word, an object is the Word application itself, but also a document, for instance. Being able to reference these objects gives you the power to programmatically manipulate these objects. VBA is a subset of Visual Basic (VB). It is very similar to VB, but it does not contain all the functionalities of VB since it is hosted within another environment. The hosting environment (such as MS Word or access ) builds the foundation for using VBA. VDay Section 1 1 Database Automation using VBA (Advanced Microsoft access ) 1. Introduction to VBA 7 VBA Core Elements Notes: VBA Core Elements VBA is comprised of some core elements that form the basic framework of the VBA environment.

10 These core elements include objects, properties, methods, events and variables. They are explained in more detail below. Objects An object is generally thought of as a physical thing. For example, an automobile object gives you access to the entire family of cars and its objects. VBA is an object-oriented programming (OOP) language. This kind of language enables programmers to define complete data structures from data types to the operations that can be applied to the data structure. You can create an entire object that contains both data and the operations that the object can perform. It is also possible to create relationships between these objects. There are many objects exposed by the object library of a particular hosting environment.