What is Microsoft Office Access 2007? Simply put, it’s the newest version of Access, a well-known and widely used relational... more

What 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 of the 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.

... less

Chapter 1 introduced the various object designers available in Access to add functionality to your database. One of these... more

Chapter 1 introduced the various object designers available in Access to add functionality to your database. One of these, the Macro designer, enables you to add automation to your database, but at this point you’re still not satisfied. Your database needs more. It is just about time to write some VBA code to work with your Access database. Before you do this, you’ll need a basic understanding of VBA and how it is used in Access. This chapter covers the differences between VBA and macros in Access, as well as a bit about using VBA in Access. It also provides information about the differences you’ll find between Access VBA and other forms of VBA, such as VBA in Excel or VBA in Word. Finally, this chapter takes a closer look at the new features that have been added to macros in Access 2007. Yes, that’s right. New features.

... less

Some of the key new features in Access 2007 will likely have a big impact on the way that developers think about VBA, macros... more

Some of the key new features in Access 2007 will likely have a big impact on the way that developers think about VBA, macros, and even their approach to database design. With changes of this magnitude, it will take a while to adapt or even to figure out what projects to upgrade and when. The main thing right now is for you to be aware of the opportunities and how you can both enhance your projects and significantly reduce development time.

If you look at the big picture, it appears that the new features in 2007 are designed to benefit just about anybody who has contact with Access. The count of new features ranges from about 50 to more than 100, depending on who is counting and the way that things are grouped and sorted. With so many changes, it would be nearly impossible for a developer to find them all on his own. This chapter describes the highlights of the key new features or feature sets so that you’ll know what to look for and can then investigate changes that are relevant to your projects and your clients. It starts with a summary of benefits to the end user, the power user, IT departments, and the developer. Then, it introduces the main improvements grouped into the following areas:

• Access’s new look

• Development environment

• Forms

• Reports

• Embedded macros

• Access data engine (ACE)

• Integration with SharePoint

• External data sources

• Security

• Easy upgrading

• Access Developer Extensions (ADEs)

There are almost 1,000 commands in Access, but the new user interface is smart about displaying relevant commands in a manner that makes it easy for you to find and select the correct one.

... less

You’ll use the VBA Editor to write almost all of your VBA code. Although you can simply open the VBA Editor and start typing... more

You’ll use the VBA Editor to write almost all of your VBA code. Although you can simply open the VBA Editor and start typing code, knowing a bit about the different components of the editor not only helps you properly structure and debug your code, but saves you time while you’re at it. In this chapter, you explore the major structural components of the VBA Editor as well as some basic code debugging techniques.

Note

The topics in Chapters 4 and 5 are so interconnected that it was difficult to decide which chapter to put first. If you get the sense that you are jumping into the middle without having covered the basics, what you think you’re missing is likely in Chapter 5.

... less

Now that you know a bit about automating Access, using macros, and how VBA fits into the Access automation picture, you’re... more

Now that you know a bit about automating Access, using macros, and how VBA fits into the Access automation picture, you’re almost ready to write some code. The next step is to review some VBA basics.

For experienced programmers, this chapter is unnecessary; however, if you’re just delving into VBA from another programming language or from VBScript, this chapter contains vital information that will help you to better understand and utilize what you learn in subsequent chapters. Here you’ll examine the basic VBA programming objects, learn about variables and how to declare them, and review some additional VBA structures that you’ll use in your code. Along the way, you’ll build a few procedures, and you will soon gain the skill and confidence to modify those procedures and to create your own.

... less

As you’ve seen in previous chapters, VBA is the programming language you use to programmatically interact with the Access... more

As you’ve seen in previous chapters, VBA is the programming language you use to programmatically interact with the Access object model. You use VBA to manipulate Access-specific objects, such as forms, reports, and so on. But because Access is a Relational Database Management System, you will undoubtedly find yourself also needing to programmatically interact with the data it contains, and indeed with the database design, or schema. Microsoft Access employs two data access object models: Data Access Objects (DAO) and ActiveX Data Objects (ADO).

Chapter 7 covers ADO; this chapter is solely concerned with the DAO model. It begins with a brief history of DAO and an indication of when it might be most appropriate to use DAO in preference to ADO. You’ll see the new features in DAO before examining the three most important objects in the DAO object hierarchy: the DBEngine, Workspace, and Database objects. Then you’ll explore database properties and how to use them.

Before you start working with DAO objects to access your data, you’ll take an in-depth look at how to use DAO to create and modify your database structure, including tables, fields, indexes, and relations. You’ll also spend some time looking at the Access Database Engine (formerly JET or Joint Engine Technology) security model, and how you can create and manipulate security objects, such as users, groups, and of course, how to read and assign object permissions. Finally, you’ll look at data access in detail using QueryDefs and Recordsets.

... less

Data Access Objects (DAO) was the default data access technology in the early versions of Access. In fact, Access was bound... more

Data Access Objects (DAO) was the default data access technology in the early versions of Access. In fact, Access was bound so closely to the Jet database engine by the fact that developers used Access as both the front-end user interface and the back-end data store that they rarely felt a need for anything else. As application designs evolved from standalone solutions into client/server architectures, the need to connect to and access data from disparate data sources became more and more important. Although Microsoft made several attempts at evolving DAO into a remote data access technology, its true strength is accessing data from local Jet databases. So to fulfill the need to connect to external data sources, Microsoft created ActiveX Data Objects (ADO). ADO is designed to provide an ActiveX standard object model for connecting to a wide variety of external data sources.

ADO is a part of Microsoft’s data access vision of the future, called Universal Data Access (UDA). UDA is a concept in which a single method is used to retrieve data from any data source: relational databases, the mainframe indexed sequential access method/virtual storage access method (ISAM/VSAM) data sources, hierarchical databases, e-mail, disk files, graphical data, and so on. OLE DB (Object Linking and Embedding Databases) is the interface that enables UDA. ADO is a development interface for OLE DB that provides similar functionality to DAO.

OLE DB sees the world in terms of data providers. It acts as an interface between the data source and data consumer. Although OLE DB was written for procedural programming models, ADO sits atop OLE DB, providing programmers with an object-oriented model they can use to access and manipulate the data sources.

When you use Access 2007 to create a standard Jet database (MDB) or standard ACE database (ACCDB), by default, Access uses the ACE OLE DB provider for the connection to the CurrentProject. Any functionality that uses the CurrentProject object will go through the OLE DB provider. To confirm this, enter the following line of code into the Visual Basic Editor’s Immediate window:

?CurrentProject.Connection

The return connection string begins with the following:

Provider=Microsoft.ACE.OLEDB.12.0;

Similarly, Access uses the Access OLE DB provider when you create an Access Data Project (ADP) against the SQL Server or MSDE. The same property call returns a connection string that begins thus:

Provider=Microsoft.Access.OLEDB.10.0;

While ADPs use the Access OLE DB provider for the CurrentProject object, the data provider for an ADP is the SQLOLEDB provider because an ADP is connected to a SQL data source. This chapter explores ADO and how it applies to an Access database solutions.

... less

In the old days of programming, procedural languages ruled, meaning that the overall program execution traveled from top... more

In the old days of programming, procedural languages ruled, meaning that the overall program execution traveled from top to bottom. The main body of any of these programs had to cover every possibility: display a screen to the user, gather input, perform edit checking, display messages, update the database (or simple files in those days), and close when everything was done. The main program also had to deal with every option or side request that the user might make. This made it difficult to understand the entire program, and it was tough to make changes because everything had to be retested when a modification was made. Those lumbering beasts included COBOL, RPG, Pascal, and earlier forms of Basic. Millions of lines of code were written in these languages.

Fortunately, those days are over for VBA programmers. VBA is an event-driven language. In every Access form and report there are a variety of events that are waiting for you to use. They are available when the form opens and closes, when records are updated, even when individual fields on the screen are changed. They’re all there at your fingertips. Each event can contain a procedure, which is where you get back to your procedural roots. Although each procedure runs from top to bottom, just like in the old days, it only runs when the event fires. Until then, it sleeps quietly, not complicating your logic or slowing down your program.

Event-driven programming makes it much easier to handle complex programming tasks. By only worrying about events in your coding when they actually happen, each procedure is simpler and easier to debug.

In this chapter, you’ll explore the nature of VBA events and see how the most common events are used, and you’ll look at how two different sections of your VBA code can run at the same time. The chapter provides some guidelines about when and how to use Public and Private procedures, class modules, and data types, and also outlines structural guidelines for procedures, shows some common string and date handling techniques, and explains how to prevent rounding errors in your calculations.

... less

When programmers use the term “error handling,” they really mean graceful or planned error handling. After all, Access takes... more

When programmers use the term “error handling,” they really mean graceful or planned error handling. After all, Access takes some kind of action for any error that it encounters in your code. Graceful error handling includes the following:

• Quietly absorbing expected errors so the user never sees them.

• Displaying a “friendly” message to the user for unexpected errors, and closing the procedure properly.

Error handling in Access VBA involves adding code to every procedure—both subroutines (also known as subs) and functions—to take specific actions when Access encounters an error. This is called handling or trapping the error. (Some developers call the encounter with an error throwing an error. Error handling is the code that catches the error and handles it properly, either by hiding it from the users or by explaining it to them.)

This chapter provides techniques to handle several types of expected and unexpected errors so that your applications look and feel more professional to your users. But first, you’ll explore why you should use error handling at all. Many Access developers see it as a mundane chore, but there are good reasons for including error handling in every procedure you write.

... less

First impressions are lasting, so be sure that your forms make it a great one. When you are providing a solution, forms are... more

First impressions are lasting, so be sure that your forms make it a great one. When you are providing a solution, forms are the first things that people see and use. So forms need to be as attractive as they are effective and intuitive.

Access 2007’s out-of-the box features and controls replace many of the common tools and functions that required testing, error trapping, and add-ins. This is more than just a time saver; it builds in consistency and motivates you to deliver better solutions. Forms can be dressed up with subtle shading, colorful lines, and sleek new image controls.

Users like being able to scroll through data sheets, instantly see the details, and even update the record. They also appreciate how easy it is to assign a task to several people, particularly because they can add a person to the list without missing a beat. And there are unlimited opportunities for including attachments right in the database.

Those are just some of the features that you’ll explore in this chapter.

If something already looks and works this good, then imagine how great your solution will be when you leverage it with VBA. That’s what this chapter is all about. In addition to seeing how to use VBA with some of the new features, you’ll also examine some of the important mainstays such as working with combo boxes, creating multiple instances of a form, and building a tree view control.

... less

Because of their rich printed view, reports have long been one of the more widely used features in Access. This chapter takes... more

Because of their rich printed view, reports have long been one of the more widely used features in Access. This chapter takes an in-depth look at reports in Access 2007. It starts from the beginning, so if you’re already familiar with creating reports in Access, you might want to skip to the section “New in Access 2007.”

The chapter also looks at several ways that you can enhance your reports by adding VBA behind them. You’ll explore the various events that are available for reports and sections, and then move into some common uses for those events. Along the way, you’ll see some issues that you should look out for as you’re designing reports. Finally, you’ll examine several new features in reports in Access 2007, including Layout view and Report view.

When you write code behind a form, there are certain things to consider, such as the flow of the application, searching for data, data entry, and validation. While reports in Access 2007 are still read-only, they have been greatly enhanced to allow for some of the form-type scenarios that you might have created in the past such as search, sorting, and filtering.

... less

The first time you open Access 2007, you’ll notice that things are different. The familiar menu bar and toolbars have been... more

The first time you open Access 2007, you’ll notice that things are different. The familiar menu bar and toolbars have been replaced with the Ribbon. The new interface may appear a bit daunting, but it’s only a matter of time until you’ve learned where commands are located and are more comfortable with it.

This chapter provides a brief look at the Ribbon in Access 2007 and discusses some of the design goals of the Ribbon that you can apply to your applications. Then comes the fun stuff: customization. You start by examining the types of objects you can create in a custom ribbon, and then use what you learn to create two sample applications with different design goals: a Ribbon that integrates with the Access Ribbon, and another to replace the Access Ribbon. As you’ll soon see, the Ribbon is highly customizable, which can help you create new and exciting interfaces for your own applications.

Ribbon customizations are written in XML. Even if you’ve never written XML or are just getting started with XML, don’t worry—it’s straightforward and easy to understand. This chapter affords you the information and tools you need to customize the Ribbon (and learn a little about XML at the same time).

... less

The capability to create self-contained software objects was first conceived in about 1970 with the development of SIMULA... more

The capability to create self-contained software objects was first conceived in about 1970 with the development of SIMULA 67 (SIMUlation LAnguage), an extension of the scientific ALGOL 60 computer language.

It took quite a while before the programming community realized the implications of the breakthrough that SIMULA represented. When they did, object-oriented programming (OOP) quickly became the new buzzword, relegating structured programming to the realm of the lesser-informed code cutters.

With the release of languages such as SmallTalk, C++ and, later, Java, OOP earned its place in the software hall of fame as the new panacea to all your programming ills. When Visual Basic 4 was released in 1993, Basic developers were tantalized by a new toy: the class module.

Long snubbed by C++ developers who had been using class modules for years, Basic developers were finally able to hold their heads high with the new found capability to create fully self-contained and reusable objects.

In OOP parlance, an object is a unique instance of a data structure, called a class, that has both properties (which define its characteristics), and executable procedures called methods (which define its behavior in modifying those properties).

The properties of a class are completely isolated from the outside world and can be modified internally only by its own methods. This doesn’t mean that the programmer can’t do anything to them, but that he can’t do anything to them directly; he must use those methods that are exposed for that purpose. The properties and methods you create are termed its implementation, whereas the methods it exposes to the programming environment constitute its interface. Thus, an object is a completely self-contained programmatic entity, in that it contains both its own data and the program code necessary to implement its own behavior.

This chapter examines VBA classes and class objects. You learn what a class actually is and the difference between it and a class object. Then you create your first class and figure out how it works. After that, you learn to identify classes and then how to get them to communicate with the rest of your application, before diving into the more advanced topics, such as building collection classes. Some object-oriented theory concludes the chapter.

Classes are not as daunting as you might first think, and it’s my hope that after reading this chapter, you will cast off any fears you may have had and happily find many uses for your new found skills.

There are many ways to do a single task in Access, and the examples in this book are intended to provide new perspective on programming with classes. Class modules in Access can be useful when applied to complex problems, and the code examples reinforce that theory.

... less

Microsoft Visual Basic for Applications (VBA) is a full-featured software development language that offers a vast array of... more

Microsoft Visual Basic for Applications (VBA) is a full-featured software development language that offers a vast array of built-in functions so that many Access developers never require anything else.

However, when you start developing more and more complex applications in Access, you may find yourself needing to do things for which VBA does not have a built-in function. Moreover, you’ll sometimes need to do things that VBA simply can’t do. That’s not to say that VBA is incomplete, but, like every other programming language, it does not include every function you’re ever likely to need. A line has to be drawn somewhere, and Microsoft drew that line at the functions provided by the API.

The Windows operating system provides a large library of functions that you can access using VBA to extend what you’re able to do in your applications. But because the API is inherently VBA-unfriendly, you must first understand what it is, and what special considerations you must take into account to use it from VBA.

This chapter explores what the Windows API is, and why you might want to use it. It describes the libraries that make up the API and how to link them into your application. Then you’ll see how to declare API functions to use them with VBA and examine the differences between the data types used in APIs and those used in VBA, learning techniques and formulas to convert between them.

Finally, the chapter introduces the VBA LastDLLError method for dynamic-link library (DLL) error handling and explains how to deploy and install applications that contain references to API or other libraries.

... less

You may be familiar with SQL; after all, it’s inside every query you create. SQL (Structured Query Language) is the language... more

You may be familiar with SQL; after all, it’s inside every query you create. SQL (Structured Query Language) is the language of queries and recordsets; it’s how you retrieve, update, insert, and delete records in your database tables.

When you use the query Design view in Access, you are actually building a SQL statement under the covers. Most of the time, you won’t actually need to look at the SQL code, but you can see it using the SQL view if you’re curious.

Conversely, you can take most SQL statements, paste them into the SQL view of a new query, and then switch over to Design view to see how they work. There are a few types of SQL statements for which this won’t work—union queries and pass-through queries, for example, cannot be viewed using Design view.

Even if you’re comfortable using SQL in queries, you may not be familiar with building SQL statements in VBA. If you’re not, you’re missing out! Using SQL in VBA is a powerful technique that can enable many great features in your Access applications. By using VBA, you can build custom SQL statements for combo boxes, forms, and reports. For example, you’ll be able to change the sorting and selecting of records on continuous forms, control the record selection on reports, and limit the drop-down lists of combo boxes based on other combo boxes.

Let’s begin by exploring how to build SQL statements using string variables in VBA.

... less

Designing complete, fully functioning database solutions in Microsoft Access is done quite often without the need for working... more

Designing complete, fully functioning database solutions in Microsoft Access is done quite often without the need for working with any another Microsoft Office application. After all, you can use Access forms to enter data, Access reports to view and print data, and the SendObject method to send Access information via e-mail. On the other hand, it is not only possible, but extremely useful to leverage the features of other Office applications to enhance an Access solution with very few lines of code. For example, you might want to use Outlook to generate a customized e-mail with information from an Access table. If the Access solution offers a method for users to export data to Excel, those users can leverage Excel features to customize data in their own way without unwanted interaction with your application. Exporting data to Microsoft Word gives users the capability to add their own text, perform mail merges, customize documentation, and much more in a practically universal file format. This chapter illustrates methods for employing other Office applications directly in your Access database solution.

You’ll use code examples for working with the other Office programs, and take a look at some real-world situations that illustrate how interaction with other Office programs can enhance your application. Please download the sample database code files for this chapter to see the code included in this chapter.

... less

The fastest growing business software product in the Microsoft family, Windows SharePoint Services is one of the hottest... more

The fastest growing business software product in the Microsoft family, Windows SharePoint Services is one of the hottest technologies for digital team site management available today. Flexible and easy to use, SharePoint provides users with simple site creation and design, robust content management, and powerful security for business data. Microsoft Office 2007 contains a myriad of new features that integrate with SharePoint to help users communicate information, and one of the pillars of the Access 2007 release is to provide seamless integration with SharePoint.

Access has added new features that fit into two basic categories: features that work from the server and features that work from within the Access client. Starting with the 2003 release, the Microsoft Office teams began a campaign to support content management on SharePoint for site design, online documents, and list data. In many cases, that could be done directly from within the Office application, such as from an Access database, a Word document, or an Excel workbook. Users of Office System 2003 may remember some of the features that Access offered to users, such as Edit in Datasheet or Access Linked Tables. While both of these features are powerful, they pale in comparison to the new features in Microsoft Office Access 2007.

This chapter describes how features of Microsoft Windows SharePoint Services 3.0 integrate with Access 2007. Interestingly, most of the features are so tightly coupled with SharePoint that you do not even need code to leverage them within your Access database solution. All of the SharePoint features in Access 2003 are still available in 2007. While there is not a lot of new VBA code available for working with the SharePoint features programmatically, a number of method and property additions and a few updates to existing OM methods are of interest.

If you don’t already have access to a Windows Server with SharePoint Services 3.0, get access to one! SharePoint 3.0 has a number of system requirements, the heaviest of which is that it can be deployed only on Windows Server 2003 or Windows Vista Server. If you do not have a copy of either version of Windows Server, evaluation copies are available at http://microsoft.com/windows/default.mspx; those will allow full access to the product for evaluation purposes for 180 days. Once you have access to a Windows Server machine, Windows SharePoint Services can be deployed. Note that Microsoft Office Server System 2007 is a different product, which provides rich server features and additional enterprise-level applications that are built on top of the SharePoint technology. For more information and content downloads for SharePoint, go to: http://office.microsoft.com/en-us/sharepointtechnology/default.aspx and http://microsoft.com/technet/windowsserver/sharepoint/default.mspx. This chapter focuses on the base SharePoint product features and how they relate to Access. Fortunately, all of the Access 2007 features work just as well with other applications built on top of other SharePoint technologies.

... less

Microsoft Office Access 2007 provides a range of security features to meet the needs of most database applications. These... more

Microsoft Office Access 2007 provides a range of security features to meet the needs of most database applications. These security features break down into two categories: database security for the ACCDB file format, which is discussed in the first part of this chapter, and database security for the MDB file format, which is the focus of the rest of the chapter. This chapter discusses the various methods available to secure your Access database applications using VBA code in either file format.

Be aware that several of the security features available in the MDB file format are not available in the ACCDB file format, and vice-versa. For the ACCDB file format portion of this chapter, you’ll examine databases encrypted with passwords and code secured by either compiling the database or locking the modules. For the MDB file format portion, you’ll explore shared-level security, user-level security, database encoding, MDE files, and using VBA to manipulate security. There’s also a discussion about user-level security and the detachment between the MDW file and the secured database application file.

It is important to understand all of the various types of security in both file formats to fully comprehend the implications of the security model being employed by the database. The recommendation is to read this entire chapter before deciding which method of security is best for the database application.

Tip

Microsoft Office Access 2007 provides security features that protect computers from databases that contain malicious code designed to attack or infect machines. In addition, Access 2007 also provides a format for securely transferring database files. These particular security enhancements are covered in Chapter 22.

... less

Access makes it easy to create applications that interact with other database formats and enterprise-level database servers... more

Access makes it easy to create applications that interact with other database formats and enterprise-level database servers. Unfortunately, the easiest methods are not always the best, and incorrect choices can have serious long-term effects on the design, stability, maintenance, and overall success of a project. A thorough understanding of how Access interacts with other databases and the various alternatives available for developers is critical to making the best design decisions for any given application.

In a typical business environment, Access database applications tend to sprout up because some individual or small group needs functionality and creates an Access database to implement a viable solution. Other people or groups notice the application’s usefulness and decide to use the solution as well. Someone may even split the data tables into a backend database and link the tables in the front-end application so that large numbers of users can use a local copy on their machines and connect to the tables stored on a central server. Before long, what began as a personal database application is now shared on the network server, contains hundreds of megabytes of business data, is used on a daily basis by 50 or so people, and requires 2 or 3 people just to maintain and administrate the database. The application has become an unintended, albeit critical, piece of the company’s business process.

The solution is cost effective. So another database is created for a different problem, and then a third database, and so on until there are hundreds of applications all over the network, some in use, some dead, and maybe some that were never even completed. Many IT workers cringe at a mere whisper of the words “Access database” because these applications become difficult and expensive to track, maintain, and support. Where does all this data come from? Who has access to the data? Who backs up the data? Who developed this application? Who is maintaining and supporting this database? These are just some of the questions to ask when thinking about how to deal with large numbers of Access databases in a business environment.

Fortunately, there are some easy answers to these tough questions that allow users the flexibility of creating Access applications and save IT the headache of having to support and maintain all of the data. If the data is stored in a controlled, centralized location, an IT department can effectively manage the database and control which users can view/modify the data. A more ideal solution would be using a database server as the centralized location, enabling both developers and administrators to leverage the server’s features to help them with their tasks. Fortunately, Access provides the capability to create front-end applications to connect to separate, back-end data sources of many different types. Being able to connect to remote data sources is the basis of the database application architecture often referred to as a client-server application, which we will discuss in the next section.

... less

The Registry is the heart and soul of the 32-bit Windows operating system. It maintains information about the hardware and... more

The Registry is the heart and soul of the 32-bit Windows operating system. It maintains information about the hardware and software installed on a computer, configuration settings, user settings, and information that the system needs to function. In fact, 32-bit Windows can’t operate without it.

The capability to access and edit the information contained in the Registry is essential to all but the most basic software developer, and, if you plan to do any serious programming, understanding the Registry is critical.

As you’ll see, VBA supports only four native Registry functions, which allow you to store, retrieve, and delete values from one specific area of the Registry. To do anything more advanced, you need to use the Windows Registry APIs. If you don’t feel confident with API programming, you should first peruse Chapter 14, which provides the background you’ll need to understand the more advanced topics in this chapter.

Although it’s true that you can’t damage anything by simply reading the Registry, it’s also true that making changes to Registry entries when you don’t know what you’re doing is like randomly pressing buttons in the control room of your local nuclear power station—press the wrong button and everything will melt into a bubbling fluorescent ooze at your feet.

This chapter is not intended to provide highly detailed information about every key, subkey, and value in the Registry; to do so would require a book far larger than you could carry. Instead, the aim is to afford you enough information so you can confidently find your way around the Registry and can write basic VBA code to create, retrieve, edit, and delete Registry values. To get started, here’s a basic look at what the Registry is, what’s in it, how it is structured, how it works, and, finally, how programmers can make best use of it.

... less

The Access Developer Extensions (ADE) is a combination of tools designed to aid in the development of Access applications... more

The Access Developer Extensions (ADE) is a combination of tools designed to aid in the development of Access applications. If you’ve used the ADE tools in the past, you’ll be glad to hear that there are some new features for Access 2007. This chapter examines the ADE tools, shows you how to use them, and discusses how using the ADE can improve Access applications. The ADE package contains several tools for application development and deployment, including the following:

• The Access Redistributable Runtime: The Access Runtime allows database applications to run on Windows machines, even when Access itself is not installed. The redistributable license allows developers to freely distribute the Access Runtime, in unlimited quantity, without infringing on any Microsoft licensing requirements. Although the Runtime does not allow customers to use the Access program itself, distributing the Access Runtime with your database application will reduce the cost impact for customers who do not already have Access 2007 installed.

• The Package Solution Wizard: The Package Solution Wizard is an Access add-in that allows developers to quickly and easily build a setup package for production database applications. The wizard bundles the database, any user specified files, and a few other optional files (such as the Access Runtime) into a single package to be used for deployment on the client machines. The resulting package file is a standard Microsoft Windows Installer (MSI) file for installing, repairing, or uninstalling a custom Access application using the conventional Windows installation tools.

• The Access Database Template Creator: New to the ADE for Office 2007, the Access Database Template creator allows developers to convert existing database applications into the Access 2007 template file format (ACCDT). This provides Access developers with the capability to easily transfer database solutions in a safe text file format, which can be used over and over to generate new database applications. If placed in the proper location on the user’s machine, these templates will even show up in the new Getting Started interface, along with the other templates installed with Access 2007.

• Source Code Control program support: Finally, the ADE includes some tools for working the Source Code Control (SCC) programs to allow developers to version database objects. The SCC support is a set of interfaces to work with standardized source code control programs to retain change information about database objects within an Access application. While the ADE does not include a specific source code control program, there are many programs available (such as Microsoft Visual Source Safe 2005) that will work very well with the ADE SCC interfaces.

Using the ADE tools to enhance Access application development can be extremely effective in reducing development cost and time. They can be an integral part of application development, especially when the application is designed to be deployed to outside customers. These tools are extremely lightweight and can be used separately or in unison, without a huge learning curve.

One last item: The ADE tools are not included with Microsoft Access 2007 or even Microsoft Office System 2007 release disk. In the Microsoft Office 2007 release, the ADE tools are available as a separate download from http://office.microsoft.com. Also, as with Microsoft Office 2003, the ADE is included with the Visual Studio Tools for Office (VSTO) product (on a separate installation CD included in the product box). For versions prior to Access 2003, the ADE tools were included in the Developer Edition of Microsoft Office.

... less

Now more than ever, you have to concern yourself with the security of your computer systems. One form of security—securing... more

Now more than ever, you have to concern yourself with the security of your computer systems. One form of security—securing the information contained in and the intellectual property built into your databases—was discussed in Chapter 18.

Another form of security has to do with preventing malicious attacks on your computers—attacks that can delete files, spread viruses, or otherwise disrupt your work. This chapter focuses on the security enhancements built into Access 2007, which help you protect your computer systems and your users’ computer systems.

In Microsoft’s efforts to make sure everything is secure, it had to deal with the fact that an Access database has a lot of power (something Access developers have known all along). And because of this power, someone who chooses to use Access maliciously can make an Access database perform destructive operations. In fact, that is the reason that Outlook does not allow Access MDB files to be sent as attachments. From a security perspective, an MDB file is essentially an executable.

To curb this power, Microsoft made changes to Access 2003 that mean that developers must do a little more work to make databases as easy to use as they have been with prior versions of Access. But face it: If your users use Access to open a database from someone else and that database then attacks their computer, they’re more likely to blame Access rather than the database they opened. Their confidence in Access would be right out the window. So, really, the security changes weren’t all bad.

But this book is about Access 2007. New security features have been added that significantly improve the user experience over that of Access 2003. This chapter explains what the new security features are and why they were added. Perhaps more important, it describes the things you can do to make it easy for your users to use your databases in Access 2007. The existing solutions of using a digital signature and Visual Basic scripts still apply, but additional features such as trusted locations have been added to make it even easier. These are not difficult solutions. And once you learn them, they become second nature.

You’ll also take a look at Expression Service and sandbox mode, which are part of the Access database engine. If you installed Access 2003 and found that you had to upgrade to the Jet Engine Service Pack 8, you’ll be glad to know that no additional download is required to use wizards or add-ins with sandbox mode in Access 2007. Because Access 2007 includes the Access database engine, everything you need is already there.

... less

This appendix is a compilation of data gleaned from a couple dozen papers, hours of testing with several versions of Access... more

This appendix is a compilation of data gleaned from a couple dozen papers, hours of testing with several versions of Access, and several years of experience—as well as information from other developers. It highlights some of the key considerations in making informed decisions and plans for converting to and working in mixed environments with Access 2007. It also provides some steps for embarking on a conversion process, and deals with concerns for special circumstances such as work group security or replication. It touches on some of the issues users face when converting from 2007 to earlier formats.

With the most dramatic addition of features and power in more than a decade, Access 2007 is designed to appeal to a wide spectrum of users and developers. The new user interface (UI) is more intuitive and can automate many of the tasks that previously either required programming or weren’t practical to do. The new features empower users to easily gather and analyze data from multiple sources, including SQL Server, Excel, e-mail, and websites, and make it easier for developers to automate processes and to provide unprecedented flexibility for user-customized reports. It is now feasible to include attachments in the database, and the new file format (ACCDB) offers security through encryption of the data file. Those are just a few of the innovative features that will lure people to 2007.

What about the individuals, businesses, and enterprises that have existing applications? They need to develop a plan to address the issues related to migrating to 2007 and potentially for working in a mixed environment.

In the past, it was easier to be a slow adapter because the advantages of upgrading might not have compelled everyone to make the move. But now, when one leads, the masses quickly follow. As soon as one person begins to leverage the new features available with the ACCDB format, co-workers want (or demand) an equal opportunity. The bottom line is that the new features will enable people to make better decisions quicker. They not only will save time and money, but also will provide an entire new spectrum of methods for people to work with, integrate, and analyze data. And, thanks to the new wizards, managers, and objects, developers can design and deploy incredibly powerful custom solutions faster and more efficiently.

Because the most powerful new features are available only with the new ACCDB file format, there is a strong incentive for users to migrate their applications to 2007 .accdb files. But of course it isn’t always feasible to move everyone at the same time. Even with a uniform deployment of Access 2007, people still need to know how to work with files from prior versions. Whether it is to work with others, link to data sources, or to pick up new code, there are a multitude of reasons to know how to safely open and use files of different versions and formats.

This appendix discusses converting and enabling Access applications so that you can work with multiple versions of Access; it isn’t intended to be a technical reference for addressing the issues that are involved with running multiple versions of Access on the same computer.

... less

Throughout this book you have seen type libraries or object libraries, such as those described in Chapter 16, used to enhance... more

Throughout this book you have seen type libraries or object libraries, such as those described in Chapter 16, used to enhance functionality through VBA code. You know how libraries can provide access to functions that manipulate the Windows System Registry or retrieve and send data to other applications.

In addition to using libraries supplied with Microsoft Office, you can acquire type libraries to help simplify a variety of programming tasks. Like Microsoft Office libraries, other vendors’ libraries provide classes to manipulate objects—the QuickBooks libraries provided in QuickBooks Software Development Kit (SDK), for example. These libraries provide classes you can use to create objects that contain data that is returned from a QuickBooks data file through an XML access method.

Acquiring libraries can be a cost-effective way to get more work done in less time. Of course, there’s always the tradeoff between what you pay for a library and the exertion that may be required to learn how to use it, and the time and effort you spend writing your own functions. And don’t forget the effort you have already put into writing your own code. After all of the “bold, test, and swears” you put into your routines, you really must consider creating code libraries from your code.

This appendix describes techniques for using references to libraries in your projects, including how to reference libraries provided by others and why the order of your reference list can be important. It also discusses the types of libraries available (DLLs and ActiveX controls, for example).

This appendix also discusses procedures for referring to the References class and why you would want to. It describes some correct techniques for writing code that will go into your own code libraries. And it suggests ways to avoid getting missing libraries and what to do when they go missing.

... less

This appendix provides information about how to call managed code from Access. It begins with an explanation of what managed... more

This appendix provides information about how to call managed code from Access. It begins with an explanation of what managed code is and why you might consider using it in your Access applications. In the process, it defines some commonly used terminology that you might come across while working with managed code. Finally, you’ll look at two ways you can use managed code in Access—using class libraries and COM add-ins.

The managed code examples in this appendix are written in both Visual Basic and Visual C#. In this appendix, Visual Basic refers to Visual Basic .NET. This matches the conventions used by Visual Studio and the documentation on MSDN.

... less

This appendix provides a detailed alphabetical list of all the objects that DAO supports, including descriptions of their... more

This appendix provides a detailed alphabetical list of all the objects that DAO supports, including descriptions of their methods, properties, and collections. These descriptions are for reference purposes only, and you should consult the online Help for more in-depth descriptions.

... less

When using VBA to access data in ODBC compliant databases, you can choose from DAO or ADO. To use either technique, you’ll... more

When using VBA to access data in ODBC compliant databases, you can choose from DAO or ADO. To use either technique, you’ll need to understand the object model for the appropriate technology. The ADO object model isn’t overly complicated, but understanding the details of all the various ADO objects will help ensure that you use the proper object for the designated task.

This appendix lists all of the major objects needed when using ADO to access data within VBA. For each of the major objects, properties, methods, events, and associated collections are provided. Keep this appendix handy when programming ADO and refer to it when you have questions about the specific property, method, or event to use in your code.

... less

The methods supplied by the various ADO objects often have parameters that require enumeration options. This appendix provides... more

The methods supplied by the various ADO objects often have parameters that require enumeration options. This appendix provides information about common enumerations for common ADO object methods. The information in this appendix relates to both Chapter 7 and Appendix E, and can be used in conjunction with both.

... less

By now you’ve probably read at least a few of the chapters in this book and have realized that there are a lot of tasks you... more

By now you’ve probably read at least a few of the chapters in this book and have realized that there are a lot of tasks you can accomplish by programming in VBA. One concept that should be abundantly clear is that to use VBA to manipulate Access, you need some knowledge about the Access object model. It was discussed briefly in Chapter 4, but as a refresher, an object model is a set of objects and collections that programmers use to manipulate a program. Microsoft Access has a rich object model. You’ll use that to manipulate forms, reports, queries, macros, and other components of the Access interface.

Much of the information in this appendix can also be found in some form within the Access Visual Basic Reference Help file.

... less

So now you probably know enough about using the Win32 API to get yourself into some serious trouble. The trick is to find... more

So now you probably know enough about using the Win32 API to get yourself into some serious trouble. The trick is to find information about the APIs that are available for use, and learn how to use them with VBA.

Unlike programming languages, information about the Windows API is somewhat harder to find. There are literally hundreds of API functions included in the Windows operating system, and the information that is available is mostly incomplete. There are also quite a few API functions that Microsoft hasn’t publicly documented, for whatever reason. We haven’t yet found a single resource that includes everything, so this appendix presents several resources that we recommend.

... less

This appendix provides information about the Windows Registry to support the tutorials in Chapter 21, including Windows Registry... more

This appendix provides information about the Windows Registry to support the tutorials in Chapter 21, including Windows Registry data types, functions, and constant and user-defined Type declarations.

... less

Access provides many tools that do a lot of work for developers. These tools not only save you time and prevent or minimize... more

Access provides many tools that do a lot of work for developers. These tools not only save you time and prevent or minimize errors, but they are also a great resource for teaching yourself how to do things. But many developers may not even know that some of the wizards exist, and if you don’t know about them, you aren’t likely to be using them to your advantage.

This appendix briefly describes the wizards, builders, and managers in Access 2007. Because you might be looking for a familiar favorite, items are noted as having been added, enhanced or relocated. Although there isn’t an official list, our best count is that Access 2007 includes 46 wizards, 9 builders, and 5 managers—that’s 60 tools designed to streamline and automate common processes for developers and users.

... less

There are numerous words that should not be used to name fields, objects, and variables. For the most part, these are called... more

There are numerous words that should not be used to name fields, objects, and variables. For the most part, these are called reserved words. Reserved words have a specific meaning to Microsoft Access, the Jet database engine, and the new Access database engine (ACE). We also list reserved words that have specific meaning to SQL Server or ODBC drivers. Depending on how your application interfaces with other programs, it may be prudent to avoid using words that have specific programmatic meanings to those as well. If you want to start a list of reserved words, begin with the list of all the properties of database objects, all Visual Basic keywords, and all third-party and user-defined names and functions.

Access 2007 creates an error message when select reserved words are used as field names. For the most part, however, using reserved words often creates error messages that do not indicate the source of the problem. For example, it is far from intuitive that the following error message:

The wizard was unable to preview your report, possibly because

a table needed by your report is exclusively locked

may have been triggered by the use of a reserved word. Consequently, a developer may unnecessarily spend time troubleshooting the wrong problem. When you are working with an application that uses reserved words, particularly as the name of tables or fields, rename the database objects if it is at all possible and feasible to do so. If it isn’t possible to rename them, then be sure to enclose the names in brackets when they are called in code or in queries. Here’s an example showing the name of the table in brackets because the term tableName is a reserved word:

SELECT fieldX

FROM [tableName]

When writing code, it is often handy to use the IntelliSense feature and just select from the available list of objects and actions. This requires the use of Me. rather than Me! However, if the name of a field is a reserved word that could also be the object’s property, the code will not compile. The debugger will stop and highlight the problem object, as shown in Figure K-1. In this instance, merely changing the syntax to use Me! (bang) instead of Me. (dot) will allow the code to compile. You cannot avoid the problem simply by not compiling the code, because that merely ensures that the code will break and stop the application from running.

... less

The logic of using a naming convention is about as easy to grasp as the logic of standardizing an alphabet or language. Both... more

The logic of using a naming convention is about as easy to grasp as the logic of standardizing an alphabet or language. Both structures are intended to make communication easier. In addition to providing standards, they also allow plenty of opportunities for customization. Adopting a naming convention provides for consistency, can avoid conflicts with reserved words, and sets the framework for building strong code that is more easily read and interpreted by the original developer (yes, you will likely forget why you wrote what you did) and by other developers. You may as well count on someone else trying to interpret your code, whether it is someone on your team, a technical consultant, or someone who inherits your project.

Using a naming convention also helps prevent conflicts due to multiple uses of the same name because, in most cases, a name should have only one meaning within an application.

If you are new to Access or to writing code, this is the perfect time to become familiar with the most common naming conventions and to start developing your own protocols. This appendix includes some guidance for naming objects and provides tables of the most commonly accepted names used in VBA. Now that macros have come into their own, complete with error handling, the appendix also tackles their naming considerations. Adopt, adapt, or create, it’s up to you. People who choose to create their own naming conventions still benefit from being familiar with standard naming conventions. After all, they still need to read and interpret code used in Access and VBA help files, books, and sample applications, and at some point, they are likely to collaborate with others on some level.

... less

This appendix provides a selection of tips, tricks, techniques, and advice to help you build better, stronger, and cooler... more

This appendix provides a selection of tips, tricks, techniques, and advice to help you build better, stronger, and cooler Access applications. There’s a lot of sample code here; you can avoid typing it yourself by downloading the Chamber application files from this book’s website.

... less