[PDF] MICROSOFT® Access® 2019 PROGRAMMING BY EXAMPLE

View - Download MICROSOFT® Access® 2019 PROGRAMMING BY EXAMPLE

Previous PDF

Next PDF

MICROSOFT® Access® 2019

P

ROGRAMMING BY EXAMPLE

LICENSE, DISCLAIMER OF LIABILITY, AND LIMITED WARRANTY By purchasing or using this book (the "Work"), you agree that this license grants permission to use the contents contained herein, but does not give you the right of ownership to any of the textual content in the book or ownership to any of the in- formation or products contained in it. ? is license does not permit uploading of the Work onto the Internet or on a network (of any kind) without the written consent of the Publisher. Duplication or dissemination of any text, code, simulations, images, etc. contained herein is limited to and subject to licensing terms for the respective products, and permission must be obtained from the Publisher or the owner of the content, etc., in order to reproduce or network any portion of the textual material (in any media) that is contained in the Work. Mercury Learning And Information ("MLI" or "the Publisher") and anyone involved in the creation, writing, or production of the companion disc, accompa- nying algorithms, code, or computer programs ("the so? ware"), and any accom- panying Web site or so? ware of the Work, cannot and do not warrant the perfor- mance or results that might be obtained by using the contents of the Work. ? e author, developers, and the Publisher have used their best e? orts to insure the ac- curacy and functionality of the textual material and/or programs contained in this package; we, however, make no warranty of any kind, express or implied, regarding the performance of these contents or programs. ? e Work is sold "as is" without warranty (except for defective materials used in manufacturing the book or due to faulty workmanship). ? e author, developers, and the publisher of any accompanying content, and any- one involved in the composition, production, and manufacturing of this work will not be liable for damages of any kind arising out of the use of (or the inability to use) the algorithms, source code, computer programs, or textual material con- tained in this publication. ? is includes, but is not limited to, loss of revenue or pro? t, or other incidental, physical, or consequential damages arising out of the use of this Work. ? e companion ? les on the disc are also available for down load by writing to the publisher at info@merclearning.com. ? e sole remedy in the event of a claim of any kind is expressly limited to replace- ment of the book, and only at the discretion of the Publisher. ? e use of "implied warranty" and certain "exclusions" vary from state to state, and might not apply to the purchaser of this product.

MICROSOFT® Access® 2019

P

ROGRAMMING BY EXAMPLE

with VBA, XML, and ASP

Julitta Korol

MERCURY LEARNING AND INFORMATION

Dulles, Virginia

Boston, Massachusetts

New Delhi

Copyright ©2019 by Mercury Learning and Information. All rights reserved. This publication, portions of it, or any accompanying software may not be reproduced in any

way, stored in a retrieval system of any type, or transmitted by any means, media, electronic display

or mechanical display, including, but not limited to, photocopy, recording, Internet postings, or scanning, without prior permission in writing from the publisher.

Publisher: David Pallai

Mercury Learning and Information

22841 Quicksilver Drive

Dulles, VA 20166

info@merclearning.com www.merclearning.com (800) 232-0223 Julitta Korol. Microsoft Access 2019 Programming by Example with VBA, XML, and ASP.

ISBN: 978-1-68392-403-6

This book is printed on acid-free paper in the United States of America. ? e publisher recognizes and respects all marks used by companies, manufacturers, and developers as a means to distinguish their products. All brand names and product names mentioned in this book are trademarks or service marks of their respective companies. Any omission or misuse (of any kind) of service marks or trademarks, etc. is not an attempt to infringe on the property of others.

Library of Congress Control Number: 2019939377

Our titles are available for adoption, license, or bulk purchase by institutions, corporations, etc. For

additional information, please contact the Customer Service Dept. at 800-232-0223(toll free).

All of our titles are available in digital format at academiccourseware.com and other digital vendors.

Companion ? les on the disc for this title are also available by contacting info@merclearning.com. ? e sole obligation of Mercury Learning and Information to the purchaser is to replace the disc, based on defective materials or faulty workmanship, but not based on the operation or functionality of the product. To a new generation of Microsoft Access programmers

CONTENTS

Acknowledgments .....................................................................................xxv

Introduction ............................................................................................xxvii

PART I ACCESS VBA PRIMER ...................................................... 1 Chapter 1 Getting Started with Access VBA...................................... 3 Understanding VBA Modules and Procedure Types ......................................4 Writing Procedures in a Standard Module .......................................................7

Executing Your Procedures .............................................................................10

Understanding Class Modules ........................................................................12

Events, Event Properties, and Event Procedures ..........................................14

Why Use Events? ...........................................................................................15

Walking Through an Event Procedure ......................................................15

Compiling Your Procedures ............................................................................21

Placing a Database in a Trusted Location ......................................................22

Summary ............................................................................................................25

Chapter 2 Getting to Know Visual Basic Editor (VBE) ..................27 Understanding the Project Explorer Window ..............................................28 Understanding the Properties Window .........................................................30 Understanding the Code Window ..................................................................32

Other Windows in the VBE .............................................................................34

Assigning a Name to the VBA Project ...........................................................35

Renaming the Module ......................................................................................36

Syntax and Programming Assistance .............................................................36

List Properties/Methods ..............................................................................36

Parameter Info ..............................................................................................38

List Constants ................................................................................................39

viii CONTENTS

Quick Info ......................................................................................................40

Complete Word .............................................................................................40

Indent/Outdent .............................................................................................41

Comment Block/Uncomment Block .........................................................42

Using the Object Browser ................................................................................42

Using the VBA Object Library ........................................................................45

Using the Immediate Window ........................................................................46

Summary ............................................................................................................49

Chapter 3 Access VBA Fundamentals ..............................................51

Introduction to Data Types .............................................................................51

Understanding and Using Variables ..............................................................54

Declaring Variables.......................................................................................54

Specifying the Data Type of a Variable ......................................................58

Using Type Declaration Characters...................................................................60

Assigning Values to Variables .....................................................................61

Forcing Declaration of Variables ................................................................63 Understanding the Scope of Variables .......................................................65

Procedure-Level (Local) Variables ....................................................................66

Module-Level Variables ......................................................................................66

Project-Level Variables ........................................................................................69

Understanding the Lifetime of Variables ...................................................70

Using Temporary Variables .........................................................................70

Creating a Temporary Variable with a TempVars

Collection Object ............................................................................................

71
Retrieving Names and Values of TempVar Objects.........................................72 Using Temporary Global Variables in Expressions .........................................73

Removing a Temporary Variable from a TempVars

Collection Object ............................................................................................

73

Using Static Variables ...................................................................................74

Using Object Variables .................................................................................75

Disposing of Object Variables ............................................................................78

Finding a Variable Definition .....................................................................78

Determining the Data Type of a Variable ..................................................78 Using Constants in VBA Procedures .............................................................80

Intrinsic Constants .......................................................................................81

Summary ............................................................................................................82

CONTENTS ix

Chapter 4 Access VBA Built-In and Custom Functions ................83

Writing Function Procedures ..........................................................................83

Various Methods of Running Function Procedures ....................................84 Specifying the Data Type for a Function"s Result .........................................86 Passing Arguments to by Reference and by Value .......................................88

Using Optional Arguments..............................................................................89

Using the IsMissing Function .........................................................................91

Using VBA Built-In Functions for User Interaction ....................................91 Using the MsgBox Function ........................................................................92 Returning Values from the MsgBox Function ..............................................101 Using the InputBox Function ....................................................................102

Converting Data Types ...................................................................................105

Summary ..........................................................................................................107

Chapter 5 Adding Decisions to Your Access VBA Programs ........109

Relational and Logical Operators ..................................................................109

If...Then Statement ........................................................................................110

Multiline If...Then Statement .......................................................................112

Decisions Based on More than One Condition ..........................................114

If...Then...Else Statement .............................................................................116

If...Then...ElseIf Statement ..........................................................................118

Nested If...Then Statements ..........................................................................119

Select Case Statement .....................................................................................123

Using is with the Case Clause ...................................................................125 Specifying a Range of Values in a Case Clause .......................................126 Specifying Multiple Expressions in a Case Clause .................................128

Summary ..........................................................................................................129

Chapter 6 Adding Repeating Actions to Your

Access VBA Programs ................................................... 131
Using the Do...While Statement ..................................................................132 Another Approach to the Do...While Statement ...................................134

Using the Do...Until Statement ....................................................................135

Another Approach to the Do...Until Statement .....................................136

Using the For...Next Statement ....................................................................137

Using the For Each...Next Statement ..........................................................140

Exiting Loops Early .........................................................................................141

x CONTENTS

Nested Loops ....................................................................................................143

Summary ..........................................................................................................144

Chapter 7 Keeping Track of Multiple Values Using Arrays ...........145

Understanding Arrays ....................................................................................146

Declaring Arrays .........................................................................................148

Array Upper and Lower Bounds ..............................................................149 Initializing and Filling an Array ...............................................................150 Filling an Array Using Individual Assignment Statements .........................150 Filling an Array Using the Array Function ...................................................150 Filling an Array Using the For...Next Loop ..................................................151 Using a One-Dimensional Array ..................................................................152

Arrays and Looping Statements ....................................................................154

Using a Two-Dimensional Array ..................................................................158

Static and Dynamic Arrays ............................................................................159

Array Functions ...............................................................................................161

The Array Function ....................................................................................162

The IsArray Function .................................................................................162

The Erase Function ....................................................................................163

The LBound and UBound Functions ......................................................164

Errors in Arrays ...............................................................................................166

Parameter Arrays.............................................................................................168

Passing Arrays to Function Procedures .......................................................169

Sorting an Array ..............................................................................................170

Summary ..........................................................................................................172

Chapter 8 Keeping Track of Multiple Values Using Object Collections ......................................................... 173
Working with Collections of Objects ...........................................................174 Declaring a Custom Collection ................................................................176 Adding Objects to a Custom Collection ..................................................176 Removing Objects from a Custom Collection ........................................178 Creating Custom Objects in Class Modules ................................................178

Creating a Class...........................................................................................179

Variable Declarations .................................................................................180

Defining the Properties for the Class .......................................................181 Creating the Property Get Procedures ...........................................................182

CONTENTS xi

Creating the Property Let Procedures ...........................................................183 Creating the Class Methods ......................................................................185 Creating an Instance of a Class .................................................................186 Event Procedures in the Class Module ....................................................187

Creating the User Interface ............................................................................188

Running the Custom Application .................................................................198 Watching the Execution of Your VBA Procedures ....................................199

Summary ..........................................................................................................202

Chapter 9 Getting to Know Built-In Tools for

Testing and Debugging ................................................. 203

Stopping a Procedure ......................................................................................205

Using Breakpoints ...........................................................................................206

Removing Breakpoints ...............................................................................211

Using the Immediate Window in Break Mode ...........................................212

Using the Stop Statement ...............................................................................214

Using the Assert Statement ............................................................................214

Using the Add Watch Window .....................................................................216 Removing Watch Expressions ...................................................................220

Using Quick Watch .........................................................................................220

Using the Locals Window ..............................................................................221

Using the Call Stack Dialog Box ....................................................................223

Stepping Through VBA Procedures .............................................................224

Stepping Over a Procedure ........................................................................225

Stepping Out of a Procedure .....................................................................226 Running a Procedure to Cursor ................................................................227

Setting the Next Statement ........................................................................227

Showing the Next Statement .....................................................................227

Navigating with Bookmarks ..........................................................................227

Stopping and Resetting VBA Procedures ....................................................228

Trapping Errors ...............................................................................................229

Using the Err Object ...................................................................................230

Procedure Testing .......................................................................................234

Setting Error-Trapping Options ...............................................................236

Summary ..........................................................................................................238

xii CONTENTS

PART II ACCESS VBA PROGRAMMING WITH

DAO AND ADO ........................................................... 239
Chapter 10 Data Access Technologies in Microsoft Access ..........241 Understanding Database Engines: Jet/ACE ................................................242 Understanding Access Versions and File Formats .....................................243 Understanding Library References ...............................................................246 Overview of Object Libraries in Microsoft Access .....................................248 The Visual Basic for Applications Object Library (VBA) .....................248 The Microsoft Access 16.0 Object Library ..............................................249

The Microsoft Office 16.0 Access Database Engine

Object Library .........................................................................................249

The Microsoft DAO 3.6 Object Library...................................................249 The Microsoft ActiveX Data Objects 6.1 Library (ADO) .....................250 Creating a Reference to the ADO Library ...................................................252 Understanding Connection Strings ..............................................................254 Using ODBC Connection Strings .................................................................255 Creating and Using ODBC DSN Connections .......................................255 Creating and Using DSN-Less ODBC Connections ..............................261 Using OLE DB Connection Strings ..............................................................262 Connection String via a Data Link File ........................................................263 Opening Microsoft Access Databases ..........................................................267

Opening a Microsoft Jet Database in Read/Write

Mode with DAO .....................................................................................268

Opening a Microsoft Jet Database in Read/Write

Mode with ADO .....................................................................................270

Opening a Microsoft Access Database in

Read-Only Mode with DAO .................................................................273

Opening a Microsoft Jet Database in Read-Only

Mode with ADO .....................................................................................273

Opening a Microsoft Jet Database Secured with a Password ...............274 Opening a Microsoft Jet Database with User-Level Security ................279 Connecting to the Current Access Database ...............................................282

Opening Other Databases, Spreadsheets, and Text

Files from Access .........................................................................................283

Connecting to an SQL Server Database ..................................................283 Opening a Microsoft Excel Workbook ....................................................284

CONTENTS xiii

Opening a Text File Using ADO ...............................................................289 Creating a New Access Database ..................................................................291 Creating a Database with DAO .................................................................291 Creating a Database with ADO ................................................................292

Copying a Database.........................................................................................293

Copying a Database with DAO .................................................................294 Copying a Database with FileSystemObject ...........................................295

Database Errors ...............................................................................................296

Compacting a Database ..................................................................................299

Summary ..........................................................................................................302

Chapter 11 Creating and Accessing Database

Tables and Fields ......................................................... 303

Creating a Microsoft Access Table and Setting

Field Properties (DAO Method) ...............................................................304

Creating a Microsoft Access Table and Setting

Field Properties (ADO Method) ...............................................................310

Copying a Table ...............................................................................................313

Deleting a Database Table ..............................................................................314

Adding New Fields to an Existing Table ......................................................316

Creating Calculated Fields .............................................................................318

Creating Multivalue Lookup Fields with DAO ...........................................320 Creating Attachment Fields with DAO .......................................................323 Creating Append Only Memo Fields with DAO ........................................325 Creating Rich Text Memo Fields with DAO ...............................................332

Removing a Field from a Table .....................................................................334

Retrieving Table Properties ...........................................................................336

Retrieving Field Properties ............................................................................338

Linking a Microsoft Access Table .................................................................339

Linking a dBASE Table ...................................................................................340

Linking a Microsoft Excel Worksheet ..........................................................340

Listing Database Tables ..................................................................................343

Listing Tables and Fields ................................................................................344

Listing Data Types ...........................................................................................346

Changing the AutoNumber ...........................................................................346

Summary ..........................................................................................................348

xiv CONTENTS Chapter 12 Setting Up Primary Keys, Indexes, and Table Relationships ..................................................... 349

Creating a Primary Key Index .......................................................................349

Creating Indexes Using ADO ........................................................................350

Creating Indexes Using DAO ........................................................................352

Creating a Single-Field Index Using ADO ..................................................354 Adding a Multiple-Field Index to a Table Using DAO ............................356quotesdbs_dbs21.pdfusesText_27

[PDF] access 3 students book pdf free download

[PDF] access 4 grammar book pdf

[PDF] access 4 student's book pdf free download

[PDF] access 97 tutorial

[PDF] access affordable housing

[PDF] access airwatch api

[PDF] access bars self treatment

[PDF] access consciousness blog

[PDF] access d login brick

[PDF] access d mobile

[PDF] access d'

[PDF] access definition government

[PDF] access definition in healthcare

[PDF] access definition law

[PDF] access definition sociology