Synopses & Reviews
You’re beyond the basics, so dive right in and really put your database skills to work This supremely organized reference is packed with hundreds of timesaving solutions, troubleshooting tips, and workarounds. It’s all muscle and no fluff. Discover how the experts tackle Access 2007and challenge yourself to new levels of mastery Create tables that support your database design strategy Import and link to data from spreadsheets, text files, databases, and other ODBC data sources Build simple to complex queries to manipulate data Learn advanced techniques for building and customizing user interface forms Design attractive reports to calculate and analyze large sets of data Automate your application with Microsoft Visual Basic for Applications Customize the Office Fluent Ribbon Explore using XML and Windows SharePoint Services to create Web-based applications
CD includes: Fully searchable eBookplus bonus chapters Sample database applicationsincluding query, form, and report examples Articles from the experts: designing databases, understanding SQL, exporting data, and more Links to demos, blogs, and user communities References for finding Access 2003 commands in Access 2007 Windows Vista Product Guide eReference and other eBooks
A Note Regarding the CD or DVD
The print version of this book ships with a CD or DVD. For those customers purchasing one of the digital formats in which this book is available, we are pleased to offer the CD/DVD content as a free download via OReilly Medias Digital Distribution services. To download this content, please visit OReillys web site, search for the title of this book to find its catalog page, and click on the link below the cover image (Examples, Companion Content, or Practice Files). Note that while we provide as much of the media content as we are able via free download, we are sometimes limited by licensing restrictions. Please direct any questions or concerns to email@example.com.
After touring the Access 2007 user interface, this guide walks through the process of creating desktop application databases and tables, designing queries to analyze and update data, building forms and reports in a desktop application, automating an application with macros and Visual Basic, and publishing Access applications. The CD- ROM contains three sample database applications, four chapters, and six articles.
You’re beyond the basics, so dive right in and really put your database skills to work! This supremely organized reference is packed with hundreds of timesaving solutions, troubleshooting tips, and workarounds. It’s all muscle and no fluff. Discover how the experts tackle Access 2007—and challenge yourself to new levels of mastery!
- Create tables that support your database design strategy
- Import and link to data from spreadsheets, text files, databases, and other ODBC data sources
- Build simple to complex queries to manipulate data
- Learn advanced techniques for building and customizing user interface forms
- Design attractive reports to calculate and analyze large sets of data
- Automate your application with Microsoft Visual Basic(R) for Applications
- Customize the Office Fluent Ribbon
- Explore using XML and Windows(R) SharePoint(R) Services to create Web-based applications
- Fully searchable eBook—plus bonus chapters
- Sample database applications—including query, form, and report examples
- Articles from the experts: designing databases, understanding SQL, exporting data, and more
- Links to demos, blogs, and user communities
- References for finding Access 2003 commands in Access 2007
- Windows Vista(R) Product Guide eReference and other eBooks
For customers who purchase an ebook version of this title, instructions for downloading the CD files can be found in the ebook.
About the Author
Jeff Conrad is a Software Design Engineer with the Access development team at Microsoft. He maintains a website with a wealth of information and resource links for those needing guidance with Access. Jeff is the author of Microsoft Access 2010 Inside Out.
John L. Viescas is a database expert who provides consulting and training services to companies around the globe. He is a Microsoft MVP and the author of several popular books, including Microsoft® Office Access® 2003 Inside Out and Building Microsoft® Access® Applications.
Table of Contents
Acknowledgments; About the CD; Whats on the CD; Sample Applications; System Requirements; Support Information; Conventions and Features Used in This Book; Text Conventions; Design Conventions; Syntax Conventions; Introduction; Getting Familiar with Access 2007; About This Book; Part 1: Understanding Microsoft Access; Chapter 1: What Is Microsoft Access?; 1.1 What Is a Database?; 1.2 Access as an RDBMS; 1.3 Access as an Application Development System; 1.4 Deciding to Move to Database Software; Chapter 2: Exploring the New Look of Access 2007; 2.1 Opening Access for the First Time; 2.2 Getting StartedA New Look for Access; 2.3 Understanding Content Security; 2.4 Understanding the New Ribbon Feature; 2.5 Understanding the New Navigation Pane; 2.6 Using the Single-Document vs. Multiple-Document Interface; 2.7 Modifying Global Settings via the Access Options Dialog Box; Chapter 3: Microsoft Office Access 2007 Overview; 3.1 The Architecture of Access; 3.2 Exploring a Desktop DatabaseHousing Reservations; 3.3 Exploring a Project FileConrad Systems Contacts; 3.4 The Many Faces of Access; Part 2: Building an Access 2007 Desktop Application; Chapter 4: Creating Your Database and Tables; 4.1 Creating a New Database; 4.2 Creating Your First Simple Table by Entering Data; 4.3 Creating a Table Using a Table Template; 4.4 Creating a Table in Design View; 4.5 Defining Fields; 4.6 Defining a Primary Key; 4.7 Defining a Table Validation Rule; 4.8 Understanding Other Table Properties; 4.9 Defining Relationships; 4.10 Adding Indexes; 4.11 Setting Table Design Options; 4.12 Creating a Default Template for New Databases; 4.13 Printing a Table Definition; 4.14 Database Limitations; Chapter 5: Modifying Your Table Design; 5.1 Before You Get Started; 5.2 Deleting Tables; 5.3 Renaming Tables; 5.4 Changing Field Names; 5.5 Moving Fields; 5.6 Inserting Fields; 5.7 Copying Fields; 5.8 Deleting Fields; 5.9 Changing Data Attributes; 5.10 Reversing Changes; 5.11 Using the Table Analyzer Wizard; 5.12 Taking a Look at Lookup Properties; 5.13 Working with Multi-Value Lookup Fields; 5.14 Changing the Primary Key; 5.15 Compacting Your Database; Chapter 6: Importing and Linking Data; 6.1 A Word About Open Database Connectivity (ODBC); 6.2 Creating a Data Source to Link to an ODBC Database; 6.3 Importing vs. Linking Database Files; 6.4 Importing Data and Databases; 6.5 Importing Spreadsheet Data; 6.6 Importing Text Files; 6.7 Modifying Imported Tables; 6.8 Linking Files; 6.9 Collecting Data via E-Mail; Chapter 7: Creating and Working with Simple Queries; 7.1 Selecting Data from a Single Table; 7.2 Testing Validation Rule Changes; 7.3 Working in Query Datasheet View; Chapter 8: Building Complex Queries; 8.1 Selecting Data from Multiple Tables; 8.2 Using a Query Wizard; 8.3 Summarizing Information with Totals Queries; 8.4 Using Query Parameters; 8.5 Customizing Query Properties; 8.6 Editing and Creating Queries in SQL View; 8.7 Limitations on Using Select Queries to Update Data; 8.8 Creating PivotTables and PivotCharts from Queries; Chapter 9: Modifying Data with Action Queries; 9.1 Updating Groups of Rows; 9.2 Creating a New Table with a Make-Table Query; 9.3 Inserting Data from Another Table; 9.4 Deleting Groups of Rows; 9.5 Troubleshooting Action Queries; Part 3: Creating Forms and Reports in a Desktop Application; Chapter 10: Using Forms; 10.1 Uses of Forms; 10.2 A Tour of Forms; 10.3 Moving Around on Forms and Working with Data; 10.4 Adding Records and Changing Data; 10.5 Searching for and Sorting Data; 10.6 Printing Forms; Chapter 11: Building a Form; 11.1 Forms and Object-Oriented Programming; 11.2 Starting from ScratchA Simple Input Form; 11.3 Working with Quick Create Commands and the Form Wizard; 11.4 Simplifying Data Input with a Form; Chapter 12: Customizing a Form; 12.1 Aligning and Sizing Controls in Design View; 12.2 Working in Layout View; 12.3 Enhancing the Look of a Form; 12.4 Setting Control Properties; 12.5 Setting Form Properties; 12.6 Setting Form and Control Defaults; Chapter 13: Advanced Form Design; 13.1 Basing a Form on a Multiple-Table Query; 13.2 Creating and Embedding Subforms; 13.3 Displaying Values in an Option Group; 13.4 Using Conditional Formatting; 13.5 Working with the Tab Control; 13.6 Creating Multiple-Page Forms; 13.7 Introducing ActiveX ControlsThe Calendar Control; 13.8 Working with PivotChart Forms; Chapter 14: Using Reports; 14.1 Uses of Reports; 14.2 A Tour of Reports; 14.3 Printing Reports; Chapter 15: Constructing a Report; 15.1 Starting from ScratchA Simple Report; 15.2 Using the Report Command; 15.3 Using the Report Wizard; 15.4 Building a Report in Layout View; Chapter 16: Advanced Report Design; 16.1 Building a Query for a Complex Report; 16.2 Creating the Basic Facility Occupancy By Date Report; 16.3 Defining the Grouping and Sorting Criteria; 16.4 Setting Section and Report Properties; 16.5 Using Calculated Values; 16.6 Creating and Embedding a Subreport; 16.7 Adding a PivotChart to a Report; Part 4: Automating an Access Application; Chapter 17: Understanding Event Processing; 17.1 Access as a Windows Event-Driven Application; 17.2 Summary of Form and Report Events; 17.3 Understanding Event Sequence and Form Editing; Chapter 18: Automating Your Application with Macros; 18.1 Uses of Macros; 18.2 The Macro Design FacilityAn Overview; 18.3 Defining Multiple Actions; 18.4 Grouping Macros; 18.5 Understanding Conditional Expressions; 18.6 Using Embedded Macros; 18.7 Using Temporary Variables; 18.8 Trapping Errors in Macros; 18.9 Understanding Macro Actions That Are Not Trusted; 18.10 Making Your Application Come Alive with Macros; 18.11 Converting Your Macros to Visual Basic; Chapter 19: Understanding Visual Basic Fundamentals; 19.1 The Visual Basic Development Environment; 19.2 Variables and Constants; 19.3 Declaring Constants and Variables; 19.4 Collections, Objects, Properties, and Methods; 19.5 Functions and Subroutines; 19.6 Understanding Class Modules; 19.7 Controlling the Flow of Statements; 19.8 Running Macro Actions and Menu Commands; 19.9 Trapping Errors; 19.10 Some Complex Visual Basic Examples; Chapter 20: Automating Your Application with Visual Basic; 20.1 Why Arent We Using Macros?; 20.2 Assisting Data Entry; 20.3 Validating Complex Data; 20.4 Controlling Tabbing on a Multiple-Page Form; 20.5 Automating Data Selection; 20.6 Linking to Related Data in Another Form or Report; 20.7 Automating Complex Tasks; 20.8 Automating Reports; Part 5: Linking Access and the Web; Chapter 21: Publishing Data on the Web; 21.1 Working with the Web; 21.2 Understanding Static Web Pages; 21.3 Viewing Static HTML Pages; 21.4 Creating a Static HTML Document; 21.5 Creating Dynamic Web Pages; 21.6 Sharing Your Data with SharePoint; Chapter 22: Working with Windows SharePoint Services; 22.1 Working Within the Windows SharePoint Services User Interface; 22.2 Using Windows SharePoint Services from Access; 22.3 Migrating an Access Database to a Windows SharePoint Services Site; Chapter 23: Using XML; 23.1 Exploring XML; 23.2 Using XML in Microsoft Access; 23.3 Modifying Table Templates; 23.4 Customizing the Ribbon with XML; Part 6: After Completing Your Application; Chapter 24: The Finishing Touches; 24.1 Creating Custom Ribbons; 24.2 Fine-Tuning with Performance Analyzer; 24.3 Disabling Layout View; 24.4 Defining Switchboard Forms; 24.5 Controlling How Your Application Starts and Runs; 24.6 Performing a Final Visual Basic Compile; Chapter 25: Distributing Your Application; 25.1 Using Linked Tables in a Desktop Database; 25.2 Understanding Runtime Mode; 25.3 Creating an Execute-Only Database; 25.4 Creating an Application Shortcut; 25.5 Encrypting Your Database; 25.6 Packaging and Signing Your Database; 25.7 Understanding the Access 2007 Developer Extensions and Runtime; Installing Your Software; Installing the Microsoft Office System; Installing SQL Server 2005 Express Edition; Converting from a Previous Release of Access; Index to Troubleshooting Topics; About the Authors; Choose the Right Book for You; Published and Upcoming Titles;