book

Access 2007 Programming by Example with VBA, XML, and ASP

by Julitta Korol

December 2007

Intermediate to advanced

938 pages

24h 52m

English

Jones & Bartlett Learning

Read now

Unlock full access

PrerequisitesHow This Book Is OrganizedHow to Work with This BookSource Files
Procedure TypesModule TypesStandard ModulesWriting Procedures in a Standard ModuleHands-On 1-1: Working in a Standard ModuleExecuting Your Procedures and FunctionsHands-On 1-2: Running Procedures and FunctionsClass ModulesEvents, Event Properties, and Event ProceduresWhy Use Events?Walking Through an Event ProcedureHands-On 1-3: Writing an Event ProcedureCompiling Your ProceduresPlacing a Database in a Trusted LocationHands-On 1-4: Placing an Access Database in a Trusted LocationChapter Summary
Understanding the Project Explorer WindowUnderstanding the Properties WindowHands-On 2-1: Using Properties Window to View Control PropertiesUnderstanding the Code WindowOther Windows in the VBEOn-the-Fly Syntax and Programming AssistanceList Properties/MethodsParameter InfoList ConstantsQuick InfoComplete WordIndent/OutdentHands-On 2-2: Using the Indent/Outdent FeatureComment Block/Uncomment BlockUsing the Object BrowserHands-On 2-3: Using the Object BrowserUsing the VBA Object LibraryHands-On 2-4: Using Built-in VBA FunctionsUsing the Immediate WindowHands-On 2-5: Experiments in the Immediate WindowHands-On 2-6: Asking Questions in the Immediate WindowChapter Summary
What Is a Variable?What Are Data Types?Creating VariablesDeclaring VariablesHands-On 3-1: Using VariablesSpecifying the Data Type of a VariableHands-On 3-2: Understanding the Data Type of a VariableUsing Type Declaration CharactersHands-On 3-3: Using Type Declaration Characters in Variable NamesAssigning Values to VariablesHands-On 3-4: Assigning Values to VariablesForcing Declaration of VariablesHands-On 3-5: Forcing Declaration of VariablesUnderstanding the Scope and Lifetime of VariablesProcedure-Level (Local) VariablesModule-Level VariablesHands-On 3-6: Understanding Module-Level VariablesPublic (Global) VariablesUnderstanding and Using Temporary VariablesCreating a Temporary Variable with a TempVars Collection ObjectRetrieving Names and Values of TempVar ObjectsUsing Temporary Global Variables in ExpressionsRemoving a Temporary Variable from a TempVars Collection ObjectUnderstanding and Using Static VariablesHands-On 3-7: Using Static VariablesUnderstanding and Using Object VariablesHands-On 3-8: Working with Object VariablesFinding a Variable DefinitionHands-On 3-9: Finding a Variable DefinitionWhat Type Is This Variable?Hands-On 3-10: Asking Questions about the Variable TypeUsing Constants in VBA ProceduresIntrinsic ConstantsHands-On 3-11: Exploring Access’s ConstantsHands-On 3-12: Using Intrinsic Constants in a VBA ProcedureGrouping Constants with the Enum TypeChapter Summary
Writing a Function ProcedureHands-On 4-1: Writing a Function Procedure with ArgumentsHands-On 4-2: Executing a Function Procedure from the Immediate WindowHands-On 4-3: Executing a Function Procedure from a SubroutineSpecifying the Data Type for a Function’s ResultHands-On 4-4: Calling a Function from a ProcedurePassing Arguments by Reference and by ValueHands-On 4-5: Passing Arguments to Subroutines and FunctionsUsing Optional ArgumentsHands-On 4-6: Using Optional ArgumentsUsing the IsMissing FunctionBuilt-in FunctionsUsing the MsgBox FunctionHands-On 4-7: Formatting the Message BoxHands-On 4-8: Using the MsgBox Function with ArgumentsReturning Values from the MsgBox FunctionHands-On 4-9: Returning Values from the MsgBox FunctionUsing the InputBox FunctionHands-On 4-10: Using the InputBox FunctionConverting Data TypesHands-On 4-11: Converting Data TypesUsing Master Procedures and SubproceduresHands-On 4-12: Breaking Up Large ProceduresChapter Summary
If…Then StatementHands-On 5-1: Using the If…Then StatementMulti-Line If…Then StatementHands-On 5-2: Using the Multi-Line If…Then StatementDecisions Based on More Than One ConditionHands-On 5-3: Using the If…Then…AND Conditional StatementIf…Then…Else StatementHands-On 5-4: Using the If…Then…Else Conditional StatementIf…Then…ElseIf StatementNested If…Then StatementsHands-On 5-5: Using Nested If…Then StatementsSelect Case StatementHands-On 5-6: Using the Select Case StatementUsing Is with the Case ClauseSpecifying a Range of Values in a Case ClauseHands-On 5-7: Using the Select Case Statement in a FunctionSpecifying Multiple Expressions in a Case ClauseChapter Summary
Using the Do…While StatementHands-On 6-1: Using the Do…While StatementAnother Approach to the Do…While StatementHands-On 6-2: Using the Do…While Statement with a Condition at the Bottom of the LoopUsing the Do…Until StatementHands-On 6-3: Using the Do…Until StatementAnother Approach to the Do…Until StatementHands-On 6-4: Using the Do…Until Statement with a Condition at the Bottom of the LoopFor…Next StatementHands-On 6-5: Using the For…Next StatementFor Each…Next StatementHands-On 6-6: Using the For Each…Next StatementExiting Loops EarlyHands-On 6-7: Early Exit from a LoopNested LoopsHands-On 6-8: Using Nested LoopsChapter Summary

Declaring ArraysArray Upper and Lower BoundsUsing Arrays in VBA ProceduresHands-On 7-1: Using a One-Dimensional ArrayArrays and Looping StatementsHands-On 7-2: Using Arrays and LoopsHands-On 7-3: Passing Elements of an Array to Another ProcedureHands-On 7-4: Using Arrays and Loops in Real LifeUsing a Two-Dimensional ArrayHands-On 7-5: Using a Two-Dimensional ArrayStatic and Dynamic ArraysHands-On 7-6: Using a Dynamic ArrayArray FunctionsThe Array FunctionHands-On 7-7: Using the Array FunctionThe IsArray FunctionHands-On 7-8: Using the IsArray FunctionThe Erase FunctionHands-On 7-9: Removing Data from an ArrayThe LBound and UBound FunctionsHands-On 7-10: Finding the Lower and Upper Bounds of an ArrayErrors in ArraysHands-On 7-11: Understanding Errors in ArraysParameter ArraysHands-On 7-12: Working with Parameter ArraysChapter Summary
TerminologyWorking with CollectionsDeclaring a Custom CollectionAdding Objects to a Custom CollectionHands-On 8-1: Creating a Custom CollectionRemoving Objects from a Custom CollectionHands-On 8-2: Removing Objects from a CollectionCreating Custom ObjectsCreating a ClassCustom Project 8-1 (Step 1): Creating a Class ModuleVariable DeclarationsCustom Project 8-1 (Step 2): Declaring Class MembersDefining the Properties for the ClassCreating the Property Get ProceduresCustom Project 8-1 (Step 3): Writing Property Get ProceduresCreating the Property Let ProceduresCustom Project 8-1 (Step 4): Writing Property Let ProceduresCreating the Class MethodsCustom Project 8-1 (Step 5): Writing Class MethodsCreating an Instance of a ClassCustom Project 8-1 (Step 6): Creating an Instance of a ClassEvent Procedures in the Class ModuleCreating the User InterfaceCustom Project 8-1 (Step 7): Designing a User FormCustom Project 8-1 (Step 8): Writing Event ProceduresRunning the Custom ApplicationCustom Project 8-1 (Step 9): Running the Custom ProjectWatching the Execution of Your VBA ProceduresCustom Project 8-1 (Step 10): Custom Project Code WalkthroughChapter Summary
Testing VBA ProceduresStopping a ProcedureUsing BreakpointsCustom Project 9-1: Debugging a Function ProcedureRemoving BreakpointsUsing the Immediate Window in Break ModeUsing the Stop StatementAdding Watch ExpressionsHands-On 9-1: Working with the Watches WindowRemoving Watch ExpressionsUsing Quick WatchHands-On 9-2: Using the Quick Watch Dialog BoxUsing the Locals Window and the Call Stack Dialog BoxHands-On 9-3: Using the Locals WindowStepping Through VBA ProceduresHands-On 9-4: Stepping Through a ProcedureStepping Over a ProcedureHands-On 9-5: Stepping Over a ProcedureStepping Out of a ProcedureRunning a Procedure to CursorSetting the Next StatementShowing the Next StatementStopping and Resetting VBA ProceduresUnderstanding and Using Conditional CompilationHands-On 9-6: Using Conditional CompilationNavigating with BookmarksTrapping ErrorsHands-On 9-7: Error Trapping TechniquesHands-On 9-8: Understanding Error TrappingGenerating Errors to Test Error HandlingChapter Summary
Understanding Database Engines: Jet/ACEUnderstanding Access Versions and File FormatsUnderstanding Library ReferencesOverview of Object Libraries in Microsoft AccessThe Visual Basic for Applications Object Library (VBA)The Microsoft Access 12.0 Object LibraryThe Microsoft Office 12.0 Access Database Engine Object LibraryThe Microsoft Access DAO 3.6 Object LibraryThe Microsoft ActiveX Data Objects 2.8 Library (ADO)Creating a Reference to the ADO LibraryHands-On 10-1: Setting Up a Reference to ADO Object LibrariesUnderstanding Connection StringsUsing ODBC Connection StringsCreating and Using ODBC DSN ConnectionsHands-On 10-2: Creating and Using the ODBC DSN Connection to Read Data from a dBASE FileCreating and Using DSN-less ODBC ConnectionsUsing OLE DB Connection StringsConnection String via a Data Link FileHands-On 10-3: Creating and Using a Universal Data Link FileOpening Microsoft Access DatabasesOpening a Microsoft Jet Database in Read/Write Mode with DAOHands-On 10-4: Opening a Database with DAO in Read/Write ModeOpening a Microsoft Jet Database in Read/Write Mode with ADOHands-On 10-5: Opening a Database with ADO in Read/Write ModeOpening a Microsoft Access Database in Read-Only Mode with DAOOpening a a Microsoft Jet Database in Read-Only Mode with ADOOpening a Microsoft Jet Database Secured with a PasswordHands-On 10-6: Setting a Database Password and Opening a Password-Protected Database with DAOHands-On 10-7: Setting a Database Password and Opening a Password-Protected Database with ADOOpening a Microsoft Jet Database with User-Level SecurityHands-On 10-8: Opening a Database Secured at the User LevelConnecting to the Current Access DatabaseHands-On 10-9: Establishing a Connection to the Current Access DatabaseOpening Other Databases, Spreadsheets, and Text Files from Access 2007Connecting to an SQL Server DatabaseHands-On 10-10: Connecting to an SQL Server Database Using SQLOLEDB ProviderOpening a Microsoft Excel SpreadsheetHands-On 10-11: Opening an Excel Spreadsheet with DAOHands-On 10-12: Opening an Excel Spreadsheet with ADOOpening a Text File Using ADOHands-On 10-13: Opening a Text File with ADOCreating a New Access DatabaseCreating a Database with DAOHands-On 10-14: Creating a New Database Using DAOCreating a Database with ADOHands-On 10-15: Creating a New Microsoft Access Database Using ADOCopying a DatabaseCopying a Database with DAOHands-On 10-16: Copying a Database with DAOCopying a Database with FileSystemObjectHands-On 10-17: Copying a FileDatabase ErrorsHands-On 10-18: Using the VBA Err Object and ADO Errors CollectionCompacting a DatabaseHands-On 10-19: Compacting a DatabaseChapter Summary
Creating a Microsoft Access Table and Setting Field Properties (DAO Method)Hands-On 11-1: Creating a Table (DAO)Creating a Microsoft Access Table and Setting Field Properties (ADO Method)Copying a TableHands-On 11-2: Making a Copy of a Table (ADO)Deleting a Database TableHands-On 11-3: Deleting a Table from a Database (ADO)Adding New Fields to an Existing TableHands-On 11-4: Adding a New Field to a Table (ADO)Hands-On 11-5: Adding a New Field to a Table (DAO)Removing a Field from a TableHands-On 11-6: Removing a Field from a Table (ADO)Hands-On 11-7: Removing a Field from a Table (DAO)Retrieving Table PropertiesHands-On 11-8: Listing Table PropertiesRetrieving Field PropertiesHands-On 11-9: Listing Field PropertiesLinking a Microsoft Access TableHands-On 11-10: Linking a Microsoft Jet TableLinking a dBASE TableLinking a Microsoft Excel SpreadsheetHands-On 11-11: Linking an Excel SpreadsheetListing Database TablesHands-On 11-12: Creating a List of Database TablesListing Tables and FieldsHands-On 11-13: Using the OpenSchema Method to List Database TablesHands-On 11-14: Listing Tables and Their Fields Using the OpenSchema MethodListing Data TypesHands-On 11-15: Listing Data TypesChanging the AutoNumberHands-On 11-16: Changing the Value of an AutoNumberChapter Summary
Creating a Primary Key IndexHands-On 12-1: Creating a Primary Key (ADO)Hands-On 12-2: Creating a Primary Key (DAO)Creating a Single-Field IndexHands-On 12-3: Adding a Single-Field Index to an Existing Table (ADO)Adding a Multiple-Field Index to a TableHands-On 12-4: Adding a Multiple-Field Index to an Existing Table (DAO)Listing Indexes in a TableHands-On 12-5: Listing Indexes in a Table (ADO)Deleting Table IndexesHands-On 12-6: Deleting Indexes from a Table (ADO)Creating Table RelationshipsHands-On 12-7: Creating a One-to-Many RelationshipChapter Summary
Introduction to DAO RecordsetsOpening Various Types of RecordsetsHands-On 13-1: Opening Table-, Dynaset-, and Snapshot-type Recordsets (DAO)Opening a Snapshot and Counting RecordsHands-On 13-2: Opening a Snapshot-type Recordset and Retrieving the Number of Records (DAO)Retrieving the Contents of a Specific Field in a TableHands-On 13-3: Retrieving Field Values (DAO)Moving between Records in a TableHands-On 13-4: Moving between Records in a Table (DAO)Finding Records in a Table-Type RecordsetHands-On 13-5: Finding Records in a Table-type Recordset (DAO)Finding Records in Dynasets or SnapshotsHands-On 13-6: Finding a Record in a Dynaset-type Recordset (DAO)Finding the nth Record in a SnapshotHands-On 13-7: Finding the nth Record in a Snapshot-type Recordset (DAO)Introduction to ADO RecordsetsCursor TypesLock TypesCursor LocationThe Options ParameterOpening a RecordsetHands-On 13-8: Opening a Recordset (ADO)Opening a Recordset Based on a Table or QueryHands-On 13-9: Opening a Recordset Based on a Table or Query (ADO)Opening a Recordset Based on an SQL StatementHands-On 13-10: Opening a Recordset Based on an SQL Statement (ADO)Opening a Recordset Based on CriteriaHands-On 13-11: Opening a Recordset Based on Criteria (ADO)Opening a Recordset DirectlyHands-On 13-12: Opening a Recordset Directly (ADO)Moving Around in a RecordsetHands-On 13-13: Moving Around in a Recordset (ADO)Finding the Record PositionHands-On 13-14: Finding the Record Position (ADO)Reading Data from a FieldHands-On 13-15: Retrieving Field Values (ADO)Returning a Recordset as a StringHands-On 13-16: Converting the Recordset to a String (ADO)Finding Records Using the Find MethodHands-On 13-17: Finding Records Using the Find Method (ADO)Finding Records Using the Seek MethodHands-On 13-18: Finding Records Using the Seek Method (ADO)Finding a Record Based on Multiple ConditionsHands-On 13-19: Finding a Record Based on Multiple Criteria (ADO)Using BookmarksHands-On 13-20: Marking Records with a Bookmark (ADO)Using Bookmarks to Filter a RecordsetHands-On 13-21: Using Bookmarks to Filter Records (ADO)Using the GetRows Method to Fill the RecordsetHands-On 13-22: Counting the Number of Returned Records (ADO)Chapter Summary
Adding a New Record with DAOHands-On 14-1: Adding a New Record to a Table (DAO)Adding a New Record with ADOHands-On 14-2: Adding a New Record to a Table (ADO)Modifying a Record with DAOHands-On 14-3: Modifying a Record in a Table (DAO)Modifying a Record with ADOHands-On 14-4: Modifying a Record (ADO)Editing Multiple Records with ADOHands-On 14-5: Performing Batch Updates (ADO)DeletingaRecordwithDAOHands-On 14-6: Deleting a Record (DAO)Deleting a Record with ADOHands-On 14-7: Deleting a Record (ADO)Copying Records to an Excel SpreadsheetHands-On 14-8: Copying Records to an Excel Worksheet (DAO)Copying Records to a Word DocumentHands-On 14-9: Copying Records to a Word Document (Example 1)Hands-On 14-10: Copying Records to a Word Document (Example 2)Copying Records to a Text FileHands-On 14-11: Copy Records to a Text File (ADO)Filtering Records Using the SQL WHERE ClauseHands-On 14-12: Filtering Records with the SQL WHERE Clause (DAO)Hands-On 14-13: Filtering Records with the SQL WHERE Clause (ADO)Filtering Records Using the Filter PropertyHands-On 14-14: Filtering Records Using the Filter Property (DAO)Hands-On 14-15: Filtering Records Using the Filter Property (ADO)Sorting RecordsHands-On 14-16: Sorting Records (ADO)Chapter Summary
Creating a Select Query ManuallyCreating a Select Query with DAOHands-On 15-1: Creating a Select Query with DAOCreating a Select Query with ADOHands-On 15-2: Creating a Select Query with ADOExecuting an Existing Select Query with ADOHands-On 15-3: Executing a Select QueryHands-On 15-4: Executing a Select Query with an ADO Catalog ObjectModifying an Existing Query with ADOHands-On 15-5: Modifying a Select Query with ADOCreating and Running a Parameter Query with DAOHands-On 15-6: Creating a Parameter Query with DAOCreating and Running a Parameter Query with ADOHands-On 15-7: Creating a Parameter Query with ADOHands-On 15-8: Executing a Parameter Query with ADOCreating and Running a Make-Table Query with DAOHands-On 15-9: Creating and Running a Make-Table Query with DAOCreating and Running an Update Query with DAOHands-On 15-10: Creating and Running an Update Query with DAOExecuting an Update Query with ADOHands-On 15-11: Executing an Update QueryHands-On 15-12: Executing an Update Query Using the Command ObjectRunning an Append Query with DAO/ADOHands-On 15-13: Running an Append Query with DAORunning a Delete Query with DAOHands-On 15-14: Running a Delete Query with DAOCreating and Running a Pass-Through Query with DAOCreating and Executing a Pass-Through Query with ADOHands-On 15-15: Creating a Pass-Through Query with ADOHands-On 15-16: Executing a Pass-Through Query Saved in Access (ADO)Performing Other Operations with QueriesRetrieving Query Properties with DAOHands-On 15-17: Listing Query Properties with DAOListing All Queries in a Database with DAO/ADOHands-On 15-18: Listing Queries in a Database with ADODeleting a Query from a Database with DAO/ADOHands-On 15-19: Deleting a Query from a Database (DAO)Hands-On 15-20: Deleting a Query from a Database (ADO)Determining if a Query Is UpdatableHands-On 15-21: Determining if Records Returned by a Query Can Be Edited (DAO)Chapter Summary
Fabricating a RecordsetHands-On 16-1: Creating a Custom Recordset (ADO)Disconnected RecordsetsHands-On 16-2: Creating a Disconnected Recordset (ADO)Saving a Recordset to DiskHands-On 16-3: Saving Records to a Disk File (ADO)Custom Project 16-1: Filling a Combo Box with a Disconnected Recordset (ADO)Custom Project 16-2: Taking Persisted Data on the RoadPart 1: Saving a Recordset to DiskPart 2: Creating an Unbound Access Form to View and Modify DataPart 3: Writing Procedures to Control the Form and Its DataPart 4: Viewing and Editing Data OfflinePart 5: Connecting to a Database to Update the Original DataCloning a RecordsetCustom Project 16-3: Displaying the Contents of the Current and Previous Record by Using the Clone MethodIntroduction to Data ShapingWriting a Simple SHAPE StatementWorking with Data ShapingHands-On 16-4: Creating a Shaped Recordset (ADO)Writing a Complex SHAPE StatementShaped Recordsets with Multiple ChildrenHands-On 16-5: Creating a Shaped Recordset with Multiple Children (ADO)Shaped Recordsets with GrandchildrenCustom Project 16-4: Using Hierarchical RecordsetsPart 1: Creating a Form with a TreeView ControlPart 2: Writing an Event Procedure for the Form Load EventTransaction ProcessingCreating a Transaction with ADOHands-On 16-6: Using a Database Transaction to Insert Records (ADO)Creating a Transaction with DAOHands-On 16-7: Using a Database Transaction to Archive Records (DAO)Chapter Summary
Two Types of Security in Microsoft AccessShare-Level Security (in Access ACCDB and MDB File Formats)User-Level SecurityUnderstanding Workgroup Information FilesCreating and Joining Workgroup Information FilesCustom Project 17-1: Securing a Microsoft Access MDB DatabasePart 1: Creating a New Access Database in the MDB File FormatPart 2: Establishing User-Level SecurityPart 3: Opening a Secured MDB DatabaseCreating and Managing Group and User AccountsHands-On 17-1: Creating User and Group Accounts (ADO)Deleting User and Group AccountsHands-On 17-2: Deleting User and Group Accounts (ADO)Listing User and Group AccountsHands-On 17-3: Listing Group and User Accounts (ADO)Listing Users in GroupsHands-On 17-4: Listing Users in Groups (ADO)Setting and Retrieving User and Group PermissionsDetermining the Object OwnerHands-On 17-5: Retrieving the Name of the Object Owner (ADO)Setting User Permissions for an ObjectHands-On 17-6: Setting User Permissions for an Object (ADO)Setting User Permissions for a DatabaseHands-On 17-7: Setting User Permissions for a Database (ADO)Setting User Permissions for ContainersHands-On 17-8: Setting User Permissions for Containers (ADO)Checking Permissions for ObjectsHands-On 17-9: Checking Permissions for a Specific Object (ADO)Setting a Database Password Using the CompactDatabase MethodHands-On 17-10: Setting a Database Password (JRO)Setting a Database Password Using the NewPassword MethodHands-On 17-11: Setting a Database Password (DAO)Changing a User PasswordHands-On 17-12: Changing a User Password (ADO)Encrypting a Secured MDB DatabaseHands-On 17-13: Encrypting a Database (JRO)Chapter Summary
Creating a Design MasterHands-On 18-1: Creating a Design MasterCreating a Full ReplicaHands-On 18-2: Creating a Full ReplicaCreating a Partial ReplicaHands-On 18-3: Creating a Partial ReplicaReplicating ObjectsHands-On 18-4: Checking If an Object Is ReplicableHands-On 18-5: Making an Object ReplicableKeeping Objects LocalHands-On 18-6: Preventing an Object from Being ReplicatedSynchronizing ReplicasHands-On 18-7: Synchronizing ReplicasRetrieving Replica PropertiesHands-On 18-8: Retrieving Replica PropertiesSynchronization ConflictsCustom Project 18-1: Replication via Microsoft Access User InterfacePart 1: Creating a Replica Named Replica_England.mdbPart 2: Creating a Replica Named Replica_France.mdbPart 3: Making Changes in the Replica_England DatabasePart 4: Making Changes in the Replica_France DatabasePart 5: Synchronizing ReplicasHands-On 18-9: Retrieving the Name of the Conflict TableChapter Summary
Hands-On 19-1: Setting the ANSI SQL Query ModeCreating TablesHands-On 19-2: Creating a Table in the Current Database (DDL with ADO)Hands-On 19-3: Creating a Table in a New Database (DDL with ADO/ADOX)Deleting TablesHands-On 19-4: Deleting a TableModifying Tables with DDLAdding New Fields to a TableHands-On 19-5: Adding a New Field to an Existing TableChanging the Data Type of a Table ColumnHands-On 19-6: Changing the Field Data TypeChanging the Size of a Text ColumnHands-On 19-7: Changing the Size of a FieldDeleting a Column from a TableHands-On 19-8: Deleting a Field from a TableAdding a Primary Key to a TableHands-On 19-9: Adding a Primary Key to a TableAdding a Multiple-Field Index to a TableHands-On 19-10: Adding a Unique Index Based on Two Fields to an Existing TableDeleting an Indexed ColumnHands-On 19-11: Deleting a Field that Is a Part of an IndexDeleting an IndexHands-On 19-12: Deleting an IndexSetting a Default Value for a Table ColumnHands-On 19-13: Setting a Default Value for a FieldChanging the Seed and Increment Value of AutoNumber ColumnsHands-On 19-14: Changing the Start (Seed) Value of the AutoNumber FieldChapter Summary
Using CHECK ConstraintsHands-On 20-1: Using a CHECK Constraint to Specify a Condition for All Values Entered for the ColumnHands-On 20-2: Creating a Table with a Validation Rule Referencing a Column in Another TableHands-On 20-3: Deleting Tables and Constraints Using the Access User InterfaceEstablishing Relationships between TablesHands-On 20-4: Relating Two Tables and Setting up Cascading Referential Integrity RulesUsing the Data Definition Query WindowHands-On 20-5: Running Data Definition Statements in the Microsoft Access User InterfaceChapter Summary
Creating Tables with IndexesHands-On 21-1: Creating a Table with a Single-Field IndexAdding an Index to an Existing TableHands-On 21-2: Adding a Single-Field Index to an Existing TableCreating a Table with a Primary KeyHands-On 21-3: Creating a Single-Field Primary KeyCreating Indexes with RestrictionsHands-On 21-4: Creating a Primary Key Index with RestrictionsHands-On 21-5: Creating an Index that Disallows Null Values in the KeyHands-On 21-6: Creating an Index with the Ignore Null OptionDeleting IndexesHands-On 21-7: Deleting an IndexChapter Summary
Setting the Database PasswordHands-On 22-1: Setting a Database PasswordRemoving the Database PasswordHands-On 22-2: Deleting a Database PasswordCreating a User AccountHands-On 22-3: Creating a User AccountChanging a User PasswordHands-On 22-4: Changing a User PasswordCreating a Group AccountHands-On 22-5: Creating a Group AccountAdding Users to GroupsHands-On 22-6: Making a User Account a Member of a GroupRemoving a User from a GroupHands-On 22-7: Removing a User Account from a GroupDeleting a User AccountHands-On 22-8: Deleting a User AccountGranting Permissions for an ObjectHands-On 22-9: Granting Permissions for Tables to an Existing GroupRevoking Security PermissionsHands-On 22-10: Revoking Security PermissionsDeleting a Group AccountHands-On 22-11: Deleting a Group AccountChapter Summary
Creating a ViewHands-On 23-1: Creating a View Based on a TableEnumerating ViewsHands-On 23-2: Generating a List of Saved ViewsDeleting a ViewHands-On 23-3: Deleting a ViewCreating a Stored ProcedureCreating a Parameterized Stored ProcedureHands-On 23-4: Creating a Stored Procedure that Accepts ParametersExecuting a Parameterized Stored ProcedureHands-On 23-5: Executing a Parameterized Stored ProcedureDeleting a Stored ProcedureHands-On 23-6: Deleting a Stored ProcedureChanging Database Records with Stored ProceduresChapter Summary
Data EventsCurrentHands-On 24-1: Writing the Form_Current Event ProcedureBeforeInsertHands-On 24-2: Writing the Form_BeforeInsert Event ProcedureAfterInsertHands-On 24-3: Writing the Form_AfterInsert Event ProcedureBeforeUpdateHands-On 24-4: Writing the Form_BeforeUpdate Event ProcedureAfterUpdateHands-On 24-5: Writing the Form_AfterUpdate Event ProcedureDirtyHands-On 24-6: Writing the Form_Dirty Event ProcedureOnUndoDeleteHands-On 24-7: Writing the Form_Delete Event ProcedureBeforeDelConfirmHands-On 24-8: Writing the Form_BeforeDelConfirm Event ProcedureAfterDelConfirmHands-On 24-9: Writing the Form_AfterDelConfirm Event ProcedureFocus EventsActivateHands-On 24-10: Writing the Form_Activate Event ProcedureDeactivateHands-On 24-11: Writing the Form_Deactivate Event ProcedureGotFocusLostFocusMouse EventsClickHands-On 24-12: Writing the Form_Click Event ProcedureDblClickMouseDownHands-On 24-13: Writing the Form_MouseDown Event ProcedureMouseMoveMouseUpMouseWheelKeyboard EventsKeyDownHands-On 24-14: Writing the Form_KeyDown Event ProcedureKeyPressHands-On 24-15: Writing the Form_KeyPress Event ProcedureKeyUpHands-On 24-16: Writing the Form_KeyUp Event ProcedureError EventsErrorHands-On 24-17: Writing the Form_Error Event ProcedureFilter EventsFilterHands-On 24-18: Writing the Form_Filter Event ProcedureApplyFilterHands-On 24-19: Writing the Form_ApplyFilter Event ProcedureTiming EventsTimerHands-On 24-20: Writing the Form_Timer Event ProcedurePivotTable/PivotChart EventsReferencing the Microsoft Office Web Components Object LibraryData Source EventsOnConnectHands-On 24-21: Writing the Form_OnConnect Event ProcedureOnDisconnectBeforeQueryQueryHands-On 24-22: Writing the Form_BeforeQuery and Form_Query Event ProceduresDisplay EventsBeforeScreenTipAfterLayoutHands-On 24-23: Writing the Form_AfterLayout Event ProcedureBeforeRenderHands-On 24-24: Writing the Form_BeforeRender Event ProcedureAfterRenderHands-On 24-25: Writing the Form_AfterRender Event ProcedureAfterFinalRenderHands-On 24-26: Writing the Form_AfterFinalRender Event ProcedureChange EventsDataChangeHands-On 24-27: Writing the Form_DataChange Event ProcedureDataSetChangePivotTableChangeHands-On 24-28: Writing the Form_PivotTableChange Event ProcedureSelectionChangeHands-On 24-29: Writing the Form_SelectionChange Event ProcedureViewChangeHands-On 24-30: Writing the Form_ViewChange Event ProcedureCommand EventsCommandEnabledHands-On 24-31: Writing the Form_CommandEnabled Event ProcedureCommandCheckedHands-On 24-32: Writing the Form_CommandChecked Event ProcedureCommandBeforeExecuteCommandExecuteHands-On 24-33: Writing the Form_CommandExecute Event ProcedureKeyboard and Mouse EventsEvents Recognized by Form SectionsDblClick (Form Section Event)Hands-On 24-34: Writing the Detail_DblClick Event ProcedureChapter Summary
OpenHands-On 25-1: Writing the Report_Open Event ProcedureCloseActivateHands-On 25-2: Writing the Report_Activate Event ProcedureDeactivateNoDataHands-On 25-3: Writing the Report_NoData Event ProcedurePageHands-On 25-4: Writing the Report_Page Event ProcedureErrorHands-On 25-5: Writing the Report_Error Event ProcedureEvents Recognized by Report SectionsFormat (Report Section Event)Hands-On 25-6: Writing the Detail_Format Event ProcedureHands-On 25-7: Writing the PageFooterSection_Format Event ProcedurePrint (Report Section Event)Hands-On 25-8: Writing the Detail_Print Event ProcedureRetreatChapter Summary
Hands-On 26-1: Launching the Custom Access ApplicationEnter (Control)Hands-On 26-2: Using the Enter Event Procedure for the Combo Box ControlBeforeUpdate (Control)Hands-On 26-3: Using the BeforeUpdate Event Procedure for the Combo Box ControlAfterUpdate (Control)Hands-On 26-4: Using the AfterUpdate Event Procedure for the Combo Box ControlNotInList (Control)Hands-On 26-5: Using the NotInList Event Procedure for the Combo Box ControlClick (Control)Hands-On 26-6: Using the Click Event Procedure for the Command Button ControlDblClick (Control)Hands-On 26-7: Using the DblClick Event Procedure for the List Box ControlChapter Summary
Sinking Events in Standalone Class ModulesCustom Project 27-1: Sinking Events in a Standalone Class ModulePart 1: File PreparationPart 2: Creating the cRecordLogger ClassPart 3: Creating an Instance of the Custom Class in the Form’s Class ModulePart 4: Testing the cRecordLogger Custom ClassPart 5: Using the cRecordLogger Custom Class with another FormWriting Event Procedure Code in Two PlacesResponding to Control Events in a ClassHands-On 27-1: Responding to Control Events in a ClassDeclaring and Raising EventsHands-On 27-2: Declaring and Raising EventsChapter Summary
Introduction to Active Server PagesHands-On 28-1: Creating an Active Server Page from a Microsoft Access TableThe ASP Object ModelInstalling Internet Information Services (IIS)Hands-On 28-2: Installing Internet Information Services (Windows 2000/ Windows XP)Creating a Virtual DirectoryHands-On 28-3: Creating a Virtual DirectoryHands-On 28-4: Requesting an ASP PageRetrieving RecordsHands-On 28-5: Creating an ASP File to Retrieve RecordsBreaking Up a Recordset When Retrieving RecordsHands-On 28-6: Creating a Multi-Page ASP FileHands-On 28-7: Creating an ASP File for Loading from a HyperlinkRetrieving Records with the GetRows MethodHands-On 28-8: Quick Data RetrievalDatabase Lookup Using Drop-Down ListsHands-On 28-9: Creating a Web Page with a Drop-Down List BoxDatabase Lookup Using a Multiple-Selection List BoxHands-On 28-10: Creating a Web Page with a Multiple-Selection List BoxAdding Data into a TableHands-On 28-11: Creating a Data Input PageModifying a RecordHands-On 28-12: Creating a Page for Data ModificationDeleting a RecordHands-On 28-13: Creating Pages that Allow Record DeletionsCreating a Web User Interface for Database AccessHands-On 28-14: Providing Easy Access to Data with FramesChapter Summary
What Is XML?XML Support in Access 2007Exporting XML DataHands-On 29-1: Exporting an Access Table to an XML FileUnderstanding the XML Data FileHands-On 29-2: Examining the Contents of an XML Data FileUnderstanding the XML Schema FileHands-On 29-3: Examining the Contents of an XML Schema FileUnderstanding the XSL Transformation FilesHands-On 29-4: Examining the Contents of an XSL FileViewing XML Documents Formatted with StylesheetsAdvanced XML Export OptionsData Export OptionsSchema Export OptionsPresentation Export OptionsApplying XSLT Transforms to Exported DataHands-On 29-5: Creating a Custom Transformation FileHands-On 29-6: Exporting Data and Applying a Custom XSL FileImporting XML DataHands-On 29-7: Importing a Schema File (XSD) to an Access DatabaseCustom Project 29-1: Importing XML Data to an Access Database and Applying a TransformPart 1: Create a Custom Transformation File to Be Used after the XML Data ImportPart 2: Export the Customers and Related Orders Tables to an XML FilePart 3: Import to an Access Database Only Two Columns from the Customers Table and Five Columns from the Orders TableExporting to and Importing from XML ProgrammaticallyExporting to XML Using the ExportXML MethodHands-On 29-8: Exporting Multiple Tables to an XML Data FileCustom Project 29-2: Exporting an Access Report to an XML Data File with ASPPart 1: Creating a VBA Procedure to Export Invoice DataPart 2: Executing the VBA Code to Export DataPart 3: Viewing the Invoice Page in the BrowserPart 4: Examining the Content of the Invoice.htm FileTransforming XML Data with the TransformXML MethodCustom Project 29-3: Applying a Stylesheet to an XML Data File with the TransformXML MethodPart 1: Creating a Custom Stylesheet for Transforming an XML Source File into Another XML Data FilePart 2: Writing a VBA Procedure to Export and Transform DataPart 3: Importing the Transformed XML Data File to AccessImporting to XML Using the ImportXML MethodManipulating XML Documents ProgrammaticallyLoading and Retrieving the Contents of an XML FileHands-On 29-9: Loading and Retrieving the Contents of an XML FileWorking with XML Document NodesHands-On 29-10: Working with XML Document NodesRetrieving Information from Element NodesHands-On 29-11: Retrieving Information from Element NodesRetrieving Specific Information from Element NodesHands-On 29-12: Retrieving Specific Information from Element NodesRetrieving the First Matching NodeUsing ActiveX Data Objects with XMLSaving an ADO Recordset as XML to DiskHands-On 29-13: Creating an XML Document from ADOAttribute-Centric and Element-Centric XMLChanging the Type of an XML FileHands-On 29-14: Creating a Stylesheet to Convert Attribute-Centric XML to Element-Centric XMLApplying an XSL StylesheetHands-On 29-15: Applying a Stylesheet to an ADO XML Document and Importing It to AccessTransforming Attribute-Centric XML Data into an HTML TableHands-On 29-16: Creating a Generic Stylesheet to Transform an Attribute-Centric XML File into HTMLHands-On 29-17: Linking the Attribute-Centric XML File with the Generic Stylesheet and Displaying the Transformed File in a Web BrowserLoading an XML Document in ExcelHands-On 29-18: From Access to Excel: Loading an XML File into an Excel WorkbookChapter Summary
The Getting Started with Microsoft Office Access WindowThe Office ButtonThe Quick Access ToolbarCustomizing the Navigation PaneHands-On 30-1: Adding a Custom Group to the Navigation PaneHands-On 30-2: Assigning Objects to Custom Groups in the Navigation PaneUsing VBA to Customize the Navigation PaneLocking the Navigation PaneControlling the Display of Database ObjectsHands-On 30-3: Using the NavigateTo Method to Control the Display of Database Objects in the Navigation PaneSetting Displayed CategoriesSaving and Loading the Configuration of the Navigation PaneHands-On 30-4: Saving and Loading the Configuration of the Navigation PaneA Quick Overview of Access 2007 Ribbon InterfaceRibbon Programming with XML, VBA, and MacrosCreating the Ribbon Customization XML MarkupHands-On 30-5: Creating an XML Document with Ribbon CustomizationsLoading Ribbon Customizations from an External XML DocumentCustom Project 30-1: Applying Ribbon Customizations from an External XML FilePart 1: Creating an Access Database and Setting Access OptionsPart 2: Setting Up the Programming EnvironmentPart 3: Writing VBA CodePart 4: Creating an Autoexec MacroPart 5: Applying the Customized RibbonEmbedding Ribbon XML Markup in a VBA ProcedureStoring Ribbon Customization XML Markup in a TableHands-On 30-6: Creating a Local System Table to Store Ribbon CustomizationAssigning Ribbon Customizations to Forms and ReportsCustom Project 30-2: Creating and Assigning Ribbon Customization to a ReportPart 1: Creating Ribbon Customization for a Report Using a Local System TablePart 2: Making Access Aware of the New CustomizationPart 3: Assigning a Ribbon Customization to a ReportUsing Images in Ribbon CustomizationsRequesting Images via the loadImage CallbackCustom Project 30-3: Loading Custom Images Using the loadImage CallbackPart 1: Creating Ribbon Customization for Loading Custom ImagesPart 2: Setting Up the Programming EnvironmentPart 3: Writing the VBA Callback ProceduresPart 4: Making Access Aware of the New CustomizationRequesting Images via the getImage CallbackHands-On 30-7: Loading Custom Images Using the getImage CallbackUnderstanding Attributes and CallbacksUsing Various Controls in Ribbon CustomizationsCreating Toggle ButtonsCreating Split Buttons, Menus, and SubmenusCreating Check BoxesCreating Edit BoxesCreating Combo Boxes and Drop DownsCreating a Dialog Box LauncherDisabling a ControlRepurposing a Built-in ControlRefreshing the RibbonThe CommandBars Object and the RibbonCustomizing the Office Button MenuChapter Summary
New Features in TablesSaving Tables as QueriesWorking with Attachment Data Type FieldsAdding Attachments Using the Attachments Dialog BoxHands-On 31-1: Adding Attachments to Database RecordsUsing an Access Query to Return Rows with AttachmentsHands-On 31-2: Using an Access Query to Return Rows with AttachmentsWorking with Attachments Using VBAListing AttachmentsHands-On 31-3: Using DAO to Retrieve the List of AttachmentsRetrieving the Fields Defined by the Attachment Data TypeSaving AttachmentsHands-On 31-4: Using DAO to Save Attachments to DiskAdding AttachmentsHands-On 31-5: Using DAO to Add an Attachment to a Table RecordDeleting AttachmentsHands-On 31-6: Using DAO to Delete an Attachment from a Table RecordWorking with Multi-value FieldsHands-On 31-7: Creating a Multi-value Lookup FieldEditing Values in a Multi-value FieldUsing an Access Query to Return Rows with Multi-value FieldsHands-On 31-8: Using an Access Query to Return Rows with Multi-value FieldsWorking with Multi-value Fields Using VBAHands-On 31-9: Using DAO to Manipulate Multi-value FieldsAdding Values to Multi-value Lookup FieldsHands-On 31-10: Using DAO to Add Values to a Multi-value FieldWorking with Append Only Memo FieldsWorking with Append Only Memo Fields Using VBACustom Project 31-1: Working with Append Only FieldsPart 1: Writing a VBA Procedure to Create a Table with an Append Only Memo FieldPart 2: Entering Data in the TablePart 3: Writing a VBA Procedure to Retrieve the History Data from the Memo FieldRich Text Memo FieldsNew Features in FormsMany Ways to Create Access FormsGrouping Controls Using LayoutsRich Text Support in FormsAutoFormatsUsing Images in Access FormsUsing the Attachments ControlCustom Project 31-2: Working with the Attachments ControlPart 1: Generating an Access FormPart 2: Modifying the Form to Display Additional Information about the AttachmentsPart 3: Writing an Event Procedure to Display Information about Attachments in the Text BoxesNew Features in ReportsUsing the Report ViewHands-On 31-11: Conditionally Formatting a Control in the Report ViewSorting and Grouping DataSaving Reports in the PDF or XPS File FormatChapter Summary
New Features in MacrosAccess 2007 Macro SecurityUsing the AutoExec MacroHands-On 32-1: Understanding and Using the AutoExec MacroUnderstanding Macro Actions, Arguments, and ConditionsCreating Macros in Access 2007Creating and Using Standalone MacrosUsing Macro GroupsRunning Standalone MacrosCreating and Using Embedded MacrosCopying Embedded MacrosHands-On 32-2: Copying Embedded MacrosWorking in Sandbox ModeError Handling in MacrosUsing Temporary Variables in MacrosConverting Macros to VBA CodeConverting a Standalone Macro to VBAConverting Embedded Macros to VBANew Features in TemplatesCreating a Custom Blank TemplateUnderstanding the ACCDT File FormatChapter Summary

Overview

Access 2007 Programming by Example with VBA, XML, and ASP shows non-programmers how Access databases can be created, managed, and customized with Visual Basic for Applications (VBA) — a powerful programming language built into Access. Hundreds of hands-on examples and projects throughout the book show users how to take charge of their Access databases with programming.
Learn how to Write and debug your programming code with the Visual Basic Editor; access and manipulate databases with Data Access Objects (DAO) and ActiveX Data Objects (ADO); use the Data Definition Language (DDL) to enforce data integrity and manage database security; modify the behavior of forms, reports, and controls by writing event procedures; publish dynamic Access data to the web using Active Server Pages (ASP) and Extensible Markup Language (XML); and work with the new features for tables, forms, reports, macros, and templates that are available in the Access 2007 user interface.