Access 2010 Programmer's Reference

Introduction

Welcome to Access 2010 Programmer's Reference, your guide to the new features and opportunities that Access 2010 has to offer. With this release, Access has expanded its reach beyond the desktop and network to join the ranks in the cloud. In support of this new level of connectivity, you will find significant changes within the developer environment that require more decisions, different programming tools, and more options. With changes of this magnitude, you will want to leverage community resources to get up-to-speed quickly so that you are working smarter and more efficiently. That's where this book comes in.

What makes this book so special? In addition to the extensive coverage of subject matter, it is the unparalleled team of authors and tech editors who are as devoted to helping fellow developers as they are passionate about Access. Jerry and Teresa have both earned Access MVP status in recognition of their expertise and contributions to the Access community, and Rob and Geoff have worked on the Microsoft Access test team. They are joined by a team of five technical editors who are all Access MVPs; they are not only technical experts but also seasoned consultants and leading-edge developers. Their areas of focus and expertise cover the spectrum from VBA to macros, and from simple solutions to enterprise deployments to web apps, and all areas in between.

Access 2010 builds on many of the new features that were introduced in Access 2007. Features such as templates, application parts, and Office themes will help you leverage existing resources to quickly create and tailor a custom application. To support the expanding use of macros, we have a new macro designer that makes it faster and easier to create and use macros. With their new functionality, you will want to learn how to utilize UI and Data macros in local solutions, not just web applications.

For many of us, being able to take advantage of the new features, right out-of-the-box, is more than enough reason to upgrade. So although the primary focus of this book is to help you extend the power of Access, we will also discuss the new features of Access 2010. Because many of you are familiar with prior versions of Access, we also point out some of the major changes, particularly if they affect the way that you will be working.

The goal is for Access 2010 Programmer's Reference to be your primary resource and tool to help you leverage Access's built-in functionality along with VBA and other tools in a manner that helps you to create solid, robust applications. Access 2010 makes it easy to start working as soon as it's installed. With the new UI (user interface), people will be building complex applications using the tools and resources that ship with Access. And, with a little outside guidance, they can work a lot smarter, with more confidence, and avoid several pitfalls.

This book is for the typical Access user as well as the seasoned programmer. It will help you utilize the power of Microsoft Access more effectively and help you choose when to let the wizards do the work, as well as showing you how to modify and enhance the code that the wizards create. Access builds great forms and reports that can be customized on-the-fly by using VBA code to respond to a multitude of events. Interactive reports, web apps, and new options for distributing applications exemplify the power and potential of Access.

With all the templates, macros, and built-in features, it is easier than ever to open the program and begin creating tables, forms, and reports with confidence. When you consider how easy it is to get started, you'll realize that it is doubly important to be working smart and in the right direction. Use this book and its online resources as your guide to better programming and more effective solutions.

Why Use VBA?

Microsoft Visual Basic for Applications (VBA) enables programmers to develop highly customized desktop applications that integrate with a variety of Microsoft and non-Microsoft programs. For example, all of the Microsoft Office System products support VBA. In addition, many third-party programs, such as accounting software packages, mapping software, and drafting programs also support VBA. Those companies often provide an integration tool, or SDK (software development kit), which typically requires VB or VBA to work with it.

VBA is actually a subset of the Visual Basic programming language and is a superset of VB Script (another member of the Visual Basic family of development tools). VBA includes a robust suite of programming tools based on the Visual Basic development, arguably the world's most popular rapid application development system for desktop solutions. Developers can add code to tailor any VBA-enabled application to their specific business processes. Whether you are starting with a blank database or building on a template, you can build complex custom solutions. For example, a construction company can use VBA within Microsoft Access to develop a sophisticated system covering estimating, ordering, scheduling, costing, and inventory control. The look and operation of the system can be tailored for each group and it can easily limit what data a person can view or change.

You might wonder why you should develop in VBA rather than Visual Basic or .NET, which are also robust, popular, and capable programming languages. Using VBA within Access gives you a couple of key benefits. First, you can leverage the built-in Access object library, taking full advantage of a wide variety of Access commands, including executing any command from the Ribbon or custom toolbar in Access. And second, it's cost effective because VBA is included in all Microsoft Office System applications. Other languages and frameworks, such as .NET, require a separate tool such as Visual Studio and thus a purchase and license. And although they can be cost-effective tools if they are needed, they can also be an unnecessary burden on a project—both in time and money. But do not fear, the book discusses leveraging the power of .NET framework as well.

Access now includes another tool that is often an alternative to VBA—the new Macro Designer—so we have a chapter dedicated to a discussion of how to create and use macros. If you've worked with macros in other Office programs, you will be a step ahead. That is one of the benefits of working with tools that are an intrinsic part of Microsoft Office. If you learn how to program using VBA and macros in Access, your code and skills are transferable to other programs. Even if your primary focus is on Access, knowing VBA will make it much easier to integrate your solution with other applications.

What Does This Book Cover?

Access 2010 Programmer's Reference covers the key topics of greatest importance to Access developers. Although the premise is that most readers will have some familiarity with the VBA programming language, the book includes a brief introduction to VBA and to macros. And to help you leverage the new tools that Access provides, it also has a chapter that highlights the new features in Microsoft Office Access 2010. And, because many people will be faced with upgrading and converting programs from prior versions, a chapter is dedicated to discussing some of the key factors and issues associated with upgrading and running Access in mixed environments.

To help you build a strong foundation on the fundamentals for programming in VBA, the book 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 areas, such as using macros, customizing the Ribbon, and working with Office Backstage are also explored.

Once the basics are covered, the focus turns to forms and reports, the two most powerful tools for working with and displaying data. Several examples demonstrate how all of the pieces fit together to create an application that not only meets your immediate needs, but is also easy to maintain and enhance.

Working with other applications is covered extensively both in general, and more specifically for Microsoft Office applications, Windows SharePoint Services, Access Services, and SQL Server. Of course, this book wouldn't be complete without discussing security issues. And, to supplement the chapters, it has a full compliment of appendixes that put helpful reference material conveniently at your fingertips.

The Chapters

Chapters 1 provide material that you need if you're new to Access, VBA, or macros. We start with an introduction to Access and then provide a review of Access 2010's new features. To help you make informed decisions about converting and upgrading, we'll discuss some of the major factors, processes, and options. After that, you learn about the 2010 macros, how to use them, and how they compare with using VBA. Chapters 5 and 6 discuss the VBA language, VBA Editor, and the building blocks of VBA, including variables, procedures, events, and properties.

The next two chapters are devoted to building a strong foundation on which you will build strong, versatile applications. Chapter 7 emphasizes the dynamics of an Access application—and the need to control what happens to the data and when. In this chapter, you will learn how do to that with a variety of tools and techniques, including using events, procedures, expressions, and recordsets. In Chapter 8, we delve a bit deeper and examine VBA classes and class objects. You'll learn how to create, name, and use classes and objects.

The next step is working toward program integrations. Chapter 9 shows you how to use application programming interface (API) calls to work with and leverage libraries and objects from other sources, such as using Windows APIs. That leads you to the Win32 Registry. In Chapter 10, you will learn how the Registry is structured and how it works. Then we show you how to write basic VBA code to create, retrieve, edit, and delete Registry values.

Chapters 11 and 12 focus on using VBA to access data. Both DAO and ADO provide methods for accessing and working with data in Microsoft Access and other external data sources, which can be relational, such as Informix, SQL Server, or other RDBMSs (relational database management systems), as well as nonrelational, such as text files, spreadsheets, and XML, and a multitude of other possible sources including numerous accounting programs.

In Chapters 13 to 17, you will begin to use VBA within an Access application—or at least in demonstrations. You start by learning the structure for writing SQL statements, and we explain some critical techniques and nuances, such as the difference between using single and double quotes. As we focus on forms and reports, we'll provide real-world examples and techniques that you can incorporate directly into your solutions. From there, we'll move into more specialized areas, such as customizing the Ribbon and working with the new Office Backstage and manipulating XML for both Ribbon and Office Backstage.

Chapters 18–21 provide information about working with other Office applications, such as Word, Excel, Outlook, and PowerPoint. In addition to learning about the new web applications, you'll also discover some new techniques for deploying database solutions and for sharing and securing data using SharePoint. We'll expand the discussion to focus on programming with .NET to automate Access 2010 and working with Visual Studio. We wrap up with a chapter on client-server development.

The final chapters discuss ways to create and deploy applications and some of the security options. Templates and application parts can provide an invaluable tool for quickly creating a solution. Chapter 22 explains how to find, create, and use templates and application parts. As you may have experienced, not everyone that wants to use your application has a copy of Microsoft Access, but that need not preclude them from using your solution. In Chapter 23, you learn how the Access Runtime enables developers to deploy solutions in a custom package that allows their application to run even though a complete version of Access is not installed on the machine. The last two chapters, 24 and 25, discuss database security and Access security features. You'll review options for locking the VBA project, using ACCDE files, and employing data encryption. With Access security, you'll learn about the trust center, disabled mode and macros, digital certificates, the sandbox mode, and some workarounds.

Although that brings you to the end of the chapters, it is far from the end of the book. There are still nine appendixes filled with reference material.

The Appendixes

As a developer, you can often spend hours going from source to source looking for reference material. The authors of this book have applied the principles of relational databases (doing the work once so it can be used many times in multiple ways) to the appendixes, and have created a compilation of data from a myriad of sources.

The first three appendixes document the object models for Access, DAO and ADO. In Appendix D, you will find a discussion on working with 64-bit Access. As mentioned in the discussions on upgrading in Chapter 3, there are significant issues to consider and specific steps to take if you are migrating to a 64-bit version of Access, particularly if you will be integrating with 32-bit applications.

Appendix E discusses references and is an invaluable resource when you are converting from or to different versions of Access or moving an application from one platform or environment to another.

The next three appendixes, F, G, and H, are about good practices, such as avoiding reserved words and special characters, implementing good naming conventions, and having a way to manage code, such as by using Source Code Control.

Appendix I, is like the extra topping on the cake. We share tips and techniques that will help you design polished and professional applications. We demonstrate the techniques and provide a working application so it can become part of your developer toolbox. In addition to our demo application, we also have tips and files from four of our esteemed colleagues, Dane Miller, UtterAccess VIP and database consultant; Garry Robinson, Access MVP and editor of www.vb123.com/kb; Steve Schapel, Access MVP; and Larry Strange, with www.AccessHosting.com.

How to Use This Book

The initial chapters provide background information to help you build a strong foundation. They are written in a tutorial format with detailed examples. Subsequent chapters delve deeper into technical areas and demonstrate numerous techniques that will help you build robust solutions. Most chapters refer to online files that provide the code snippets and help demonstrate the scenarios. True to the Wrox Programmer's Reference format, the book includes numerous reference appendixes with details on the various object models, as well as guidelines and best practices that will help you to work with Access and to write VBA code.

Real-world examples are given for many, if not most, of the programming topics covered in this book. These are just of few of the topics and examples that are included:

Creating intuitive, user-friendly forms to quickly find and work with data, and minimizing data entry errors

Creating custom reports based on information entered on a form

Leveraging report browse, the interactive report feature that enables drilling into data on reports

Summarizing and graphically displaying data using cross-tab reports

Using VBA to transfer data between Access and other Office programs such as Outlook, Word, and Excel

Configuring custom ribbons, toolbars, and menus for your Access database applications

Creating custom web apps and leveraging the features offered through SharePoint and Access Services

Throughout the book, we've also included tips and tricks discovered during the authors' programming experiences.

We recommend that as you go through the book, you also work with the code and sample databases provided in the companion download files on Wrox.com. Being able to run the code, see the effect of minor changes, and experiment on your own will reinforce the learning process. (See the Source Code section later in this Introduction for details on downloading the code.) By working with the code and examples, you will begin to take ownership of the concepts and learn how to incorporate them into your work and solutions.

Other Access/VBA Sources

You've heard the saying that there are as many ways to build a solution as there are programmers. And that may only be a slight exaggeration. So, although this book is an excellent reference for all of your Access 2010 programming needs, there just isn't enough time and ink to cover everything—not to mention fixes, updates, and add-ons. That's where networking, newsgroups, and other information sites come in. Here are some of the authors' favorites for you to check out:

Microsoft Answers: Microsoft provides a landing place for users to ask questions about their products, and Access is one of several products that are covered at the forums. To quickly get to the Access questions and answers, start with this URL: http://social.answers.microsoft.com:80/Forums/en-US/addbuz/threads?filter=mf%3a6edab96e-a4cc-4ccb-ae44-7bcdb5ffa589

The newsgroupcomp.databases.ms-access: An active community that can be accessed using an NNTP newsreader as well as by using groups.google.com. If you do not have NNTP access, free NNTP servers are available—for example, eternal-september.org.

Microsoft's Community website: At (discussion groups) www.microsoft.com/office/community/en-us/default.mspx

MVPS.ORG (www.mvps.org/access): Your jumping-off point to a number of interesting offerings provided by a few folks associated with the Microsoft Most Valuable Professional (MVP) program.

Microsoft Office Online—Access (http://office.microsoft.com/en-us/FX010857911033.aspx): Provides quick tips, and direct links to Access resources such as downloads, templates, training, add-ins, and other pertinent information.

Utter Access (www.utteraccess.com): Currently the leading independent forum for Microsoft Access questions and solutions.

The Access developer center: Programming resources through Microsoft (http://msdn.microsoft.com/en-us/office/aa905400.aspx).

vb123 Knowledge Base: A compilation of Access tips, tools, and articles. Hosted by Garry Robinson, Access MVP (www.vb123.com/kb).

Conventions

Several different styles of text are used in this book to help you understand different types of information. Some of those styles are illustrated here:

We highlight with italics new terms and important words when we introduce them.

We show keyboard strokes like this: Ctrl+A.

We present notes, warnings, and sidebar information in shaded boxes:

This is a note. It contains additional information about the subject under discussion.

This is a warning. It provides information about how to avoid problems related to the subject under discussion.

This Is a Sidebar

It contains asides that provide information related to the subject under discussion.

We show filenames, URLs, and code within the text like so: sample.doc, persistence.properties, and www.microsoft.com.

We present code in two different ways:

We use a monofont type with no highlighting for most code examples.

We use bold to emphasize code that is particularly important in the present context or to show changes from a previous code snippet.

Source Code

As you work through the examples in this book, you may choose either to type in all the code manually or to use the source code files that accompany the book. All the source code used in this book is available for download at www.wrox.com. When at the site, simply locate the book's title (use the Search box or one of the title lists) and click the Download Code link on the book's detail page to obtain all the source code for the book. Code that is included on the website is highlighted by the following icon:

Listings include the filename in the title. If it is just a code snippet, you'll find the filename in a code note such as this:

code snippet filename

Because many books have similar titles, you may find it easiest to search by ISBN; this book's ISBN is 978-0-470-59166-6.

Once you download the code, just decompress it with your favorite compression tool. Alternately, you can go to the main Wrox code download page at www.wrox.com/dynamic/books/download.aspx to see the code available for this book and all other Wrox books.

Errata

We make every effort to ensure that there are no errors in the text or in the code. However, no one is perfect, and mistakes do occur. If you find an error in one of our books, like a spelling mistake or faulty piece of code, we would be very grateful for your feedback. By sending in errata, you may save another reader hours of frustration, and at the same time, you will be helping us provide even higher quality information.

To find the errata page for this book, go to www.wrox.com and locate the title using the Search box or one of the title lists. Then, on the book details page, click the Book Errata link. On this page, you can view all errata that has been submitted for this book and posted by Wrox editors. A complete book list, including links to each book's errata, is also available at www.wrox.com/misc-pages/booklist.shtml.

If you don't spot your error on the Book Errata page, go to www.wrox.com/contact/techsupport.shtml and complete the form there to send us the error you have found. We'll check the information and, if appropriate, post a message to the book's errata page and fix the problem in subsequent editions of the book.

p2p.wrox.com

For author and peer discussion, join the P2P forums at p2p.wrox.com. The forums are a Web-based system for you to post messages relating to Wrox books and related technologies and interact with other readers and technology users. The forums offer a subscription feature to e-mail you topics of interest of your choosing when new posts are made to the forums. Wrox authors, editors, other industry experts, and your fellow readers are present on these forums.

At http://p2p.wrox.com, you will find a number of different forums that will help you, not only as you read this book, but also as you develop your own applications. To join the forums, just follow these steps:

1. Go to p2p.wrox.com and click the Register link.

2. Read the terms of use and click Agree.

3. Complete the required information to join, as well as any optional information you wish to provide, and click Submit.

4. You will receive an e-mail with information describing how to verify your account and complete the joining process.

You can read messages in the forums without joining P2P, but in order to post your own messages, you must join.

Once you join, you can post new messages and respond to messages other users post. You can read messages at any time on the Web. If you would like to have new messages from a particular forum e-mailed to you, click the Subscribe to this Forum icon by the forum name in the forum listing.

For more information about how to use the Wrox P2P, be sure to read the P2P FAQs for answers to questions about how the forum software works, as well as many common questions specific to P2P and Wrox books. To read the FAQs, click the FAQ link on any P2P page.

1

Introduction to Microsoft Access 2010

What's In This Chapter?

A short history of the Access product

A discussion of when to use Access

An introduction to Access 2010 and its main features

A discussion of how to create each of the Access object types using the Access designers

Microsoft Office Access 2010 is the latest version of Microsoft Access, the world-class relational database management system (RDBMS) for the Microsoft Windows platform, designed for building small- to medium-scale database applications. Access 2010 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 2010, to manage data using an Access database or even incorporate Access functionality into an external application. Access 2010, simply put, is a database system that provides a wide variety of functionality and flexibility to build Windows-based applications.

If you're reading this book, you probably already know a good deal about Microsoft Office Access 2010 or a previous version. While this book presents the various aspects of programming Access applications using primarily VBA code, as well as a number of other methods, 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 many tools for creating, designing, and editing database objects that do not require any code at all. If you've used Access before and are familiar with the visual designers and other Access tools, you can easily skip ahead to the next chapter to begin learning about the new features included in Access 2010.

A Brief History of Access

Microsoft Access has been around for nearly 18 years. The first version, 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 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 and 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 2003 was released, there were over 100 million users based in over 80 countries around the world. 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 and data import and export were improved in a number of ways, and signed database projects and disabled mode were introduced for added security. The Access 2003 VBA Programmer's Reference (Wrox, 2004, ISBN 978-0-764-55903-7), the original Access programmer's reference in this series, focused on this version, the eighth release of Access.

Fast-forward to the present, and you have Microsoft Office Access 2010, the tenth full release of Access. Now shipping in over 40 languages, Access is used throughout the world on Windows systems everywhere. For this release, there is a large focus on enhancing ease-of-use, and you'll notice major changes from the 2007 version as soon as you boot the program. There are a number of new features added to the ACCDB file format in this release, as well as a number of new builders, a completely overhauled Macro Designer, and the new SharePoint Web Application features, all of which will be covered more in Chapter 2. After trying out Access 2010, I'm sure you'll see that Microsoft Office Access 2010 is the absolute best release of Access ever.

When to Use Access

Some may ask whether Access is 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 Microsoft SQL Server. If you've used only 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 which database system is best for your data storage needs.

Microsoft Office Access 2010

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 define application logic. 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. Access does not require that a server environment be running to work directly with the database file, and there are a number of different methods for connecting to and working with an Access database. And, although the maximum supported Access database size is 2GB that is usually ample space for almost any personal database.

Multipleuser applications are supported by Access, although there are a number of considerations you should be aware of. 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 multiuser Access applications is to create a distributable front-end database (for each user) that connects to a back-end 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, called a Client-Server application, works well in a single-user or multiuser environment, which is discussed more in Chapter 21, the chapter on Client-Server development. Even then, applications that have a large number of users or data transactions may encounter performance limitations in the ACE database engine. When the database application grows too large, Microsoft recommends moving the database to Microsoft SQL Server, which is specifically designed to handle larger loads.

SQL Server 2008 Express Edition

The Microsoft SQL Server 2008 Express Edition is a scaled-down version of SQL Server 2008. 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. 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 Data Project (ADP) file. Also, Access can link to the tables in SQL Server Express, just as with full SQL Server. SQL Server Express is perfect for scenarios where a SQL database engine is needed, without a large number of user accounts.

However, database development using SQL Server Express requires a fair amount of 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, which is used by the application 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 and connected to a back-end SQL database on a machine running any version of SQL Server 2008 to enjoy the benefits of the SQL Server database engine.

SQL Server 2008

Full Microsoft SQL Server 2008 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 database systems available for Windows. But, as with SQL Server Express, SQL Server will require a front-end application to be developed that will allow users to access the data stored in the SQL database. And, all of this power comes with an associated cost. SQL Server is not free; in fact, it is quite expensive! Additionally, creating database applications with SQL Server also requires 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 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 realistically 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 simultaneous 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?

Will the database need to provide user-level security?

Even answering these questions won't provide a definitive answer 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, will store confidential data, and will need to be accessed by thousands of users at any given time you might consider employing SQL Server. 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 2010 is the perfect choice.

Access Database Basics

The majority of this book is devoted to discussing how to write VBA code for Access database applications; however, many of the features that Access 2010 provides do not require any code whatsoever. In fact, Access is specifically designed to make it easy to build database applications without knowing how to write a lick of code. Although almost any operation in an Access database application can be created by writing and executing code, often there are simpler methods that Access provides, such as the various designers and wizards built into Access. Knowing when it is best to use code and when to use other tools is critical to building cost-effective database solutions, and fortunately, Access 2010 makes this very easy to do!

Getting Started in Access 2010

As soon as you start Access 2010, you will see immediate changes when compared to previous versions. Instead of seeing the Getting Started screen from Access 2007 or a blank window, as in prior versions of Access, you are taken to the new Office 2010 Backstage. The Backstage enables the user to quickly open an existing database, create a new blank database, or even create a new database template. 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 Office button from Access 2007 has been replaced by the File Ribbon tab. The File Ribbon tab exposes all of the Access functionality for working with the database save and load options, the Access Options dialog box for database and applications settings, as well as many of the database analysis tools that Access 2010 provides. The new Backstage feature is the new launching point for all Access database sessions and will be discussed throughout this book.

Access 2010 Database Templates

The Access team has continued to leverage its database template features added in Access 2007 and has created a brand new set of templates for Access 2010. Many of the previous database template applications have been updated and a new slew of templates has been created, many of which are SharePoint Web Applications that work with the new features of SharePoint 2010. New to Access 2010 and Office online are Access database templates that have been submitted by the community. This allows developers from all over the world to share their database applications as part of the Microsoft community. Database templates are a great starting point for building a database solution.

To create a new database using a template, click on the New tab on the left panel of Access 2010 Backstage (the File Ribbon tab). Then click the Sample Templates category to show the built-in templates, or simply select one of the templates from the Office.com templates. Once the template is selected, the preview pane on the right side of the Backstage window will show the template's metadata details. If the template is from Office Online, you will see a Download button; otherwise, you see the Create button. Figure 1.1 provides an illustration of the Backstage with the Northwind template selected.

Figure 1.1

Clicking the Download or Create button will create a new database from the template — the Northwind template, in this example. Once you start the database creation process, you briefly see the Preparing Template dialog box and then the new database will be opened in the Access client window.

The Access Navigation Pane

Originally released in Access 2007, the Navigation Pane provides the user with the ability to see and open database objects. The Navigation Pane replaces its predecessor, the Database window, and provides a number of additional features not previously available. The Navigation Pane is found on the left side of the Access client window when a database is opened in a normal Access session. The Navigation Pane is the primary interface for working with database objects in Access 2010.

By default, when the Northwind database template is created, the Navigation Pane is collapsed. Click on the Navigation Pane to expand it and see the database objects contained in the Northwind database application. The Navigation Pane is highly customizable and provides a number of methods for grouping and filtering database objects. In the case of the Northwind database, a custom grouping, named Northwind Traders, has been created and is shown by that name at the top of the pane. Clicking the top of the pane displays the various object grouping options available in the database. If you choose the Object Type grouping option, the Navigation Pane will show all of the database objects, grouped by each object type, just as the Database window would have in versions of Access prior to 2007. If you are not familiar with the Navigation Pane, this view will be much more familiar and is comparable to the Database window.

The Access Ribbon

Introduced originally in Access 2007, the Access Ribbon is designed to be the primary interface for the majority of operations that Access 2010 provides. The Ribbon replaces the standard Windows style application menu bar and provides much more flexibility than its predecessor. The Ribbon is found at the top of the Access client window. The Access Ribbon is context driven, so certain Ribbons and functionality will only be available in certain modes. For example, the Access Form Design Tools Ribbon is only open when a form is open in Design View. Get to know the Ribbon well; it will be your primary method for working with the Access client functionality.

The Access Security Bar

The Access Security Bar was originally added in Access 2007 and is found directly below the Ribbon when a database is in disabled mode, which is the default mode for Access sessions. The Access Security Bar was added to provide a quick method to enable a database application to allow the execution of code, action queries, unsafe macros, and other operations that are deemed potentially dangerous. To enable a database application in Access 2010, simply click on the Enable Content button on the Access Security Bar.

Access Database Objects

Now that you have a grasp of the basic components to the Access 2010 UI, it is good to have an overview of how to create database objects using the tools that Access 2010 provides. Access 2010 provides six basic object types: Tables, Queries, Forms, Reports, Macros, and Modules. These objects are used to build a database application and, if you are already familiar with Access, are most likely very familiar with each of these objects. Although any of these objects can be created by writing and executing code, often it is much simpler and faster to use the Access design tools to do so. The Create Ribbon is the primary location used for creating all of the major database objects, which will be discussed in this section.

Creating Tables

Tables are the backbone of any database. Tables store all of the data in an Access database application and designing them correctly the first time can save a lot of time and effort in the future, as any changes made to a table once it is already in use may also require substantial changes to all objects that depend on that table. Tables have columns, called Fields, and rows, called Records. The type of data you need to store in any given table field is dictated by its purpose in the application. For example, if you need to store the date on which some event occurred, you would use a Date/Time field data type. You could use a Text field type to store a date and there may be cases where that makes sense, but most of the time, the Date/Time type will be more beneficial because it enables you to leverage the comparison operations provided by the ACE (Access Connectivity Engine) database engine, which could not be done with the Text field type.

Creating tables through the Access 2010 UI is accomplished by choosing any of the options in the Tables group on the Create Ribbon. The Tables group contains three primary options for creating tables: a Table, a Table Design, and a SharePoint Lists option. The Table option opens a new table in the Table Layout designer. The Table Design option opens a new table in the standard Access Table designer. And the SharePoint Lists option enables you to create a new SharePoint Linked-Table from a selection of common list types or by selecting from an existing SharePoint list. Clicking on any of these Ribbon options will create the new table in the currently open Access database and open it in the main Access window.

Access 2010 Field Types

Access 2010 supports eleven field data types for building database tables. The field types supported by the ACE 2010 database engine are: Attachment, AutoNumber, Calculated, Currency, Date/Time, Hyperlink, Memo, Number, OLE Object, Text, and Yes/No.

The Table Designer has one new option available for ACCDB fields in Access 2010. New to Access 2010 is the Calculated field type, which is very similar to a calculated field in a query. This type allows the value of a field to be calculated based on the function supplied when the field is created. Although this is shown as a new field type and technically is its own field type, the data types that are stored in the field are still the basic data types. In itself, the Calculated field is not a new type of data, just a new option for tables.

The Access Table Designer also shows the Lookup field option as a separate option in the field type list. However, Lookup fields are nothing more than a Number or Text field type that defines a key value in another table, so Lookup field types are also not a separate data type.

Access 2010 also supports Complex Data fields for many data types, which were originally introduced in the Access 2007 release. The complex data field option allows multiple values to be selected from a value list for a field in a record. For example, one might have a field that allows selecting a fruit type and it list several kinds of fruit: an Apple, an Orange, and a Banana. If the Allow Multiple Values option is selected for this field, then the user would be able to select several options, instead of just a single option, such as Apples and Orange, instead of just one or the other. The data stored in a Complex Data field can be manipulated as a set of data or as single values. Although not really a different data type, complex data fields allow the Access data types to be used in a different fashion than allowed traditionally.

The following table provides a brief description of each data type's purpose and whether it supports complex data.

Each data type has its own unique purposes, some of which overlap, so be sure to choose the data types wisely. For example, both Text and Memo field types store text characters. Because both types are searchable up to 256 characters and the memo field can hold much more than 256 characters, one might assume that all strings should be stored in Memo fields. However, if that implementation were the case, database users might encounter performance issues when running queries against large sets of Memo field data, which could be avoided by using a Text field type instead. Be sure to completely analyze the data needs before creating a table so that required data types can be planned for appropriately.

Creating Queries

Queries can be created by selecting either option in the Queries group on the Create Ribbon. Queries can be used for a wide variety of purposes such as filtering data based on selection criteria, calculating values, joining records stored in different tables, deleting records from a table, updating records based upon specific criteria, creating new tables, and much, much more. If you have prior database development experience, you've probably had to write SQL statements and already know how complex they can be (and how difficult they can be to get correct). The Access Query Designer provides a graphical interface to help generate the correct SQL statement for many query types, often helping to reduce the complexity of creating accurate SQL statements. Access 2010 also continues to provide the Query Wizard, a tool specifically designed to walk the user through creating each of the pieces of the query in a step-by-step fashion. Switching any query to SQL View mode allows for direct modification of the SQL statement for the query. Access 2010 makes building queries extremely easy, whether you're using the Query Designer, using the Query Wizard, or writing the SQL statement from scratch.

Creating Forms

Forms are vital for allowing users to add, modify, and delete data in a database application. Access provides an extensive forms package and several designers for building robust Windows-style forms. The Create Ribbon provides the Forms group, which offers several options for creating new forms. There are 16 different options for creating various predefined form types, provided by Access to reduce development time by quickly creating common form types. It is important to remember that many options in the Forms group require that a database object be selected in the Navigation Pane, upon which the new form will be created when the option in the Ribbon is chosen. For example, selecting a query in the Navigation Pane and then clicking the Form button creates a new form based on that query. This rule applies for tables, queries, forms, and reports that are selected in the Navigation Pane. If you select any object that does not support the creation of one of the quick forms, such as a macro, many of the form options in the Ribbon will be disabled. It is important to note that any of the predefined form options can be created from any other form type by setting the correct set of form properties. Clicking on any of the form options on the Create Ribbon will create a new form and open it for editing in the Access window.

It should be noted that Access 2010 actually has two different form designers available. Originally introduced in the Access 2007 release, the Form Layout View mode designer allows the user to build the form while being able to see the actual data that will be shown in it. The original designer in Access 2010 is the classic Design View mode designer. Around since the beginning of Access, the Design View designer allows the user to build forms in a grid-based layout, where all of the sections of the form are broken up to show as separate parts. Both designers have their pros and cons, but you will probably find yourself using both to build forms in Access, depending on the specific task at hand.

Creating Reports

Reports are probably the most common way that users will view their data, and that's why it is one of the more robust features in Access. The Reports group of the Create Ribbon provides all of the options for creating reports through the Access UI. Much like forms, Access provides a number of different options for building predefined report types, many of which require an object to be selected in the Navigation Pane before the option is available. Clicking on any of these options will create a new report and open it in the main Access window.

Similar to forms, there are also two designers available for building reports in Access 2010: the classic Design View designer, and the newer Layout View designer. These designers operate in the same manner as they do with forms, but it is worth noting that the Layout View designer is extremely useful for reports because you can see the actual data that you are building the report for. The both Report designers provide two panes: the Grouping and Sorting pane, which greatly improves grouping, sorting, and filtering tasks in a report; and the Design Task pane, which provides the Property Sheet and the Field List. Finally, Access 2010 also provides two standard View modes for Reports: Report View and Print Preview modes. Report View is the default View mode for Reports and allows the user to interact directly with the data in the Report. Print Preview mode is a standard Windows Print Preview window that shows what the printed report will look like and provides options for actually printing the report. Reports in Access 2010 are extremely flexible and useful for any database application.

Creating Macros

The Access 2010 Create Ribbon provides only one option for creating macros, which is the Macro button. Clicking the Macro button option will create a new database-level Macro object and open it in the Access Macro Designer. Embedded macros can also be created via the Property Sheet. However, these macros live in the module for the host object and are not standalone database objects. Macros are often used as a simple alternative to writing VBA code to execute custom application functionality. Although Macros are discussed in depth in Chapter 4, this section will discuss a few of their basics.

Macros in Access are a set of one or more predefined actions that can be set to the events of objects to be executed when those events are triggered. One of the major new features of Access 2010 is that is provides a completely updated macros designer, as well as several new major features for macros. Macros come in two flavors: Safe and Unsafe. Safe macros can be executed when the database is in disabled mode, but unsafe macros cannot. Macros are extremely powerful because they are easy to create, can provide custom functionality just about anywhere in a database application, and provide a large number of common features. Chapter 4 of this book is devoted to discussing macros in Access 2010 and provides more in-depth information on the subject.

Creating Modules

Finally, the last database object type to discuss here, but certainly the most important database object type in terms of this book, is the Module object type. Modules provide storage for VBA code, which can be called in many places throughout a database application. There are three basic types of modules: Modules, class modules, and Form/report modules. While each of these module types has a slightly different purpose and design, they all provide basically the same functionality, which is a storage location for VBA code inside of the database file.

Modules and class modules are themselves individual database objects. Form/report modules are slightly different in that they are tied directly to the Form/Report that they live on. They are not shown as separate database objects in Access, although they are shown as separate modules in VBE (the Visual Basic Editor). Once any of these modules has been created, the user can begin writing VBA code within that module.

To create a new module in a database, simply click on any of the Module options on the Create Ribbon. The new module is created and VBE opens, with the module open for editing in the main window. A user can begin writing code and, when all changes have been completed, the Save button can be used to save the module to the database. In the case of Form/report modules, the module is created the first time the Code Builder or Macro Builder is invoked for a particular form or report. Any subsequent call to embedded code just re-uses that already created module. Regardless of the method chosen, creating modules in Access 2010 is a snap!

Summary

This chapter discussed some of the basics of Microsoft Office Access 2010 and how to create database applications. You learned when it is appropriate to use an Access database to store your data and when it is appropriate to use other database systems. This chapter explored some of the benefits of using the designers to quickly build database objects without writing any code, as well as all of the major database objects that Access 2010 provides. And the Access 2010 UI makes it extremely easy to accomplish all of these database development tasks.

However, this book is about programming Access 2010 and you're probably wondering when you're going to write some code. Stay tuned—although Chapters 2 and 3 cover the new features of Access and upgrading to Access 2010 respectively, Chapter 4 kicks off the discussion of programming Access 2010 by discussing how to program macros. Chapter 5 and most of the subsequent chapters in this book discuss programming Access with VBA code to build high quality, robust database applications with the latest cutting-edge features available in Access 2010.

2

New Features

What's In This Chapter?

Access's new look

Features and enhancements for developers

What's new in forms

UI macros and Data macros

Publishing to the Web with SharePoint

Tools for Web applications

Deprecated features

Access 2010 provides the most revolutionary changes to Access, arguably since Access was first released. Taking a giant step forward, it now offers you the capability to provide Web applications that rely on the Internet browser rather than requiring the user to have Access installed. These features can be combined with rich client applications to create robust hybrid solutions.

Access 2010 is built on the same platform as Access 2007, so you may be familiar with many of the features that provide a user interface that is functional and intuitive. The interactive display of commands and options is based on where you are in the interface, but you still retain control with the capability to resize, hide, and customize the display.

Most of the new features will be discussed in more detail in the chapters that follow, so this chapter will only briefly introduce those features to provide you with a quick overview. If you are migrating to Access 2010 directly from Access 2003 or older, you will appreciate the discussion about some of the 2007 new features that we've included in this chapter's download on Wrox.com.

New Look

You will immediately notice one change when you open Access and look at the first screen. Among the options, you can open a blank database or choose from online and offline files. If you are creating a new database, you can get a jump-start by selecting from the online templates that include an ever expanding list of files submitted by other users, as seen in Figure 2.1. If you click on the Recent tab you will have quick access to the list of files you've recently worked on. You can also click on the Open icon to browse to additional files that are not listed in the Recent window. If you are looking for a template, you can browse the templates that are grouped by topic, such as personal, business, and so on. When you choose a template, the pane on the right prompts and guides you through downloading the file or creating the database and linking it to a SharePoint site. We'll briefly talk about some of the new features for working with SharePoint in the section Integration with SharePoint later in this chapter. Chapter 19 discusses SharePoint in more