Everand Logo

Welcome to Everand!

  • Discover millions of ebooks, audiobooks, and so much more with a free trial

    Only $11.99/month after trial. Cancel anytime.

    Access 2007 Bible
    Access 2007 Bible
    Access 2007 Bible
    Ebook2,206 pages21 hours

    Access 2007 Bible

    By Michael R. Groh, Joseph C. Stockman, Gavin Powell and

    3.5/5

    ()

    Read preview

    About this ebook

    "I recommend this book for anyone who wants a strong foundation in Access."
    —Jeff Lenamon, CIBC World Markets

    Updated edition with exciting new Access 2007 features!

    Harness the power of Access 2007 with the expert guidance in this comprehensive reference. Beginners will appreciate the thorough attention to database fundamentals and terminology. Experienced users can jump right into Access 2007 enhancements like the all-new user interface and wider use of XML and Web services. Each of the book's six parts thoroughly focuses on key elements in a logical sequence, so you have what you need, when you need it. Designed as both a reference and a tutorial, Access 2007 Bible is a powerful tool for developers needing to make the most of the new features in Access 2007.

    • Build Access tables using good relational database techniques
    • Construct efficient databases using a five-step design method
    • Design efficient data-entry and data display forms
    • Utilize the improved Access report designer
    • Use Visual Basic(r) for Applications and the VBA Editor to automate applications
    • Build and customize Access 2007 ribbons
    • Seamlessly exchange Access data with SharePoint(r)
    • Employ advanced techniques such as the Windows(r) API and object-oriented programming
    • Add security and use data replication in your Access applications

    What's on the CD-ROM?

    Follow the examples in the book chapter by chapter using the bonus materials on the CD-ROM. You'll find separate Microsoft Access database files for each chapter and other working files, including

    • All the examples and databases used in the book, including database files, images, data files in various formats, and icon files used in the book's examples
    • A complete sample application file, including queries, reports, objects, and modules, that you can use as a reference

    See the CD-ROM appendix for details and complete system requirements.

    Note: CD-ROM/DVD and other supplementary materials are not included as part of eBook file.

    LanguageEnglish
    PublisherWiley
    Release dateJun 15, 2011
    ISBN9781118050774
    Access 2007 Bible

    Related to Access 2007 Bible

    Titles in the series (96)

    View More
    View More

    Related ebooks

    Databases For You

    View More
    View More

    Related podcast episodes

    Related articles

    • Article

      Set Up Your First Database

      Aug 25, 2020

      Set Up Your First Database
      1 min read

    • Article

      Bitwarden

      Sep 19, 2023

      Bitwarden
      2 min read

    • Article

      Enterprise Soaring Success

      Aug 27, 2019

      Enterprise Soaring Success
      7 min read

    • Article

      Newsdesk

      Mar 5, 2024

      Newsdesk
      11 min read

    • Article

      Tip Sheet NEED AN APP? THERE'S AN APP FOR THAT

      Nov 1, 2022

      WHAT IF YOU COULD build an app for your company without having to hire expensive—and hard-to-find—software developers? That's the promise of low-code and no-code platforms: wresting the power to solve workflow problems from the hands of the engineers

      1 min read

    • Article

      TECHNOLOGY, FINANCE & MARKETING

      Sep 19, 2023

      The Shop Small Resource Hub gives business owners access to complimentary marketing supplies and resources, while Amex Business Savings Suite provides technology and shipping solutions for small businesses. U.S. consumers can also use the Shop Small

      2 min read

    • Article

      The Cost Of Creativity Crisis

      Oct 6, 2022

      With inflation at a forty-year high, we’re surrounded by rising prices. And when it comes to the business of writing, the cost of creativity is increasing, too. There’s also a growing trend in the software industry to move away from one-time purchase

      6 min read

    • Article

      5 Software Stocks That Analysts Love

      May 12, 2021

      The software industry is driving the digital transformation of our society and economy. At this point, software runs on almost every device we use. So it's no surprise that software stocks enjoyed huge gains in 2020, as the coronavirus pandemic force

      1 min read

    • Article

      Funding The Free

      Sep 22, 2020

      Funding The Free
      7 min read

    • Article

      The Top 50 Private Titans

      Mar 17, 2020

      Founded in 2007. A.I.- powered advertising and marketing firm. Founded in 2008. Rents short-term stays in privately owned homes and apartments. Founded in 2007. Provides SaaS-technology business management. Founded in 2003. Provides universal privile

      2 min read

    • Article

      Bitwarden

      Oct 5, 2023

      Bitwarden
      3 min read

    • Article

      2018 Annual Software Guide

      Feb 25, 2018

      The following pages feature the best and most relevant software products designed to improve the built environment, facilitate work and streamline processes. The guide is also an opportunity for software providers to present their corporate vision an

      3 min read

    • Article

      Tale Of A Turnaround

      Nov 18, 2019

      Tale Of A Turnaround
      3 min read

    • Article

      Three Low-code Options

      Nov 12, 2020

      Counting Intel, Vodafone and VW among its customers, OutSystems helps businesses create cloudbased, on-premises and hybrid applications for mobile and web. Its development environment is predominantly drag-and-drop, with views for processes, data and

      3 min read

    • Article

      Innovation Grows Software

      Mar 8, 2019

      Innovation Grows Software
      8 min read

    • Article

      Elevating Smart Home Tech

      Jul 1, 2022

      Jennifer Mallett has one of those names that just keeps popping up around professional residential tech circles. Whether serving on an expert panel, speaking on a podcast, or posting on social media, the CEO of Northborough, Massachusetts-based custo

      5 min read

    • Article

      Wix

      Jan 10, 2023

      Wix
      2 min read

    • Article

      Creating Better Proposals

      Mar 29, 2024

      Creating Better Proposals
      5 min read

    • Article

      Global Voices Seeks Project Writer For Participatory Research Project

      Feb 21, 2022

      Rising Voices, the digital inclusion initiative of Global Voices, is seeking a Writer to support a research project about the digital security and safety needs of global language communities.

      1 min read

    Related categories

    Reviews for Access 2007 Bible

    3.5/5

    4 ratings0 reviews

    What did you think?

    Tap to rate

    Review must be at least 10 words

      Book preview

      Access 2007 Bible - Michael R. Groh

      Acknowledgements

      When we first saw Access in July of 1992, we were instantly sold on this new-generation database management and access tool. We’ve all spent the last 15 years using Access daily. In fact, we eat, breathe, live, and sleep Access!

      The fact that we can earn a living from our work with principally one product is a tribute to the Microsoft Access designers. This product has changed the productivity of corporations and private citizens of the world. More people use this product to run their businesses, manage their affairs, track the most important things in their lives, and manage data in their work and play than any other product ever written. It is indeed a privilege to be part of this worldwide community.

      Now we have completely rewritten this book for Access 2007, with new examples and more in-depth coverage. We’ve covered every new feature we could think of for the beginning and intermediate users and especially enhanced our programming section. Over 500,000 copies of our Access Bibles have been sold for all versions of Microsoft Access; for this we thank all of our loyal readers.

      Our first acknowledgment is to all the users of Access who have profited and benefited beyond everyone’s wildest dreams.

      There are many people who assisted us in writing this book. We’d like to recognize each of them.

      To Greg Croy, whom we complain to each day. Thanks for listening, Pilgrim.

      To Carole McClendon, the very best literary agent in the business, and all the folks at Waterside Productions for being our agents.

      A special thank you to Erik Rucker, Clint Covington, Michael McCormack, Jensen Harris, Shavaj Dhanial, and the rest of the Microsoft Access 2007 Team! You’ve built a terrific product, and we thank you!

      Also, thanks to the Fixed Income–IT group at Raymond James Financial: Julie Valdez, Marek Pokropinski, Aalan Elliot, Aly Fernandez, Bernard Brown, Coni Brown, Lilly Dejesus-Normand, Mae Mello, Michel Thiran, Nancy Hawkins, Renee Crumity, and Steven Twors. Thanks so much for your patience during my many absences during this project!

      Thanks to these wonderful people, we were able to deliver a quality book to our readers.

      For Pam. You are the one.

      —Mike Groh

      This book is dedicated to my mom, who sadly passed away a few days after I finished the project. She has always supported me and encouraged me in everything that I’ve done. She may not have known what Access is or does, but she knew I loved working with it. I’m sure she’s looking into bookstores everywhere and is proud to see her son’s name. I miss you, mom, and you’ll always be with me. Also, a big thanks to my family and friends, who supported me during this project and the difficult time afterwards.

      —Joe Stockman

      This book is dedicated to anyone and everyone using Access 2007. My hope is that readers have as much fun exploring the interface and features as I did during the process of working on parts of this book.

      —Gavin Powell

      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:

      note

      This icon highlights a special point of interest about the topic under discussion.

      tip

      This icon points to a useful hint that may save you time or trouble.

      caution

      This icon alerts you that the operation being described can cause problems if you’re not careful.

      cross_ref

      This icon points to a more complete discussion in another chapter of the book.

      on_the_cd

      This icon highlights information for readers who are following the examples and using the sample files included on the disc accompanying this book.

      newfeature

      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 [email protected] 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.

      note

      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.

      tip

      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

      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 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.

      cross_ref

      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

      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

      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

      The Access Auto Auctions Sales Invoice form used to show multiple linked subforms and totals

      Part I

      Access Building Blocks

      In This Part

      Chapter 1 An Introduction to Database Development

      Chapter 2 Building Access Tables

      Chapter 3 Designing Bulletproof Databases

      Chapter 4 Selecting Data with Queries

      Chapter 5 Using Operators and Expressions in Access

      Chapter 6 Working with Datasheet View

      Chapter 7 Creating Basic Access Forms

      Chapter 8 Working with Data on Access Forms

      Chapter 9 Presenting Data with Access Reports

      Each part of this book builds on previous parts, and the chapters in each part contain examples that draw on techniques explained in previous parts and chapters. As a developer, your applications will benefit from the skills you acquire by reading the chapters and practicing the examples contained in this book.

      But everyone has to start somewhere when they approach a new discipline, and Part I of this book presents the fundamental skills necessary for anyone to succeed at database development with Microsoft Access. The topics covered in this part explain the skills and techniques that are necessary to successfully use the Microsoft Access capabilities documented in the remaining parts of this book.

      The chapters in this part provide the information that you’ll need to build strong applications with Microsoft Access. These chapters go well beyond simply describing how to build tables, forms, and reports with Access. They give you the essential skills necessary to normalize data and plan and implement effective tables. Not the least of these essential skills is choosing the data types for the fields in your tables and providing strong, descriptive names for these important database objects. You’ll also examine the steps necessary to properly create relationships between tables and specify the characteristics that govern those relationships.

      If you’re already familiar with the steps involved in database design, you may want to skim these chapters to learn how to perform these operations with Access 2007. Even if you’re familiar with earlier versions of Access, you have a lot to learn from these chapters because so much has changed in the Access developer environment. And if you’re new to Access, you’ll want to read carefully the chapters in this part and spend enough time working through the examples to gain a thorough understanding of these important topics.

      Chapter 1: An Introduction to Database Development

      In This Chapter

      Understanding what a database is

      Examining the differences between databases, tables, records, fields, and values

      Learning why multiple tables are used in a database

      Looking at database objects

      Learning a five-step design method

      Creating the overall design of a database system

      Designing database tables and relationships

      Designing input forms

      Designing menus

      In this chapter, you learn the concepts and terminology of databases and how to design the tables that your forms and reports will use. Finally, you build the actual tables used by this book’s Access Auto Auctions example database.

      The fundamental concept underlying Access databases is that data is stored in tables. Tables are comprised of rows and columns of data, much like an Excel worksheet. Each table represents a single entity, such as a person or product.

      As you work with Access, you’ll spend considerable time designing and refining the tables in your Access applications. Table design and implementation are two characteristics that distinguish database development from most other activities you may pursue.

      After you understand the basic concepts and terminology, the next important lesson to learn is good database design. Without a good design, you constantly rework your tables, and you may not be able to extract the information you want from your database. Throughout this book, you learn how to use the basic components of Access applications, including queries, forms, and reports. You also learn how to design and implement each of these objects. The Access Auto Auctions case study provides invented examples, but the concepts are not fictitious.

      This chapter is not easy to understand; some of its concepts are complex. If your goal is to get right into Access, you may want to skip to Chapter 2 and read about the process of building tables. If you’re fairly familiar with Access but new to designing and creating tables, you may want to read this chapter before starting to create tables.

      cross_ref

      To jump right into using Access, skip to Chapter 2.

      The Database Terminology of Access

      Before examining the actual table examples in this book, it’s a good idea to have a firm understanding of the terminology that is used when working with databases—especially Access databases. Microsoft Access follows traditional database terminology. The terms database, table, record, field, and value indicate a hierarchy from largest to smallest.

      Databases

      Generally, the word database is a computer term for a collection of information concerning a certain topic or business application. Databases help you organize this related information in a logical fashion for easy access and retrieval.

      Databases aren’t only for computers. There are also manual databases; we simply refer to these as manual filing systems or manual database systems. These filing systems usually consist of people, papers, folders, and filing cabinets—paper is the key to a manual database system. In a real manual database system, you probably have in/out baskets and some type of formal filing method. You access information manually by opening a file cabinet, taking out a file folder, and finding the correct piece of paper. You use paper forms for input, perhaps by using a typewriter. You find information by manually sorting the papers or by copying information from many papers to another piece of paper (or even into an Excel spreadsheet). You may use a spreadsheet or calculator to analyze the data or display it in new and interesting ways.

      An Access database is nothing more than an automated version of the filing and retrieval functions of a paper filing system. Access databases store information in a carefully defined structure. Access tables store data in a variety of forms, from simple lines of text (such as name and address) to complex data such as pictures, sounds, or video images. Storing data in a precise, known format enables a database management system (DBMS) like Access to turn data into useful information.

      Tables serve as the primary data repository in an Access database. Queries, forms, and reports provide access to the data, enabling a user to add or extract data, and presenting the data in useful ways. Most developers add macros or Visual Basic for Applications (VBA) code to forms and reports to make their applications easier to use.

      A relational database management system (RDBMS), such as Access, stores data in related tables. For instance, a table containing employee data (names and addresses) may be related to a table containing payroll data (pay date, pay amount, and check number). Queries allow the user to ask complex questions (such as What is the sum of all paychecks issued to Jane Doe in 2007?) from these related tables, with the answers displayed as on-screen forms and printed reports.

      In Access, a database is the overall container for the data and associated objects. It is more than the collection of tables, however—a database includes many types of objects, including queries, forms, reports, macros, and code modules.

      Access works a single database at a time. As you open Access, a single database is presented for you to use. You may open several copies of Access at the same time and simultaneously work with more than one database.

      Many Access databases contain hundreds, or even thousands, of tables, forms, queries, reports, macros, and modules. With a few exceptions, all of the objects in an Access 2007 database reside within a single file with an extension of accdb, .accde, or .adp.

      The .adp file format is a special database format used by Access to act as a front end to work with SQL Server data.

      Tables

      A table is just a container for raw information (called data), similar to a folder in a manual filing system. Each table in an Access database contains information about a single entity, such as a person or product, and the data is organized into rows and columns.

      In the section titled A Five-Step Design Method later in this chapter, you learn a successful technique for planning Access tables. In Chapters 2 and 3, you learn the very important rules governing relational table design and how to incorporate those rules into your Access databases. These rules and guidelines ensure your applications perform with the very best performance while protecting the integrity of the data contained within your tables.

      In fact, it is very important that you begin to think of the objects managed by your applications in abstract terms. Because each Access table defines an entity, you must learn to think of the table as the entity. As you design and build Access databases, or even when working with an existing application, you must think of how the tables and other database objects represent the physical entities managed by your database.

      After you create a table, you view the table in a spreadsheet-like form, called a datasheet, comprising rows and columns (known as records and fields, respectively—see the following section, Records and fields). Figure 1-1 shows the datasheet view of the Contacts table in the Access Auto Auction application.

      The Contacts table represents people who work with the Auto Auction. Notice how the table is divided into horizontal (left-to-right) rows, and vertical (top-to-bottom) columns of data. Each row (or record) defines a single contact, while each column (or field) represents one type of information known about a contact entity.

      Figure 1-1

      A table displayed as a datasheet

      A table displayed as a datasheet

      For instance, the top row in tblContacts contains data describing John Jones, including his first name and last name, his address, and the company he works for. Each bit of information describing Mr. Jones is a field (FirstName, LastName, Address, Company, and so on). Fields are combined to form a record, and records are grouped to build the table.

      Each field in an Access table includes many properties that specify the type of data contained within the field, and how Access should handle the field’s data. These properties include the name of the field (LastName) and the type of data in the field (Text). A field may include other properties as well. For instance, the Size property tells Access how many characters to allow for a person’s last name. (You learn much more about fields and field properties in Chapter 2.)

      Records and fields

      As Figure 1-1 shows, the datasheet is divided into rows (called records) and columns (called fields), with the first row (the heading on top of each column) containing the names of the fields in the database. Each row is a single record containing fields that are related to that record. In a manual system, the rows are individual forms (sheets of paper), and the fields are equivalent to the blank areas on a printed form that you fill in.

      Values

      At the intersection of a row (record) and a column (field) is a value—the actual data element. For example, John, the name in the first record, represents one data value. You may have a couple questions, such as: What makes this row different from other rows in the table? Is it possible to have another John Jones in the same table? If there is more than one John Jones, how does the database tell them apart?

      Relational Databases

      Microsoft Access is a relational database development system. Access data is stored in related tables, where data in one table (such as customers) is related to data in another table (such as orders). Access maintains the relationships between related tables, making it easy to extract a customer and all of the customer’s orders, without losing any data or pulling order records not owned by the customer.

      Working with multiple tables

      Multiple tables simplify data entry and reporting by decreasing the input of redundant data. By defining two tables for an application that uses customer information, for example, you don’t need to store the customer’s name and address every time the customer purchases an item.

      After you’ve created the tables, they need to be related to each other. For example, if you have a Contacts table and a Sales table, you must relate the Contacts table to the Sales table in order to see all the sales records for a Contact. If you had only one table, you would have to repeat the Contact name and address for each sale record. Two tables let you look up information in the Contact table for each sale by using the related fields Contact ID (in Contacts) and Buyer ID (in Sales). This way, when a customer changes address, for example, the address changes only in one record in the Contact table; when the Sales information is on-screen, the correct contact address is always visible.

      Separating data into multiple tables within a database makes the system easier to maintain because all records of a given type are within the same table. By taking the time to segment data properly into multiple tables, you experience a significant reduction in design and work time. This process is known as normalization. (You can read about normalization in Chapter 2.)

      Later in this chapter in the section titled A Five-Step Design Method, you have the opportunity to work through a case study for the Access Auto Auctions that consists of five tables.

      Knowing why you should create multiple tables

      The prospect of creating multiple tables always intimidates beginning database users. Most often, they want to create one huge table that contains all of the information they need—in this case, a Customer table with all the sales performed by the customer and all the items sold or bought for each customer.

      So, they create a single table containing a lot of fields, including fields for customer information (contact), sales information (date of sale, salesperson, amount paid, discounts, and so on), and the product information (quantity sold, product description, individual prices, and so on) for each sale. Such a table quickly grows to an unmanageable number of fields and continues growing as new items are added.

      As you can see, the table design begins to take on a life of its own. After you’ve created the single table, it becomes even more difficult to maintain. You begin to realize that you have to input the customer information for every sale a customer makes (repeating the information over and over). The same is true for the items purchased for each sale, which is multiple items for each sale (thus, duplicating information again). This makes the system more inefficient and prone to data-entry mistakes. The information stored in the table becomes inefficiently maintained—many fields may not be appropriate for each record, and the table ends up with a lot of empty fields.

      It’s important to create tables that hold the minimum of information while still making the system easy to use and flexible enough to grow. To accomplish this, you need to consider making more than one table, with each table containing records with fields that are related only to the focus of that table. Then, after you create the tables, you link them so that you’re able to glean useful information from them. Although this process sounds extremely complex, the actual implementation is relatively easy. Again, this process of creating multiple tables from a single table is known as normalization (or normalizing your tables).

      Access Database Objects and Views

      If you’re new to databases (or even if you’re an experienced database user), you need to understand some key concepts before starting to build Access databases. The Access database contains seven types of top-level objects, which consist of the data and tools that you need to use Access:

      • Table: Holds the actual data

      • Query: Searches for, sorts, and retrieves specific data

      • Form: Lets you enter and display data in a customized format

      • Report: Displays and prints formatted data

      • Pages: Publishes data to a corporate intranet

      • Macro: Automates tasks without programming

      • Module: Contains programs written in the Visual Basic for Applications (VBA) programming language

      Datasheets

      Datasheets are one of the many ways by which you can view data in Access. Although not a database object, a datasheet displays a list of records from a table in a format similar to an accounting spreadsheet or Excel worksheet. A datasheet displays data as a series of rows and columns (comparable to an Excel spreadsheet). A datasheet displays a table’s information in its raw form. The datasheet view is the default mode for displaying all fields for all records.

      You scroll through the datasheet using the directional keys on your keyboard. You can also display related records in other tables while in a datasheet. In addition, you can make changes to the displayed data.

      caution

      Use caution when making changes or allowing a user to modify data in datasheet format. When a datasheet record is updated, the data in the underlying table is permanently changed.

      Queries

      Queries extract information from a database. A query selects and defines a group of records that fulfill a certain condition. Many forms and most reports are based on queries that pre-filter data before it is displayed. Queries are often called from VBA procedures to change, add, or delete database records.

      An example of a query is when a person at the Auto Sales office tells the database, Show me all customers, in alphabetical order by name, who live in Massachusetts and bought something over the past six months, and display them sorted by Customer name, or Show me all customers who bought cars for a value of $35,000 or more for the past six months and display them sorted by customer name and then by value of the car.

      Instead of asking the question in English words, the person uses the query by example (QBE) method. When you enter instructions into the QBE Design window, the query translates the instructions into Structured Query Language (SQL) and retrieves the desired data. Chapter 4 discusses the QBE Design window and building queries.

      In the first example, the query first combines data from both the Sales and Contact tables, using the related field Contact ID (the common link between the tables). Next, it retrieves the first name, last name, and any other data you want to see. Access then filters the records, selecting only those in which the value of the sales date is within six months of the current date. The query sorts the resulting records first by contact’s last and first names. Finally, the records appear on-screen in a datasheet.

      A similar action takes place for the second example—using sales, contacts, invoice items, and products and the criteria applied to the search is where the Description field has a car bought whose value in the Price field is greater than or equal to $35,000.

      After you run a query, the resulting set of records may be used in a form that is displayed on-screen or printed on a report. In this way, user access is limited to the data that meets the criteria in the returned records.

      Data-entry and display forms

      Data-entry forms help users get information into a database table quickly, easily, and accurately. Data-entry and display forms provide a more structured view of the data than what a datasheet provides. From this structured view, database records can be viewed, added, changed, or deleted. Entering data through the data-entry forms is the most common way to get the data into the database table.

      Data-entry forms restrict access to certain fields within the table. Forms also check the validity of your data before it is added to the database table.

      Most users prefer to enter information into data-entry forms rather than datasheet views of tables. Data-entry forms often resemble familiar paper documents and can aid the user with data-entry tasks. Forms make data entry self-explanatory by guiding the user through the fields of the table being updated.

      Display-only screens and forms are solely for inquiry purposes. These forms allow for the selective display of certain fields within a given table. Displaying some fields and not others means that you can limit a user’s access to sensitive data while allowing inquiry into other fields.

      Reports

      Reports present your data in printed format. Access supports several different types of reports. A report may list all records in a given table (such as a customer table) or may list only the records meeting a certain criterion, such as all customers living in the State of Washington. You do this by basing the report on a query that selects only the records needed by the report.

      Your reports can combine multiple tables to present complex relationships among different sets of data. An example is printing an invoice. You access the customer table to obtain the customer’s name and address (and other relevant data) and related records in the sales table to print the individual line-item information for the products ordered. You then instruct Access to calculate the totals and print them in a specific format on the form. Additionally, you can have Access output records into an invoice report, a printed document that summarizes the invoice.

      tip

      When you design your database tables, keep in mind all the types of information that you want to print. Doing so ensures that the information you require in your various reports is available from within your database tables.

      Designing the system’s objects

      To create database objects, such as tables, forms, and reports, you first complete a series of tasks known as design. The better your design is, the better your application will be. The more you think through your design, the faster you can complete any system. The design process is not some necessary evil, nor is its intent to produce voluminous amounts of documentation. The sole intent of designing an object is to produce a clear-cut path to follow as you implement it.

      A Five-Step Design Method

      Figure 1-2 is a version of the design method that is modified especially for use with Access. This is a top-down approach, starting with the overall system design and ending with the forms design, and it consists of five steps.

      Figure 1-2

      The five-step design flowchart. This design methodology is particularly well-suited for Access databases.

      The five-step design flowchart. This design methodology is particularly well-suited for Access databases.

      These five design steps, along with the database system illustrated by the examples in this book, teach a great deal about Access and provide a great foundation for creating database applications—including tables, queries, forms, data pages, reports, macros, and simple VBA (Visual Basic for Applications) modules.

      The time you spend on each step depends entirely on the circumstances of the database you’re building. For example, sometimes the users give you an example of a report they want printed from their Access database, and the sources of data on the report are so obvious that designing the report takes a few minutes. Other times, particularly when the users’ requirements are complex, or the business processes supported by the application require a great deal of research, you may spend many days on Step 1.

      As you read through each step of the design process, always look at the design in terms of outputs and inputs. Although you see actual components of the system (cars, buyers, sellers, and transactions), remember that the focus of this chapter is how to design each step. As you watch the Access Auto Auctions system being designed, pay attention to the design process, not the actual system.

      Step 1: The overall design—from concept to reality

      All software developers face similar problems, the first of which is determining how to meet the needs of the end user. It’s important to understand the overall requirements before zeroing in on the details.

      The five-step design method shown in Figure 1-2 helps you to create the system that you need, at an affordable price (measured in time or dollars). The Access Auto Auctions database, for example, allows the client to sell items (vehicles and parts) to customers. The Access Auto Auctions database automates the following tasks:

      • Entering and maintaining contact information for customers and sellers (name, address, and financial history)

      • Entering and maintaining sales information (sales date; payment method; total amount, including tax; buyer ID; and other fields)

      • Entering and maintaining sales line item information (details of items actually purchased)

      • Viewing information from all the tables (sales, contacts, sales line items purchased, and payment information)

      • Asking all types of questions about the information in the database

      • Producing a current contacts directory

      • Producing a monthly invoice report

      • Producing a customer sales history

      • Producing mailing labels and mail-merge reports

      These nine tasks that the Access Auto Auctions automates have been expressed by the client. You may need to consider other tasks as you start the design process.

      Most of the information that is necessary to build the system comes from the eventual users. This means that you need to sit down with them and learn how the existing process works. To accomplish this you need to do a thorough needs analysis of the existing system and how you might automate it.

      One way to accomplish this is to prepare a series of questions that give insight to the client’s business and how the client uses his data. For example, when considering automating an auto auction business, you may consider asking these questions:

      • What reports and forms are currently used?

      • How are sales, customer, contacts, and other records currently stored?

      • How are billings processed?

      As you ask these questions and others, the client will probably remember other things about his business that you should know.

      A walkthrough of the existing process is also necessary to get a feel for the business. Most likely, you’ll have to go back several times to observe the existing process and how the employees work.

      When you prepare to follow the remaining steps, keep the client involved—let him know what you’re doing and ask for his input as to what you want to accomplish, making sure it is within the scope of his needs.

      Step 2: Report design

      Although it may seem odd to start with reports, in many cases users are more interested in the printed output from a database than they are in any other aspect of the application. Reports often include virtually every bit of data managed by an application. Because reports tend to be comprehensive, reports are often the best way to gather important information about a database’s requirements. In the case of the Access Auto Auctions database, the printed reports contain detailed and summarized versions of most all the data in the database.

      After you’ve defined the Access Auto Auctions’ overall systems in terms of what must be accomplished, you can begin report design.

      When you see the reports that you will create in this section, you may wonder, Which comes first—the chicken or the egg? Does the report layout come first, or do you first determine the data items and text that make up the report? Actually, these items are considered at the same time.

      It isn’t important how you lay out the fields in a report. The more time you take now, however, the easier it will be to construct the report. Some people go so far as to place gridlines on the report so that they will know the exact location they want each bit of data to occupy. In this example, you can just do it visually.

      The reports in Figures 1-3 and 1-4 were created with two different purposes. The report in Figure 1-3 displays information about an individual contact (buyer, seller, or both). In contrast, the report in Figure 1-4 is an invoice with billing and customer information. Both of these reports were based on the type of information they use. The design and layout of each report is driven by the report’s purpose and the data it contains.

      Figure 1-3

      A contact information report

      A contact information report

      Figure 1-4

      A sales invoice report containing sales information

      A sales invoice report containing sales informationcross_ref

      You can read more about the reports for the Access Auto Auctions system in Chapters 9 and 20.

      Step 3: Data design: What fields are required?

      The next step in the design phase is to take an inventory of all the information or data fields that are needed by the reports. One of the best methods is to list the data items in each report. As you do so, take careful note of items that are included in more than one report. Make sure that you keep the same name for a data item that is

      Enjoying the preview?
      Page 1 of 1