Vba Code Access2003 Samples

Microsoft Access 2003 is a powerful database application that allows you to build standalone and client-server database applications. Access applications are used in many enterprises for storing inventory and maintaining customer records, sales data, or nearly any other type of data that needs tracking. Access has powerful wizards and tools that make it relatively easy for users to build a database application. However, such databases must often be expanded to include additional features not supported by the simple design provided by the wizards. VBA is a powerful programming language that can be used in Access applications to expand the functionality in ways you never imagined. If you are hoping to take your current Access databases or future databases to a more robust level of functionality, then Beginning Access 2003 VBA is the book for you.

Who Should Read This Book

This book assumes you have created Access databases in the past that have tables, forms, and possibly macros. You may have even written some VBA code in Access before, or you may have experience with Visual Basic or VBScript and want to learn how to write VBA code from within Access applications. No prior experience with VBA is required. As long as you have the ability to create Access tables and forms and the desire to learn VBA, this book will have a lot to offer you.

It is very possible that you have realized limitations with your current Access applications and now desire to build more sophisticated solutions. For example, your current application may need additional features that must be programmed with VBA. Because your application has been a great success, it may have outgrown its current design and may need improvement to support more users or a client-server environment. Or, you may want to begin creating your new database applications with expanded functionality. The tools and techniques covered in this book will teach you how to take your Access applications to the next level.

What This Book Covers

Although Beginning Access 2003 VBA focuses on Access 2003, VBA has not changed a whole lot since the prior versions of Access. Even if you are using a prior version of Access, you can still gain a lot from this book. However, some examples will deal with features supported only in Access 2003.

This is the fifth edition of the book, and I was challenged with rewriting it from scratch to give the book a fresh look. One issue I struggled with is whether to cover DAO in any detail. DAO is a data access methodology that is still supported in Access 2003, but ADO is the better and more recommended data access methodology for use in client-server applications. It also provides enhanced functionality. Prior editions of the book focused on DAO, but some also included examples of how to use ADO. Because DAO is becoming more and more outdated and you will probably want to design a multi-user, enterprise-wide solution now or at some migration point in the future, ADO is the better and more flexible data access method. For these reasons, I decided to include only a brief explanation of DAO, and I have focused all of the data access examples throughout the book on ADO. If you are faced with maintaining an existing application that uses DAO and you need further information, numerous books are written on the topic, as well as extensive resources on the Internet.

As you read this book, you will learn:

□ Basic programming life cycles and architecture concepts

□ How VBA can be used for professional application development

□ The basics of programming using VBA

□ Basic object-oriented programming techniques using VBA

□ How to use existing objects

□ How to create custom objects

□ How to use ActiveX Data Objects (ADO) to retrieve and update data

□ How to retrieve data from external data sources

□ How to create reports and Web content from the database

□ How to integrate with Office applications

□ How to build SQL Server solutions with Access projects

□ How to fine-tune and distribute the application

How This Book Is Structured

I decided to structure the chapters with standalone examples that do not require you to follow the chapters in order. This structure will allow you to go straight to a chapter that interests you, or to skip around among chapters as desired. Certainly, some chapters will not make as much sense unless you have read the prior chapters or are already familiar with the topics covered therein. At least, you will not be required to read the prior chapters that cover topics you already know just for the purpose of building parts of a solution that are prerequisites to the chapter you are really interested in.

Chapter 1 provides an overview of Access 2003 VBA and the programming life cycle, and it introduces the Visual Basic Editor environment. Chapter 2 covers several important VBA programming concepts, such as how to create code procedures, how to control the flow of execution in your code, how to make decisions in your code, how to handle errors, and how to debug your application.

Chapters 3 and 4 demystify object-oriented programming and teach you how to use existing objects and how to create your own custom objects. Chapter 5 illustrates how to use ADO to retrieve and update data in databases and how to create and execute SQL statements using ADO. Chapter 6 covers numerous techniques for building interactive forms, such as creating switchboard forms and choosing the right control for your form. Chapter 7 shows various ways to import, link, and export data to and from external data sources such as MDB files, SQL Server databases, XML files, Web services, and other data sources.

Chapter 8 shows how you can use VBA to create reports programmatically, as well as how to export data in your Access 2003 application to various Web formats, such as HTML, ASP, and data access pages. Chapter 9 illustrates how to create a new Access project as a front end to SQL Server for more robust applications, and it also covers how to migrate an existing Access database to an Access project that uses SQL Server.

Chapter 10 covers advanced VBA topics including how to work with external DLLs and how to use automation to control external programs such as Microsoft Word and Excel, as well as security, transactions, and multi-user considerations. Chapter 11 rounds out the prior chapters by covering some finishing touches that you can apply to prepare your application for distribution.

Chapters 12 and 13 provide two comprehensive case studies that will give you hands-on experience in building real-world solutions using the concepts covered in the book. Chapter 12 focuses on building a standalone project tracking application. The project tracking application allows a user to track projects and related details, such as comments, contacts, and file attachments. A few examples of the user interface for the project tracking application you will create in Chapter 12 are shown in Figures I.1 and I.2.

iJ Project Tracker



Project Id:

Add New

Save Changes


Record 1 Of 3

Project Id:

Project Title:

Project Description:


Comments/Tasks I Contacts File Attachments


Comments/Tasks I Contacts File Attachments


ü Denise








i i:i :


[view Details For Selecied Contact | | Add ■ Manage Contacts | | Delete Selected Contact

[view Details For Selecied Contact | | Add ■ Manage Contacts | | Delete Selected Contact

SWovj Unclosed

Show All

Figure I.1

Chapter 13 focuses on building a client-server customer service application that uses SQL Server as the database. The customer service application allows a customer service agent to locate a customer record based on search criteria and then view and modify the customer record details. A few examples of the

Figure I.2

user interface for this customer service application that you will create in Chapter 13 are shown in Figures I.3 and I.4.

At the end of each chapter, you will be provided with exercises to test your understanding of some of the important concepts covered. Solutions to the exercises at the end of each chapter are provided in Appendix A.

What You Need to Use This Book

At a minimum, you need Access 2003 in order to implement the examples provided throughout the book. One topic covered in Chapter 7 is how to retrieve data from Web services, which requires the Office Web Services toolkit. The Office Web Services toolkit is available for download from Microsoft's Web site. Chapters 9 and 13 deal with examples based on SQL Server 2000 as the database, and thus a copy of SQL Server 2000 is needed to implement those examples. A free entry-level version of SQL Server 2000, called MSDE 2000, is included with versions of Office 2003 that have Access 2003.

Figure I.3


To help you get the most from the text and keep track of what's happening, I've used a number of conventions throughout the book.

Try It Out

The Try It Out is an exercise you should work through, following the text in the book.

1. The exercise usually consists of a set of steps.

2. Each step has a number.

3. Follow the steps using your copy of the database.

How It Works

After each Try It Out, the code you've typed will be explained in detail.

Figure I.4

Boxes like this one hold important, not-to-be-forgotten information that is directly relevant to the surrounding text.

Tips, hints, tricks, and asides to the current discussion are offset and placed in italics like this. As for styles in the text:

□ We highlight important words when we introduce them

□ We show keyboard strokes like this: Ctrl+A

□ We show filenames, URLs, and code within the text like so: persistence.properties

□ We present code in two different ways:

In code examples we highlight new and important code with a gray background.

The gray highlighting is not used for code that's less important in the present context, or has been shown before.

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 http://www.wrox.com. When you are at the site, simply locate the book's title (either by using the Search box or by using 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.

Because many books have similar titles, you may find it easiest to search by ISBN; for this book the ISBN is 0-7645-5659-2.

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


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 http://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's 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.


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 to interact with other readers and technology users. The forums offer a subscription feature, which will enable us 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.

After 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 answers to many common questions specific to P2P and Wrox books. To read the FAQs, click the FAQ link on any P2P page.

0 0

Post a comment