book

Sams Teach Yourself Microsoft® Access 2002 Programming in 24 Hours

Name: Sams Teach Yourself Microsoft® Access 2002 Programming in 24 Hours
Author: Paul Kimmel
ISBN: 9780768683660

by Paul Kimmel

August 2001

Beginner

576 pages

12h 12m

English

Sams

Read now

Unlock full access

Copyright
Dedication
Inside Front Cover
About the Author
Acknowledgments
Bibliography
Tell Us What You Think!
Introduction
I. Access Programming 101
1. What’s New in Access 2002
New Side Panes for Easier NavigationCustomizing Menus and ToolbarsUsing the More Menu ItemPersonalizing Menus and ToolbarsAssigning Hyperlinks to Menus and ToolbarsSpell-Checking DataEditing OptionsMultiple Undos and RedosClippit Office AssistantAsk a QuestionExpanded Clipboard FunctionalitySpeech Command and ControlCollaborating OnlineSetting Up NetMeetingSo You’re Connected. Now What?Network PlacesRemove Author InformationIntroducing ActiveX Data ObjectsIntroducing a New Object for Database Security—ADOXSupport for Stored ProceduresProgramming ObjectsAdding Data to Web PagesAccess ProjectsSummaryQ&AWorkshopQuizExercises
2. Exploring VBA
Understanding How Access VBA WorksLearning Access VBA KeywordsAccess Operators and OperandsData: What a Program KnowsPutting It All TogetherArithmetic OperatorsComparison OperatorsLogical OperatorsString Concatenation OperatorsPrelude to Advanced TopicsSummaryQ&AWorkshopQuizExercises

3. Storing Your Program’s Data
How Memory Is UsedDeclaring VariablesDim VariablesReDim VariablesConst VariablesGlobal VariablesData Assignment and EvaluationBe ExplicitVariant Data TypesImplicit Variable DeclarationUsing Pop-up HintsSetting Up Test CodeDebugging and Using Pop-up HintsUsing the Locals WindowUsing the Watches WindowAdding a Watch ItemEditing WatchesUsing Quick WatchesRunning Code in the Immediate WindowEmploying the Call StackSummaryQ&AWorkshopQuizExercises
4. Controlling Program Flow and Performing Calculations
What Goes into an EquationEquation DataLiteral ValuesVariablesConstantsGlobalsFunction ResultsRules of the RoadUnderstanding Operator CountUsing Arithmetic OperatorsPerforming ComparisonsThe Truth of Logical OperatorsAndEqvImpOrXorNotBitwise OperationsConcatenate ItWho Has Precedence Here?Special OperatorsUsing the Is OperatorPattern Matching with LikeAddressOf OperationsSummaryQ&AWorkshopQuizExercises
II. Writing Code to Manage Your Access Data
5. Learning to Write Conditional Code
A Programmer’s Traffic CopUsing ElseNesting Conditional StatementsHousekeepingTidying Up Nested Conditional StatementsWant to Go for a Spin?Take It Once Around the BlockIterating Through DataIterating Forward and BackwardUbound and LboundIterating Collections of DataShort-circuiting CodeUsing the Switch FunctionSummaryQ&AWorkshopQuizExercises
6. Managing Your Database
Access 2002 Is Backward Compatible with Access 2000New File Format Provides Better PerformanceMDE Database File ExtensionADE Database File ExtensionImproved Compact and Repair FeatureCreating a DatabaseDefining the DatabaseCreating a Table with Access 2002Creating a Module in the Contacts DatabaseCreating a Table with CodeManaging a Table with CodeDatabase Terms Refresher CourseThe CatalogThe ConnectionThe RecordsetRows, Columns, and FieldsAccessing Table InformationIteratively Inputting User DataUsing Conditional Code to Find DataSummaryQ&AWorkshopQuizExercises
7. Using Advanced Data Types to Manage Data
Understanding OLE AutomationWelcome to the World of ObjectsUseful ObjectsComparing ADO to DAOUsing the ADODB ObjectUsing a ConnectionOpening a ConnectionClosing a ConnectionUsing a RecordsetOpening a RecordsetClosing a RecordsetModifying a RecordTesting for the First and Last RecordChanging RecordsUsing FieldsUsing the ADOX ObjectCatalogThe ActiveConnection AttributeTables CollectionGroups, Users, and ViewsSummaryQ&AWorkshopQuizExercises
III. Using Access Resources to Get the Job Done
8. Solving Problems a Piece at a Time
The Mechanics of Writing SubroutinesWriting the First and Last Lines of SubroutinesNaming SubroutinesNaming Subroutine ArgumentsDefining Argument TypesUsing ByVal ArgumentsUsing ByRef ArgumentsUsing Optional ArgumentsThe Mechanics of Writing FunctionsProgramming Rules of ThumbRule 1: Make Recurring Code into a FunctionRule 2: Keep Functions ShortRule 3: Strictly Limit the Number of ArgumentsRule 4: Use Argument Qualifiers to Inhibit Variable MisuseRule 5: Use Contract ProgrammingRule 6: Don’t Be Afraid to CommentTying It All TogetherCreating a Table with CodeImporting a Comma-Delimited Text FileFinding a RecordUsing the Windows RegistrySummaryQ&AWorkshopQuizExercises
9. Using Macros As Learning Aids
Creating a Macro 101Creating a Table with SQLCreating a MacroDefining Macro Names and Conditional CodeTesting and Debugging a MacroUsing CopyObjectUsing DeleteObjectThe Keys to the CityImporting DataTransferring a DatabaseSpecifying the Database TypeSpecifying the Database NameChoosing an Object Type to TransferSpecifying a Transfer Source and DestinationReplicating Structure OnlyTransferring Text DataSpecifying a Text Transfer TypeCreating a Transfer SpecificationSpecifying a Table NameSpecifying a FilenameDoes the Text Source Contain Field Names?Using HTML Table DataIndicating a Code PageUsing Macro Resources in CodeTo Macro or Not to MacroSummaryQ&AWorkshopQuizExercises
10. Employing Access Predefined Solutions
Using String-Handling FunctionsConverting Between String and Numeric ValuesDual Type String FunctionsGeneral Data Type Conversion FunctionsSearching StringsDynamically Allocating StringsFormatting DataUsing Date and Time FunctionsManaging File I/OUsing Open and CloseReading and Writing Text DataReading and Writing Binary DataDynamic User Input FunctionsUsing Function, Method, Property, and Statement ReferencesSummaryQ&AWorkshopQuizExercises
IV. Defining Data Types and Using Arrays and Collections
11. Making the Complex Simple: Creating Your Own Data Types
Understanding AggregationUsing the Type DeclarationWhat Kind of Data Can Go into My Type?Declaring Instances of User TypesDefining Enumerated TypesPutting It All TogetherSummaryQ&AWorkshopQuizExercises
12. Managing Varying Amounts of Data
Understanding ArraysDeclaring ArraysDeclaring Fixed ArraysDeclaring Dynamic ArraysDeclaring Static ArraysSetting Array Base IndexesUsing Arrays to Store DataFunctions for Managing ArraysTesting Variables with IsArrayPassing Arrays of Parameters to ProceduresReturning Arrays from a FunctionThings You Should Do When Using ArraysSorting Array DataBubble-Sorting an Array of StringsSelection-Sorting ArraysQuick-Sorting ArraysSummaryQ&AWorkshopQuizExercises
13. Storing Information in a Collection
Understanding CollectionsUses for CollectionsThe Terminology of Object-Oriented ProgrammingDeclaring Collection VariablesManaging a Data CollectionAdding Data to a CollectionRemoving Data from a CollectionUsing the Item Method and Count PropertyIterating Through a Collection with For Each LoopsPlaces You’ll Find CollectionsSummaryQ&AWorkshopQuizExercises
V. Database Programming in Access
14. Managing Your Code
Naming ConventionsMeaning Is Conveyed with Whole WordsPairing Verbs and Nouns Make Subroutines and Functions ClearAvoid Nonstandard AbbreviationsUsing Whitespace ConsistentlyManaging ComplexityAvoid Nesting Conditional StatementsUse Procedures to Implement Conditional CodeKeep Procedures Short and to the PointCommenting GuidelinesWrite Comments in Complete SentencesComment Lengthy CodeComment Ambiguous Code or Code Copied from an External SourceUnderstanding the Benefits of Code ReuseTesting and DebuggingManaging DataSummaryQ&AWorkshopQuizExercises
15. Data Programming Made Easy with ADODB
Connecting to a DatabaseOpen Method’s Connection ArgumentsCreating an ODBC AliasWhat Is a Provider?Specifying a Username and PasswordSelecting a Connection ModeManaging a RecordsetOpening and Closing RecordsetsThe Ingredients in a RecordsetCommand ObjectsStored ProceduresLiteral SQL CodeTables and QueriesAdding and Updating DataModifying Field DataDeleting DataFinding RecordsUsing a TableUsing a QueryUsing the Filter PropertyCopying Data to a Collection ObjectUsing AddItem and RemoveItemFinding ActiveX Data Objects Resources OnlineSummaryQ&AWorkshopQuizExercises
16. Using Advanced SQL Techniques
Using the SELECT StatementWriting a Simple SELECT StatementFiltering the Recordset with the WHERE ClauseOperators for the WHERE ClauseSpecifying a Range with BETWEENPerforming Set Operations with INUsing Logic OperatorsUsing Nested SELECT Statements in WHERE ClausesSorting QueriesGrouping ColumnsUsing the HAVING Clause to Filter Result SetsJoining TablesUsing the INNER JOINUsing the LEFT JOINUsing the RIGHT JOINCreating a Union of TablesDefining a Column AliasInserting New DataInserting into Specified Fields OnlyInserting into Implied FieldsInserting with ParametersInserting from a Table with SELECTUpdating DataDeleting DataCalling Functions in SQL CodeWriting Stored ProceduresAdding a Stored Procedure to a CatalogExecuting a Stored ProcedureQuerying a SQL Server DatabaseSummaryQ&AWorkshopQuizExercises
VI. Mastering Error Handling
17. Removing Bugs
ScaffoldingWhy Scaffolding Early Is ImportantUnderstanding What to Look ForHow to Scaffold Access CodeWhat to Do If Your Code Has DependenciesWhat to Do If the Code Doesn’t Solve the ProblemWhat to Do in the Event of Unhandled ErrorsTrapping CodeWhat Is a Trap?Using TrapsTracing CodeAsserting AssumptionsContract ProgrammingReusing the Debugging TechniquesUsing Compiler DirectivesMaking Debug Code Read-OnlyDevelopment Versus Deployment Error HandlingSummaryQ&AWorkshopQuizExercises
18. Adding Code to Handle Errors
Comparing Error-Handling StylesConditional Error Checking: The Old WayException Handling: A Better WayWriting Error HandlersStarting an Error HandlerNaming LabelsWriting Your Exit StatementGeneral Rules for Error HandlersClearing the Error HandlerWriting Silent Error HandlersUsing Resume in an Error-Handling BlockUsing Resume Next in an Error-Handling BlockUsing the Err ObjectErr Object PropertiesErr Object MethodsUsing Error Handlers to Validate User InputCreating the Resource-Protection Block IdiomReviewing the Debug ObjectSummaryQ&AWorkshopQuizExercises
VII. Creating Access User Interfaces
19. Creating Custom Forms and Reports
Using Form WizardsAn Overview of Each Form Design TypeDesign ViewForm WizardColumnar AutoFormTabular AutoFormDatasheet AutoFormPivotTable AutoFormPivotChart AutoFormChart WizardPivotTable WizardCreating a Form with the Form WizardUsing Data-Management Menu ItemsFiltering Form RecordsUsing Filter by FormUsing Filter by SelectionUsing Filter Excluding SelectionBuilding Advanced FiltersCustomizing Wizard FormsUnderstanding the Three Facets of Visual ObjectsAssigning Data to PropertiesCalling MethodsProgramming Event HandlersAdding Controls to Your FormSelecting Additional Controls from the ToolboxMaking Non-Component Objects Available to Your ProgramsWriting CodeEnhanced Editing CapabilitiesExtended Capabilities for Forms and ReportsNew Properties for Forms and ReportsNew Form EventsNew Methods for Forms and ReportsLive Subreport FormTesting Your FormDefining a ReportUsing the New Printer ObjectAdding a Menu Item to Access to Print the ReportSetting the Startup Point for Your ProgramSummaryQ&AWorkshopQuizExercises
20. Adding Data to Web Pages
Comparing Intranets to InternetsInstalling and Running Internet Information ServicesEstablishing a Web SiteUnderstanding Web PagesWhat Is a Data Access Page?Advanced Web Page TopicsBuilding the Database Used for the DemoUsing Wizards to Generate Web PagesDefining a Multiple-Table QueryUsing the Page WizardUsing Web Page Design ToolsUsing the Data Access Page DesignerData Outline View and Server FiltersVisual Control Sizing and Snap-to-GridIntuitive Drop ZonesAutoSum Feature and Group FilteringDesigner Inherits Extended Properties from the DatabaseTaking a Tour of Data Access Page Menus and ToolbarsAdding a Control to a Data Access PageUsing the Properties DialogBinding a Field to a ControlSorting and Grouping DataAdding a PivotTable to Web PagesUniform Naming ConventionOffice Data ConnectionXML’s Role in AccessSummaryQ&AWorkshopQuizExercises
VIII. Object-Oriented Programming in Access
21. Class Programming Basics
Understanding the Need for ClassesCreating Your First ClassHiding Information to Make Code Easier to UseDefining Class MethodsDefining PropertiesUsing Object PropertiesUsing Static PropertiesWriting Initialization and Termination CodeWriting a New ClassTesting ClassesExtending Existing ClassesCreating an Instance of a ClassSummaryQ&AWorkshopQuizExercises
22. Adding Capabilities to Your Data Types
Defining the ObjectiveUnderstanding When to Write a ClassApplying Basic Principles of Object-Oriented DesignNaming What the Class Must Do and KnowTips for Implementing ClassesExtending Existing ClassesUsing a Collection to Store the Associative Array DataImplementing the File Streaming CapabilitiesTesting Your New ClassUsing the Strings ClassSummaryQ&AWorkshopQuizExercises
23. Writing Access Add-Ins
Introducing Add-InsDefining an Error-Logging DatabaseAdding Code to Create the Table DynamicallyLogging Exceptions to the DatabaseDefining a Viewer for the Error LogTesting the Error Logging Add-In DatabaseInstalling and Uninstalling Add-insCreating the Registry Information TableInstalling the Add-InUninstalling and Modifying Add-InsGoing for a Test DriveSummarizing the ProcessSummaryQ&AWorkshopQuizExercises
24. Managing Outlook Contact Information
Understanding Outlook 2002Introducing the Outlook Object ModelCreating an Instance of OutlookUsing NamespacesUsing a FolderViewing Outlook Contacts in AccessUpdating Outlook InformationSearching the Body of a Mail ItemUndeleting Mail ItemsSending Mail to Contacts AutomaticallySummaryQ&AWorkshopQuizExercises
IX. Appendix
A.
Hour 1QuizExercisesHour 2QuizExercisesHour 3QuizExercisesHour 4QuizExercisesHour 5QuizExercisesHour 6QuizExercisesHour 7QuizExercisesHour 8QuizExerciseHour 9QuizExerciseHour 10QuizExercisesHour 11QuizExercisesHour 12QuizExercisesHour 13QuizExercisesHour 14QuizExercisesHour 15QuizExercisesHour 16QuizExercisesHour 17QuizExercisesHour 18QuizExercisesHour 19QuizExercisesHour 20QuizExercisesHour 21QuizExercisesHour 22QuizExercisesHour 23QuizExercisesHour 24QuizExercises
Quick Reference for DoCmd Methods

Content preview from Sams Teach Yourself Microsoft® Access 2002 Programming in 24 Hours

Chapter 16. Using Advanced SQL Techniques

Access programming is Visual Basic for Applications programming. That is, when you program with Access, you are writing Visual Basic (VBA) code. Structured Query Language (SQL, pronounced sequel) is not the same thing as VBA. VBA and SQL are two separate languages. VBA is an all-purpose programming language, and SQL is a language designed specifically for programming databases.

No book on Access programming would be complete without a tour of SQL programming because SQL is especially useful for programming databases. In the last hour, you had an opportunity to write code using the ADODB object. What might not be readily apparent is that the ADODB object converts a lot of the VBA code that you write into ...

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: 9780768683660Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Sams Teach Yourself Microsoft® Access 2002 Programming in 24 Hours

by Paul Kimmel

Chapter 16. Using Advanced SQL Techniques

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.