Microsoft Access 2010 VBA Macro Programming

## Develop custom Access VBA macros Perfect for power users, Microsoft Access 2010 VBA Macro Programming reveals how to maximize the features and functionality of Access 2010. You'll get in-depth details on Access VBA programming and application development followed by 20 real-world projects--complete with source code--that show you how to set up specific subroutines and functions. This practical resource then explains how to include the subroutines in the Access menu system and transform a set of interrelated VBA macros into an Access add-in package. Create your own Access 2010 VBA macros right away with help from this hands-on guide. Learn how to:* Create and enhance forms and reports * Design custom dialog boxes and buttons * Develop custom menus for the Ribbon * Use SQL queries with VBA * Create table macros * Use Office object models to interact with other Microsoft applications * Create and manipulate charts and graphs * Work with external databases * Add functionality to your programs with API calls * Animate objects in Access * Enhance database security * Create audit trails * Handle large text files * Transfer data via FTP Contents 12 Acknowledgments 22 Introduction 24 Part I: Programming in Access VBA 28 Chapter 1 The Basics 30 Macros and VBA Modules in Access 30 Exploring the Visual Basic Editor in Access 31 Saving Your Code 39 Chapter 2 Variables, Arrays, Constants, and Data Types 40 Variables 40 Data Types 45 VBA Data Types 47 Arrays 49 User-Defined Types 51 Constants 52 Reserved Words 53 Chapter 3 Modules, Functions, and Subroutines 54 Modules 54 The Difference Between Subroutines and Functions 55 Writing a Simple Subroutine 56 Writing a Simple Function 57 Public and Private Functions and Subroutines 59 Argument Data Types 60 Optional Arguments 60 Passing Arguments by Value 60 Chapter 4 Programming Basics: Decisions and Looping 62 Decisions 63 Looping 67 Chapter 5 Strings, Functions, and Message Boxes 72 Strings 72 Functions 76 Conversion Functions 78 Format Function 79 Date and Time Functions 83 The SendKeys Command 89 Message Boxes 93 Chapter 6 Operators 98 Arithmetic Operators 99 Comparison Operators 101 Concatenation Operator 102 Logical Operators 102 Other Operators 106 Chapter 7 Debugging 108 Types of Errors 108 Design Time, Runtime, and Break Mode 109 Breakpoints 111 Using Stop Statements 111 Running Selected Parts of Your Code 111 The Debug Window 113 Events That Can Cause Problems When Debugging 115 Using Message Boxes in Debugging 116 Avoiding Bugs 117 Chapter 8 Errors and the Error Function 118 The Resume Statement 120 Implications of Error Trapping 121 Generating Your Own Errors 121 Chapter 9 Forms and Reports 124 Forms 124 Reports 141 Chapter 10 Common Dialog Control 150 The Open File Dialog 151 The Print Dialog 155 Chapter 11 Working with the Ribbon 156 Creating a Ribbon Customization 157 Using the Custom UI Editor 159 Creating Code for Your Custom Buttons 163 Images 165 How Can You Use VBA Code with the Ribbon 165 More on the Ribbon 168 Chapter 12 SQL Queries 176 Using the Query Design Window 177 Select Query 181 Union Query 183 The Delete Query 184 Make Table Query 185 Append Query 186 Update Query 188 Pass-Through Query 190 Using Custom Functions within Queries 190 Chapter 13 Table Macros 192 Creating a Table Macro 192 Part II: Object Models 200 Chapter 14 The Access Object Models 202 Properties and Methods Explained 203 Using the Object Browser 211 Hierarchy 217 Chapter 15 The Main Objects 220 Application Object 220 Me Object 228 CurrentDb Object 232 Recordset Object 234 Chapter 16 The DoCmd Object 240 DoCmd Methods 240 Chapter 17 Using Access to Interact with Other Office Programs 248 Driving Microsoft Outlook 251 Driving Access from Other Office Programs 253 Part III: Advanced Techniques in Access VBA 256 Chapter 18 Charts and Graphs 258 Working with the Series Collection Object 263 Exporting a Chart as a Picture File 264 Chapter 19 Working with External Databases 266 Linking to Other Access Databases 267 ODBC Links and DSNs 267 Using a DSN 270 Problems Using Linked Tables 272 Using Pass-Through Queries 273 Using ADO 275 Chapter 20 API Calls 280 What Is an API Call? 280 Using an API Call 281 Chapter 21 Class Modules 292 Creating a Data Services Layer 293 Inserting a Class Module 293 Creating an Object 294 Creating a Collection 295 Using the PNames Collection 297 Using the PNames Collection as a Multi-tier Application 299 Chapter 22 Animation 300 Part IV: Access VBA in Action 304 Chapter 23 Getting the Login ID 306 A Simple Use of the User Name 308 Chapter 24 Securing Your Database 310 Using an ACCDE File to Protect Your Application 310 Using VBA to Lock Your Application Down 311 Chapter 25 Creating Audit Trails on Tables 316 Who Is the User? 316 The Audit Trail on the Table Structure 317 Chapter 26 Creating and Editing Queries in VBA 320 Creating a New Query 320 Deleting an Existing Query 321 Updating a SQL Query 321 Chapter 27 Search and Replace in Queries 324 Searching for a Specific String Within All Queries 324 Search and Replace in a Query 326 Chapter 28 Using the DateAdd Function 328 Using DateAdd to Pause Your Code 330 Chapter 29 Monitoring Table Statistics 332 Chapter 30 Handling Large Text Files 334 Chapter 31 Create and Change Table Structures 340 Creating a Table 340 Deleting a Table or a Field 341 Editing Field Properties 342 Creating Indexes 342 Chapter 32 Create an Objects Inventory 344 Chapter 33 Manipulate Chart Colors 348 Chapter 34 Drill Down on Charts 352 Chapter 35 Use Excel For Output 356 Using a Recordset to Create a Spreadsheet 356 Using an Existing Spreadsheet as a Template 357 Transferring Individual Numbers to Excel 359 Allowing Users to Design Their Excel Reports 360 Chapter 36 Use FTP in VBA 364 Using VBA Code to Transfer Files 365 Use of Semaphore Files 367 Chapter 37 What Happens When the 2-GB Limit Is Reached 370 Chapter 38 Creating Menu Structures with the Ribbon 372 Chapter 39 Make Controls on Forms Interactive 376 Creating a Simple Table 377 Creating a Form for the Table 378 Chapter 40 Set Up Levels of User Security 382 Appendix: ASCII Character Codes 386 Index 392 A 392 B 393 C 393 D 393 E 394 F 395 G 395 H 396 I 396 K 396 L 396 M 396 N 397 O 397 P 397 Q 398 R 398 S 398 T 399 U 399 V 399 W 400 X 400 Y 400

Take your database skills to the next level—develop powerful, custom Access 2010 applications using Visual Basic for Applications

Microsoft Access 2010 VBA Macro Programming guides you through the structure of VBA, showing you how to use it specifically in Access. The book explains, step by step, how to write VBA code—from the basics to dealing with errors and debugging code. This practical guide includes examples that illustrate how to use VBA in Access to do specific tasks. You’ll learn real-world skills by example.



Microsoft Access 2010 VBA Macro Programming features:

• Easy-to-understand instructions that explain how to accomplish programming tasks right away

• 20 practical sample applications

• Details on adding custom menus and Access add-ins



All-inclusive coverage:

The Basics; Variables, Arrays, Constants and Data Types; Modules, Functions and Subroutines; Programming Basics: Decisions and Looping; Strings and Functions and Message Boxes; Operators; Debugging; Errors and the Error Function; Forms and Reports; Common Dialog Control; Working with the Ribbon; SQL Queries; Data Macros/Triggers; The Access and DAO Object Models; The Access and DAO Object Models - Main Objects; The DoCmd Object; Using Access to Interact with Other Office Programs; Charts and Graphs; Working with External Databases; API Calls; Class Modules; Animation; Get the Login ID; Secure Your Database; Creating Audit Trails on Tables; Create and Edit Queries in VBA; Search and Replace in Queries; Compact the Current Database using VBA; Monitoring Table Statistics; Handling Large Text Files; Create and Change Table Structures; Create and Objects Inventory; Manipulate Charts; Drill Down on Charts; Create an Excel Spreadsheet for output of Data; Using FTP in VBA; Use of Data Macros/Triggers; Use of Ribbon; Make Controls on Forms Interactive; Set Up Levels of User Security

"Perfect for power users, Microsoft Access 2010 VBA Macro Programming reveals how to maximize the features and functionality of Access 2010. You'll get in-depth details on Access VBA programming and application development followed by 20 real-world projects--complete with source code--that show you how to set up specific subroutines and functions. This practical resource then explains how to include the subroutines in the Access menu system and transform a set of interrelated VBA macros into an Access add-in package. Create your own Access 2010 VBA macros right away with help from this hands-on guide"--Provided by publisher