Access 2010 Programmer's Reference

Read an Excerpt

Access 2010 Programmer's Reference

John Wiley & Sons

Chapter One

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-ofuse 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 builtin 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, enduser 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.

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.

(Continues...)

---

Excerpted from Access 2010 Programmer's Reference by Teresa Hennig Rob Cooper Geoffrey L. Griffith Jerry Dennison Copyright © 2010 by John Wiley & Sons, Ltd. Excerpted by permission of John Wiley & Sons. All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.
Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site.

---

Read More