Access 2003 Power Programming with VBA

John Wiley & Sons

Chapter One

IN THIS CHAPTER

* The various versions of Access and how they differ

* Developing database applications with Access

* The future of Access

Microsoft Access was introduced as a desktop database management system (DBMS) in 1992, and in the years that followed, became a leader in its category. As a part of the popular Microsoft Office suite of applications, Access is used daily by millions of people. Since its inception, Access has grown progressively more capable and easier to use. The most recent versions, while retaining their position as the most popular desktop DBMS in the world, enable developers to build applications that access information from large networked client/server databases based on Microsoft's enterprise scale SQL Server client/server DBMS.

Although many people use Access to perform relatively simple data management tasks that don't require any kind of programming, the Visual Basic for Applications (VBA) language gives Access the power to host systems that are customized to meet the exact needs of a wide variety of organizations, regardless of their size or the kinds of tasks that they need to perform.

The purpose of this book, after laying a little groundwork, is to take you from a non-programming Access power user to a developer who is comfortable using the full power of VBA to buildsophisticated applications that execute complex tasks while making things easy for the user.

Access Versions

Access 1.0 appeared in 1992, shortly after the appearance of Microsoft Windows. Access 1.1 came along in 1993, and in 1994, Access 2.0 arrived on the scene. Each new version was an incremental improvement that built on the strengths of the prior versions. Access 95 followed in 1995, Access 97 in 1997, Access 2000 in 1999, and Access 2002 in 2001. With each new version, Access has grown easier to use and has expanded its data-handling capability.

Access 1.0, 1.1, and 2.0

When Access 1.0 first saw the light of day, it introduced features that were new to personal database products, or for that matter, any kind of database products:

* OLE technology in Table Designer enabled users to create databases that contained graphical images and audio and video files, in addition to text and numbers. OLE stands for object linking and embedding, which is rather descriptive of what OLE does. Users were even able to incorporate Word documents and Excel spreadsheets into an Access database.

* The Forms Package enabled users to create custom forms by dragging form elements onto a blank page. This was a major step forward in ease of use.

* The Report Wizard made report creation equally easy.

* The drag-and-drop Query Designer used query by example (QBE) technology to enable users to create queries without writing program code.

* The Graph Wizard turned chart production into an intuitive step-by-step process.

* In addition to its native files, Access 1.0 could also operate on dBASE, Paradox, and Btrieve files with native drivers, as well as others, such as Sybase SQL Server, DEC Rdb, and FoxPro) via an ODBC interface.

ODBC stands for Object DataBase Connectivity, and provides a common application programmer's interface (API) to a large number of different and mutually incompatible databases.

Access 1.1 added the following improvements:

* Enhanced ODBC and Btrieve support.

* Improved connectivity to FoxPro.

* Easy data export to Word for mail-merge applications.

* Database sizes up to 1GB (about eight times larger than 1.0 could handle).

* An Access runtime became available. Developers could create applications that could operate without Access installed on the system.

Access 2.0 added more capabilities:

* Wizards that automated tasks that had previously required coding on the part of the application developer.

* More sophisticated event handling.

* A debugger in the development tools, plus an automatic documenter and an Add-in Manager.

* Jet database engine improvements, allowing more complex databases to be built and making queries run over twice as fast.

Access 95

Access 95 was the first version to officially be a part of Microsoft Office. Its controls were brought into conformance with the other members of the Office application suite.

* VBA was one of several development tools that Access shared with other Office programs, such as Word and Excel.

* The Import Wizard and the PivotTable Wizard were added.

* The Form and Report Wizards were improved.

Access 97

Access 97 became more Web-aware:

* It stored hyperlinks as a native data type and allowed saving to HTML. Users could publish database objects to the Web, and extract data from an HTML document that contained a table, and then put the data into a database table.

* Class modules could contain the definition of a new object.

* Procedures in a module could hold the properties and methods of that object.

* Conditional compilation enabled developers to create both debug and production compilations of their applications by setting flags in the code.

* Developers could remove source code from an application, thus speeding execution and preventing people from viewing and copying it.

* New ActiveX controls were introduced, and compatibility with source code control systems was added to the Developer Edition of Office 97.

Access 2000

Many aspects of Access were enhanced for this version, improving performance and expanding capability. This Access version introduced these features:

* ActiveX Data Objects (ADO) replaced practically all the data access functions that were previously performed with Data Access Objects (DAO).

* Access Project (.adp) files work with SQL Server and Microsoft Data Engine files as smoothly as .mdb files work with the Jet engine.

* Data access pages (DAPs) were introduced, enabling you to put things that look like Access forms and reports on the Web.

Access 2002

The previous Access release added these features:

* The Data Access Page Designer was improved with these features:

* Multi-level undo and redo

* Better grouping with multi-table data entry

* Enhanced support for the SQL Server Desktop Engine was included, plus cascading updates and deletes.

* The Upsizing Wizard converted applications written to drive the Jet engine so they work with the SQL Server Desktop Engine.

* XML support was added.

Access 2003

New features of Access 2003 include:

* Improved ease of use

* Expanded ability to import, export, and work with XML data files

* Flagging of common errors

* Identification of object dependencies

Access as an Application Development Environment

Access provides a particularly congenial application development environment for several different classes of developers. It is congenial for the following reasons:

* Low barrier to entry. You can start developing useful applications with a minimum of study or training.

* Intuitive. As you use Access more and increase your proficiency, you can tackle more complex assignments, thus using more of Access's power.

* Part of the Office suite. Access can draw on the capabilities of the other members of Microsoft Office, such as Word and Excel.

* Help from wizards. The many wizards that automate a wide variety of tasks can dramatically reduce development time.

* Tight integration with Microsoft SQL Server. Because of this, Access is applicable to a broad range of development tasks, from the smallest personal database application to enterprise-wide applications for large multinational firms. Other DBMS products typically aim for one end of the size spectrum or the other.

The difference between databases and database applications

Sometimes people fail to make the distinction between databases and database applications. This is particularly true of people who "learn by doing" with Access rather than of those who take a formal course in database theory. Access lumps database development and database application development together, with no apparent separation of the two:

* A database is a self-describing collection of integrated records. It is self-describing because the information that describes the structure of the database is included in the database. The records are integrated because relationships between data items are explicitly maintained. These two characteristics differentiate a database from a mere collection of data items.

A database is a structured collection of data. By itself, it doesn't solve any problem or serve any purpose. The database application uses the data in the database to solve problems or to accomplish a purpose.

* A database application is a program that works with a database to maintain and deliver desired information. A database application developer may write the database application or it may be generated semi-automatically by a DBMS, such as Access.

In a relational database, the data is stored in tables, which give the data its structure. A database application may include forms and reports that enable users to interact with the data in the database, with the objective of accomplishing some purpose.

The Database window

When you enter Access and specify the name of the database that you want to work on, the Database window shown in Figure 1-1 is displayed.

The column shown on the left lists the kinds of objects that it deals with: tables, queries, forms, reports, pages, macros, and modules.

For example, if Tables is selected, the pane on the right displays the names of all the tables in the current database plus three options for creating tables, either in Design View, with a wizard, or just by entering data. Similarly, selecting Forms displays the names of any forms that have been created, and gives you the option of creating a new form either in Design View or by using a wizard. Access makes it supremely easy to create all the different database objects that you may need for your application.

Creating tables

Because this is an advanced book on programming with Access, we assume that you can already create a table using Design View. Figure 1-2 shows an example of Design View after entering the specification for the first field of a table. You enter the field name, data type, and description of the field, and then specify some of its properties, such as field length and whether the field is indexed. After you have done this for all the fields in a table, name and save the table. That is all that's involved in creating a database table. Of course, we assume that you have already carefully thought about what fields should be included in the table, what their data types should be, what properties the fields should have, and how this table relates to other tables in the database.

After you have created the tables, you have created the database skeleton or structure. All you need to do now to complete the database is to fill it with data. You can do this in several ways, including through a data-entry form, by typing directly into the table, or by filling the table automatically from an existing file.

The following sections are about creating parts of a database application (forms, reports, data access pages, and so forth), and are not about creating the database itself. Unlike tables, forms, reports, and data access pages are all involved in performing specific tasks or answering specific questions.

Creating forms

For most applications, users see and interact with forms. Access forms are flexible tools. Forms can be different sizes, and they can contain such objects as controls, text, and graphics. For example, you can

* Create switchboard forms that control navigation around an application from one screen to another.

* Use a form to

* Enter data into one or more database tables

* Modify existing table data

* Delete data from tables

* View the data in one or more tables

Figure 1-3 shows a blank form.

Creating reports

The report creation process is similar to the form creation process. The main difference is that reports are more likely to be multi-page documents, and as a result, are more likely to be divided into sections, such as report header and footer, page header and footer, detail section, and group sections. Forms can also have form and page headers and footers, but often do not. Figure 1-4 shows the Design View of a blank report. You can add controls and other objects from the toolbox to appropriate sections of the report to achieve the report that you want.

Creating data access pages

Data access pages, introduced in Access 2000, provide an easy way to display information on the Web that on a local machine would be contained in forms and reports. Data access pages are HTML files that are bound to data in a data source. With data access pages you can

* Browse through the records in a data source

* Add, modify, and delete data

* Display the data either sorted or grouped

You can display database data on the Web in other ways, but data access pages, which are specifically designed for the task, provide an easy way to put your data online.

Using macros

Macros are simple constructs for executing a sequence of operations. Before VBA became available to Access developers, macros provided the only way to automate execution. Now, there is little reason to use macros for any serious development. The many advantages of VBA make it the preferred alternative.

Continues...

---

Excerpted from Access 2003 Power Programming with VBA by Allen G. Taylor Virginia Andersen Excerpted by permission.
All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.
Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site.

---