book

Pro Access 2010 Development

Name: Pro Access 2010 Development
Author: Mark Collins
ISBN: 9781430235781

by Mark Collins

June 2011

Intermediate to advanced

604 pages

12h 30m

English

Apress

Read now

Unlock full access

Copyright
About the Author
About the Technical Reviewer
Acknowledgments
Preface
I. Introduction
1. Introduction
1.1. What Is Access?1.1.1. Defining the User Interface1.1.2. Choosing the Application Architecture1.2. Using This Book1.3. The Sample Application1.4. Getting Started
II. Defining the Database Schema
2. Defining and Relating Tables
2.1. Considering Design Practices2.1.1. Using Primary Keys2.1.2. Normalizing a Database2.1.3. Database Constraints2.2. Designing the Tables2.2.1. Creating the Customer Table2.2.1.1. Using Quick Start Fields2.2.1.2. Adding Search Fields2.2.1.3. Using an Input Mask2.2.1.4. Adding Customers2.2.2. Creating the Category Table2.2.2.1. Making a Field Required2.2.2.2. Adding a Unique Constraint2.2.2.3. Defining Categories2.2.3. Creating the Media Table2.2.3.1. Adding Field Validation2.2.3.2. Entering a Field Caption2.2.3.3. Using Default Values2.2.3.4. Defining Media Types2.2.4. Creating the Item Table2.2.4.1. Creating Lookup Columns2.2.4.2. Adding the MediaID Column2.2.4.3. Viewing the Relationships2.2.4.4. Adding the Remaining Columns2.2.4.5. Adding a Calculated Field2.2.4.6. Adding Items to the Database2.2.5. Creating the ItemInventory Table2.2.5.1. Adding a Lookup to the Item Table2.2.5.2. Adding a Lookup with Fixed Options2.2.5.3. Specifying the Item's Condition2.2.5.4. Adding InventoryItem Records2.2.6. Creating the Loan Table2.2.6.1. Referencing the Customer Table2.2.6.2. Referencing the InventoryItem table2.2.6.3. Adding the Remaining Fields2.2.7. Creating the Request Table2.2.7.1. Defining the Status Field2.2.7.2. Adding the InventoryItemID Field2.3. Viewing the Relationships2.4. Summary
3. Using Data Macros
3.1. Understanding Data Macros3.1.1. Identifying Data Macro Limitations3.1.2. Creating Your First Data Macro3.1.3. Testing Your Macro3.2. Exploring the Macro Editor3.2.1. Understanding Data Blocks3.2.2. Using Aliases3.2.2.1. Using Default Aliases3.2.2.2. Using the EditRecord Action3.2.2.3. When to Use an Alias3.2.3. Using Data Actions3.2.4. Navigating the Macro Editor3.3. Implementing the Loan Before Change Event3.3.1. Making Sure the Item is Available3.3.2. Calculating the Due Date3.3.3. Calculating the Late Fee3.3.4. Validating Renewals3.4. Adding the Current Loan Reference3.4.1. Adding the Lookup Field3.4.2. Modifying the Loan After Insert Event3.4.3. Modifying the Loan After Update Event3.5. Handling Requested Items3.5.1. Implementing the Before Change Event3.5.2. Creating a Named Data Macro3.5.3. Calling a Named Macro3.6. Computing Overdue Fees3.6.1. Creating a Named Data Macro to Compute Overdue Fees3.6.2. Creating a User-Executed Macro3.7. Debugging Data Macros3.7.1. Using the LogEvent Action3.7.2. Viewing the Application Log3.8. Testing the Application3.9. Summary

4. Designing Queries
4.1. Creating Select Queries4.1.1. Creating a Simple Query4.1.2. Adding Tables4.1.3. Using Joins4.1.3.1. Understanding Multiplicity4.1.3.2. Using the Join Properties4.1.3.3. Adding Columns4.1.4. Making Additional Changes4.1.4.1. Adding Calculated Columns4.1.4.2. Sorting the Results4.1.4.3. Using the SQL View4.1.5. Using Queries as Views4.2. Creating Action Queries4.2.1. Creating the AddInventoryItem Query4.2.1.1. Changing the Query Type4.2.1.2. Adding a Parameter4.2.1.3. Running the Query4.2.2. Enhancing the Request Feature4.2.2.1. Modifying the Request Table4.2.2.2. Creating the CancelOldRequests Query4.2.2.3. Adding a Data Macro4.2.2.4. Running the Query4.3. Creating a Crosstab Query4.3.1. Building the AllLoans Query4.3.2. Designing the LoanSummary Query4.4. Summary
5. Creating PivotTables
5.1. Slicing, Dicing, and Drilling5.2. Creating a PivotTable View5.2.1. Understanding the PivotTable Layout5.2.2. Using the PivotTable Fields5.2.3. Defining the Column Field5.2.4. Using Date Fields5.2.5. Adding a Filter Field5.2.6. Refreshing the PivotTable5.3. Adding Other Fields5.3.1. Including Multiple Values5.3.2. Using Calculated Columns5.3.3. Adding Additional Column Fields5.3.4. Creating a Field Hierarchy5.4. Using the PivotChart View5.4.1. Configuring a PivotChart View5.4.2. Changing the Chart Type5.5. Exporting a PivotTable View to Excel5.6. Summary
III. Creating Forms and Reports
6. Standard Forms
6.1. Creating a Single Form6.1.1. Using the Form Wizard6.1.2. Using the Available Views6.1.3. Sorting the Records6.2. Using Split Forms6.2.1. Generating the Media Form6.2.2. Modifying the Form Fields6.3. Using Continuous Forms6.3.1. Generating the InventoryItem Form6.3.2. Designing the InventoryItem Form6.3.3. Modifying the Form Fields6.4. Understanding the Layout Options6.4.1. Using the Tabular Layout Option6.4.2. Using the Datasheet Layout Option6.4.3. Using the Columnar Layout Option6.4.4. Using the Justified Layout Option6.4.5. Using a Layout6.5. Creating the Item Form6.5.1. Using the Standard Form Template6.5.2. Arranging the Form's Layout6.5.3. Adding a Subform6.6. Summary
7. Creating a CheckOut Form
7.1. Implementing a Customer Search Feature7.1.1. Populating the Customer Table7.1.2. Creating the CustomerSearch Dialog Box7.1.2.1. Adding the Detail Fields7.1.2.2. Adding the Search Fields7.1.2.3. Implementing the Search Logic7.1.3. Building a CustomerDisplay Form7.1.4. Creating the CheckOut Form7.1.4.1. Adding the Customer Controls7.1.4.2. Connecting the Controls7.1.4.3. Invoking the Search Dialog7.2. Providing a CheckOut Feature7.2.1. Building an InventoryItemLookup Form7.2.1.1. Creating an Ad-Hoc Query7.2.1.2. Adding the Fields to the Form7.2.1.3. Modifying the Form Design7.2.1.4. Finalizing the Form Details7.2.2. Linking the InventoryItemLookup Subform7.2.3. Designing the CheckOut Details7.2.3.1. Altering the Loan Table7.2.3.2. Creating a LoanDetail Query7.2.3.3. Designing the Detail Section7.2.3.4. Adding the Header and Footer Controls7.2.4. Implementing the CheckOut Logic7.2.4.1. Enabling the CheckOut Button7.2.4.2. Implementing the CheckOut Button7.2.4.3. Implementing the Complete Button7.3. Testing the Application7.4. Summary
8. Creating a Customer Admin Form
8.1. Building the Customer Profile Tab8.1.1. Creating a CustomerUpdate Form8.1.2. Configuring the Form Controls8.1.3. Creating the CustomerAdmin Form8.1.3.1. Designing the Form Header8.1.3.2. Using a Tab Control8.1.3.3. Designing the Profile Page8.1.3.4. Implementing the Connecting Code8.1.4. Testing the Profile Page8.2. Building the Items on Loan Tab8.2.1. Enhancing the LoanDetails Query8.2.2. Designing a CustomerLoan Form8.2.2.1. Creating the Initial CustomerLoans Form8.2.2.2. Arranging the Controls8.2.2.3. Modifying the Form Layout8.2.3. Configuring the Datasheet View8.2.4. Designing the Items on Loan Page8.2.5. Connecting the Pieces8.2.6. Testing the Page8.3. Building the Loan History Tab8.4. Summary
9. Enhancing Product Administration
9.1. Using Data-Bound Images9.1.1. Image Support in Access9.1.1.1. Storing Bitmaps in Access9.1.1.2. Storing OLE Objects9.1.1.3. Storing Images in the File System9.1.2. Adding a Picture to the Item Table9.1.3. Modifying the Item Form9.1.4. Displaying an Image9.1.4.1. Adding the Code to Display an Image9.1.4.2. Loading the Image Files9.2. Implementing Item Search9.2.1. Importing Item Data9.2.2. Designing the Search Form9.2.2.1. Designing the Basic Search Page9.2.2.2. Designing the Detail Section9.2.2.3. Adding the VBA Code9.2.3. Testing the Search Function9.2.4. Using Conditional Formatting9.2.5. Invoking the ItemSearch Form9.3. Enhancing the Item Form9.3.1. Adding an Inventory Item9.3.2. Adding a Web Browser9.3.3. Using Page Breaks9.3.4. Modifying the CustomerLoan Form9.4. Summary
10. Enhancing the User Experience
10.1. Form Navigation10.1.1. Creating the Menu Form10.1.2. Auto-Loading the Menu Form10.2. Ribbon Navigation10.2.1. Implementing a Sample Ribbon Tab10.2.2. Displaying the System Objects10.3. Building a Custom Ribbon10.3.1. Designing the Tables10.3.2. Creating the Menu Forms10.3.3. Populating the Menu Tables10.3.4. Using MSO Images10.3.5. Implementing the Custom Ribbon10.3.5.1. Designing the mnuCommands Query10.3.5.2. Generating the Ribbon XML10.3.5.3. Implementing the Callback Methods10.3.5.4. Using the Autoexec Macro10.3.5.5. Selecting the Library Ribbon10.4. Locking Down the Database10.4.1. Removing Navigation10.4.2. Compiling the Database10.5. Summary
11. Branding with Themes and Styles
11.1. Using Office Themes11.1.1. Understanding Office Themes11.1.1.1. Creating a Custom Color Theme11.1.1.2. Defining the Font Scheme11.1.1.3. Creating an Office Theme11.1.2. Applying an Office Theme11.1.3. Making Other Visual Adjustments11.2. Using Graphics11.2.1. Adding a Banner Graphic11.2.2. Using a Background Image11.3. Summary
12. Reports
12.1. Exploring Access Reports12.1.1. Understanding Report Sections12.1.1.1. Using Page Sections12.1.1.2. Using Group Sections12.1.2. Options for Creating Reports12.2. Creating a Simple Report12.2.1. Modifying the Page Setup12.2.2. Modifying the Report Layout12.3. Creating the AllLoans Report12.3.1. Using the Report Wizard12.3.1.1. Selecting the Data Source12.3.1.2. Grouping Records12.3.1.3. Sorting and Summarizing the Details12.3.1.4. Selecting the Format12.3.2. Exploring the Design View12.3.3. Using the Layout View12.3.4. Configuring Grouping and Sorting12.3.5. Using Unbound Controls12.3.5.1. Formatting Page Numbers12.3.5.2. Displaying the Current Date and Time12.3.5.3. Using Totals and Subtotals12.3.5.4. Fixing the Percentage Formula12.4. Creating a CheckOut Report12.4.1. Adding the Data-Bound Controls12.4.2. Formatting the Report Header12.4.3. Adding a Subreport12.4.4. Modifying the CheckOut Form12.5. Generating InventoryItem Labels12.5.1. Creating an InventoryItemDetail Query12.5.2. Using the Label Wizard12.5.2.1. Selecting the Label Stock Template12.5.2.2. Formatting the Labels12.5.3. Modifying the Color Scheme12.6. Auto-Generating a DailyLoans Report12.6.1. Creating the DailyLoans Report12.6.2. Creating a DailyReport Macro12.6.3. Creating a Scheduled Task12.6.4. Modifying the Daily Report Macro12.7. Summary
IV. Multiuser Considerations
13. Upsizing
13.1. Understanding Upsizing13.1.1. Using Linked Tables13.1.2. Project Overview13.2. Upsizing with Access13.3. Upsizing with SQL Server13.3.1. Using the Upsizing Wizard13.3.2. Viewing the Upsizing Results13.3.3. Adjusting the OpenRecordset Code13.4. Upsizing with SQL Azure13.4.1. Installing SQL Server Migration Assistant13.4.2. Configuring a SQL Azure Database13.4.2.1. Setting Up a SQL Azure Database Server13.4.2.2. Creating a User Account13.4.3. Migrating the Data to SQL Azure13.4.4. Viewing the Migration Results13.4.5. Linking the Views13.5. Data Macros with Linked Tables13.5.1. Data Macros in a Split Access Database13.5.2. Data Macros in Linked SQL Server Tables13.6. Summary
14. Distributing the Application
14.1. Using the Access Runtime14.1.1. Simulating the Access Runtime14.1.2. Understanding the Filename Extensions14.1.3. Downloading the Access Runtime14.2. Modifying the Application14.2.1. Supporting Images in a Distributed Environment14.2.2. Modifying the Application14.2.3. Relinking the Linked Tables14.2.4. Creating an Executable File14.3. Creating an Installation Package14.3.1. Installing the Package Solution Wizard14.3.2. Using the Package Solution Wizard14.3.3. Deploying the Installation File14.4. Summary
15. Publishing to the Web
15.1. Preparing to Publish15.1.1. Updating the Data Macros15.1.2. Using the Compatibility Checker15.2. Publishing the Access Database15.2.1. Publishing to Access Services15.2.2. Exploring the SharePoint Site15.2.3. Exploring the Access Database15.2.4. Restoring the VBA Code15.2.5. Fixing the Default Value15.2.6. Using Web Databases15.3. Creating Web Forms15.3.1. Creating a Category Web Form15.3.2. Creating a Media Web Form15.3.3. Creating a Customer List Web Form15.3.4. Creating a Navigation Form15.3.5. Setting the Default Web Form15.4. Adding an Item Web Form15.4.1. Creating the Initial Form15.4.2. Creating the InventoryItem Subform15.4.3. Adding the Picture and Web Browser15.4.3.1. Storing Images in SharePoint15.4.3.2. Displaying the Item Picture15.4.3.3. Adding the Web Browser Control15.4.4. Adding an InventoryItem Record15.4.4.1. Creating the Data Macro15.4.4.2. Adding a Command Button15.4.5. Providing a Search Feature15.4.6. Modifying the Navigation Form15.5. Summary
V. Advanced Topics
16. Integrating Outlook
16.1. Sending E-mails16.1.1. Creating the OverdueItems Query16.1.2. Creating a Data Macro16.1.3. Adding a UI Macro16.1.4. Sending the E-mails16.2. Sending E-mails with VBA16.2.1. Creating the OverdueItemDetails Query16.2.2. Implementing the VBA Code16.2.3. Sending the E-mails16.3. Using Data Collection16.3.1. Understanding Data Collection16.3.2. Defining a Data Collection E-mail16.3.2.1. Creating the Results Table16.3.2.2. Installing the Data Collection Add-In16.3.2.3. Setting up Data Collection16.3.3. Processing the Replies16.3.4. Resending the E-mail16.4. Summary
17. Using External Data
17.1. Linking and Importing17.2. Linking a SharePoint List17.2.1. Creating a Team SharePoint Site17.2.2. Creating a Linked Table17.2.3. Using the Linked Tables17.3. Linking an Outlook Folder17.3.1. Modifying the Data File Name17.3.2. Creating the Linked Table17.3.3. Using the Linked Table17.4. Importing an XML File17.4.1. Understanding the XML Import Process17.4.2. Importing the Exchange Rates17.5. Summary
18. Miscellaneous Features
18.1. Using Timers18.1.1. Adding a Digital Clock18.1.2. Creating Simple Animation18.2. Calling the Windows API18.3. Using TempVars18.3.1. Creating the UserName TempVar18.3.2. Storing the CheckOut User18.4. Displaying HTML Content18.4.1. Modifying the Item Form18.4.2. Passing HTML Content to a Web Browser Control18.5. Adding a Chart18.6. Summary
19. Security
19.1. Using Trusted Documents19.1.1. Understanding Disabled Content19.1.2. Trusting a Document19.1.2.1. Using Trusted Locations19.1.2.2. Configuring Trusted Documents19.2. Signing a Database19.2.1. Creating a Certificate19.2.2. Package an Access Database19.2.3. Installing a Signed Database19.3. Configuring Sandbox Mode19.4. Encrypting an Access Database19.5. Summary
A. Northwind Web Database
A.1. Installing the Northwind Web TemplateA.1.1. Downloading the TemplateA.1.2. Publishing the DatabaseA.2. Understanding the Sample DatabaseA.2.1. Setting Up Your Employee RecordA.2.2. Exploring the Access Client Application

Overview

Pro Access 2010 Development is a fundamental resource for developing business applications that take advantage of the features of Access 2010 and the many sources of data available to your business. In this book, you'll learn how to build database applications, create Web-based databases, develop macros and VBA tools for Access applications, integrate Access with SharePoint and other business systems, and much more.

Using a practical, hands-on approach, this book will take you through all the facets of developing Access-based solutions, such as data modeling, complex form development, and UI customizations. You'll then deploy your solution to the web and integrate it with other external data sources. This book is full of handy tricks to help you get the most out of what Access has to offer, including its comprehensive set of features and tools for collecting, using, and acting on business data, whether your data is in Access or stored on another platform. You'll also see how to smoothly integrate your applications with SQL Server databases and other Office programs, such as Outlook.

What you'll learn

How to build business applications that integrate local data, web-generated data, and data available from SQL Server and other sources
How to develop VBA routines and macros to enhance the automation of your database
How to deploy databases to your users and "web-ify" your application for access from anywhere
How to secure access to your data and maintain its integrity
How to integrate Access with SharePoint and other business applications

Who this book is for

This book is for intermediate to advanced Access developers who want to get up to speed with the new features available in Access 2010. Experienced Access users who want to begin developing code-based applications and project solutions in Access will also find this book useful.

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 9781430235781Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills