book

Access® 2010 Bible

Name: Access® 2010 Bible
Author: Michael R. Groh
ISBN: 9780470475348

by Michael R. Groh

June 2010

Beginner

1389 pages

34h 9m

English

Wiley

Read now

Unlock full access

Copyright
About the Author
About the Technical Editor
Credits
Acknowledfments
Introduction
Is This Book for You?Yes — If you have no database experienceYes — If you've used other database managers like FileMakerYes — If you want to learn the basics of Visual Basic for Applications (VBA) programmingYes — If you are an Access 2007 developerPardon My Dust!Conventions Used in This BookIcons and AlertsHow This Book Is OrganizedPart I: Access Building BlocksPart II: Programming Microsoft AccessPart III: More-Advanced Access TechniquesPart IV: Professional Database DevelopmentPart V: Access and Windows SharePoint ServicesPart VI: Access as an Enterprise PlatformPart VII: AppendixesGuide to the ExamplesThe main Switchboard menuData tablesThe Products formThe Invoice form
I. Access Building Blocks
1. An Introduction to Database Development
1.1. The Database Terminology of Access1.1.1. Databases1.1.2. Tables1.1.3. Records and fields1.1.4. Values1.2. Relational Databases1.3. Access Database Objects1.3.1. Datasheets1.3.2. Queries1.3.3. Data-entry and display forms1.3.4. Reports1.3.5. Database objects1.4. A Five-Step Design Method1.4.1. Step 1: The overall design — from concept to reality1.4.2. Step 2: Report design1.4.3. Step 3: Data design1.4.3.1. Customer information1.4.3.2. Sales information1.4.3.3. Line-item information1.4.4. Step 4: Table design1.4.5. Step 5: Form design1.5. Summary
2. Creating Access Tables
2.1. Getting Started with Access2.1.1. The Templates section2.1.2. The Office Backstage View2.2. Creating a Database2.3. The Access 2010 Environment2.3.1. The Navigation Pane2.3.2. The ribbon2.3.3. Other relevant features of the Access environment2.4. Creating a New Table2.4.1. Designing tables2.4.2. Using the Design tab2.4.2.1. Primary Key2.4.2.2. Insert Rows2.4.2.3. Delete Rows2.4.2.4. Property Sheet2.4.2.5. Indexes2.4.3. Working with fields2.4.3.1. Naming a field2.4.3.2. Specifying a data type2.4.3.3. Specifying data validation rules2.4.3.4. Assigning field data types2.4.3.4.1. Text data type2.4.3.4.2. Memo data type2.4.3.4.3. Number data type2.4.3.4.4. Date/Time data type2.4.3.4.5. Currency2.4.3.4.6. AutoNumber2.4.3.4.7. Yes/No2.4.3.4.8. OLE Object2.4.3.4.9. Attachment2.4.3.4.10. Hyperlink data type2.4.3.4.11. Lookup Wizard2.4.3.5. Entering a field description2.5. Creating tblCustomers2.5.1. Using AutoNumber fields2.5.2. Completing tblCustomers2.6. Changing a Table Design2.6.1. Inserting a new field2.6.2. Deleting a field2.6.3. Changing a field location2.6.4. Changing a field name2.6.5. Changing a field size2.6.6. Handling data conversion issues2.6.7. Assigning field properties2.6.7.1. Common properties2.6.7.2. Format2.6.7.2.1. Number and Currency field formats2.6.7.2.2. Custom numeric formats2.6.7.3. Date/Time field formats2.6.7.3.1. Built-in Date/Time formats2.6.7.3.2. Custom Date/Time formats2.6.7.4. Text and Memo field formats2.6.7.5. Yes/No field formats2.6.7.5.1. Hyperlink data-type format2.6.7.6. Input Mask2.6.7.6.1. The Input Mask Wizard2.6.7.7. Caption2.6.7.8. Validation Rule and Validation Text2.6.7.9. Required2.6.7.10. AllowZeroLength2.6.7.11. Indexed2.6.7.11.1. The AutoIndex option2.6.7.11.2. When to index2.7. Understanding tblCustomers Field Properties2.8. Setting the Primary Key2.8.1. Choosing a primary key2.8.2. Creating the primary key2.8.3. Creating composite primary keys2.9. Indexing Access Tables2.9.1. The importance of indexes2.9.2. Multiple-field indexes2.9.3. When to index tables2.10. Printing a Table Design2.11. Saving the Completed Table2.12. Manipulating Tables2.12.1. Renaming tables2.12.2. Deleting tables2.12.3. Copying tables in a database2.12.4. Copying a table to another database2.13. Adding Records to a Database Table2.14. Understanding Attachment Fields2.15. Summary
3. Designing Bulletproof Databases
3.1. Building Bulletproof Databases3.2. Data Normalization3.2.1. First normal form3.2.2. Second normal form3.2.2.1. Further optimization: Adding tables to the scheme3.2.2.2. Breaking the rules3.2.3. Third normal form3.2.4. Denormalization3.3. Table Relationships3.3.1. Connecting the data3.3.2. One-to-one3.3.3. One-to-many3.3.4. Many-to-many3.4. Integrity Rules3.4.1. No primary key can contain a null value3.4.2. All foreign key values must be matched by corresponding primary keys3.5. Keys3.5.1. Deciding on a primary key3.5.2. The benefits of a primary key3.5.3. Designating a primary key3.5.3.1. Single-field versus composite primary keys3.5.3.2. Natural versus surrogate primary keys3.5.3.3. Creating primary keys3.5.4. Creating relationships and enforcing referential integrity3.5.4.1. Specifying the Join Type between tables3.5.4.2. Enforcing referential integrity3.5.4.2.1. Choosing the Cascade Update Related Fields option3.5.4.2.2. Choosing the Cascade Delete Related Records option3.5.5. Viewing all relationships3.5.6. Deleting relationships3.5.7. Application-specific integrity rules3.6. Summary

4. Selecting Data with Queries
4.1. Introducing Queries4.1.1. What queries are4.1.2. Types of queries4.1.3. What queries can do4.1.4. What queries return4.2. Creating a Query4.3. Adding Fields4.3.1. Adding a single field4.3.2. Adding multiple fields4.4. Displaying the Recordset4.5. Working with Fields4.5.1. Selecting a field in the QBE grid4.5.2. Changing field order4.5.3. Resizing columns in the QBE grid4.5.4. Removing a field4.5.5. Inserting a field4.5.6. Providing an alias for the field name4.5.7. Showing a field4.6. Changing the Sort Order4.7. Displaying Only Selected Records4.7.1. Understanding selection criteria4.7.2. Entering simple string criteria4.7.3. Entering other simple criteria4.8. Printing a Query's Recordset4.9. Saving a Query4.10. Creating Multi-Table Queries4.10.1. Viewing table names4.10.2. Adding multiple fields4.10.3. Multi-table query limitations4.10.4. Overcoming query limitations4.10.4.1. Updating a unique index (primary key)4.10.4.2. Replacing existing data in a query with a one-to-many relationship4.10.4.3. Updating fields in queries4.11. Working with the Table Pane4.11.1. Looking at the join line4.11.2. Manipulating Field Lists4.11.3. Moving a table4.11.4. Removing a table4.11.5. Adding more tables4.12. Creating and Working with Query Joins4.12.1. Using ad hoc table joins4.12.2. Specifying the type of join4.12.3. Deleting joins4.13. Understanding Table Join Types4.13.1. Working with inner joins (equi-joins)4.13.2. Understanding outer joins4.13.3. Working with self-joins4.13.4. Creating a Cartesian product4.14. Summary
5. Using Operators and Expressions in Access
5.1. Introducing Operators5.1.1. Looking at the types of operators5.1.1.1. Mathematical operators5.1.1.1.1. The addition operator: +5.1.1.1.2. The subtraction operator: −5.1.1.1.3. The multiplication operator: *5.1.1.1.4. The division operator: /5.1.1.1.5. The integer division operator: \5.1.1.1.6. The exponentiation operator: ^5.1.1.1.7. The modulo division operator: Mod5.1.1.2. Comparison operators5.1.1.2.1. The equal operator: =5.1.1.2.2. The not-equal operator: <>5.1.1.2.3. The less-than operator: <5.1.1.2.4. The less-than-or-equal-to operator: <=5.1.1.2.5. The greater-than operator: >5.1.1.2.6. The greater-than-or-equal-to operator: >=5.1.1.3. String operators5.1.1.3.1. The concatenation operator: &5.1.1.3.2. The Like and Not Like operators5.1.1.4. Boolean (logical) operators5.1.1.4.1. The And operator5.1.1.4.2. The Or operator5.1.1.4.3. The Not operator5.1.1.5. Miscellaneous operators5.1.1.5.1. The Between...And operator5.1.1.5.2. The In operator5.1.1.5.3. The Is operator5.1.2. Operator precedence5.1.2.1. The mathematical precedence5.1.2.2. The comparison precedence5.1.2.3. The Boolean precedence5.2. Using Operators and Expressions in Queries5.2.1. Using query comparison operators5.2.2. Understanding complex criteria5.2.3. Using functions in select queries5.2.4. Referencing fields in select queries5.3. Entering Single-Value Field Criteria5.3.1. Entering character (Text or Memo) criteria5.3.2. The Like operator and wildcards5.3.3. Specifying non-matching values5.3.4. Entering numeric criteria5.3.5. Entering true or false criteria5.3.6. Entering OLE object criteria5.4. Using Multiple Criteria in a Query5.4.1. Understanding an Or operation5.4.2. Specifying multiple values with the Or operator5.4.3. Using the Or cell of the QBE pane5.4.4. Using a list of values with the In operator5.4.5. Using And to specify a range5.4.6. Using the Between...And operator5.4.7. Searching for Null data5.5. Entering Criteria in Multiple Fields5.5.1. Using And and Or across fields in a query5.5.2. Specifying Or criteria across fields of a query5.5.3. Using And and Or together in different fields5.5.4. A complex query on different lines5.6. Summary
6. Working with Datasheet View
6.1. Understanding Datasheets6.2. Looking at the Datasheet Window6.2.1. Moving within a datasheet6.2.2. Using the Navigation buttons6.2.3. Examining the Datasheet ribbon6.2.3.1. Views6.2.3.2. Clipboard6.2.3.3. Sort & Filter6.2.3.4. Records6.2.3.5. Find6.2.3.6. Window6.2.3.7. Text Formatting6.3. Opening a Datasheet6.4. Entering New Data6.4.1. Saving the record6.4.2. Understanding automatic data-type validation6.4.3. Knowing how properties affect data entry6.4.3.1. Standard text data entry6.4.3.2. Date/Time data entry6.4.3.3. Number/Currency data entry with data validation6.4.3.4. OLE object data entry6.4.3.5. Memo field data entry6.5. Navigating Records in a Datasheet6.5.1. Moving between records6.5.2. Finding a specific value6.6. Changing Values in a Datasheet6.6.1. Manually replacing an existing value6.6.2. Changing an existing value6.7. Using the Undo Feature6.8. Copying and Pasting Values6.9. Replacing Values6.10. Adding New Records6.11. Deleting Records6.12. Displaying Records6.12.1. Changing the field order6.12.2. Changing the field display width6.12.3. Changing the record display height6.12.4. Changing display fonts6.12.5. Displaying cell gridlines and alternate row colors6.12.6. Aligning data in columns6.12.7. Hiding and unhiding columns6.12.8. Freezing columns6.12.9. Saving the changed layout6.12.10. Saving a record6.13. Sorting and Filtering Records in a Datasheet6.13.1. Sorting your records with QuickSort6.13.2. Filtering a selection6.13.3. Filtering by form6.14. Focusing on Special Features of Datasheets6.15. Printing Records6.15.1. Printing the datasheet6.15.2. Using the Print Preview window6.16. Summary
7. Creating Basic Access Forms
7.1. Formulating Forms7.1.1. Creating a new form7.1.1.1. Using the Form command7.1.1.2. Using the Form Wizard7.1.2. Looking at special types of forms7.1.2.1. Navigation forms7.1.2.2. Multiple-items forms7.1.2.3. Split forms7.1.2.4. Datasheet forms7.1.3. Resizing the form area7.1.4. Saving your form7.2. Working with Controls7.2.1. Categorizing controls7.2.1.1. Bound controls7.2.1.2. Unbound controls7.2.1.3. Calculated controls7.2.2. Adding a control7.2.2.1. Using the Controls group7.2.2.2. Using the Field List7.2.3. Selecting and deselecting controls7.2.3.1. Selecting a single control7.2.3.2. Selecting multiple controls7.2.3.3. Deselecting controls7.2.4. Manipulating controls7.2.4.1. Resizing a control7.2.4.2. Sizing controls automatically7.2.4.3. Moving a control7.2.4.4. Aligning controls7.2.4.5. Modifying the appearance of a control7.2.4.6. Grouping controls7.2.4.7. Attaching (and reattaching) a label to a control7.2.4.8. Changing a control's type7.2.4.9. Copying a control7.2.4.10. Deleting a control7.3. Introducing Properties7.3.1. Displaying the Property Sheet7.3.2. Getting acquainted with the Property Sheet7.3.3. Changing a control's property setting7.3.4. Naming control labels and their captions7.4. Summary
8. Working with Data on Access Forms
8.1. Using Form View8.1.1. Looking at the Home ribbon tab8.1.1.1. The Views group8.1.1.2. The Clipboard group8.1.1.3. The Sort & Filter group8.1.1.4. The Records group8.1.1.5. The Find group8.1.1.6. The Window group8.1.1.7. The Text Formatting group8.1.2. Navigating among fields8.1.3. Moving among records in a form8.2. Changing Values in a Form8.2.1. Knowing which controls you can't edit8.2.2. Working with pictures and OLE objects8.2.3. Entering data in the Memo field8.2.4. Entering data in the Date field8.2.5. Using option groups8.2.6. Using combo boxes and list boxes8.2.7. Switching to Datasheet view8.2.8. Saving a record8.3. Printing a Form8.4. Working with Form Properties8.4.1. Changing the title bar text with the Caption property8.4.2. Creating a bound form8.4.3. Specifying how to view the form8.4.4. Removing the Record Selector8.4.5. Looking at other form properties8.5. Adding a Form Header or Footer8.6. Changing the Layout8.6.1. Changing a control's properties8.6.2. Setting the Tab Order8.6.3. Aligning controls8.6.4. Modifying the format of text in a control8.6.5. Using the Field List to add controls8.7. Creating a Calculated Control8.8. Converting a Form to a Report8.9. Summary
9. Presenting Data with Access Reports
9.1. Introducing Reports9.1.1. Identifying the different types of reports9.1.1.1. Tabular reports9.1.1.2. Columnar reports9.1.1.3. Mailing-label reports9.1.2. Distinguishing between reports and forms9.2. Creating a Report, from Beginning to End9.2.1. Defining the report layout9.2.2. Assembling the data9.2.3. Creating a report with the Report Wizard9.2.3.1. Creating a new report9.2.3.2. Selecting the grouping levels9.2.3.3. Defining the group data9.2.3.4. Selecting the sort order9.2.3.5. Selecting summary options9.2.3.6. Selecting the layout9.2.3.7. Opening the report design9.2.3.8. Adjusting the report's layout9.2.3.9. Choosing a theme9.2.3.10. Creating new theme color schemes9.2.3.11. Using the Print Preview window9.2.3.12. Publishing in alternate formats9.2.3.13. Viewing the Report Design window9.2.4. Printing or viewing the report9.2.4.1. Printing the report9.2.4.2. Viewing the report9.2.5. Saving the report9.3. Banded Report Design Concepts9.3.1. The Report Header section9.3.2. The Page Header section9.3.3. The Group Header section9.3.4. The Detail section9.3.5. The Group Footer section9.3.6. The Page Footer section9.3.7. The Report Footer section9.4. Creating a Report from Scratch9.4.1. Creating a new report and binding it to a query9.4.2. Defining the report page size and layout9.4.3. Placing controls on the report9.4.4. Resizing a section9.4.4.1. Modifying the appearance of text in a control9.4.5. Working with text boxes9.4.5.1. Adding and using text-box controls9.4.5.2. Entering an expression in a text control9.4.5.3. Sizing a text-box control or label control9.4.5.4. Deleting and cutting attached labels from text controls9.4.5.5. Pasting labels into a report section9.4.5.6. Moving label and text controls9.4.5.7. Modifying the appearance of multiple controls9.4.6. Changing label and text-box control properties9.4.7. Growing and shrinking text-box controls9.4.8. Sorting and grouping data9.4.8.1. Creating a group header or footer9.4.9. Sorting data within groups9.4.9.1. Removing a group header or footer9.4.9.2. Hiding a section9.4.9.3. Sizing a section9.4.9.4. Moving controls between sections9.4.10. Adding page breaks9.5. Improving the Report's Appearance9.5.1. Adjusting the page header9.5.2. Creating an expression in the group header9.5.3. Enhancing the Detail section9.5.4. Creating a report header9.6. Summary
II. Programming Microsoft Access
10. VBA Programming Fundamentals
10.1. Introducing Visual Basic for Applications10.2. Understanding VBA Terminology10.3. Starting with VBA Code Basics10.4. Migrating from Macros to VBA10.4.1. Knowing when to use macros and when to use VBA10.4.2. Converting your existing macros to VBA10.4.3. Using the Command Button Wizard10.5. Creating VBA Programs10.5.1. Events and event procedures10.5.1.1. Subprocedures10.5.1.2. Functions10.5.2. Modules10.5.2.1. Looking at the types of modules10.5.2.1.1. Standard modules10.5.2.1.2. Form and report modules10.5.2.2. Creating a new module10.5.2.2.1. Working in the code window10.5.2.2.2. The declarations section10.5.2.2.3. Creating a new procedure10.5.2.2.4. Using IntelliSense10.5.2.2.5. Compiling procedures10.5.2.2.6. Saving a module10.5.2.2.7. Creating procedures in the form or report design window10.5.2.2.8. Editing an existing procedure10.5.2.2.9. Checking your results in the Immediate window10.6. Understanding VBA Branching Constructs10.6.1. Branching10.6.1.1. The If...Then...Else...End If construct10.6.1.2. The Select Case...End Select statement10.6.2. Looping10.6.2.1. The Do...Loop statement10.6.2.2. The For...Next statement10.7. Working with Objects and Collections10.7.1. An object primer10.7.2. The With statement10.7.3. The For Each statement10.8. Looking at Access Options for Developers10.8.1. The Editor tab of the Options dialog box10.8.1.1. Auto Indent10.8.1.2. Auto Syntax Check10.8.1.3. Break on all Errors10.8.1.4. Require Variable Declaration10.8.1.5. Compile on Demand10.8.1.6. Auto List Members10.8.1.7. Auto Quick Info10.8.1.8. Auto Data Tips10.8.2. The Project Properties dialog box10.8.2.1. Project Name10.8.2.2. Project Description10.8.2.3. Conditional Compilation Arguments10.8.3. Command-line arguments10.9. Summary
11. Mastering VBA Data Types and Procedures
11.1. Introducing the Access VBA Editor11.2. Using Variables11.2.1. Naming variables11.2.2. Declaring variables11.2.2.1. The Dim keyword11.2.2.2. The Public keyword11.2.2.3. The Private keyword11.3. Working with Data Types11.3.1. Comparing implicit and explicit variables11.3.2. Forcing explicit declaration11.3.3. Using a naming convention11.3.4. Understanding variable scope and lifetime11.3.4.1. Examining scope11.3.4.2. Determining a variable's lifetime11.4. Understanding Subs and Functions11.4.1. Understanding where to create a procedure11.4.2. Calling VBA procedures11.4.3. Creating subs11.5. Creating Functions11.5.1. Handling parameters11.5.2. Calling a function and passing parameters11.5.3. Creating a function to calculate sales tax11.6. Simplifying Code with Named Arguments11.7. Summary
12. The Access Event Model
12.1. Programming Events12.1.1. Understanding how events trigger VBA code12.1.2. Creating event procedures12.2. Identifying Common Events12.2.1. Form event procedures12.2.1.1. Essential form events12.2.1.2. Form mouse and keyboard events12.2.1.3. Form data events12.2.2. Control event procedures12.2.3. Report event procedures12.2.4. Report section event procedures12.2.4.1. The Format event12.2.4.2. Form PivotTable events12.3. Paying Attention to Event Sequence12.3.1. Looking at common event sequences12.3.2. Writing simple form and control event procedures12.3.2.1. Opening a form with an event procedure12.3.2.2. Running an event procedure when closing a form12.3.2.3. Using an event procedure to confirm record deletion12.4. Summary
13. Accessing Data with VBA Code
13.1. Understanding SQL13.1.1. Viewing SQL statements in queries13.1.2. A SQL primer13.1.2.1. SELECT13.1.2.2. ALL13.1.2.3. DISTINCT13.1.2.4. DISTINCTROW13.1.2.5. TOP13.1.2.6. FROM13.1.2.7. WHERE13.1.2.8. ORDER BY13.2. Working with Data13.3. Understanding ADO Objects13.3.1. The ADO Connection object13.3.2. The ADO Command object13.3.3. The ADO Recordset object13.3.3.1. Navigating Recordsets13.3.3.2. Understanding CursorType13.3.3.3. Detecting the recordset end or beginning13.3.3.4. Counting records13.4. Understanding DAO Objects13.4.1. The DAO DBEngine object13.4.2. The DAO Workspace object13.4.3. The DAO Database object13.4.4. The DAO TableDef object13.4.5. The DAO QueryDef object13.4.6. The DAO Recordset object13.4.7. The DAO Field objects (recordsets)13.5. Writing VBA Code to Update a Table13.5.1. Updating fields in a record using ADO13.5.2. Updating a calculated control13.5.2.1. Recalculating a control when updating or adding a record13.5.2.2. Checking the status of a record deletion13.5.3. Adding a new record13.5.4. Deleting a record13.5.5. Deleting related records in multiple tables13.6. Summary
14. Debugging Your Access Applications
14.1. Testing Your Applications14.2. Using the Module Options14.2.1. Auto Syntax Check14.2.2. Require Variable Declaration14.2.3. Auto List Members14.2.4. Auto Quick Info14.2.5. Auto Data Tips14.2.6. Break on All Errors14.2.7. Compile on Demand14.3. Organizing VBA Code14.4. Compiling VBA Code14.5. Traditional Debugging Techniques14.5.1. Using MsgBox14.5.2. Using Debug.Print14.6. Using the Access Debugging Tools14.6.1. Running code with the Immediate window14.6.2. Suspending execution with breakpoints14.6.3. Looking at variables with the Locals window14.6.4. Setting watches with the Watches window14.6.5. Using conditional watches14.6.6. Using the Call Stack window14.7. Summary
15. Using Access Data Macros
15.1. Introducing Data Macros15.2. Looking at How Data Macros Are Created15.2.1. Using the Macro Designer15.2.2. Using the Action Catalog15.2.2.1. Program flow15.2.2.2. Data blocks15.2.2.3. Data actions15.3. Discovering Table Events15.3.1. Before events15.3.2. After events15.4. Building Macros15.4.1. Adding macro items15.4.2. Manipulating macro items15.4.3. Moving macro items15.4.4. Collapsing macro items15.4.5. Saving a macro as XML15.5. Recognizing the Limitations of Data Macros15.6. Summary
III. More-Advanced Access Techniques
16. Working with External Data
16.1. Looking at How Access Works with External Data16.1.1. Types of external data16.1.2. Ways of working with external data16.1.2.1. When to link to external data16.1.2.2. When to import external data16.1.2.3. When to export internal data16.2. Linking External Data16.2.1. Linking to external database tables16.2.2. Limitations of linked data16.2.3. Linking to other Access database tables16.2.4. Linking to ODBC data sources16.2.5. Linking to xBase files16.2.6. Linking to non-database data16.2.6.1. Linking to Excel16.2.6.2. Linking to HTML files16.2.6.3. Linking to text files16.3. Working with Linked Tables16.3.1. Setting view properties16.3.2. Setting relationships16.3.3. Optimizing linked tables16.3.4. Deleting a linked table reference16.3.5. Viewing or changing information for linked tables16.4. Using Code to Link Tables in Access16.4.1. The Connect and SourceTableName properties16.4.2. Checking links16.5. Summary
17. Importing and Exporting Data
17.1. Looking at Your Options for Importing and Exporting17.2. Importing External Data17.2.1. Importing from another Access database17.2.2. Importing from an Excel spreadsheet17.2.3. Importing a SharePoint list17.2.4. Importing data from text files17.2.4.1. Delimited text files17.2.4.2. Fixed-width text files17.2.5. Importing an XML document17.2.6. Importing an HTML document17.2.7. Importing Access objects other than tables17.2.8. Importing an Outlook folder17.2.9. Importing dBase tables17.2.10. Troubleshooting import errors17.2.10.1. Import errors for new tables17.2.10.2. Import errors for existing tables17.3. Exporting to External Formats17.3.1. Exporting objects to other Access databases17.3.2. Exporting through ODBC drivers17.3.3. Functionality exclusive to exports17.4. Summary
18. Advanced Access Query Techniques
18.1. Using Calculated Fields18.2. Calculated Fields and the Expression Builder18.3. Counting Records in a Table or Query18.4. Finding the Top (n) Records in a Query18.5. How Queries Save Field Selections18.5.1. Hiding (not showing) fields18.5.2. Renaming fields in queries18.6. Query Design Options18.7. Setting Query Properties18.8. Creating Queries That Calculate Totals18.8.1. Showing and hiding the Total row in the QBE pane18.8.2. The Total row options18.8.2.1. Group By18.8.2.2. Expression18.8.2.3. Using a Where clause18.8.2.4. Aggregate functions18.8.3. Performing totals on all records18.8.4. Performing totals on groups of records18.8.4.1. Calculating totals for a single group18.8.4.2. Calculating totals for several groups18.8.5. Specifying criteria for a total query18.8.5.1. Specifying criteria for a Group By field18.8.5.2. Specifying criteria for an Aggregate Total field18.8.5.3. Specifying criteria for a Non-Aggregate Total field18.8.6. Creating expressions for totals18.9. Creating Crosstab Queries18.9.1. Understanding the crosstab query18.9.2. Creating the crosstab query18.10. Understanding Action Queries18.10.1. Types of action queries18.10.2. Creating action queries18.10.2.1. Creating an update action query18.10.2.2. Creating a new table using a make-table query18.10.2.3. Creating queries to append records18.10.2.4. Creating a query to delete records18.10.2.5. Running an action query18.10.3. Troubleshooting action queries18.10.3.1. Data-type errors in appending and updating18.10.3.2. Key violations in action queries18.10.3.3. Record-locked fields in multiuser environments18.10.3.4. Text fields18.11. Summary
19. Advanced Access Form Techniques
19.1. Setting Control Properties19.1.1. Customizing default properties19.1.2. Manipulating controls at runtime19.1.3. Reading control properties19.2. Working with Subforms19.3. Designing Forms19.3.1. Using the Tab Stop property19.3.2. Tallying check boxes19.3.3. Adding animation19.3.4. Using SQL for a faster refresh19.3.5. Selecting data for overtyping19.3.6. Toggling properties with Not19.3.7. Creating an auto-closing form19.3.8. Setting up combo boxes and list boxes19.3.9. Determining whether a form is open19.4. Tackling Advanced Forms Techniques19.4.1. Using the Page Number and Date/Time controls19.4.2. Using the Image control19.4.3. Morphing a control19.4.4. Using the Format Painter19.4.5. Offering more end-user help19.4.6. Adding background pictures19.4.7. Fine-tuning your form's behavior with form events19.4.7.1. ApplyFilter19.4.8. Filter event19.5. Using the Tab Control19.6. Using Dialog Boxes to Collect Information19.6.1. Composing the SQL statement19.6.2. Adding a default button19.6.3. Setting a Cancel button19.6.4. Removing the control menu19.6.5. Closing the form19.7. Summary
20. Advanced Access Report Techniques
20.1. Grouping and Sorting Data20.1.1. Grouping data alphabetically20.1.2. Grouping on date intervals20.1.3. Hiding repeating information20.1.4. Hiding a page header20.1.5. Starting a new page number for each group20.2. Formatting Data20.2.1. Creating numbered lists20.2.2. Adding bullet characters20.2.3. Adding emphasis at runtime20.2.4. Avoiding empty reports20.2.5. Avoiding null values in a tabular report20.2.6. Inserting vertical lines between columns20.2.7. Adding a blank line every n records20.2.8. Even-odd page printing20.2.9. Using different formats in the same text box20.2.10. Centering the title20.2.11. Easily aligning control labels20.2.12. Micro-adjusting controls20.3. Adding Data20.3.1. Adding more information to a report20.3.2. Adding the user's name to a bound report20.4. Trying More Techniques20.4.1. Displaying all reports in a combo box20.4.2. Fast printing from queried data20.4.3. Hiding forms during Print Preview20.4.4. Using snaking columns in a report20.4.5. Exploiting two-pass report processing20.4.6. Assigning unique names to controls20.5. Summary
21. Building Multiuser Applications
21.1. Working on a Network21.1.1. Network performance21.1.2. File location21.1.2.1. All files on the server21.1.2.2. Distributed installation21.1.2.3. All files on the client21.1.3. Data sources21.2. Considering the Options for Opening a Database21.3. Splitting a Database for Network Access21.3.1. Detailing the benefits of splitting a database21.3.2. Knowing where to put which objects21.3.3. Using the Database Splitter add-in21.4. Finding the Key to Locking Issues21.4.1. Access's built-in record-locking features21.4.1.1. Default Open Mode21.4.1.2. Default Record Locking21.4.1.3. Refresh Interval21.4.1.4. Number of Update Retries21.4.1.5. ODBC Refresh Interval21.4.1.6. Update Retry Interval21.4.2. Record-lock error handling21.4.2.1. Error 3260: Couldn't update; currently locked by user . . .21.4.2.2. Error 3186: Couldn't save; currently locked by user x on machine y21.4.2.3. Error 3188: Could not update; currently locked by another session on this machine21.4.2.4. Error 3197: Data has changed; operation stopped21.4.2.5. A function to handle lock errors21.5. Reducing Multiuser Errors with Unbound Forms21.5.1. Creating an unbound form21.5.2. Making an unbound form work21.5.2.1. Opening the form21.5.2.2. Navigating through records21.5.2.3. Using navigation buttons21.5.2.4. Using a combo box21.5.2.5. Editing data21.6. Summary
22. Integrating Access with Other Applications
22.1. Using Automation in Access22.1.1. Understanding how Automation works22.1.2. Creating Automation references22.1.3. Binding your VBA object variables to objects in the Automation interface22.1.3.1. Early-binding an object22.1.3.2. Late-binding an object22.1.4. Creating an instance of an Automation object22.1.4.1. Creating a new instance with New22.1.4.2. Creating a new instance with CreateObject22.1.5. Getting an existing object instance22.1.6. Working with Automation objects22.1.7. Closing an instance of an Automation object22.2. Looking at an Automation Example Using Word22.2.1. Creating an instance of a Word object22.2.2. Making the instance of Word visible22.2.3. Creating a new document based on an existing template22.2.4. Inserting data22.2.5. Activating the instance of Word22.2.6. Moving the cursor in Word22.2.7. Discarding the Word object instance22.2.8. Inserting pictures by using bookmarks22.2.9. Using Office's macro recorder22.3. Collecting Data with Microsoft Outlook22.3.1. Creating an e-mail22.3.2. Managing replies22.3.2.1. Choosing how to process replies22.3.2.2. Automatically processing replies22.3.2.3. Manually processing replies22.4. Summary
23. Handling Errors and Exceptions
23.1. Dealing with Errors23.1.1. Logical errors23.1.2. Runtime errors23.1.2.1. When fatal errors occur23.1.2.2. When Access reports the error23.1.2.3. When the application handles the errors23.1.2.4. When unanticipated application errors occur23.2. Identifying Which Errors Can Be Detected23.2.1. What an error handler is23.2.2. How to set a basic error trap23.3. Trapping Errors with VBA23.3.1. The Err object23.3.2. VBA error-handling statements23.3.2.1. On Error GoTo Label23.3.2.2. On Error GoTo 023.3.2.3. On Error Resume Next23.3.2.4. VBA Resume statements23.3.2.4.1. Resume23.3.2.4.2. Resume Next23.3.2.4.3. Resume Label23.3.3. The Error event23.3.4. The ADO Errors collection23.4. Summary
IV. Professional Database Development
24. Optimizing Access Applications
24.1. Understanding Module Load on Demand24.1.1. Organizing your modules24.1.2. Pruning the call tree24.2. Using the .accdb Database File Format24.3. Distributing .accde Files24.4. Understanding the Compiled State24.4.1. Putting your application's code into a compiled state24.4.2. Losing the compiled state24.4.3. Distributing applications in a compiled or uncompiled state24.4.3.1. Distributing source code for your application24.4.3.2. Putting an application in an uncompiled state24.4.3.3. Storing commonly used code in a library24.4.3.4. Creating a library reference for distributed applications24.5. Improving Absolute Speed24.5.1. Tuning your system24.5.2. Getting the most from your tables24.5.2.1. Creating efficient indexes24.5.2.2. Deciding which fields to index24.5.2.3. Using multiple-field indexes24.5.3. Getting the most from your queries24.5.4. Getting the most from your forms and reports24.5.4.1. Minimizing form and report complexity and size24.5.4.2. Using bitmaps on forms and reports24.5.4.3. Speeding up list boxes and combo boxes24.5.5. Getting the most from your modules24.5.5.1. Using appropriate data types24.5.5.2. Writing faster routines24.5.5.2.1. Using control variables24.5.5.2.2. Using field variables24.5.5.2.3. Increasing the speed of finding data in code24.5.5.3. Eliminating dead code and unused variables24.5.6. Increasing network performance24.6. Improving Perceived Speed24.6.1. Using a splash screen24.6.2. Loading and keeping forms hidden24.6.3. Using the hourglass24.6.4. Using the built-in progress meter24.6.5. Creating a progress meter as a pop-up form24.6.6. Speeding up the progress meter display24.7. Working with Large Access Databases24.7.1. Understanding how databases grow in size24.7.2. Recognizing that compiling and compacting may not be enough24.7.2.1. Rebooting to get a clean memory map24.7.2.2. Fixing a corrupt form by removing the record source24.7.2.3. Creating a new database and importing all objects24.7.3. Using the decompile option24.7.4. Detecting an uncompiled database and automatically recompiling24.7.5. Making small changes to large databases24.8. Summary
25. Advanced Data Access with VBA
25.1. Adding an Unbound Combo Box to a Form to Find Data25.1.1. Using the FindRecord method25.1.2. Using a bookmark25.2. Filtering a Form25.2.1. With code25.2.2. With a query25.2.2.1. Creating a parameter query25.2.2.2. Creating an interactive filter dialog box25.2.2.3. Linking the dialog box to another form25.3. Summary
26. Bulletproofing Access Applications
26.1. Introducing Bulletproofing26.2. Looking at the Characteristics of Bulletproofed Applications26.3. Identifying the Principles of Bulletproofing26.3.1. Building to a specification26.3.2. Becoming one with documentation26.3.2.1. Documenting the code you write26.3.2.2. Documenting the application26.3.3. Considering your users26.3.3.1. What they know26.3.3.2. What they want26.3.4. Getting the application to the users26.3.4.1. Using professional installation tools26.3.4.2. Providing the Access runtime module to users26.3.5. Enabling the users to actually use the application26.3.5.1. Making the application easy to start26.3.5.2. Using start-up options26.3.5.3. Using a login form26.3.5.4. Making a splash with a splash screen26.3.6. Controlling the flow of information26.3.6.1. Adding switchboards to the application26.3.6.2. Controlling the ribbons26.3.6.3. Hiding the Navigation Pane and removing menus26.3.6.4. Displaying one form at a time26.3.6.5. Choosing the user-interface style26.3.6.5.1. Tabbed Documents26.3.6.5.2. Overlapping Windows26.3.7. Keeping the user informed26.3.7.1. Getting your message across with a message box26.3.7.2. Creating and using a progress meter26.3.8. Tracking down problems26.3.8.1. Checking for obvious hardware errors26.3.8.2. Maintaining usage logs26.3.9. Securing the environment26.3.9.1. Setting start-up options in code26.3.9.2. Disabling start-up bypass26.3.9.2.1. Setting property values26.3.9.2.2. Getting property values26.3.10. Protecting your database26.3.10.1. Building bulletproof forms26.3.10.2. Validating user input26.3.10.3. Using the /runtime option26.3.10.4. Encrypting or encoding a database26.3.10.5. Removing a database password26.3.10.6. Protecting Visual Basic code26.3.10.7. Understanding macro security26.3.10.7.1. Enabling sandbox mode26.3.10.7.2. The Trust Center26.3.11. Continuing to improve the product26.4. Summary
27. Using the Windows Application Programming Interface
27.1. What the Windows API Is27.2. Reasons to Use the Windows API27.2.1. Common code base27.2.2. Tested and proven code27.2.3. Cross-platform compatibility27.2.4. Smaller application footprint27.2.5. Application consistency27.3. DLL Documentation27.3.1. Finding the documentation27.3.2. Making sense of the documentation27.3.3. Recognizing what you can't do with the API27.4. How to Use the Windows API27.4.1. The Declare statement27.4.1.1. Function or Sub27.4.1.2. Function name27.4.1.3. Lib "LibraryName"27.4.1.4. Alias "AliasName"27.4.1.5. ArgumentList27.4.1.5.1. ByVal or ByRef?27.4.1.6. As DataType27.4.2. Wrapper functions27.5. API Examples27.5.1. Retrieving system information27.5.1.1. GetCommandLineA27.5.1.2. GetWindowsDirectoryA27.5.1.3. GetTempPathA27.5.1.4. GetVersionExA27.5.1.5. GetUserNameA27.5.1.6. GetComputerNameA27.5.1.7. GetDriveTypeA27.5.1.8. GetVolumeInformationA27.5.1.9. GetSystemDirectoryA27.5.2. Going over general-purpose Windows API functions27.5.2.1. GetParent27.5.2.2. GetWindowTextA27.5.2.3. GetClassNameA27.5.2.4. SetWindowTextA27.5.3. Manipulating application settings with the Windows API27.5.3.1. GetPrivateProfileStringA27.5.3.2. GetPrivateProfileIntA27.5.3.3. GetProfileStringA27.5.3.4. WritePrivateProfileStringA27.5.3.5. WriteProfileStringA27.6. Summary
28. Object-Oriented Programming with VBA
28.1. Introducing Object-Oriented Programming28.1.1. Getting to know objects28.1.1.1. Understanding what objects are28.1.1.2. Using objects in applications28.1.2. Defining objects with class modules28.1.3. Looking at a simple class module28.1.4. Adding a class module to a database28.1.5. Creating simple product properties28.1.6. Creating methods28.1.7. Using the product object28.1.8. Creating bulletproof property procedures28.2. Recognizing the Benefits of Object-Oriented Programming28.2.1. Encapsulating functionality28.2.2. Simplifying programming tasks28.2.3. Managing a class's interface28.3. Using Property Procedures28.3.1. Looking at the types of property procedures28.3.1.1. Property Let28.3.1.2. Property Set28.3.1.3. Property Get28.3.2. Exploring property-value persistence28.3.3. Heeding property procedure rules28.4. Modifying the Product Class28.4.1. Retrieving product details28.4.2. Looking at the new ProductID property28.4.3. Adding a new property to provide extra information28.4.4. Adding a new method to the product class28.5. Learning about Class Events28.5.1. The Class_Initialize event procedure28.5.2. The Class_Terminate event procedure28.6. Adding Events to Class Modules28.6.1. Learning about events in Access28.6.2. Recognizing the need for events28.6.3. Creating custom events28.6.4. Raising events28.6.5. Trapping custom events28.6.6. Passing data through events28.6.7. Exploiting Access class module events28.7. Summary
29. Customizing Access Ribbons
29.1. Why Replace Toolbars and Menus?29.1.1. New controls for Access ribbons29.1.2. SplitButton29.1.3. DropDown29.1.4. Gallery29.1.5. SuperTips29.2. Working with the Access Ribbon29.2.1. Tabs29.2.2. Groups29.2.3. Controls29.2.4. Managing the ribbon29.3. Working with the Quick Access Toolbar29.4. Editing the Default Access Ribbon29.5. Developing Custom Access Ribbons29.5.1. The ribbon creation process29.5.2. Using VBA callbacks29.6. The Ribbon Hierarchy29.7. Getting Started with Access Ribbons29.7.1. Step 1: Design the ribbon and build the XML29.7.2. Step 2: Write the callback routines29.7.3. Step 3: Create the USysRibbons table29.7.4. Step 4: Add XML to USysRibbons29.7.5. Step 5: Specify the custom ribbon property29.8. The Basic Ribbon XML29.9. Adding Ribbon Controls29.9.1. Specifying imageMso29.9.2. The Label control29.9.3. The Button control29.9.4. Separators29.9.5. Check boxes29.9.6. The DropDown control29.9.7. The SplitButton Control29.10. Using Visual Web Developer29.11. Managing Ribbons29.12. Completely Removing the Access Ribbon29.13. Summary
30. Using Access Macros
30.1. An Introduction to Macros30.1.1. Creating a macro30.1.2. Assigning a macro to an event30.2. Multi-Action Macros30.3. Submacros30.4. Conditions30.4.1. Opening reports using conditions30.4.2. Multiple actions in conditions30.5. Temporary Variables30.5.1. Enhancing a macro you've already created30.5.2. Using temporary variables to simplify macros30.5.3. Using temporary variables in VBA30.6. Error Handling and Macro Debugging30.6.1. The OnError action30.6.2. The MacroError object30.6.3. Debugging macros30.7. Embedded Macros30.8. Macros versus VBA Statements30.8.1. Choosing between macros and VBA30.8.2. Converting existing macros to VBA30.9. Summary
31. Distributing Access Applications
31.1. Defining the Current Database Options31.1.1. Application options31.1.1.1. Application Title31.1.1.2. Application Icon31.1.1.3. Display Form31.1.1.4. Display Status Bar31.1.1.5. Document Window Options31.1.1.6. Use Access Special Keys31.1.1.7. Compact on Close31.1.1.8. Remove Personal Information from File Properties on Save31.1.1.9. Use Windows-Themed Controls on Forms31.1.1.10. Enable Layout View31.1.1.11. Enable Design Changes for Tables in Datasheet View31.1.1.12. Check for Truncated Number Fields31.1.1.13. Picture Property Storage Format31.1.2. Navigation options31.1.2.1. The Display Navigation Pane check box31.1.2.2. The Navigation Options button31.1.3. Ribbon and toolbar options31.1.3.1. Ribbon Name31.1.3.2. Shortcut Menu Bar31.1.3.3. Allow Full Menus31.1.3.4. Allow Default Shortcut Menus31.1.4. Name AutoCorrect Options31.2. Testing the Application before Distribution31.3. Polishing Your Application31.3.1. Giving your application a consistent look and feel31.3.2. Adding common professional components31.3.2.1. A splash screen31.3.2.2. An application switchboard31.3.2.3. An About box31.4. Bulletproofing an Application31.4.1. Using error trapping on all Visual Basic procedures31.4.2. Separating tables from the rest of the application31.4.3. Documenting the application31.5. Summary
V. Access and Windows SharePoint Services
32. Understanding Windows SharePoint Services
32.1. Introducing SharePoint32.2. Reviewing Various Types of SharePoint Sites32.3. Working with SharePoint Lists32.4. Looking at a SharePoint Web Site32.4.1. Editing SharePoint list items32.4.2. Creating SharePoint lists32.5. Summary
33. Integrating Access with SharePoint
33.1. Introducing SharePoint as a Data Source33.2. Sharing Access Data with SharePoint33.2.1. Linking to SharePoint lists33.2.2. Exporting Access tables to SharePoint33.2.3. Moving Access tables to SharePoint33.3. Using SharePoint Templates33.4. Summary
34. Understanding Access Services
34.1. Explaining Managed Applications34.2. Looking at Web Publishing in Access34.2.1. Why SharePoint?34.2.2. Leveraging SharePoint features34.2.3. Publishing Access applications to SharePoint34.3. Understanding Access Services34.4. Examining Access Web Application Limits34.4.1. Not public-facing34.4.2. Fewer than 20,000 rows of data34.4.3. Modest transactional requirements34.5. Summary
35. Deploying Access Applications to SharePoint
35.1. Looking at SharePoint Deployment Options35.1.1. Enhanced table exporting option35.1.2. Publishing to SharePoint option35.1.2.1. Understanding publishing to SharePoint deployment35.1.2.2. Revisiting Access Services35.1.2.3. Using Compatibility Checker35.1.2.4. Publishing to SharePoint35.2. Dealing with Compatibility Checker Problems35.2.1. General errors35.2.2. Relationship and lookup errors35.2.2.1. Incompatible fields35.2.2.2. Table relationships35.2.2.3. Primary key constraints35.2.2.4. Lookup field issues35.2.3. Form and report errors35.2.3.1. Unsupported form controls35.2.3.2. Problems with form events35.2.3.3. Control issues35.2.3.4. Unsupported report controls35.2.3.5. Reports do not support events35.2.3.6. Reports do not support subreports35.2.3.7. Object name violations35.2.4. Query errors35.2.4.1. Problems with SQL statements35.2.4.2. Query join issues35.2.4.3. Invalid expression errors35.2.4.4. Invalid query name35.2.4.5. Queries cannot include subqueries35.2.5. Macro errors35.2.5.1. Reserved word collisions35.2.5.2. Incompatible macro actions35.2.6. Schema errors35.2.6.1. Lookup field issues35.2.6.2. Field data type issues35.2.6.3. Primary key issues35.2.6.4. Attachment field limitations35.2.6.5. Calculated field issues35.2.6.6. Table field issues35.2.6.7. Table name issues35.2.6.8. Custom field formats are not compatible35.2.6.9. Unique property must be false35.2.6.10. AutoNumbers are reserved for primary keys35.3. Summary
VI. Access as an Enterprise Platform
36. Client/Server Concepts
36.1. Looking at the Parts of Client/Server Architecture36.1.1. Applications36.1.2. The back office36.1.2.1. The database36.1.2.2. Access's role in client/server computing36.1.2.3. Web servers and application servers36.2. Making Sense of Multi-Tier Architecture36.2.1. Two-tier systems36.2.2. Three-tier systems36.3. Putting It All Together: Access, Client-Server, and Multiple Tiers36.3.1. Access as a database repository36.3.2. Access as an Internet database36.4. Summary
37. SQL Server as an Access Companion
37.1. Connecting to SQL Server37.1.1. Introducing connection strings37.1.2. Connecting to SQL Server from Access37.2. Working with SQL Server Objects37.2.1. Using SQL Server tables from Access37.2.2. Views37.2.3. Stored procedures37.2.4. Triggers37.3. Summary
38. Upsizing Access Databases to SQL Server
38.1. Introducing SQL Server Express38.2. Upsizing Access and the Upsizing Wizard38.2.1. Before upsizing an application38.2.2. Running the Upsizing Wizard38.2.3. Working with an Access ADP file38.2.4. Comparing Access to SQL Server data types38.3. Summary
VII. Appendixes
A. Access 2010 Specifications
A.1. Microsoft Access Database SpecificationsA.2. Microsoft SQL Server Express Specifications
B. What's New in Access 2010
B.1. The User InterfaceB.2. Publish Access to the WebB.3. TablesB.4. Datasheet ViewB.5. FormsB.6. ReportsB.7. MacrosB.8. SecurityB.9. SharePointB.10. Summary
C. What's on the CD-ROM
C.1. System RequirementsC.2. Using the CDC.3. What's on the CDC.3.1. Example filesC.3.2. eBook version of Access 2010 BibleC.4. Troubleshooting

Content preview from Access® 2010 Bible

Chapter 16. Working with External Data

IN THIS CHAPTER

Looking at the types of external data and methods for working with them
Linking your Access database to external data
Using linked tables
Linking to external data sources with code

So far, you've worked with data in Access tables found within the current database. In this chapter, you explore the use of data from other types of files. You learn to work with data from database, spreadsheet, HTML, and text-based files. After I describe the general relationship between Access and external data, I explain the major methods of working with external data: linking and importing/exporting.

Note

This chapter uses the Chapter16.accdb database as well as several other files that you'll use for linking. If you haven't already copied these files onto your machine from the CD, you'll need to do so now.

Note that, because the point of this chapter is to show how Access works with external data, there are examples of external data that you need to copy to your machine. Unfortunately, when working with external data, Access requires an exact path to each file — it can't work with relative paths. That means that when you copy Chapter16.accdb to your machine, it won't work until you relink the various external files. I show you how to do that in this chapter. For now, be aware that the following tables are linked to the files indicated:

Table	External File Type	Filename(s)
`CONTACTS`	`dBase 5.0`	`CONTACTS.DBF, CONTACTS.DBT, Contacts.INF, CONTACTS.MDX`
`Customers`	Excel ...

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: 9780470475348

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Access® 2010 Bible

by Michael R. Groh

Chapter 16. Working with External Data

Note

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.