Introduction
Welcome to the Access 2007 Bible, your personal guide to a powerful, easy-to-use database-management system. This book is in its tenth revision and has been totally rewritten for Microsoft Access 2007 with new text, new pictures, and a completely new and improved set of example files.
This book examines Access 2007 with more examples than any other Access 2007 book. We strongly believe that Microsoft Access is an excellent database manager and the best desktop and workgroup database-development system available today. Our goal with this book is to share what we know about Access and, in the process, to help make your work and your life easier.
This book contains everything you need in order to learn Microsoft Access to a mid-advanced level. The book starts off with database basics and builds, chapter by chapter, on topics previously covered. In places where it is essential that you understand previously covered topics, we present the concepts again and review how to perform specific tasks before moving on. Although each chapter is an integral part of the book as a whole, each chapter can also stand on its own and has its own example files. You can read the book in any order you want, skipping from chapter to chapter and from topic to topic. (Note that this book’s index is particularly thorough; you can refer to the index to find the location of a particular topic you’re interested in.)
The examples in this book have been well thought out to simulate the types of tables, queries, forms, and reports most people need to create when performing common business activities. There are many notes, tips, and techniques (and even a few secrets) to help you better understand Microsoft Access.
This book easily substitutes for the online help included with Access. This book guides you through each task you need to perform with Access. This book follows a much more structured approach than the Microsoft Access online help, going into more depth on almost every topic and showing many different types of examples. You’re also going to find much more detail than in most other books on Microsoft Access.
Is This Book for You?
We wrote this book for beginning, intermediate, and even advanced users of Microsoft Access 2007. With any product, most users start at the beginning. If, however, you’re already familiar with Microsoft Access and you’ve worked with the sample files or other Access applications, you may want to start with the later parts of this book. Note, however, that starting at the beginning of a book is usually a good idea so you don’t miss out on the secrets and tips in the early chapters.
We think this book covers Microsoft Access 2007 in detail better than any other book currently on the market. We hope you’ll find this book helpful while working with Access, and that you enjoy the innovative style of a Wiley book.
Yes — If you have no database experience
If you’re new to the world of database management, this book has everything you need to get started with Microsoft Access 2007. It then offers advanced topics for reference and learning. Beginning developers should pay particular attention to Part I, where we cover the essential skills necessary for building successful and efficient databases. Your ability as a database designer is constantly judged by how well the applications you build perform, and how well they handle data entrusted to them by their users. The chapters in Part I won’t necessarily make you an expert database designer, but we guarantee you’ll be a better developer if you carefully read this material.
Yes — If you’ve used other database managers like Filemaker
If you’re abandoning another database (such as Filemaker, Paradox, or FoxPro) or even upgrading from an earlier version of Access, this book is for you. You’ll have a head start because you’re already familiar with database managers and how to use them. With Microsoft Access, you will be able to do all the tasks you’ve performed with other database systems — without programming or getting lost. This book will take you through each subject step by step.
Yes — If you want to learn the basics of Visual Basic for Applications (VBA) programming
We understand that a very large book is needed to properly cover VBA, but we took the time to put together many chapters that build on what you learn in the forms chapters of this book. The VBA programming chapters use the same examples you’ll be familiar with by the end of the book. Part II of this book explains the nuts and bolts — with lots of gritty technical details — of writing VBA procedures and building Access applications around the code you add to your databases. Part II provides everything you need (other than a lot of practice!) to become a bona-fide VBA programmer.
Conventions Used in This Book
The following conventions are used in this book:
• When you’re instructed to press a key combination (press and hold down one key while pressing another key), the key combination is separated by a plus sign. Ctrl+Esc, for example, indicates that you must hold down the Ctrl key and press the Esc key; then release both keys.
• Point the mouse refers to moving the mouse so that the mouse pointer is on a specific item. Click refers to pressing the left mouse button once and releasing it. Double-click refers to pressing the left mouse button twice in rapid succession and then releasing it. Right-click refers to pressing the right mouse button once and releasing it. Drag refers to pressing and holding down the left mouse button while moving the mouse.
• Italic type is used for new terms and for emphasis.
• Bold type is used for material you need to type directly into the computer.
• A special typeface is used for information you see on-screen — error messages, expressions, and formulas, for example.
Icons and Alerts
You’ll notice special graphic symbols, or icons, used in the margins throughout this book. These icons are intended to alert you to points that are particularly important or noteworthy. The following icons are used in this book:
This icon highlights a special point of interest about the topic under discussion.
This icon points to a useful hint that may save you time or trouble.
This icon alerts you that the operation being described can cause problems if you’re not careful.
This icon points to a more complete discussion in another chapter of the book.
This icon highlights information for readers who are following the examples and using the sample files included on the disc accompanying this book.
This icon calls attention to new features of Access 2007.
Sidebars
In addition to noticing the icons used throughout this book, you’ll also notice material placed in gray boxes. This material offers background information, an expanded discussion, or a deeper insight about the topic under discussion. Some sidebars offer nuts-and-bolts technical explanations, and others provide useful anecdotal material.
How This Book Is Organized
This book contains 41 chapters divided into five parts. In addition, the book contains a sixth part containing three appendixes.
Part I: Access Building Blocks
Part I consists of nine chapters that cover virtually every aspect of Access development. For many Access developers, these chapters are all that you’ll ever need. The chapters in this part cover basic database design, referential integrity, constructing tables and queries, building forms and reports, and using the new features in Access 2007.
Chapters 1 through 3 contains great conceptual material on understanding the basic elements of data, introduces you to the buzzwords of database management, and teaches you how to plan tables and work with Access data types. Chapter 4 through 6 teaches you Access queries, expressions, and working with Datasheet view. Much has changed in Access 2007, and even experienced Access users are easily confused by the new user interface.
Chapters 7 through 9 take you on a tour of various types of forms and get a complete understanding of form controls. These chapters drill into the process of creating great-looking and effective forms and reports. You’ll learn how to take best advantage of the new features in Access 2007.
Part II: Programming Microsoft Access
Virtually every serious Access application uses VBA code to perform operations not possible with macros, or to make using the application easier and more reliable. Learning VBA programming is often a daunting task, so the six chapters in this part take extra care to explain the principles behind VBA programming, and show you how to take advantage of this powerful programming language.
In these chapters, you’ll learn not only the fundamental skills required to become proficient in VBA, you’ll also learn many “inside” tricks and techniques to apply to your Access application development projects. You’ll come to understand and appreciate the complex object and event models that drive Access applications, and how to construct the VBA code necessary to take advantage of this rich programming environment.
Part III: More Advanced Access Techniques
One you’ve gotten through the basics of building Access applications, you’ll want your database development skills to extend and enhance your Access applications. Part III includes ten chapters that cover virtually every aspect of advanced Access development, including importing and exporting data, exchanging data with other Windows applications, and integrating Access with Microsoft SharePoint.
The techniques in Part III would normally take most Access developers several years to master. We’ve carefully selected a potpourri of techniques that have proven valuable to each of us in relevant development efforts. Each chapter is accompanied by an example database that demonstrates the techniques documented in the chapter.
Part IV: Professional Database Development
Over the years, Access has grown in its features and capabilities. Although most Access developers never have to use the techniques and features documented in Part IV, we’ve included these techniques to make the Microsoft Access 2007 Bible the most comprehensive reference possible.
Part IV includes 11 chapters covering a wide range of professional-level Access techniques. In these chapters, you’ll read about advanced features such as database replication, object-oriented programming in Access, using the Windows API, creating Access libraries as a way to reuse your VBA code, and customizing the Access 2007 ribbons. Almost all of the information in Part IV has been added for this edition of the Microsoft Access Bible, and reflects the growth and expansion of Access’s capabilities.
Part V: Access as an Enterprise Platform
Access is often employed in “enterprise” environments as a front-end to data stored in a variety of server database systems, such as Microsoft SQL Server and Oracle. In addition, Microsoft has improved SharePoint Services, and has added seamless integration and data sharing between Access and SharePoint. The five chapters in Part V cover a variety of topics that are of interest to developers working in enterprise environments. In these chapters, you’ll see how XML is often used as a data exchange medium, and how Access integrates with server database engines such as SQL Server and Oracle.
You’ll also learn how to upsize Access applications to SQL Server. Access 2007 seamlessly integrates with SQL Server, as either a simple consumer of SQL Server data, or as a direct interface to a SQL Server database. The chapters in Part V cover this important technology in detail.
Part VI: Appendixes
The last part contains three appendixes. Appendix A presents a series of tables listing Access specifications, including maximum and minimum sizes of many of the controls in Access. Appendix B describes the contents of the CD-ROM. And Appendix C tells you what’s new with Access 2007.
Pardon Our Dust!
It almost goes without saying that this book was written during the Access 2007 beta testing phase. It’s possible that a few of the figures in this book don’t exactly match what you see when you open Access 2007, or that the terminology will have changed between the time we wrote our chapters and the time you installed Access 2007 on your computer. Please bear with us — Microsoft has done a great job of documenting its plans and expectations for Access 2007 and we authors have done our best to explain the many changes. We hope that any differences you encounter between our descriptions and explanations and your experience with Access 2007 are minor and do not impact your workflow.
Please feel free to drop us an e-mail at AccessBible@mikegroh.com if you have a question or comment about the material in the Access 2007 Bible. Also, contact us if you have a more general question about development with Access or SQL Server, and we will try to help you out. Before to prefix the subject line of your e-mail with AccessBible: or you won’t get past the spam blocker on this account.
Guide to the Examples
The examples in Access 2007 Bible are specially designed to maximize your learning experience. Throughout this book, you’ll see many examples of good business table design and implementation, form and report creation, and module coding in Visual Basic. You’ll see examples that use both Jet (the internal database of Microsoft Access) as well as examples that connect to SQL Server databases. You’ll also see forms that work with SharePoint data located in remote locations on the Internet.
As every developer knows, it’s important to understand what you’re creating and programming from the application standpoint. This is sometimes called “the business of business,” and in this book we have chosen a simple example that we hope any business or developer can relate to. More importantly, in this or any book you must relate to it successfully in order to learn. When developing systems, you often find yourself analyzing applications that you don’t have a lot of experience with. Obviously an aerospace engineer makes a better analyst when developing a system to track airplane engines, but any good developer can develop any system as long as he’s willing to work with the business experts. In this book, the authors and their words will serve as the business experts.
The examples in this book use a fictitious company named Access Auto Auctions, or AA Auctions for short. AA Auctions buys and sells cars, trucks, and other vehicles. They directly sell these vehicles and also offer them for sale through auctions both at their equally fictitious showroom and on the Internet. The example database contains the necessary tables, queries, forms, reports, and module code to facilitate their business needs.
Within this guide we use some terms that have not been thoroughly explained yet. Feel free to skip over them and return to this guide often as you start new chapters that use these forms and reports.
While professional developers will always split program and data objects into two separate database files, it is acceptable during development to combine all of the objects into one database and split them when development is complete. When you’re working in a program database and you’re linked to your data file, you must load the data database file before you can make changes to the table design. You’ll learn more about this throughout the book.
The Main Menu Switchboard
When you load the completed example file (Access Auto Auctions.mdb), you’ll see the main menu (known as a Switchboard) shown in Figure FM-1. This Switchboard contains buttons that display the main areas of the system.
Figure FM-1
The Access Auto Auctions main Switchboard that allows the user to open various forms and reports
These main areas include
• Contacts: Buyers and Sellers of vehicles and parts that AA Auctions deal with. Rather than traditionally separate Customer and Supplier tables, the Contacts table provides a single source of all people working with AA Auctions.
• Sales: This button displays an invoice form that lets AA Auctions enter information about the buyer (which comes from the Contacts information). Sales allows for an unlimited number of line items on the Invoice, and each item is selected from information stored in the Products system.
• Products: Lists of everything that AA Auctions sells or offers for auctions These include vehicles, parts, and anything that needs to be tracked for sales or inventory purposes including descriptions, costs, selling prices, and even pictures of each vehicle or part.
• Reports: Any good application contains reports at many levels. This button actually does nothing. Normally, it would be used to display a generic report manager that displays reports while allowing specifications of the report name and parameters that only shows data between certain dates or for certain vehicle types.
• Company Setup: This displays a form that contains information used by the entire system. This is used when you need global values such as your company name (Access Auto Auctions in this example) or other information that can be used by the entire application.
Understanding the Data Tables
Data is the most important part of any system and in Access (as well as every other database management system), data is arranged into logical groupings known as tables. Tables help define the structure of the data, as well as hold the data itself. Tables are related to each other in order to pass data back and forth and to help assemble the chaos of data into well-defined and well-formatted information.
The diagram in Figure FM-2 displays the table schema in the Access Auto Auctions example. As you will learn in Part I of this book, the lines, arrows, and symbols between the tables mean something important and communicate to the developer how the data interacts. You’ll learn terms like table, field, record, relationship, referential integrity, normalization, primary keys, and foreign keys as you begin to understand how tables work within a database.
Figure FM-2
The Access Auto Auctions relationship diagram
The example database consists of the 11 core tables shown in Figure FM-2. Many of the smaller tables are lookup tables whose sole purpose is to provide a list of valid selections. The larger tables hold data used by the database application itself. All of these tables include a number of data fields that are used as the definitions of the data. The lines between the tables show how tables are related:
• tblSales: Contains fields for the main part of the sale. This includes information that occurs once for each sale, such as the invoice number, dates of the sale, the buyer ID (which links to the tblContacts table to retrieve information about the buyer including taxing information), the salesperson ID (which links to the tblSalesperson table), the taxing location (which links to the tblTaxRates table), and various other financial information.
• tblSalesPerson: Contains salespeople that sell products for Access Auto Auctions along with their commission rates. It is linked to the sales invoice and is used when a salesperson is selected in the invoice form.
• tblTaxRates: Contains a list of taxing locations and tax rates and is used by the sales invoice when the buyer is selected in the form. The taxing location is retrieved from tblTaxRates, and then the tax rate used by the invoice to calculate taxes owed.
• tblSalesLineItems: Contains fields for the individual line items that will make up the sale. The sale may contain a variety of items. Several vehicles may be sold to a single buyer at one time. The buyer may buy parts, accessories, or services. You’ll see a form created later that allows for the data entry of an invoice and an unlimited number of line items that will be stored in this table.
The data fields in the tblSalesLineItems table include the invoice number, which is used to link the main invoice table to the invoice line items table as well as the quantity purchased. The product ID field (which links to the tblProducts table) is used to retrieve information about the product including the item description, price, and taxability status. A discount field allows a discount to be entered.
The way this table is used violates true relational database theory. Rather than simply link from the tblSalesLineItems table to the tblProducts table by the product ID field, data values from the tblProducts table are copied to the tblSalesLineItems. This is often done with time-dependent data. If a customer bought a part today with a price of $10 and next week the price goes up to $15 as stored in the tblProducts table, it would be wrong if the invoice then showed the price of $15.
You learn more about relational database theory and how to build tables in Part I of this book
• tblSalesPayments: Contains fields for the individual payment lines. The invoice may be paid for by a variety of methods. The customer may make a deposit for the sale with a check, and then split the remaining amount owed with a variety of credit cards. By having unlimited payment lines in the invoice form you can do this.
The data fields in tblSalesPayments include the invoice number which is used to link the main invoice table. There is a field for the payment type (which links to tblPaymentType) to only allow entry of valid payment types, as well as the payment date, payment amount, and any check or credit-card number and the credit-card expiration date.
• tblPaymentType: Is simply a lookup table with valid values for types of payments. Only valid payment types can be chosen for a payment.
• tblContacts: Contains information about all the people and companies that Access Auto Auctions works with. This data includes customers, suppliers, buyers, and sellers. Names, physical addresses, phone and fax numbers, e-mail addresses, and Web sites and all the financial information about the contact is stored in this table. Unlike the tblSalesLineitems table information, this data is linked from an invoice form and, with the exception of some changing financial data, is never copied to any other table. This way if a customer changes his address or phone number, any invoice that is related to the contact data, instantly shows the updated information.
• tblContactLog: Contains zero or more entries for each contact in tblContacts. This information includes the contact date, notes or items discussed, and follow-up information. The contacts form manages all of this information.
• tblCustomerTypes: Simply contains a list of valid customer types that can be selected through the Contacts form. It is important in all applications that certain data be limited to valid values. In this example, each valid value triggers certain business rules. Therefore, data entry must be limited to those values.
• tblProducts: Contains information about the items sold or auctioned by Access Auto Auctions. This table contains information used by the invoices line items.
tblProducts will be one of the main tables used in this book. The frmProducts form is used to teach nearly all form development lessons in the book so you should pay particular attention to it.
• tblCategories: Is used to lookup a list of valid categories.
Understanding the Products Form
frmProducts, shown in Figure FM-3, is the first form that shows how to build Access forms. It is also one of the forms that you’ll use frequently through the book. The Products form was developed with most of the form control types used in Microsoft Access to handle data types such as text, currency, date, yes/no, memo, and OLE pictures.
It is important to have a good understanding of the use of the form as well as the technical details of building it. The form contains information about each product and is bound (tied) to tblProducts. As you enter information into the frmProducts form, it is stored in the tblProducts table.
The top of the frmProducts form contains a control that allows you to quickly find a record. This Quick Find is programmed using VBA code behind a combo box selection. The bottom of the form contains a series of command buttons that will be used to demonstrate how to create new records, delete existing records, and display a custom search and custom print dialog box.
Understanding the Product Form Subform
frmProducts is a great example of how a form works. It displays many records at once but only selected fields. There is also a button alongside each record to delete any records that are no longer needed. Each of the column headers is actually a button with code behind it that can be clicked on to sort the records displayed by the form. One click and the data in that column is used to sort the records in ascending order. The next click sorts the records in descending order.
Figure FM-3
The Access Auto Auctions Products form, which allows data entry for all vehicles and parts sold or auctioned
Understanding the Contacts Form
frmContacts, shown in Figure FM-4, is used to maintain information about the various Access Auto Auctions contacts. This includes the contact’s name and address, whether they are a buyer, seller, or both. This form includes information if the buyer or seller is a car dealer or parts store that they regularly do business with or someone who just once came to an auction, bid on a car, and won.
The Contact form, like the Products form, contains a tab control. This allows you to show several screens within one form. The Contacts form is used in later chapters to illustrate how to display objects within a form based on certain conditions and to show how to use a calendar to store and display data as well.
Figure FM-4
The Access Auto Auctions frmContacts form showing a tabbed dialog box and values used with the tblContacts table
Using the Sales Form
frmSales, shown in Figure FM-5, demonstrates some more advanced form concepts. Unlike all the other forms, the Invoice form contains two subforms, each of which uses a relationship known as one-to-many. This means that there may be one or more records in each subform that relate to (use the same key as) the main form. In this example, each invoice is used to sell one or more products to a buyer. After all the products are selected for the invoice and a total price is calculated, you enter one or more payments to pay for the vehicle. The buyer may make a deposit with a check, and then pay the remaining balance with two different credit cards.
This form also demonstrates simple and complex calculations. The calculation of the Amount column in the invoice line items is Qty x Price x (1-Discount%) for example. All of the amount records have to be totaled to calculate the subtotal field. Then a tax rate is retrieved and calculated to get the tax amount. This plus the other amount must be summed to get the total. All this is happening using fields in the Invoice Line items (fsubSalesLineitems) subform.
fsubSalesPayments subform also shows how to calculate a total in one subform (the total of all payments) and use that total with controls in other parts of the form. This is how the amount due is calculated, using data from the main form and both subforms.
The invoice form also shows several other important techniques, including displaying values in other forms. Each line item and payment can also be deleted by using a button and the code will be explained here as well. The bottom of the invoice form also contains buttons to create a new record to fill in any defaults, as well as to delete an unneeded invoice and to display search and print dialog boxes.
Figure FM-5
The Access Auto Auctions Sales Invoice form used to show multiple linked subforms and totals
