How This Book Is Organized

This book is divided into five parts (29 chapters) that progressively introduce you to programming Microsoft Access databases. An appendix in the form of a PDF file is available to download from

Part I introduces you to Access 2003 VBA programming. Visual Basic for Applications (VBA) is the programming language for Microsoft Office Access. In this part of the book you acquire the fundamentals of VBA that you will use over and over again in building real-life Microsoft Access database applications.

Part I consists of the following nine chapters: Chapter 1 — Procedures and Modules

In this chapter you learn about types of Access procedures that you can write, and learn how and where they are written.

Chapter 2 — The Visual Basic Editor (VBE)

In this chapter you learn almost everything you need to know about working with the Visual Basic Editor window, commonly referred to as VBE. Some of the programming tools that are not covered here are discussed and put to use in Chapter 9.

Chapter 3 — Variables, Data Types, and Constants

In this chapter you are introduced to basic VBA concepts that allow you to store various pieces of information for later use.

Chapter 4 — Passing Arguments to Procedures and Functions

In this chapter you find out how to provide additional information to your procedures and functions before they are run.

Chapter 5 — Decision Making with VBA

In this chapter you learn how to control your program flow with a number of different decision-making statements.

Chapter 6 — Repeating Actions in VBA

In this chapter you learn how you can repeat the same actions by using so-called looping structures.

Chapter 7 — Working with Arrays

In this chapter you learn the concept of static and dynamic arrays that you can use for holding various values. Chapter 8 — Custom Collections and Class Modules

In this chapter you learn how to create and use your own objects and collections of objects.

Chapter 9 — Debugging VBA Procedures and Handling Errors

In this chapter you begin using built-in debugging tools to test your programming code and trap errors.

The skills obtained in Part I of this book are fairly portable. They can be utilized in programming other Microsoft Office applications that also use VBA as their native programming language (Excel, Word, PowerPoint, or Outlook).

Part II introduces you to a set of programming objects known as ActiveX Data Objects (ADO) that enable Microsoft Office Access and other client applications to access and manipulate data. In this part of the book you learn how to use ADO objects in your VBA code to connect to a data source; create, modify, and secure database objects; as well as read, add, update, delete, and replicate data.

Part II consists of the following nine chapters: Chapter 10 — Accessing Data Using ADO

In this chapter you start your encounter with ADO objects by learning several ways of accessing data from various data sources (Microsoft Access, Excel, and dBASE, as well as text files).

Chapter 11 — Creating and Accessing Tables and Fields with ADO In this chapter you learn how to create, copy, link, and delete database tables programmatically. You also learn how to write code to add and delete fields, as well as create listings of existing tables in a database and fields in a table.

Chapter 12 — Setting Up Indexes and Table Relationships with ADO

In this chapter you learn how to write VBA code to add primary keys and indexes to your database tables. You also learn how to use objects from the ADOX library to create relationships between your tables.

Chapter 13 —ADO Techniques for Finding and Reading Records

In this chapter you practice various methods of using programming code to open a set of database records commonly referred to as a recordset. You learn how to move around in a recordset, and find, filter, and sort the required records, as well as read their contents.

Chapter 14 — Working with Records

In this chapter you learn how to perform essential database operations such as adding, updating, and deleting records. You also learn how to render your database records into three popular formats (Excel, Word, and a text file).

Chapter 15 — Creating and Running Queries with ADO

In this chapter you learn how to use VBA code instead of the Query Design view to create and run various types of database queries.

Chapter 16 — Using Advanced ADO Features

In this chapter you learn several advanced ADO features such as how to disconnect a recordset from a database, save it in a disk file, clone it, and shape it. You also learn about database transactions.

Chapter 17 — Implementing Database Security with ADOX and JRO

In this chapter you learn how to secure a Microsoft Access database from VBA procedures by using various methods that are available in the ADOX and JRO object libraries. This is a chapter to refer to if you need to create user and group accounts, set database and user passwords, and grant/revoke database permissions.

Chapter 18 — Database Replication

In this chapter you learn how to make a database available in various geographical locations by using database replication.

You will find the skills obtained in Part II of this book essential in accessing, manipulating, and securing Access databases.

Part III introduces the Data Definition Language (DDL), an important component of the Structured Query Language (SQL). Like ADO (covered in Part II) and DAO (covered in Appendix A), DDL is used for defining database objects (tables, views, stored procedures, primary keys, indexes, and constraints) and managing database security. In this part of the book, you learn how to use DDL statements with Jet databases, ADO, and the Jet OLE DB provider.

Part III consists of the following five chapters:

Chapter 19 — Creating, Modifying, and Deleting Tables and Fields

In this chapter you learn special Data Definition Language commands for creating a new Access database, as well as creating, modifying, and deleting tables. You also learn commands for adding, modifying, and deleting fields and indexes.

Chapter 20 — Enforcing Data Integrity and Relationships between Tables In this chapter you learn how to define rules regarding the values allowed in table fields to enforce data integrity and relationships between tables.

Chapter 21 — Defining Indexes and Primary Keys

In this chapter you learn DDL commands for creating indexes and primary keys.

Chapter 22 — Database Security

In this chapter you learn how to use DDL commands to manage security in the Microsoft Access database. You learn how to quickly create, modify, and remove a database password, and manage user-level accounts.

Chapter 23 — Views and Stored Procedures

In this chapter you work with two powerful database objects: views and stored procedures. You learn how views are similar to Select queries, and how stored procedures can perform various actions similar to what Access Action queries and Select queries with parameters can do.

The skills you learn in Part III of this book will allow you to create, manipulate, and secure your Microsoft Access databases using SQL DDL statements. Numerous Access SQL/DDL statements and concepts introduced here are important in laying the groundwork for moving into the client/server environment (porting your Microsoft Access database to SQL Server).

Part IV introduces you to responding to events that occur in Access forms and reports. The behavior of the Microsoft Access objects such as forms, reports, and controls can be modified by writing programming code known as event procedures or event handlers. In this part of the book you learn how you can make your forms, reports, and controls do useful things by writing event procedures in class modules.

Part IV consists of the following four chapters: Chapter 24 — Using Form Events

In this chapter you learn the types of events that can occur on an Access form and write event procedures to handle various form events.

Chapter 25 — Using Report Events

In this chapter you learn about events that are triggered when the Access report is run. You write your own event procedures to specify what happens when the report is opened, activated/deactivated, or closed.

Chapter 26 — Events Recognized by Controls

In this chapter you work with a custom application and write event procedures for various controls that are placed on an Access form.

Chapter 27 — More about Event Programming

In this chapter you learn about advanced concepts in event programming. You learn how to respond to events in standalone class modules to make your code more manageable and portable to other objects. You also learn how to create and raise your own events.

The skills acquired in Part IV of this book will allow you to enhance and alter the way users interact with your database application.

Part V introduces you to programming Microsoft Access databases for Internet access. Gone are the times when working with Access required the presence of the Microsoft Access application on a user's desktop. Thanks to the development of Internet technologies, you can now publish both static and dynamic Access data to the web. In this part of the book you learn how Active Server Pages (ASP) and Extensible Markup Language (XML) are used with Microsoft Access to develop database solutions for the World Wide Web.

Part V consists of the following two chapters: Chapter 28 — Access and Active Server Pages

In this chapter you learn how to use the Microsoft-developed Active Server Pages (ASP) technology to view, insert, delete, and modify records stored in a Microsoft Access database from a web browser. Before you go on to explore numerous examples of database access presented in this chapter, you are walked through the installation of the Internet Information Services (IIS) on your computer.

Chapter 29 — XML Features in Access 2003

In this chapter you learn how to use the Extensible Markup Language (XML) with Access. You learn how to manually and programmatically export Access data to XML files as well as import an XML file to Access and display its data in a table. You also learn how to use stylesheets and transformations to present Access data to a user in a desired format.

The skills acquired in Part V of the book will make your Access applications Internet and intranet ready. You are now able to connect to, read from, and write to Access databases from within a web browser using two important Microsoft technologies.

This book would not be complete without giving you some overview and practice with the older but still quite popular method of accessing database data from VBA procedures using Data Access Objects (DAO). In order to make this book user-friendly in the sense of portability, Appendix A, which can be downloaded from, provides an overview of DAO. This appendix covers a variety of topics about working with a database, including creating and linking tables, creating indexes and queries, working with records, and implementing database security.

0 0

Post a comment