book

Special Edition Using Microsoft® Access 2000

by Roger Jennings

May 1999

Intermediate to advanced

1300 pages

35h 28m

English

Que

Read now

Unlock full access

Dedication
About the AuthorAcknowledgmentsTell Us What You Think!
Who Should Read this BookHow this Book Is OrganizedPart I: Learning Access FundamentalsPart II: Getting the Most Out of QueriesPart III: Designing Forms and ReportsPart IV: Publishing Data on Intranets and the InternetPart V: Integrating Access with Other Office 2000 ApplicationsPart VI: Using Advanced Access TechniquesPart VII: Programming and Converting Access ApplicationsGlossaryThe Accompanying CD-ROMHow this Book Is DesignedTypographic Conventions Used in this BookKey Combinations and Menu ChoicesSQL Statements and Keywords in Other LanguagesTypographic Conventions Used for VBASystem Requirements for Access 2000Other Sources of Information for AccessBooksPeriodicalsInternet
Putting Access 2000 in PerspectiveDeploying Data Access Pages with Office Web ComponentsThe Office Web ComponentsTest Drive a DAPDAP Design ModeCreating Access Data Projects for the Microsoft Data EngineNew ADP Tools and ObjectsExplore the NorthwindCS Project and da Vinci ToolsetIntegrating the Office VBA Editor with AccessWorking with the HTML Source Editor for DAP and DHTMLViewing and Editing Related Records in SubdatasheetsConforming Jet and SQL Server 7.0/MSDE DatabasesGiving Access a Year 2000 FaceliftFour-Digit Year Option SettingsThe Database WindowForms and ReportsName AutoCorrectOffice 2000-Related Enhancements or ImpedimentsSynthetic Single-Document InterfaceIntellimenusHTML HelpOnline CollaborationInstalling Access 2000Making an Initial Installation of Access 2000Customizing Access 2000Installing and Configuring the Microsoft Data EngineIn the Real World—Why Upgrade?
Understanding Access's Approach to Application DesignCreating an Access Application from a Template FileTouring the Contact Management ApplicationTable Objects in the Database WindowThe Switchboard FormAccess FormsAccess ReportsAccess ModulesUsing the Switchboard ManagerExploring Form Design View and VBA Class ModulesThe Real World—Putting What You've Learned in Perspective
Understanding Access Functions and ModesDefining Access FunctionsDefining Access Operating ModesUnderstanding Access's Table DisplayMaximized Document WindowsDocument Windows Minimized to IconsThe Toolbars in Table Datasheet ViewThe Table Datasheet ToolbarThe Datasheet Formatting ToolbarToolbar CustomizationRight-Click Shortcut MenusUsing the Function KeysGlobal Function KeysFunction-Key Assignments for Fields, Grids, and Text BoxesSetting Default OptionsSystem DefaultsView OptionsGeneral OptionsEdit/Find OptionsKeyboard OptionsAdvanced OptionsDefaults for Datasheet ViewUsing Access HelpContext-Sensitive HelpThe Help MenuThe Microsoft Access Help WindowThe Contents PageUnderstanding the Help WindowThe Answer Wizard PageThe Index PageThe Help Window OptionsThe Office AssistantUsing the Database UtilitiesCompacting and Repairing DatabasesConverting Databases to Access 2000 FormatConverting Databases to Access 97 FormatAdding a Switchboard FormCreating .mde FilesTroubleshootingCompile Errors in the Convert Database ProcessIn the Real World—HTML Help or Hindrance
Defining the Elements of Access DatabasesUnderstanding Relational DatabasesUsing Access Database Files and TablesThe Access System DatabaseAccess Library DatabasesCreating a New DatabaseUnderstanding the Properties of Tables and FieldsChoosing Field Data Types, Sizes, and FormatsChoosing Field Sizes for Numeric and Text DataSubtypes for Numeric DataFixed-Width Text FieldsSubtypes for the OLE Object Data TypeSelecting a Display FormatStandard Formats for Number, Date/Time, and Yes/No Data TypesThe Null Value in Access TablesCustom Display FormatsUsing Input MasksUsing the Northwind Traders Sample DatabaseUsing the Table Wizard to Create New TablesAdding a New Table to an Existing DatabaseDesigning the Personnel Actions TableDetermining What Information the Table Should IncludeAssigning Information to FieldsCreating the Personnel Actions TableCreating a Table Directly in Datasheet ViewSetting Default Values of FieldsWorking with Relations, Key Fields, and IndexesEstablishing Relationships Between TablesEnforcing Referential IntegrityUnderstanding How Referential Integrity Is EnforcedCascading Updates and DeletionsSelecting a Primary KeyAdding Indexes to TablesAltering Fields and RelationshipsRearranging the Sequence of Fields in a TableChanging Field Data Types and SizesNumeric FieldsText FieldsConversion Between Number, Date, and Text Field Data TypesChanging Relationships Between TablesCopying and Pasting TablesTroubleshootingGaps in AutoNumber Field ValuesExtra Indexes Added by AccessIn the Real World—Database Strategy and Table TacticsWhy Table Design Comes Before Database Design in This BookNaming Conventions for Tables and Fields
Entering Test Data in Access TablesUsing Keyboard Operations for Entering and Editing DataCreating an Experimental Copy of Northwind.mdbUsing Data-Entry and Editing KeysUsing Key Combinations for Windows Clipboard OperationsUsing Shortcut Keys for Fields and Text BoxesSetting Data-Entry OptionsAdding Records to a TableSelecting, Appending, Replacing, and Deleting Table RecordsValidating Data EntryAdding Field-Level Validation RulesAdding Table-Level Validation Rules and Using the Expression BuilderAdding Records to the Personnel Actions TableEntering Personnel Actions Table Data and Testing Validation RulesTroubleshootingField Property Values Cause Paste FailuresMultiple Record Selection Causes Silent Paste FailuresError Messages from Validation EnforcementIn the Real World—Heads-Down Data EntryComparing Heads-Down Keypunch Data Entry with Access's Datasheet ViewReplacing the Punched Card Verifying StepWhere Not to Use Datasheet Entry
Understanding the Role of Sorting and FilteringSorting Table DataFreezing Display of a Table FieldSorting Data on a Single FieldSorting Data on Multiple FieldsRemoving a Table Sort Order and Thawing ColumnsFinding Matching Records in a TableReplacing Matched Field Values AutomaticallyFiltering Table DataFiltering by SelectionFiltering by FormAdvanced Filters and SortsAdding a Multifield Sort and Compound Filter CriteriaUsing Composite CriteriaSaving Your Filter as a Query and Loading a FilterCustomizing Datasheet ViewCopying, Exporting, and Mailing Sorted and Filtered DataTroubleshootingFilter by Form Doesn't Find the Expected RecordsIn the Real World—Computer-Based Sorting and SearchingThe Influence of Computer Power on Knuth's ApproachKnuth and Indexes

Moving Data from and to Other ApplicationsUnderstanding How Access Handles Tables in Other Database File FormatsIdentifying PC Database File FormatsLinking and Importing External ISAM TablesLinking Visual FoxPro Tables with ODBCDealing with Images in External FilesConverting Field Data Types to Access Data TypesUsing the Linked Table Manager Add-in to Relink TablesImporting Versus Linking Database Files as TablesImporting and Linking Spreadsheet FilesCreating a Table by Importing an Excel WorksheetLinking Excel WorksheetsWorking with Microsoft Outlook and Exchange FoldersExporting and Importing Jet 4.0 Tables with OutlookLinking with the Exchange/Outlook WizardImporting Text FilesUsing the Import Text WizardThe Import Text Wizard's Advanced OptionsUsing Delimited Text FilesComma-Delimited Text Files Without Text-Identifier CharactersComma-Delimited Text Files with Text-Identifier CharactersTab-Delimited Text FilesHandling Fixed-Width Text FilesAppending Text Data to an Existing TableUsing the Clipboard to Import DataPasting New Records to a TableReplacing Records by Pasting from the ClipboardExporting Data from Access TablesExporting Data Through the Windows ClipboardExporting Data as a Text FileExporting Data in Other File FormatsTroubleshootingThe Incorrect Password DialogThe Null Value in Index DialogThe Missing Memo File DialogImporting Fixed-Width Text FilesImporting SpreadsheetsIn the Real World—Microsoft Giveth and Microsoft Taketh Away
Introducing QueriesTrying the Simple Query WizardUsing the Query Design WindowSelecting Fields for Your QuerySelecting Records by Criteria and Sorting the DisplayCreating More Complex QueriesChanging the Names of Query Column HeadersPrinting Your Query as a ReportUsing the Data from Your QueryCreating Other Types of QueriesCreating and Using a Simple Make-Table Action QueryAdding a Parameter to Your Make-Table QueryTroubleshootingMissing Required FieldsNon-Updatable Summary QueriesIn the Real World—Query Design OptimizationThe Art of Query DesignThe Scientific Side of Query Design
Writing Expressions for Query Criteria and Data ValidationUnderstanding the Elements in ExpressionsOperatorsArithmetic OperatorsAssignment and Comparison OperatorsLogical OperatorsConcatenation OperatorsIdentifier OperatorsOther OperatorsLiteralsIdentifiersFunctionsUsing the Immediate WindowThe Variant Data Type in Access and VBAFunctions for Date and TimeText-Manipulation FunctionsNumeric, Logical, Date/Time, and String Data-Type Conversion FunctionsIntrinsic and Named ConstantsCreating Access ExpressionsExpressions for Creating Default ValuesExpressions for Validating DataExpressions for Query CriteriaEntering a Query CriterionUsing the Expression Builder to Add Query CriteriaExpressions for Calculating Query Field ValuesOther Uses for ExpressionsTroubleshootingQuery Expressions Fail to ExecuteFour-Digit Years Turn into Two Digits in Query CriteriaIn the Real World—The Algebra of Access Expressions
Introducing Joins on TablesJoining Tables to Create Multitable QueriesCreating Conventional Single-Column Equi-JoinsSpecifying a Sort Order for the Query Result SetCreating Queries from Tables with Indirect RelationshipsCreating Multicolumn Equi-Joins and Selecting Unique ValuesUsing Lookup Fields in TablesAdding a Foreign-Key Dropdown List with the Lookup WizardAdding a Fixed-Value Lookup List to a TableAdding Subdatasheets to a Table or QueryTable SubdatasheetsQuery SubdatasheetsOuter, Self, and Theta JoinsCreating Outer JoinsCreating Self-JoinsCreating Not-Equal Theta Joins with CriteriaUpdating Table Data with QueriesCharacteristics That Determine Whether You Can Update a QueryFormatting Data with the Query Field Properties WindowMaking All Fields of Tables AccessibleMaking Calculations on Multiple RecordsUsing the SQL Aggregate FunctionsMaking Calculations Based on All Records of a TableMaking Calculations Based on Selected Records of a TableDesigning Parameter QueriesAdding a Parameter to the Monthly Sales QuerySpecifying the Parameter's Data TypeCreating Crosstab QueriesUsing the Wizard to Generate a Quarterly Product Sales Crosstab QueryDesigning a Monthly Product Sales Crosstab QueryUsing Fixed Column Headings with Crosstab QueriesCreating Queries from Tables in Other DatabasesTroubleshootingMissing Objects in QueriesQueries with Linked Tables Aren't UpdatableIn the Real World—Optimizing Multitable QueriesSubdatasheetsAggregate QueriesCrosstab Queries
Getting Acquainted with Action QueriesCreating New Tables with Make-Table QueriesDesigning and Testing the Select QueryConverting the Select Query to a Make-Table QueryEstablishing Relationships for the New TableUsing the New tblShipAddresses TableCreating Action Queries to Append Records to a TableDeleting Records from a Table with an Action QueryUpdating Values of Multiple Records in a TableTesting Cascading Deletion and UpdatingCreating the Test Tables and Establishing RelationshipsTesting Cascading DeletionTesting Cascading UpdatesTroubleshootingAppending Records Causes Primary Key ProblemsAccess Won't Create a Relationship to a New TableIn the Real World—Alternatives to Action QueriesBrowse-Mode UpdatingForm-Based UpdatingUpdating with SQL StatementsUpdating with SQL Server Stored Procedures
Understanding the Role of Access Forms and ControlsCreating a Transaction-Processing Form with the Form WizardChoosing Data Sources for the FormCreating the Basic Form with the Form WizardUsing the Form Design WindowElements of the Form Design WindowForm Design Toolbar Buttons and Menu ChoicesThe Formatting ToolbarDefault Values for FormsUsing AutoFormatApplying an AutoFormatCreating, Customizing, and Deleting AutoFormatsChanging an Object's ColorsBackground ColorsChanging the Background BitmapForeground Color, Border Color, and Border StyleCreating Custom Colors with the Color BuilderSelecting, Editing, and Moving Form Elements and ControlsChanging the Size of the Form Header and Form FooterSelecting, Moving, and Sizing a Single ControlAligning Controls to the GridSelecting and Moving Multiple ControlAligning a Group of ControlsUsing the Windows Clipboard and Deleting ControlsChanging the Color and Border Style of a ControlChanging the Content of Text ControlsUsing the Format PainterRearranging the Personnel Actions FormSetting Properties of the Main FormSetting the Properties of a SubformUsing Transaction-Processing FormsToolbar Buttons in Form ViewUsing the Personnel Actions FormAppending New Records to the Personnel Actions TableEditing Existing DataCommitting and Rolling Back Changes to TablesModifying the Properties of a Form or Control After TestingChanging the Order of Fields for Data EntryRemoving Fields from the Tab OrderIn the Real World—The Art of Form DesignUnderstand the AudienceDesign in Client Monitor ResolutionStrive for Consistency and Simplicity
Expanding Your Form Design RepertoireUnderstanding the Access ToolboxControl CategoriesThe ToolboxAccess's Control Wizards, Builders, and ToolbarsAccess Control WizardsAccess BuildersCustomizable ToolbarsUsing the Toolbox To Add ControlsCreating the Query on Which to Base the Main FormCreating a Blank Form with a Header and FooterAdding a Label to the Form HeaderFormatting Text and Adjusting Text Control SizesCreating Bound, Multiline, and Calculated Text BoxesAdding Text Boxes Bound to FieldsAdding a Calculated Text Box and Formatting Date/Time ValuesUsing the Clipboard with ControlsChanging the Default View and Obtaining Help for PropertiesAdding Option Groups with the WizardUsing the Clipboard to Copy Controls to Another FormUsing Combo and List BoxesUsing the Combo Box WizardUsing the Query Builder to Populate a Combo BoxCreating a Combo Box with a List of Static ValuesCreating a Combo Box to Find Specific RecordsCreating a Tab ControlAdding Tab Control PagesChanging the Page OrderDeleting a Tab Control PageSetting the Tab Control's PropertiesPlacing Other Controls on Tab PagesChanging One Control Type to AnotherCompleting the Main Personnel Actions Entry FormCreating a Subform Using the Subform/Subreport WizardModifying the Design of Continuous FormsOverriding the Field Properties of TablesAdding Page Headers and Footers for Printing FormsTroubleshootingError Messages on Copied ControlsIn the Real World—Access Wizardry
Understanding the Relationship Between Forms and ReportsCategorizing Types of Access ReportsCreating a Grouping Report with the Report WizardUsing Access's Report WindowsUsing AutoFormat and Customizing Report StylesModifying a Basic Wizard ReportDeleting, Relocating, and Editing Existing ControlsAdding Calculated Controls to a ReportChanging the Report's Record SourceAdding the Calculated FieldsAligning and Formatting Controls and Adjusting Line SpacingAligning Controls Horizontally and VerticallyFormatting ControlsAdjusting Line SpacingAdjusting Margins and Printing Conventional ReportsPreventing Widowed Records with the Group Keep Together PropertyPrinting Multicolumn Reports as Mailing LabelsTroubleshootingEliminating Empty PagesUnexpected Parameters DialogsIn the Real World—The Apocryphal Paperless Office
Creating Reports from ScratchGrouping and Sorting Report DataGrouping DataGrouping by Numeric ValuesGrouping by Alphabetic Code CharactersGrouping with SubgroupsUsing a Function to Group by RangeGrouping on Date and TimeSorting Data GroupsWorking from a Blank ReportUsing a Report as a SubreportCreating the Monthly Sales by Category ReportIncorporating SubreportsAdding a Linked Subreport to a Bound ReportUsing Unlinked Subreports and Unbound ReportsCustomizing De Novo ReportsAdding and Deleting Sections of Your ReportControlling Page Breaks and Printing Page Headers and FootersReducing the Length of ReportsAdding Other Controls to ReportsMailing Report SnapshotsViewing and Printing the Report SnapshotTroubleshootingLink Expression ErrorsReport Snapshots Won't OpenIn the Real World—The Art of Report Design
Data-Enabling Web PagesPutting Microsoft's Internet Program in PerspectiveThe Digital Nervous System and Windows DNAMicrosoft's Internet-Related Client ProductsMicrosoft's Server-Side ComponentsMicrosoft Technologies Supporting Internet-Related ProductsNavigating the Web and Intranets with HyperlinksUnderstanding Access 2000's Hyperlink Field Data TypeTesting Hyperlinks in the Northwind Orders TableEditing and Inserting Conventional HyperlinksLinking to Bookmarks in a Word DocumentSpecifying Hyperlinks to Pages on an Intranet Server or the WebAdding a ScreenTip to the HyperlinkUsing Table Hyperlinks to Open an Access ObjectUsing Hyperlinks with Access ControlsSpecifying Other Internet Uniform Resource LocatorsTroubleshootingIntranet Connection ProblemsIn the Real World—To Internet or NotMicrosoft Gets a Clue and the VirusInnoculation Against Internet Fever
Easing the Way to Web-Based Decision SupportExporting Table and Query Datasheets to HTMLCreating an Unformatted Web PageCreating a Web Page from a QueryUsing HTML TemplatesUsing the Access HTML Templates Included with Office 2000Exporting a Query Datasheet with a TemplateExporting Reports to HTMLImporting Data from HTML TablesCreating Dynamic Web PagesUnderstanding ASPCreating an ODBC Data Source for ASPExporting an Access Query to ASPTroubleshootingHTML Table Import ErrorsIn the Real World—ASP versus DAP
Moving to a New Access Form ModelUnderstanding Access's Dynamic HTML ImplementationTechnologies Supporting DHTML and DAPDOM HTML and DHTMLGetting Acquainted with DAPThe Review Products PageThe Review Orders PageThe HTML Source EditorThe Analyze Sales PivotTable PageThe Sales PageRead-Write PagesUsing the Page Wizard to Create Simple DAPUsing AutoPage to Create Columnar DAPUsing the Record Navigation Control's Filter and Sort FeaturesModifying the Design of AutoPage DAPAltering Record Navigation Control PropertiesStarting a DAP from ScratchAdding a PivotTable with the Page Field ListWorking with the PivotTable List Control in IE 5.0Altering Pivot Control Properties in IE 5.0Adding Charts to DAP with a PivotTable ListDesigning the Query for the PivotTable ListAdding and Formatting the PivotTable ListUsing the Chart Wizard to Bind an Office Chart to the PivotTable ListGenerating a Grouped PageCreating a Three-Level Hierarchical Grouped Page StructureFilling in the DetailsAdding Fields of Related Tables and CaptionsTroubleshootingDAP Lose Their StyleDAP Clients Can't Find the Jet Data SourceIn the Real World—Are DAP Ready for Prime Time?Internet Economics 101Sweet Spot(s) on the Software Elasticity Curve
Enlivening Forms and Reports with GraphicsCreating Graphs and Charts with Microsoft Graph 2000Creating the Query on Which to Base the GraphUsing the Chart Wizard to Create an Unlinked GraphModifying the Design Features of Your GraphCreating a Graph from a Crosstab QueryLinking the Graph to a Single Record of a Table or QueryUsing the Chart Web Control in PagesDesigning Queries for the Chart Web ControlAdding an Office Chart Based on the Single-Column SeriesAltering the Properties of the Office ChartAdding a Bound Object Control to a Form or ReportIncluding Photos in the Personnel Actions QueryDisplaying the Employee's Picture in the Personnel Actions FormScaling Graphic ObjectsExamining Bitmap Image File FormatsUsing the Image ControlTroubleshootingReversing the X-Axis and Legend LabelsFixing Errors When Opening OLE ObjectsIn the Real World—Visualizing DataMeaning, Significance, and VisualizationManagement by Trend Exception
Slicing and Dicing Data with PivotTablesUsing the Access PivotTable WizardCreating the Query for the PivotTableGenerating a PivotTable Form with the WizardManipulating PivotTables in ExcelImproving PivotTable FormattingSlicing PivotTable DataSlicing by FilteringCollapsing or Expanding the Display of Detail DataChanging a Dimension's AxisFormatting PivotTable ReportsCreating a PivotChart from a PivotTableUsing Excel as an OLE ServerEmbedding a Conventional Excel Worksheet in a FormExtracting Values from an OLE ObjectLinking to a Range of Cells in an Excel WorksheetTroubleshootingDisabled Refresh Data Menu ChoiceExcel Workbook Won't Embed in an Object FrameExcel Adornments Don't Appear in Activated Object FrameIn the Real World—OLAP and PivotTablesMeasures and DimensionsSo What's OLAP Have to Do with Access 2000?
Integrating Access 2000 with Word 2000Using the Access Mail Merge WizardCreating and Previewing a New Form LetterUsing an Existing Main Merge Document with a New Data SourceUsing Word 2000's Mail Merge Feature with Access DatabasesCreating a New Mail Merge Data Source with Microsoft Query and an ODBC Data SourceCreating Form Letters from an Existing QueryEmbedding or Linking Word Documents in Access TablesEmbedding or Linking a Word 2000 Document in a TableCreating a Form to Display the Embedded DocumentTroubleshootingMissing OLE Server Registry EntriesIn the Real World—Microsoft Query and OLE DB
Reviewing Access 2000's New Database Design FeaturesIntegrating Objects and Relational DatabasesUnderstanding Database SystemsThe Objectives of Database DesignThe Process of Database DesignThe Object-Oriented Approach to Database DesignConsidering Static and Dynamic Properties of ObjectsDescribing Data Entities and Their AttributesAccounting for the Behavior of Objects with MethodsCombining Different Entities in a Single TableDatabase TerminologyTypes of Tables and Keys in Relational DatabasesData ModelingApplication DatabasesSubject DatabasesDiagrammatic Data ModelsDatabase SchemaNormalizing Data to the Relational ModelNormalization RulesFirst Normal FormSecond Normal FormThird Normal FormFourth Normal FormFifth Normal Form and Combined EntitiesTypes of RelationshipsOne-to-One RelationshipsOne-to-Many RelationshipsMany-to-One RelationshipsMany-to-Many Relationships and Fourth Normal FormUsing Access 2000's Table Analyzer WizardGenerating a Data Dictionary with the Database DocumenterUsing Access IndexesEnforcing Database IntegrityEnsuring Entity Integrity and AuditabilityMaintaining Referential IntegrityTroubleshootingAutoNumber Starting Value ErrorsIn the Real World—Why Learn Relational Theory?
Understanding the Role of SQL in Access 2000Using Access to Learn SQLUnderstanding SQL GrammarWriting SELECT Queries in SQLUsing SQL Punctuation and SymbolsUsing SQL Statements to Create Access QueriesUsing the SQL Aggregate FunctionsCreating Joins with SQLUsing UNION QueriesImplementing SubqueriesWriting Action and Crosstab QueriesAdding IN to Use Tables in Another DatabaseWorking with Another Access DatabaseUsing the IN Clause with Other Types of DatabasesCreating Tables with Jet DDLComparing ANSI and Jet SQLSQL Reserved Words in AccessJet SQL Reserved Words Corresponding to ANSI SQL KeywordsAccess Functions and Operators Used in Place of ANSI SQL KeywordsJet SQL Reserved Words, Operators, and Functions Not in ANSI SQLJet's DISTINCTROW and SQL's DISTINCT KeywordsCommon ANSI SQL Keywords and Features Not Supported by Jet SQL Reserved WordsUsing SQL Statements in Forms, Reports, and MacrosTroubleshootingUnexpected Enter Parameter DialogsIn the Real World—SQL As a Second LanguageMaking Custom Queries Easy for UsersMicrosoft English Query
Networking Access 2000 ApplicationsInstalling Access in a Networked EnvironmentSharing Your Access Database Files with Other UsersCreating a Folder and System File for File SharingPreparing to Share Your Database FilesSplitting Databases for File SharingFixing Links, Data Sources, and Hyperlinks of DAPUpdating Database Window Links to DAPChanging the Data Source of DAPChoosing Workgroups with the Workgroup AdministratorUsing Command-Line Options to Open a Shared DatabaseMaintaining Database SecuritySpecifying the Principles of Database Security on a LANPassword-Protecting a Single DatabaseManaging Groups and UsersEstablishing Your Own Admins Name, Password, and PIDEstablishing Members of Access GroupsAdding a New GroupDeleting Users and GroupsClearing Forgotten PasswordsUnderstanding Database Object OwnershipOwner Permissions for ObjectsChanging the Ownership of Database ObjectsGranting and Revoking Permissions for Database ObjectsUsing the Security Wizard to Change PermissionsAltering Group Permissions ManuallyGranting Additional Permissions to Specific UsersGranting Permissions for a Database in Another WorkgroupSharing Databases on the NetworkSharing Database Files on a Windows 9x NetworkSharing Files with User-Level SecuritySharing Database Files from a Network ServerAccessing the Shared Workgroup and Data FilesAttaching the Shared Workgroup System FileRefreshing the Links to the Shared Data FileAdministering Databases and ApplicationsBacking Up and Restoring DatabasesCompacting and Repairing Database FilesEncrypting and Decrypting Database FilesTroubleshootingInvalid Path ErrorsIn the Real World—Shared-File versus Client/Server Back Ends
Moving Access to the Client/Server ModelUnderstanding the Role of MSDESQL Server Versions and FeaturesMSDE BenefitsInstalling and Starting MSDEGetting Acquainted with ADPAccommodating MSDE and SQL Server 7.0 FeaturesRunning the NorthwindCS Sample ProjectUsing the Project DesignerWorking with MSDE TablesExploring MSDE ViewsDiagramming Table RelationshipsWriting Stored ProceduresAdding Triggers to a TableUsing the Upsizing Wizard to Create ADPRunning the Access Upsizing WizardChecking the Wizard's Successes and FailuresDownsizing Databases with the DTS WizardConnecting to Remote MSDE DatabasesUsing the SQL Server Client Network UtilityTesting and Using Remote DatabasesEstablishing MSDE SecurityTroubleshootingRemote Database Connection ProblemsIn the Real World—ADP on TrialADP DrawbacksSQL Server Advantages—ADP, MSDE, and SQL Server 7.0
Understanding the Role of VBA in AccessIntroducing VBA 6.0Where You Use VBA CodeTypographic and Naming Conventions Used for VBAModules, Functions, and SubproceduresElements of ModulesReferences to VBA and Access ModulesData Types and Database Objects in VBAVariables and Naming ConventionsImplicit VariablesExplicit VariablesScope and Duration of VariablesUser-Defined Data TypesVBA ArraysNamed Database Objects as Variables in VBA CodeObject Properties and the With…End With StructureSymbolic ConstantsAccess System-Defined ConstantsAccess Intrinsic ConstantsVBA Named and Optional ArgumentsControlling Program FlowBranching and LabelsSkipping Blocks of Code with GoToAvoiding Spaghetti Code by Not Using GoToConditional StatementsThe If…Then…End If StructureThe Select Case…End Select ConstructRepetitive Operations: LoopingUsing the For…Next StatementUsing For…Next Loops to Assign Values to Array ElementsUnderstanding the Do While…Loop and Do Until…LoopMaking Sure Statements in a Loop Occur at Least OnceHandling Runtime ErrorsDetecting the Type of Error with the Err ObjectUsing the Error Event in Form and Report ModulesExploring the VBA EditorThe Toolbar of the Module WindowModule Shortcut KeysThe VBA Help SystemExamining the Utility Functions ModuleAdding a Breakpoint to the IsLoaded() FunctionPrinting to the Immediate Window with the Debug ObjectUsing Text Comparison OptionsIn the Real World—Macro Schizophrenia
Gaining a Perspective on Microsoft's New Data Access ComponentsInterfacing with a Wide Range of Data SourcesRedesigning from the Bottomup with OLE DBMapping OLE DB Interfaces to ADOComparing ADO and DAO ObjectsCreating ADODB.RecordsetsDesigning a Form Bound to an ADODB.Recordset ObjectBinding Controls to a Recordset with CodeMaking the Form UpdatableConnecting to the NorthwindCS MSDE DatabaseSubstituting DAO 3.6 for ADO 2.1Exploring Top-Level ADO Properties, Methods, and EventsObject Browser and ADOWorking with the ADODB.Connection ObjectConnection PropertiesErrors Collection and Error ObjectsConnection MethodsConnection EventsUsing the ADODB.Command ObjectCommand PropertiesParameters CollectionParameter ObjectCommand MethodsCode to Pass Parameter Values to a Stored ProcedureUnderstanding the ADODB.Recordset ObjectRecordset PropertiesFields Collection and Field ObjectsRecordset MethodsDisconnected RecordsetsRecordset EventsTroubleshootingCursor Types for the Form.Recordset PropertyMSDataShape and SQLOLEDB ProvidersSpaces in ADO Object NamesIn the Real World—Struggling with ADOWhy Learn ADO?Where's ADOX?What's the Upshot?
Introducing Event-Driven ProgrammingUnderstanding the Role of Class ModulesThe Main Switchboard Class ModuleEvent-Handling Code in the Main Switchboard FormExamining Project Class Module Members in the Object Browser and Project ExplorerAdding Event-Handling Code with the Command Button WizardUsing Functions to Respond to EventsUnderstanding Access 2000's Event RepertoireWorking with Access 2000's DoCmd MethodsArguments of DoCmd MethodsCustomizing Applications with CommandBar ObjectsSpecifying a Custom CommandBar and Setting Other Startup PropertiesReferring to Access Objects with VBAReferring to Open Forms or Reports and Their PropertiesReferring to Controls and Their PropertiesReferring to Controls on a Subform or the Main FormUsing Alternative Collection SyntaxResponding to Data Events Triggered by Forms and ControlsTroubleshootingCalling Procedures and Functions in Class ModulesMissing Objects in CollectionsIn the Real World—Dealing with Event-Driven Programming
Streamlining Decision Support Front EndsConstraining Query Choices with Combo BoxesDesigning the Decision-Support QueryCreating the Form and Adding a List BoxAdding the Query Combo Boxes to the FormAdding Code to Create the Query's SQL StatementConverting Your Combo Box Form to an Access Data ProjectImporting and Testing the Combo Box FormConforming Row Source SQL Statements to Transact-SQL SyntaxDrilling Down from a List Box SelectionCreating the Drill-Down Query and Adding the List BoxProgramming the Drill-Down List BoxAdding New Features to List and Combo BoxesIterating List Box Items and Selecting an ItemAdding an Option to Select All Countries or ProductsDealing with Jet-Specific Functions in Migrating to ADPTroubleshootingRun-Time Error '2465'Spurious Paramater MessagesIn the Real World—Access Combo and List Boxes
Navigating Recordsets with VBAGenerating the Temporary RecordsetApplying Move… MethodsUsing the EOF and BOF Properties in LoopsUsing the AbsolutePosition PropertyUsing the Find Method and BookmarksModifying Rows of RecordsetsEditing and Adding RowsDeleting RowsPopulating a Combo Box from a RecordsetCreating frmCombo2 and Altering the Combo Box DesignPopulating the Combo Boxes with CodeAltering the Sequence of Combo Box ListsFilling List Boxes from Recordset ObjectsFormatting Value List Combo Box ColumnsPorting frmComboVBA to an Access Data ProjectTroubleshootingMaking ADODB.Recordsets UpdatableIn the Real World—Adapting to ADO
Understanding the .mdb File Upgrade ProcessConverting Unsecured Files from Access 9x to Access 2000Upgrading on First Opening the File in Access 2000Upgrading After Opening the File in Access 2000Converting Secure Access 9x FilesUpgrading in a Mixed Access 9x and 2000 EnvironmentUpgrading the Back-End Database and Workgroup FileUpgrading Access 2.0 Application .mdb Files to Access 2000Converting from Win16 to Win32 Function CallsConverting and Adding References to Libraries and Add-InsAccommodating the 32-Index Limit on TablesConverting 16-Bit OLE Controls to 32-Bit ActiveX ControlsTroubleshootingMissing Library or Project MessageIn the Real World—The Upgrade Blues

Content preview from Special Edition Using Microsoft® Access 2000

Chapter 27. Understanding Universal Data Access, OLE DB, and ADO

In this chapter

Gaining a Perspective on Microsoft's New Data Access Components

Access 2000 represents the pivot point of Microsoft's strategy for data access. Under the umbrella of "Universal Data Access," Microsoft wants all Office users, not just Access developers, to abandon Data Access Objects (DAO), ODBCDirect, and the Open Database ...