|
Microsoft® Access® 2010 Bible |
1 |
|
|
About the Author |
5 |
|
|
About the Technical Editor |
7 |
|
|
Credits |
9 |
|
|
Contents at a Glance |
11 |
|
|
Contents |
13 |
|
|
Acknowledgments |
35 |
|
|
Introduction |
37 |
|
|
Part I: Access Building Blocks |
49 |
|
|
Chapter 1: An Introduction to Database Development |
51 |
|
|
The Database Terminology of Access |
52 |
|
|
Relational Databases |
56 |
|
|
Access Database Objects |
58 |
|
|
A Five-Step Design Method |
60 |
|
|
Summary |
71 |
|
|
Chapter 2: Creating Access Tables |
73 |
|
|
Getting Started with Access |
73 |
|
|
Creating a Database |
76 |
|
|
The Access 2010 Environment |
81 |
|
|
Creating a New Table |
87 |
|
|
Creating tblCustomers |
103 |
|
|
Changing a Table Design |
104 |
|
|
Understanding tblCustomers Field Properties |
121 |
|
|
Setting the Primary Key |
124 |
|
|
Indexing Access Tables |
126 |
|
|
Printing a Table Design |
132 |
|
|
Saving the Completed Table |
133 |
|
|
Manipulating Tables |
133 |
|
|
Adding Records to a Database Table |
136 |
|
|
Understanding Attachment Fields |
136 |
|
|
Summary |
138 |
|
|
Chapter 3: Designing Bulletproof Databases |
139 |
|
|
Building Bulletproof Databases |
140 |
|
|
Data Normalization |
142 |
|
|
Table Relationships |
152 |
|
|
Integrity Rules |
160 |
|
|
Keys |
162 |
|
|
Summary |
175 |
|
|
Chapter 4: Selecting Data with Queries |
177 |
|
|
Introducing Queries |
178 |
|
|
Creating a Query |
183 |
|
|
Adding Fields |
186 |
|
|
Displaying the Recordset |
188 |
|
|
Working with Fields |
189 |
|
|
Changing the Sort Order |
195 |
|
|
Displaying Only Selected Records |
197 |
|
|
Printing a Query’s Recordset |
201 |
|
|
Saving a Query |
201 |
|
|
Creating Multi-Table Queries |
201 |
|
|
Working with the Table Pane |
206 |
|
|
Creating and Working with Query Joins |
208 |
|
|
Understanding Table Join Types |
213 |
|
|
Summary |
218 |
|
|
Chapter 5: Using Operators and Expressions in Access |
219 |
|
|
Introducing Operators |
219 |
|
|
Using Operators and Expressions in Queries |
235 |
|
|
Entering Single-Value Field Criteria |
241 |
|
|
Using Multiple Criteria in a Query |
249 |
|
|
Entering Criteria in Multiple Fields |
256 |
|
|
Summary |
261 |
|
|
Chapter 6: Working with Datasheet View |
263 |
|
|
Understanding Datasheets |
263 |
|
|
Looking at the Datasheet Window |
265 |
|
|
Opening a Datasheet |
269 |
|
|
Entering New Data |
269 |
|
|
Navigating Records in a Datasheet |
275 |
|
|
Changing Values in a Datasheet |
278 |
|
|
Using the Undo Feature |
280 |
|
|
Copying and Pasting Values |
280 |
|
|
Replacing Values |
281 |
|
|
Adding New Records |
282 |
|
|
Deleting Records |
282 |
|
|
Displaying Records |
283 |
|
|
Sorting and Filtering Records in a Datasheet |
290 |
|
|
Focusing on Special Features of Datasheets |
295 |
|
|
Printing Records |
296 |
|
|
Summary |
297 |
|
|
Chapter 7: Creating Basic Access Forms |
299 |
|
|
Formulating Forms |
300 |
|
|
Working with Controls |
312 |
|
|
Introducing Properties |
330 |
|
|
Summary |
335 |
|
|
Chapter 8: Working with Data on Access Forms |
337 |
|
|
Using Form View |
337 |
|
|
Changing Values in a Form |
343 |
|
|
Printing a Form |
348 |
|
|
Working with Form Properties |
349 |
|
|
Adding a Form Header or Footer |
359 |
|
|
Changing the Layout |
360 |
|
|
Creating a Calculated Control |
364 |
|
|
Converting a Form to a Report |
365 |
|
|
Summary |
365 |
|
|
Chapter 9: Presenting Data with Access Reports |
367 |
|
|
Introducing Reports |
367 |
|
|
Creating a Report, from Beginning to End |
371 |
|
|
Banded Report Design Concepts |
388 |
|
|
Creating a Report from Scratch |
393 |
|
|
Improving the Report’s Appearance |
415 |
|
|
Summary |
421 |
|
|
Part II: Programming Microsoft Access |
423 |
|
|
Chapter 10: VBA Programming Fundamentals |
425 |
|
|
Introducing Visual Basic for Applications |
426 |
|
|
Understanding VBA Terminology |
427 |
|
|
Starting with VBA Code Basics |
428 |
|
|
Migrating from Macros to VBA |
429 |
|
|
Creating VBA Programs |
434 |
|
|
Understanding VBA Branching Constructs |
449 |
|
|
Working with Objects and Collections |
454 |
|
|
Looking at Access Options for Developers |
458 |
|
|
Summary |
464 |
|
|
Chapter 11: Mastering VBA Data Types and Procedures |
465 |
|
|
Introducing the Access VBA Editor |
466 |
|
|
Using Variables |
469 |
|
|
Working with Data Types |
475 |
|
|
Understanding Subs and Functions |
483 |
|
|
Creating Functions |
489 |
|
|
Simplifying Code with Named Arguments |
495 |
|
|
Summary |
497 |
|
|
Chapter 12: The Access Event Model |
499 |
|
|
Programming Events |
500 |
|
|
Identifying Common Events |
502 |
|
|
Paying Attention to Event Sequence |
513 |
|
|
Summary |
519 |
|
|
Chapter 13: Accessing Data with VBA Code |
521 |
|
|
Understanding SQL |
521 |
|
|
Working with Data |
533 |
|
|
Understanding ADO Objects |
536 |
|
|
Understanding DAO Objects |
547 |
|
|
Writing VBA Code to Update a Table |
556 |
|
|
Summary |
569 |
|
|
Chapter 14: Debugging Your Access Applications |
571 |
|
|
Testing Your Applications |
572 |
|
|
Using the Module Options |
573 |
|
|
Organizing VBA Code |
578 |
|
|
Compiling VBA Code |
580 |
|
|
Traditional Debugging Techniques |
582 |
|
|
Using the Access Debugging Tools |
586 |
|
|
Summary |
597 |
|
|
Chapter 15: Using Access Data Macros |
599 |
|
|
Introducing Data Macros |
599 |
|
|
Looking at How Data Macros Are Created |
601 |
|
|
Discovering Table Events |
610 |
|
|
Building Macros |
614 |
|
|
Recognizing the Limitations of Data Macros |
623 |
|
|
Summary |
624 |
|
|
Part III: More-Advanced Access Techniques |
625 |
|
|
Chapter 16: Working with External Data |
627 |
|
|
Looking at How Access Works with External Data |
628 |
|
|
Linking External Data |
632 |
|
|
Working with Linked Tables |
645 |
|
|
Using Code to Link Tables in Access |
648 |
|
|
Summary |
656 |
|
|
Chapter 17: Importing and Exporting Data |
657 |
|
|
Looking at Your Options for Importing and Exporting |
657 |
|
|
Importing External Data |
659 |
|
|
Exporting to External Formats |
682 |
|
|
Summary |
684 |
|
|
Chapter 18: Advanced Access Query Techniques |
685 |
|
|
Using Calculated Fields |
686 |
|
|
Calculated Fields and the Expression Builder |
688 |
|
|
Counting Records in a Table or Query |
690 |
|
|
Finding the Top (n) Records in a Query |
692 |
|
|
How Queries Save Field Selections |
694 |
|
|
Query Design Options |
697 |
|
|
Setting Query Properties |
699 |
|
|
Creating Queries That Calculate Totals |
701 |
|
|
Creating Crosstab Queries |
714 |
|
|
Understanding Action Queries |
719 |
|
|
Summary |
725 |
|
|
Chapter 19: Advanced Access Form Techniques |
727 |
|
|
Setting Control Properties |
727 |
|
|
Working with Subforms |
734 |
|
|
Designing Forms |
736 |
|
|
Tackling Advanced Forms Techniques |
742 |
|
|
Using the Tab Control |
748 |
|
|
Using Dialog Boxes to Collect Information |
751 |
|
|
Summary |
755 |
|
|
Chapter 20: Advanced Access Report Techniques |
757 |
|
|
Grouping and Sorting Data |
758 |
|
|
Formatting Data |
768 |
|
|
Adding Data |
785 |
|
|
Trying More Techniques |
787 |
|
|
Summary |
798 |
|
|
Chapter 21: Building Multiuser Applications |
799 |
|
|
Working on a Network |
800 |
|
|
Considering the Options for Opening a Database |
804 |
|
|
Splitting a Database for Network Access |
807 |
|
|
Finding the Key to Locking Issues |
812 |
|
|
Reducing Multiuser Errors with Unbound Forms |
828 |
|
|
Summary |
836 |
|
|
Chapter 22: Integrating Access with Other Applications |
837 |
|
|
Using Automation in Access |
838 |
|
|
Looking at an Automation Example Using Word |
848 |
|
|
Collecting Data with Microsoft Outlook |
859 |
|
|
Summary |
866 |
|
|
Chapter 23: Handling Errors and Exceptions |
867 |
|
|
Dealing with Errors |
868 |
|
|
Identifying Which Errors Can Be Detected |
872 |
|
|
Trapping Errors with VBA |
874 |
|
|
Summary |
886 |
|
|
Part IV: Professional Database Development |
887 |
|
|
Chapter 24: Optimizing Access Applications |
889 |
|
|
Understanding Module Load on Demand |
889 |
|
|
Using the .accdb Database File Format |
892 |
|
|
Distributing .accde Files |
894 |
|
|
Understanding the Compiled State |
896 |
|
|
Improving Absolute Speed |
902 |
|
|
Improving Perceived Speed |
915 |
|
|
Working with Large Access Databases |
920 |
|
|
Summary |
927 |
|
|
Chapter 25: Advanced Data Access with VBA |
929 |
|
|
Adding an Unbound Combo Box to a Form to Find Data |
929 |
|
|
Filtering a Form |
936 |
|
|
Summary |
943 |
|
|
Chapter 26: Bulletproofing Access Applications |
945 |
|
|
Introducing Bulletproofing |
946 |
|
|
Looking at the Characteristics of Bulletproofed Applications |
946 |
|
|
Identifying the Principles of Bulletproofing |
947 |
|
|
Summary |
986 |
|
|
Chapter 27: Using the Windows Application Programming Interface |
987 |
|
|
What the Windows API Is |
987 |
|
|
Reasons to Use the Windows API |
988 |
|
|
DLL Documentation |
990 |
|
|
How to Use the Windows API |
994 |
|
|
API Examples |
998 |
|
|
Summary |
1015 |
|
|
Chapter 28: Object-Oriented Programming with VBA |
1017 |
|
|
Introducing Object-Oriented Programming |
1018 |
|
|
Recognizing the Benefits of Object-Oriented Programming |
1029 |
|
|
Using Property Procedures |
1034 |
|
|
Modifying the Product Class |
1039 |
|
|
Learning about Class Events |
1045 |
|
|
Adding Events to Class Modules |
1047 |
|
|
Summary |
1056 |
|
|
Chapter 29: Customizing Access Ribbons |
1057 |
|
|
Why Replace Toolbars and Menus? |
1057 |
|
|
Working with the Access Ribbon |
1063 |
|
|
Working with the Quick Access Toolbar |
1066 |
|
|
Editing the Default Access Ribbon |
1068 |
|
|
Developing Custom Access Ribbons |
1071 |
|
|
The Ribbon Hierarchy |
1073 |
|
|
Getting Started with Access Ribbons |
1074 |
|
|
The Basic Ribbon XML |
1081 |
|
|
Adding Ribbon Controls |
1083 |
|
|
Using Visual Web Developer |
1090 |
|
|
Managing Ribbons |
1093 |
|
|
Completely Removing the Access Ribbon |
1094 |
|
|
Summary |
1095 |
|
|
Chapter 30: Using Access Macros |
1097 |
|
|
An Introduction to Macros |
1097 |
|
|
Multi-Action Macros |
1102 |
|
|
Submacros |
1105 |
|
|
Conditions |
1109 |
|
|
Temporary Variables |
1113 |
|
|
Error Handling and Macro Debugging |
1117 |
|
|
Embedded Macros |
1123 |
|
|
Macros versus VBA Statements |
1125 |
|
|
Summary |
1129 |
|
|
Chapter 31: Distributing Access Applications |
1131 |
|
|
Defining the Current Database Options |
1132 |
|
|
Testing the Application before Distribution |
1140 |
|
|
Polishing Your Application |
1142 |
|
|
Bulletproofing an Application |
1146 |
|
|
Summary |
1148 |
|
|
Part V: Access and Windows SharePoint Services |
1149 |
|
|
Chapter 32: Understanding Windows SharePoint Services |
1151 |
|
|
Introducing SharePoint |
1152 |
|
|
Reviewing Various Types of SharePoint Sites |
1154 |
|
|
Working with SharePoint Lists |
1155 |
|
|
Looking at a SharePoint Web Site |
1156 |
|
|
Summary |
1163 |
|
|
Chapter 33: Integrating Access with SharePoint |
1165 |
|
|
Introducing SharePoint as a Data Source |
1166 |
|
|
Sharing Access Data with SharePoint |
1167 |
|
|
Using SharePoint Templates |
1179 |
|
|
Summary |
1182 |
|
|
Chapter 34: Understanding Access Services |
1183 |
|
|
Explaining Managed Applications |
1184 |
|
|
Looking at Web Publishing in Access |
1185 |
|
|
Understanding Access Services |
1188 |
|
|
Examining Access Web Application Limits |
1189 |
|
|
Summary |
1192 |
|
|
Chapter 35: Deploying Access Applications to SharePoint |
1193 |
|
|
Looking at SharePoint Deployment Options |
1194 |
|
|
Dealing with Compatibility Checker Problems |
1222 |
|
|
Summary |
1227 |
|
|
Part VI: Access as an Enterprise Platform |
1229 |
|
|
Chapter 36: Client/Server Concepts |
1231 |
|
|
Looking at the Parts of Client/Server Architecture |
1232 |
|
|
Making Sense of Multi-Tier Architecture |
1240 |
|
|
Putting It All Together: Access, Client-Server, and Multiple Tiers |
1242 |
|
|
Summary |
1245 |
|
|
Chapter 37: SQL Server as an Access Companion |
1247 |
|
|
Connecting to SQL Server |
1249 |
|
|
Working with SQL Server Objects |
1262 |
|
|
Summary |
1269 |
|
|
Chapter 38: Upsizing Access Databases to SQL Server |
1271 |
|
|
Introducing SQL Server Express |
1272 |
|
|
Upsizing Access and the Upsizing Wizard |
1273 |
|
|
Summary |
1288 |
|
|
Part VII: Appendixes |
1291 |
|
|
Appendix A: Access 2010 Specifications |
1293 |
|
|
Microsoft Access Database Specifications |
1295 |
|
|
Microsoft SQL Server Express Specifications |
1297 |
|
|
Appendix B: What’s New in Access 2010 |
1301 |
|
|
The User Interface |
1301 |
|
|
Publish Access to the Web |
1306 |
|
|
Tables |
1306 |
|
|
Datasheet View |
1307 |
|
|
Forms |
1309 |
|
|
Reports |
1311 |
|
|
Macros |
1311 |
|
|
Security |
1313 |
|
|
SharePoint |
1313 |
|
|
Summary |
1314 |
|
|
Appendix C: What’s on the CD-ROM |
1315 |
|
|
System Requirements |
1315 |
|
|
Using the CD |
1316 |
|
|
What’s on the CD |
1316 |
|
|
Troubleshooting |
1317 |
|
|
Index |
1319 |
|