In this chapter, you take a look at the different mechanisms available for programming for Access and what it means to... more

In this chapter, you take a look at the different mechanisms available for programming for Access and what it means to program Access applications. You will see that with the ability to use the Access object model outside of Access, programming for Access is not necessarily the same as programming in Access. And, while the possibility is there to develop external applications that consume the Access object model, the primary focus of the book is scenarios that use the Access object model and Visual Basic for Applications (VBA) from within Access itself.

In this chapter, you:

• Learn about using managed code and how it can be used to work with Access applications

• Review object models that are often used with Access

• Learn about off-the-shelf applications in the context of Access development and what it means to develop off-the-shelf applications

... less

VBA simplifies certain tasks, such as reading data from files, asking the user for information, or even launching an external... more

VBA simplifies certain tasks, such as reading data from files, asking the user for information, or even launching an external application. As a programming language, it also does a good job at preventing you from shooting yourself in the foot. As a result, a fair amount of functionality to developers of other languages, such as C++ or C#, is not available natively. Therefore, you utilize the Windows API to achieve some of this same functionality.

In this chapter, you learn how you can leverage the Windows API in your applications. More specifically, you learn the following:

• How to translate an API function declaration written in C/C++ to VBA

• How to use the API to retrieve information about the system

• How to control processes and threads, and work with the Windows shell

• How to use new task dialogs in Windows Vista that allow you to create exciting user interfaces for your applications running on Vista

... less

Class modules can be a powerful tool in your programming toolbox and writing them is one of our favorite programming tasks... more

Class modules can be a powerful tool in your programming toolbox and writing them is one of our favorite programming tasks in VBA. If you have worked with classes in object-oriented languages such as C++ or C#, you might be disappointed to learn that VBA is not a true object-oriented programming language because both VB and VBA lack implementation inheritance. However, class modules in VBA provide many other strengths that can save you time when programming Access or VBA applications. With that in mind, you learn the following about class modules by the end of this chapter:

• The components that make up a class module including properties, methods, enumerations, and events

• How to reuse code that you write in class modules

• How to subclass other classes to add your own functionality or to reduce the amount of code to write

• How to write interfaces in VBA

• How to write powerful and reusable collection classes

• How to create custom events

• Design considerations for VBA class modules

... less

We all have to deal with certain tasks of application development. While customers may never directly see these tasks—such... more

We all have to deal with certain tasks of application development. While customers may never directly see these tasks—such as debugging and error handling—the applications we develop are better for it. We do these tasks in our applications all the time during development, and it’s often a part of the job that developers don’t enjoy.

Although the effects of these activities are not necessarily obvious to your users, improper or insufficient error handling is very obvious. Think about how many times you have tried to hide Access error messages from your users. For an end user, seeing a custom error message provides a better experience than a dialog box that asks them to debug.

In this chapter you learn:

• How to effectively debug VBA code

• How to programmatically build a call stack

• How to create debug builds for your applications that provide functionality during development

• How to use assertions in code to help with debugging

• How to write your own Assert method

• How to use error handling as a feature to notify you when things go wrong

• General coding practices that are helpful in day-to-day development

... less

When working with data in a database, you tend to take for granted the structured nature of the data.... more

When working with data in a database, you tend to take for granted the structured nature of the data. Well-designed databases have schema that provide for granular storage of data. Well-designed relational databases are normalized so that duplicate data is minimized.

But lots of times, you need to work with less structured data that exists in external files of various formats, or that may exist in poorly designed databases that are not sufficiently granular. You often need to extract bits and pieces of data locked away in these files and scary databases. This chapter is all about how to work with this less structured data.

In this chapter, you learn how to do the following:

• Read data from and write data to external text files

• Split data apart and combine it together

• Use the VBA string handling functions to isolate data within strings

• Perform token replacements within strings

• Identify strategies for splitting up complex items into their component parts

• Create HTML files from code

... less

Structured Query Language—more commonly referred to as SQL—is the language used by relational databases to define... more

Structured Query Language—more commonly referred to as SQL—is the language used by relational databases to define queries against a database. The database engine accepts requests created using the SQL language and returns a recordset or executes actions based on the values in the SQL request.

In this lengthy chapter, we examine the following:

• How to create select queries to return data from a database

• How to create action queries to add, modify, and remove data from a database

• How to create and work with subqueries

• How to create and work with crosstab and union queries

• How to use Data Definition Language queries to create and modify schema

• Some of the subtleties of working with ANSI-92 compatibility mode

The Microsoft Access database engine (commonly referred to as ACE) was introduced with Microsoft Access 2007. ACE is an update to the venerable Microsoft Jet database engine which has powered all previous versions of Access, and which is shipped with every version of Microsoft Windows since Windows 98. ACE is completely backward compatible with Jet, and adds support for the multi-valued lookup fields (often referred to as complex data) introduced in Access 2007. With the exception of syntax added to interact with multi-valued lookup fields, the SQL syntax is identical for both Jet and ACE, and all of the sample queries presented in this chapter will work on either Jet or ACE unless we mention otherwise.

By default, both the Jet and ACE database engines use a variant of SQL based on the ANSI-89 standard, which varies slightly from the later ANSI-92 standards. Unless you are an experienced SQL Server developer accustomed to ANSI syntax, you will be more than satisfied with the default syntax used by Access, and this is the language we will use in the examples throughout this book. The section "SQL Pass-Through Queries" describes the differences between standard Jet behaviors and ANSI mode behavior and how to tell the database engine you want to use ANSI-92 syntax.

Most of the examples in this chapter will be based on the Northwind Traders sample database that has shipped with every version of Access since version 1.0. Unfortunately, the Northwind 2007.accdb that ships with Access 2007 was completely revised and in the process, most of the useful samples—and more importantly, the useful data—were removed or made unrecognizable. We will be using the old Northwind.mdb file that ships with Access 2000, Access 2002, and Access 2003. This file is available for download from www.microsoft.com/downloads (search for northwind.mdb).

... less

The primary function of a relational database application, let alone an Access application, is to manage data.... more

The primary function of a relational database application, let alone an Access application, is to manage data. Data is our bread and butter, and is probably a big reason behind the work that you’re doing. Regardless of the amount of data that you have, it should be easy to work with. Finding it should be not only fast, but easy as well.

The Jet database engine that was used with Access from the beginning is now included with Access 2007 and known as the Microsoft Office Access database engine. This updated version of the database engine includes some pretty interesting new features, such as new multiple-value data types that make very simple many-to-many relationships easier to use, as well as introduce new features such as append-only memo fields and attachments.

In this chapter, we look at a wealth of different techniques for working with your data, including strategies and techniques to:

• Locate specific records that match criteria

• Position and manipulate the cursor in a recordset

• Implement custom sorting solutions

• Work with Append-Only Memo fields

• Work with Attachment fields

... less

Aside from the data itself, forms are another key part of an application. Many Access developers tend to prevent users... more

Aside from the data itself, forms are another key part of an application. Many Access developers tend to prevent users interacting with the tables directly, which makes forms the primary mechanism for presentation for data in the application. Unlike reports, forms also provide users with the ability to update data, making them the primary means for interacting with the data as well.

Because forms represent the user interface of the application, what you can do with them is virtually unlimited (or at least within the amount of hours you can bill if you’re a consultant). Regardless of the layout and presentation, however, the ultimate goal for creating forms is to create something for the user that is both useful and enjoyable. Because the requirements for users and the application itself can greatly vary from one application to the next, this is an open statement. Keeping issues of style aside, however, there are several form-related features found throughout database applications and this chapter focuses on those. More specifically, in this chapter you will:

• Work with events on forms to perform tasks such as validating data and moving a borderless form

• Work with events on controls to perform tasks such as working with list boxes to present usable interfaces, creating custom progress bars, and to validate data

• Learn how to create common types of forms such as menus for navigation, splash screens, and dashboards

• See different ways you can create navigation experiences for your users

... less

Reports are an important part of many Access applications. They provide the ability to view information in the database... more

Reports are an important part of many Access applications. They provide the ability to view information in the database in a manner suitable for printing. We use them for items such as invoices, calendars, and financial reporting. Access 2007 includes many new features for reports, such as improved sorting and grouping, Layout view, and Report view. In this chapter, you:

• Learn how to create interactive reports using Access 2007

• Navigate to a Web site from a report

• Simulate a new conditional formatting feature found in Excel 2007

• Utilize code to enhance reports and create different types of reports

• Create a report manager to manage reports in an application

... less

As much power as Access provides on its own, it doesn’t exist in a vacuum. Applications may have requirements to... more

As much power as Access provides on its own, it doesn’t exist in a vacuum. Applications may have requirements to send e-mail to recipients or print custom invoices using Word. When an application grows outside the core functionality of Access, it may be time to start looking at using other applications to help get the job done. By using tools for what they are designed to do and piecing them together, applications turn into solutions. In this chapter, you learn how to use automation to drive other applications to help get the job done. More specifically, you learn the following:

• The basics of automation, including the low-level difference between the CreateObject and GetObject functions

• How to automate objects in Windows such as the Windows Shell and the file system

• How to automate applications in Office to extend the functionality of your applications beyond Access

• How to automate Internet Explorer to integrate the Web into your applications

... less

The Ribbon, first introduced in Office 2007, provides many new and interesting opportunities for user-interface development... more

The Ribbon, first introduced in Office 2007, provides many new and interesting opportunities for user-interface development in Access applications. Unlike menus, the Ribbon gives you a chance to expose functionality in an application that might otherwise be overlooked. For new users, the Ribbon is designed to reduce the barrier to entry, making it easier to find the item the user is looking for.

In this chapter, you learn how to:

• Create ribbon customizations for use in Access applications

• Program the ribbon to provide dynamic user experiences

• Use images effectively in ribbon customizations to provide experiences that are fun and easy-to-use

• Disable or repurpose built-in controls to provide your own functionality

... less

If you’re writing an off-the-shelf application or even an in-house application, it might not always be clear how... more

If you’re writing an off-the-shelf application or even an in-house application, it might not always be clear how all of your customers will use your product. Many times, users need to determine how they work best. To enable users to work in a way they feel most comfortable, you can add the capability to customize your application. In this chapter, we explore how to create applications that are flexible and that give users the ability to configure them to meet their needs. More specifically, in this chapter you learn:

• How to create localized Access applications that can be used in locales other than your own

• How to create options for your applications to provide users with choices for configuration that meet their needs

• How to create styles for your forms and reports to provide a uniform look throughout your application

... less

Very few topics can stimulate discussion like security. Whether you’re working with security for the first time... more

Very few topics can stimulate discussion like security. Whether you’re working with security for the first time or the hundredth time, the questions and discussions are likely to be the same—who has access, who does not have access, who can do what? When it comes to security, understanding the types of questions to ask is just as important as the security itself.

Security needs can vary from one application to the next and, in fact, some applications don’t require any security at all. Sometimes security is added to an application after it has been developed. This can result in difficult changes and decisions, occasionally at the architectural level. For this reason, we recommend determining your security needs up front wherever possible. We find that designing with security in mind from the beginning makes the development process much, much simpler.

In this chapter we discuss:

• New security features available in Access 2007

• Adding personalization to your applications using Windows

• Password-protecting objects such as forms

• Restricting the number of login attempts

• Using the Windows API to lock the computer and receive notifications when the computer is locked or unlocked

• Using the Windows logon name to simulate record-level security

• Some best practices related to security for your applications

... less

You’ve written your application, tested it, and it’s time to get it out to your users.... more

You’ve written your application, tested it, and it’s time to get it out to your users. If the application you’re deploying is within an organization, this might be as simple as copying it to a network share. Off-the-shelf applications, however, may have additional requirements. For example, do you take steps to try and prevent piracy? Do you require a registration process or provide a limited-use application?

Regardless of how your application is distributed, you may still have additional runtime requirements such as an external database server. Issues such as these are problems to solve as part of deployment because that’s when problems may occur.

This chapter covers the following:

• Creating a build process for Access-based applications

• Ensuring that application dependencies are taken care of

• Updating an application

• Licensing and registration

... less

Thorough user documentation and user assistance are two features required of any commercial application,... more

Thorough user documentation and user assistance are two features required of any commercial application, and even small workgroup applications can benefit from help documentation. If you don’t have time to create complete documentation for your database application, a simple help feature can provide lots of benefits to your user, and reduce the amount of effort you need to provide in support of your users.

In this chapter, we discuss several strategies for providing assistance to end users. We show you how to:

• Use intrinsic properties to control the status bar and control tips (tooltips) on forms and reports

• Create and edit custom properties using VBA code

• Create a context-sensitive help system using Access forms

• Create a help system using HTML pages

• Create HTML pages from data in an Access database

• Create a help system using compiled help files

We will begin by looking at some of the built-in features for communicating with users through the Access user interface.

... less

This appendix contains some useful tips and tricks that you can use in your applications. Many of these code snippets... more

This appendix contains some useful tips and tricks that you can use in your applications. Many of these code snippets are ones we have used over the years to help standardize our code or represent specific problems that we were trying to solve. Several of the techniques were born out of refactoring efforts to help make code more elegant. Refactoring is the process of making code more readable or simplified without changing the end result.

This appendix also covers such topics as running code dynamically and working with objects and dates. The appendix ends with some cool tools that you can add to your applications to make the process of writing code easier.

... less

Many different factors determine how efficient and performant your queries will be. Simply reordering elements of your... more

Many different factors determine how efficient and performant your queries will be. Simply reordering elements of your query can make a world of difference. Other times, changes to your database schema will provide a more efficient design that the database engine can use to decrease the time it takes to run your queries. Even simple periodic maintenance can speed things up.

In this appendix, we discuss several issues and techniques you can use to design tables and queries that will make it easy to create efficient queries.

... less

Throughout this book, we have presented code that you can re-use in many applications. The decision about whether to create... more

Throughout this book, we have presented code that you can re-use in many applications. The decision about whether to create a library for some of this code may be personal in some instances and practical in others, but the fact is that by re-using code you can save yourself quite a bit of time. Code that you re-use has the benefits of being well-tested and proven, and likely very efficient.

In this appendix, we discuss patterns and consolidate some of them into a single location that you can use to quickly find information. We’ll do this by:

• Introducing patterns

• Calling out the patterns that we’ve used in this book

• Providing pointers about where you can find more patterns

... less