The book also discusses how to create and name variables, how to use Data Access Object (DAO) and ActiveX Data Object (ADO) to manipulate data both within Access and within other applications, proper error handling techniques, and advanced functions such as creating classes and using APIs. Key new objects such as using Macros and the Ribbon are explored, too, as are forms and reports, the two most powerful tools for working with and displaying data. Working with other applications is covered extensively both in a general nature and for working specifically with Microsoft Office applications, Windows SharePoint Services, and SQL Server. Of course, this book wouldn’t be complete without discussing security issues and the Developer Extensions.
The book also discusses how to create and name variables, how to use Data Access Object (DAO) and ActiveX Data Object (ADO) to manipulate data both within Access and within other applications, proper error handling techniques, and advanced functions such as creating classes and using APIs. Key new objects such as using Macros and the Ribbon are explored, too, as are forms and reports, the two most powerful tools for working with and displaying data. Working with other applications is covered extensively both in a general nature and for working specifically with Microsoft Office applications, Windows SharePoint Services, and SQL Server. Of course, this book wouldn’t be complete without discussing security issues and the Developer Extensions.
Access 2007 VBA Programmer's Reference
1152
Access 2007 VBA Programmer's Reference
1152eBook
Available on Compatible NOOK devices, the free NOOK App and in My Digital Library.
Related collections and offers
Overview
The book also discusses how to create and name variables, how to use Data Access Object (DAO) and ActiveX Data Object (ADO) to manipulate data both within Access and within other applications, proper error handling techniques, and advanced functions such as creating classes and using APIs. Key new objects such as using Macros and the Ribbon are explored, too, as are forms and reports, the two most powerful tools for working with and displaying data. Working with other applications is covered extensively both in a general nature and for working specifically with Microsoft Office applications, Windows SharePoint Services, and SQL Server. Of course, this book wouldn’t be complete without discussing security issues and the Developer Extensions.
Product Details
| ISBN-13: | 9781118058527 |
|---|---|
| Publisher: | Wiley |
| Publication date: | 06/01/2011 |
| Sold by: | JOHN WILEY & SONS |
| Format: | eBook |
| Pages: | 1152 |
| File size: | 14 MB |
| Note: | This product may take a few minutes to download. |
About the Author
Rob Cooper is a test lead on the Access team at Microsoft. During the Access 2007 release, he led the security efforts across the test team and worked on several new features including disabled mode, database encryption, Office Trust Center, and sorting, grouping and totals. Rob also led efforts around the Access object model and continues to provide direction around programmability and security in Access.
A long-time fan of Access, Rob is a frequent speaker at the Seattle Access Group and PNWADG meetings and has written for the Microsoft Knowledge Base and Access Advisor.
Geoffrey Griffith is an avid Access user who contributed to the Access 2007 product as a Software Design Engineer in Test for the Microsoft Office Access team. He enjoys participating in software community events by attending and speaking for local users groups and helping all those who would seek it.
Armen Stein is a Microsoft Access MVP and the president of J Street Technology, Inc., a team of database application developers in Redmond, Washington. Armen is President Emeritus of the Pacific Northwest Access Developers Group, and has also spoken at Seattle Access and Portland Access Users Group meetings. He has taught database classes at Bellevue Community College, and also developed and taught one-day training classes on Access and Access/SQL Server development.
Read an Excerpt
Access 2007 VBA Programmer's Reference
By Teresa Hennig Rob Cooper Geoffrey L. Griffith Armen Stein
John Wiley & Sons
Copyright © 2007 John Wiley & Sons, LtdAll right reserved.
ISBN: 978-0-470-04703-3
Chapter One
Introduction to Microsoft Access 2007What is Microsoft Office Access 2007? Simply put, it's the newest version of Access, a well-known and widely used relational database management system (RDBMS) for Microsoft Windows designed for building small- to medium-scale database applications. Access 2007 provides a rich set of features and tools for designing, creating, storing, analyzing, and viewing data, as well as the capability to connect to a large variety of other data sources. Access combines ease-of-use features with software development capabilities to support a wide range of user skill sets. Access also provides a Primary Interop Assembly (PIA) to allow other development platforms, such as Microsoft Visual Studio .NET 2005, to manage data using an Access database or even incorporate Access functionality into an external application.
If you're reading this book, you probably already know a good deal about Microsoft Office Access 2007 or a previous version. While this book presents the various aspects of programming Access applications using VBA code, this chapter provides an overview of Access and discusses some ofthe basics. Although it's possible to create and administer a database application using only code, there are also many tools for creating, designing, and editing database objects. Some of the more common tools are briefly covered in this chapter. If you've used Access before and are familiar with the visual designers and other Access tools, you can easily skip ahead to Chapter 3 to learn about the new features included in Access 2007.
A Brief History of Access
Microsoft Access has been around for nearly 15 years. The first version of Access, Microsoft Access 1.0, was released in November of 1992. Built on top of the Jet Database Engine, Access was designed to enable users to create and manipulate Jet-compatible database applications through a variety of visual designers and a scripting language called Access Basic. Access quickly became one of the most popular database development systems for Windows and the user base started growing rapidly.
With Microsoft Access 95, the fourth release, Access was adopted as a new member of the Microsoft Office product line. This was the perfect move for the product because it allowed Access to integrate and leverage many great features shared among other Office applications, such as Spell Checking or the Format Painter. Access Basic was replaced with the integration of Visual Basic for Applications (VBA) across the Office applications to provide a common programming language for creating solutions using the core Office products.
By the time Access 97 was released, millions of people were using Access routinely to build applications to store and manage their personal and business data. Access 97 is still in use today by many individual and business users and it is widely regarded as one of the best releases of Access ever. Some of the key features for that release were increased Web support, the hyperlink data type, and many new wizards. For developers, the release showcased the introduction of the Object Browser, VBA class modules, source code control, conditional compilations, and programmable command bars. That's a truly compelling set of features for users developing advanced applications.
Access 2003 VBA Programmer's Reference, the predecessor to this book, focused on the Microsoft Office Access 2003 product, the eighth release of Access. By 2003, everyone from individual users to the United States government was using Access. Access 2003 included a number of feature enhancements, as well as new additions. XML support, Data Import, and Data Export were improved in a number of ways, and signed database projects and disabled mode were introduced for added security.
Fast-forward to the present, and you have Microsoft Office Access 2007, the ninth full release of Access. Now shipping in 38 languages, Access is used throughout the world on Windows systems everywhere. For this release, there is a large focus on ease of use, and you'll notice major changes from previous versions as soon as you boot the program. Access 2007 probably has as many new elements and enhancements as the last four releases combined, and there are a number of developer-oriented features as well. One of the largest features is a new database engine called the Access Connectivity Engine (ACE), which supports several new data types, such as Attachment fields and Complex Data. Additionally, there are a number of new form and report designers, which make build Access database solutions even faster than before. After trying out Access 2007, I'm sure you'll see that Microsoft Office Access 2007 is the absolute best release of Access ever.
Is Access the Only Database System?
Some may ask the question, is Access the end-all to database systems? The simple answer is, "No." Access is not the only database product on the market, nor is it the only database product available from Microsoft or for Windows. There are times you might want to use a different type of database system such as SQL Server or SQL Server Express. If you've only used Microsoft Access for your database needs, you might be wondering why you'd ever need another database system. It could be argued that Access can connect to so many different types of data sources that there's no need for other front-end products. Moreover, developers could make a case that an Access database is a perfect solution for data storage for an application developed outside of the Access client, such as a .NET application that stores data in a back-end Access database. Still, there may be several reasons to use other database products, and the following sections discuss Access features, as well as other database system features, to help you choose what is right for your scenario.
Microsoft Office Access 2007
Microsoft Access is the perfect solution for single-user applications. Access provides many built-in features for quickly and easily building forms, reports, charts, and queries to view data. The user interface (UI) is designed to be simple and intuitive so that even novice users can accomplish their tasks. Developers have the ability to create Macros and write VBA code to support application development. Another key feature of an Access database that is often overlooked is the storage of all database objects in a single file, which makes the database easy to distribute to others. The maximum supported database size is 2GB of data, which provides ample space for almost any personal database.
Multiple-user applications are supported by Access, although there are a number of considerations of which you should be aware. There are record-locking options that affect how data is accessed, and some operations require the database to be opened in exclusive mode, thus locking other users out of the application. The recommendation for multi-user Access applications is to create a distributable front-end database (for each user) that connects to a backend database that stores the data. For example, a front-end application written in Visual Basic can take advantage of DAO or ADO to make calls to retrieve and modify data in the back-end Access database. This type of application works well in a single- or multi-user environment, because the data is only manipulated when DAO or ADO code manipulates the back-end database. Even then, applications that have large numbers of data transactions may encounter performance limitations in the ACE database engine.
SQL Server 2005 Express Edition
The Microsoft SQL Server 2005 Express edition is a scaled-down version of SQL Server 2005. Microsoft provides this product for free and it can be distributed for free as one of many ways to integrate data with .NET applications. It is ideal as an embedded database for small desktop applications that call for a fully functional SQL Server database, but do not require a large number of users. Some of the features in SQL Server Express include new reporting tools and many feature enhancements for data views. SQL Server supports database triggers and stored procedures, which are database features not supported by the ACE database engine, although they can be used by Access in an Access project (ADP) file.
However, database development using SQL Server Express requires fair knowledge and there is no built-in forms package. You would not be able to build a complete Windows database application using only SQL Server Express in the same way you could using Access. Probably the most common scenario for using SQL Server Express is when developing a front-end application using Microsoft .NET Framework technology, in a programming language such as C#, which connects to the SQL Server database engine to manage data. It is worth noting that a fully functioning front-end database application (complete with forms, reports, and charts) easily could be created in Access 2007 and connected to a back-end SQL database on a machine running any version of SQL Server 2005 to enjoy many of the benefits of the SQL Server database engine.
SQL Server 2005
Microsoft SQL Server 2005 is the perfect solution for large-scale database applications. Typically, applications that require a large number of users, many concurrent connections, great amounts of data storage, data transactions, direct data security, or that need routine database backups are ideal for SQL Server. SQL Server is one of the most robust and scalable databases systems available for Windows. But, as with SQL Server Express, SQL Server requires a front-end application to be developed to allow users to access the data stored in the SQL database. All of this power comes with an associated cost. SQL Server is not free, so there is a monetary factor to consider when using it. Additionally, creating database applications with SQL Server also requires rather in-depth knowledge of database design and how to work with SQL Server. Although not the best choice for a small, end-user database solution, Microsoft SQL Server is ideal for very large databases in enterprise systems used for storing critical and sensitive business data.
How Do You Choose?
If you're not sure which type of database to create for your application, ask yourself the following questions:
Will your database grow beyond 2GB? Are there security concerns for the data stored and used by your application? Is the data in your application critical or irreplaceable? Does your application require a large number of transactions at any given time? Does your database need to be accessed by a large number of users simultaneously? How will users work with the data from the database in the application?
Even answering these questions won't provide a definitive solution as to which type of database you should use for any given application. Every application's data storage mechanism should be evaluated on a separate basis by gathering storage requirements and researching the application's purpose to determine which type of database management system to use. For example, if the application will need to store 1.5GB of data, store confidential data, and need to be accessed by thousands of users at any given time, you might consider employing SQL Server 2005. However, if an application requires less than 1GB of data, needs to accommodate 20 users with relatively low traffic, and must maintain low development and support costs, Microsoft Office Access 2007 is the perfect choice.
Whatever database management system you choose, be sure to adequately understand the application requirements and research database system options before beginning work. The cost of redeveloping and porting an existing system can be huge, and in many cases, much more expensive than the cost of developing the proper system initially. Doing a little research and choosing the correct system the first time almost always pays off in long-term development and support costs.
Developing Databases Without VBA Code
This book is about automating Access with VBA code, but not everything you need to do with a database solution should be accomplished via code. Part of being a good developer is knowing how to develop an application with the most features, stability, and flexibility at the least possible cost. Access provides a powerful development environment that includes a variety of wizards and built-in tools to help improve efficiency in developing your application.
As soon as you start Access 2007, you will see immediate enhancements when compared to previous versions. Instead of a blank window, you are presented with the new Getting Started interface. It enables you to quickly open an existing database, create a new blank database, or even create a fully functional database application using the new database template feature. If the computer has an Internet connection and is online, links to Office online and its content are also present to help keep you connected to the latest resources available. You may also notice that the old Windows-style menus have been replaced by the new Ribbon user interface-the Office button, which replaces the File menu, exposes the Access Options dialog box for database and applications settings, as well as other common file options.
Access 2007 Database Templates
New to Access 2007, database templates are a great starting point for a simple database solution. Several different types of business and personal database templates are installed with Access and more are available from Office Online. Some of the different types of database applications you can create include:
Assets: For tracking tangible items. Contacts: For tracking people or organizations. Events: For tracking important dates. Issues: For tracking assignable issues or problems. Tasks: For tracking groups of work tasks.
To create a new database using a template, click on one of the categories on the left side of Getting Started. Then click on a template in that category to select it. The template preview pane opens on the right side of the Getting Started window. If the template is from Office Online, you will see a Download button; otherwise, you see the Create button. Go ahead, choose the Business category and click on the Issues template, as shown in Figure 1-1.
Clicking the Download or Create button creates the new database from the template-the Issues template, in this example. Once you start the database creation process, you briefly see the Preparing Template dialog box and then the new database solution opens in the Access client window, as shown in Figure 1-2.
Many new Access features can be used in the Issues application just created. Among them is the Navigation pane, which replaces the Database Container window and is the primary interface for accessing database objects in Access. In the Issues database, by default, the Navigation pane is collapsed on the left side of the Access client window.
Click on the Navigation pane to expand it and see the database objects contained in the database application. Notice that the default grouping of objects is much different than in previous versions of Access. The Navigation Pane is a highly flexible and customizable feature that provides a number of methods for grouping and filtering database objects based on various properties of the particular object. In the case of the Issues database, a custom Navigation pane grouping named Issues Navigation is defined; it's shown at the top of the Navigation pane. Clicking the top of the pane displays the various object grouping options available in the database. Click the text that says Issues Navigation at the top of the Navigation pane and choose the Object Type option. The Navigation pane grouping now shows all of the database objects grouped by their object types, as shown in Figure 1-3.
(Continues...)
Excerpted from Access 2007 VBA Programmer's Reference by Teresa Hennig Rob Cooper Geoffrey L. Griffith Armen Stein Copyright © 2007 by John Wiley & Sons, Ltd. Excerpted by permission.
All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.
Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site.
Table of Contents
Acknowledgments xiForeword xiii
Introduction xxxi
Chapter 1: Introduction to Microsoft Access 2007 1
A Brief History of Access 1
Is Access the Only Database System? 2
Developing Databases Without VBA Code 4
Summary 18
Chapter 2: Access, VBA, and Macros 19
VBA in Access 19
VBA versus Macros in Access 22
Summary 31
Chapter 3: New Features in Access 2007 33
Who Benefits 34
New Look 37
Development Environment 39
Forms 43
Reports 47
Embedded Macros 50
Access Data Engine 52
Integration with SharePoint 55
External Data Sources 59
Security 60
Convert with Confidence 63
ADE and Creating Runtime Files 63
What’s Gone or Deprecated 65
Summary 66
Chapter 4: Using the VBA Editor 67
Anatomy of the VBA Editor 67
Using the Object Browser 69
Testing and Debugging VBA Code 71
Summary 82
Chapter 5: VBA Basics 83
VBA Objects 83
Variables and VBA Syntax 85
Other VBA Structures 104
Summary 110
Chapter 6: Using DAO to Access Data 111
Data Access Objects 111
Why Use DAO? 112
New Features in DAO 113
Referring to DAO Objects 115
The DBEngine Object 117
The Databases Collection 122
DAO Object Properties 127
Creating Schema Objects with DAO 132
Database Encryption with DAO 143
Managing Access (JET) Security with DAO 147
Data Access with DAO 157
Append Only Fields 187
Summary 189
Chapter 7: Using ADO to Access Data 191
Ambiguous References 192
Referring to ADO Objects 193
Connecting to a Data Source 193
Rolling Your Own Connection String 196
Data Access with ADO 200
Creating ADO Recordsets 204
Creating Schema Recordsets 219
Using ADO Events 221
Testing the State Property 223
Creating Schema Objects with ADOX 223
Managing Security with ADO 231
Summary 231
Chapter 8: Executing VBA 233
When Events Fire 233
VBA Procedures 238
Evaluating Expressions in VBA 247
Using Recordsets 249
Using Multiple Recordsets 252
Using VBA in Forms and Reports 256
Debugging VBA 260
Investigating Variables 261
Common VBA Techniques 267
String Concatenation Techniques 273
Summary 274
Chapter 9: VBA Error Handling 275
Why Use Error Handling? 275
Two Kinds of Errors: Unexpected and Expected 276
Basic Error Handling 278
Cleaning Up After an Error 285
More on Absorbing Expected Errors 286
Issues in Error Handling 289
Summary 290
Chapter 10: Using VBA to Enhance Forms 291
VBA Basics 291
Creating Forms the 2007 Way 295
Summary 346
Chapter 11: Enhancing Reports with VBA 347
Introduction to Reports 347
Creating a Report 351
Working with VBA in Reports 351
Important Report Events and Properties 352
Report Properties 358
Working with Charts 360
Common Report Requests 361
Layout View 369
Report View 370
Summary 373
Chapter 12: Customizing the Ribbon 375
Ribbon Overview 375
Custom Menu Bars and Toolbars 376
Ribbon Customization 377
Saving a Custom Ribbon 377
Specifying the Custom Ribbon 378
Creating an Integrated Ribbon 401
Creating a Ribbon from Scratch 407
Customizing the Office Menu 418
Customizing the Quick Access Toolbar 420
More Ribbon Tips 421
Summary 422
Chapter 13: Creating Classes in VBA 423
A Touch of Class 424
Why Use Classes? 426
Creating a Class Module 427
Naming Objects 440
Using Class Events 443
Forms as Objects 452
Variable Scope and Lifetime 458
The Me Property 461
Creating a Clone Method 463
Creating and Using Collection Classes 464
The Three Pillars 476
Summary 483
Chapter 14: Extending VBA with APIs 485
Introducing the Win32 API 485
Introducing Linking 489
Linking Libraries in Access 2007 490
Understanding C Parameters 497
Err.LastDLLError 505
Distributing Applications That Reference Type Libraries and Custom DLLs 507
Summary 507
Chapter 15: SQL and VBA 509
Working with SQL Strings in VBA 509
Using SQL When Opening Forms and Reports 513
Using SQL to Enhance Forms 514
The ReplaceOrderByClause and ReplaceWhereClause Functions 530
Chapter 16: Working with Office Applications 537
Sharing Information Is a Two-Way Street 537
Working with Outlook 538
Sending Information from Access to Excel 543
Exchanging Data with Microsoft Word 549
Sending Data to PowerPoint 553
Pulling Data from Access 556
Summary 559
Chapter 17: Working with SharePoint 561
Overview 562
Access Features on SharePoint 563
SharePoint Features in Access 576
Summary 594
Chapter 18: Database Security 595
Security for the ACCDB File Format 595
Security for the MDB File Format 606
Summary 647
Chapter 19: Understanding Client-Server Development with VBA 649
Client-Server Applications 650
Choosing the Correct File Format 652
Controlling the Logon Process 672
Binding ADODB Recordsets 678
Using Unbound Forms 684
Summary 692
Chapter 20: Working with the Win32 Registry 693
About the Registry 694
Using the Built-In VBA Registry Functions 703
Using the Win32 Registry APIs 709
Summary 720
Chapter 21: Using the ADE Tools 721
The Redistributable Access Runtime 722
The Package Solution Wizard 724
Save as Template 731
Source Code Control Support 747
Summary 753
Chapter 22: Protecting Yourself with Access 2007 Security 755
The Office Trust Center 756
Disabled Mode 761
Digital Signatures and Certificates 769
Access Database Engine Expression Service 778
Summary 780
Appendix A: Upgrading to Access 2007 783
Appendix B: References for Projects 805
Appendix C: Calling Managed Code 815
Appendix D: DAO Object Method and Property Descriptions 839
Appendix E: ADO Object Model Reference 873
Appendix F: ADO Object Argument Enumeration Information 889
Appendix G: The Access Object Model 905
Appendix H: Windows API Reference Information 973
Appendix I: Windows Registry Information 981
Appendix J: Access Wizards, Builders, and Managers 1009
Appendix K: Reserved Words and Special Characters 1017
Appendix L: Naming Conventions 1027
Appendix M: Tips and Tricks 1045
Index 1081