Automating Microsoft Access with VBA

, by Gunderloy, Mike; Harkins, Susan Sales

Note: Supplemental materials are not guaranteed with Rental or Used book purchases.

ISBN: 9780789732446 | 0789732440
Cover: Paperback
Copyright: 9/14/2004

Sorry, this item is currently not available.

If you use Microsoft Access in your every day business life but haven't learned to fully exploit the program, now's your chance.Automating Microsoft Access with VBAis a thorough introduction to programming Microsoft Accessing using Visual Basic for Applications (VBA). With this book, you will gain a working knowledge of VBA and be able to customize your Microsoft Access databases. You will cover topics that include: Operators and functions Debugging and error handling Menu commands Forms and reports Jet databases DAO object model Save money by learning to use VBA to customize your databases instead of paying someone else to do it for you!

Introduction

(1)

Who This Book Is For

(1)

What's in This Book

(2)

Conventions Used in This Book

(1)

The Sample Code for This Book

(1)

Contacting the Authors

(4)

I LAYING THE FOUNDATION

Why Access? Why VBA?

(8)

Understanding the Place of Access in Office

(2)

Choosing Between Access and Excel

(2)

Choosing Between Access and OneNote

(1)

Understanding Access Programming Choices

(2)

Using Macros

(1)

Using SQL

(1)

Using VBA

(1)

Case Study

(4)

Using the TimeTrack Sample Database

(4)

Getting Around in the Visual Basic Editor

(16)

Your First Glance at the Visual Basic Editor

(4)

Introducing the VBA Modules

(1)

Entering and Running VBA Code

(3)

Saving the Code

(1)

Getting Help on Code

(3)

Helpful Shortcuts for Entering Code

(2)

Establishing Good Habits in Coding

(5)

Using a Naming Convention

(2)

Indenting Your Code

(1)

Commenting Your Code

(3)

Using Variables, Constants, and Data Types

(12)

Declaring Variables and Constants

(6)

Declaring Variables

(1)

Using Option Explicit

(3)

Naming Variables

(1)

Declaring Constants

(1)

Intrinsic Constants

(1)

VBA Data Types

(3)

The Boolean Data Type

(1)

The Byte Data Type

(1)

The Currency Data Type

(1)

The Date Data Type

(1)

The Decimal Data Type

(1)

The Double Data Type

(1)

The Integer Data Type

(1)

The Long Data Type

(1)

The Object Data Type

(1)

The Single Data Type

(1)

The String Data Type

(1)

The Variant Data Type

(1)

Referencing Syntax

(3)

Using Procedures

(12)

Understanding Procedure Types

(3)

Creating and Using Sub Procedures

(2)

Creating and Using Function Procedures

(1)

Declaring Procedures as Public or Private

(1)

Passing Arguments

(2)

Using Optional Arguments and Default Values

(1)

Passing Arguments by Reference

(1)

Passing Arguments by Value

(1)

Giving a Function a Data Type

(1)

Implementing Error Handling

(2)

Using On Error Resume Next

(1)

Using On Error Goto

(1)

Debugging Code

(3)

Using Run and Break Mode

(1)

Single-Stepping

(1)

Setting Breakpoints

(1)

Choosing the Right VBA Function

(34)

Introducing VBA's Built-in Functions

(1)

Converting Data Types with VBA Functions

(7)

Converting to a Boolean Data Type

(1)

Converting to a Byte Data Type

(1)

Converting to a Date Data Type

(1)

Converting to an Integer Data Type

(1)

Converting to a String Data Type

(1)

Converting to a Variant Data Type

(1)

Converting Null Values

(2)

Working with Date Functions

(5)

Returning the Date

(1)

Adding to and Subtracting from Dates

(1)

Determining the Difference Between Two Dates

(2)

Extracting Date Components

(1)

Creating a Date from Individual Components

(1)

Creating a Date from a String Expression

(1)

Returning a Specific Date or Time Component

(1)

Using Mathematical and Financial Functions

(5)

The Abs Function

(1)

The Int Function

(1)

The Rnd Function

(1)

The Ddb Function

(1)

The FV Function

(1)

The IPmt Function

(1)

The NPer Function

(1)

The Pmt Function

(1)

The PPmt Function

(1)

The Rate Function

(1)

The Syd Function

(1)

Using String Functions

(6)

The Asc Function

(1)

The Chr Function

(1)

The Case Functions

(1)

The Len String Function

(1)

The Left, Right, and Mid Functions

(1)

The Replace Function

(1)

The Space Function

(1)

The Split String Function

(1)

The StrComp Function

(1)

The Three Trimming Functions

(2)

Using the Format Function

(4)

Applying User-Defined Formats

(3)

Using the Is Functions for Flawless Processing

(1)

Interacting Functions

(3)

The InputBox Function

(1)

The MsgBox Function

(2)

Case Study

(3)

Business Days

(3)

Using Flow-of-Control Statements

(14)

Branching and Looping

(1)

Using If_Then_Else

(3)

The Simple If Statement

(1)

Creating More Complex Conditions

(1)

Adding the Else Statement

(1)

Using the ElseIf Statement

(1)

Using Select Case

(1)

Using For_Next

(4)

Looping in Reverse

(1)

Using a Variable for the Loop Counter

(1)

Nesting For_Next Loops

(1)

Aborting a For_Next Loop

(1)

Using Do Loops

(2)

Creating a Simple Do Loop

(1)

Varieties of the Do Loop

100

(1)

Aborting a Do Loop

101

(1)

Using GoTo

101

(1)

Case Study

102

(3)

Billing for Work in Progress

102

(3)

Working with Arrays

105

(8)

Introducing Array Variables

105

(1)

Declaring an Array Variable

105

(1)

Understanding the Array's Index

106

(1)

Using Option Base

107

(1)

Working with Array Elements

107

(3)

Defining Array Elements

108

(1)

Referencing Array Elements

109

(1)

Arrays with Multiple Dimensions

110

(1)

Expanding to Dynamic Arrays

110

(3)

About ReDim

110

(3)

Understanding Objects

113

(18)

Introducing Objects

113

(3)

Digressing into the Real World

113

(1)

An Object Example from Access

114

(1)

Creating Objects in Code

114

(2)

Reading and Setting Properties

116

(1)

Invoking Methods

117

(2)

Working with Collections

119

(2)

Working with an Object Model

121

(3)

Using an Object Model

121

(1)

Using References

122

(1)

The Object Browser

123

(1)

Creating Your Own Objects

124

(2)

Working with Events

126

(2)

Case Study

128

(3)

Opening Forms and Handling Errors

128

(3)

Understanding Scope and Lifetime

131

(14)

What's Scope?

131

(5)

Procedure-Level Variables

131

(2)

Module-Level Variables and Constants

133

(1)

Public Variables and Constants

134

(2)

Measuring the Lifetime of a Variable or Constant

136

(3)

The Lifetime of a Procedure-Level Variable

137

(1)

The Lifetime of a Module-Level Variable

137

(1)

The Lifetime of a Public Variable

138

(1)

Using Static Variables

139

(6)

II WORKING WITH THE ACCESS USER INTERFACE

Working with Forms

145

(16)

Opening and Closing Forms

145

(2)

Opening a Form

145

(1)

Closing a Form

146

(1)

The Form Module and Event Handling

147

(1)

Performing Common Tasks

148

(6)

Checking for a Form's Existence

148

(1)

Determining Whether a Form Is Loaded

149

(1)

Resizing a Form

150

(1)

Passing Arguments Using OpenArgs

151

(1)

Populating the Form

152

(2)

Handling Errors at the Form Level

154

(3)

Working with Multiple Form Instances

157

(1)

Case Study

158

(3)

Working with Two Instances of the Same Form

158

(3)

Analyzing the Access Event Model

161

(14)

Responding to Events

161

(1)

The Event Sequence for Controls

162

(5)

Focus Events

163

(2)

Data Events

165

(2)

Control-Specific Events

167

(1)

The Event Sequence for Forms

167

(3)

Navigation Events

168

(1)

Data Events

168

(1)

Behind the Scenes: Data Buffers

169

(1)

The Event Sequence for Reports

170

(1)

Canceling Events

171

(1)

Case Study

172

(3)

Validating Data Before Saving It

172

(3)

Working with List and Combo Boxes

175

(20)

Populating a List Control

175

(6)

A Simple Filtering List Control

177

(4)

Adding to the List---or Not

181

(6)

Updating a Value List

181

(2)

Updating a Table/Query List

183

(4)

Working with Multiselect Controls

187

(2)

Determining What's Selected and What's Not

187

(2)

Considering Callback Functions

189

(2)

Case Study

191

(4)

Using List Box Controls as Drill-Down Controls

191

(4)

Working with Other Controls

195

(14)

Working with Text Boxes

195

(5)

Key Properties of Text Boxes

195

(2)

Tracking the Focus

197

(1)

Working with Unbound Text Boxes

198

(2)

Using Controls in Option Groups

200

(2)

Working with Subforms

202

(1)

Working with the Tag Property

202

(1)

Case Study

203

(6)

Creating a Master Viewing Form

203

(6)

Working with Reports

209

(14)

Introducing the Report Module and Events

209

(1)

Opening and Closing Reports

210

(2)

Opening a Report

210

(1)

Closing a Report

211

(1)

Passing Arguments Using OpenArgs

212

(1)

Populating the Report

213

(2)

Applying a Filter and Sort Order

214

(1)

Handling Report-Level Errors

215

(3)

What to Do When There's No Data

217

(1)

Using VBA to Determine Group Properties

218

(2)

Case Study

220

(3)

Adding a Daily Report

220

(3)

Working with the Application Collections

223

(14)

Investigating the Application Collections

223

(2)

Retrieving Lists of Objects

225

(1)

Working with Object Properties

226

(3)

Programmatically Determining Dependencies

229

(3)

Case Study

232

(5)

Enhancing the Master Form

232

(5)

III WORKING WITH ACCESS DATA

Retrieving Data with ADO

237

(16)

What's ADO and Why Do You Need It?

237

(1)

The ADO Object Model

237

(1)

Using the ADO Connection Object

238

(4)

Opening the Connection

239

(1)

About Connection Strings

240

(2)

Closing a Connection

242

(1)

Working with Command Objects

242

(2)

Creating a Command Object

243

(1)

Executing the Command Object

243

(1)

Understanding the Different Types of Recordsets

244

(1)

Creating and Opening a Recordset

245

(2)

Filtering Recordsets

247

(1)

Using the Recordset Property

248

(2)

Case Study

250

(3)

Who's Connected to the Database?

250

(3)

Manipulating Data with ADO

253

(20)

Moving Through a Recordset

253

(3)

Referencing Recordset Fields

256

(1)

Finding Data in a Recordset

256

(4)

An Alternative to Find---the ADO seek Method

258

(2)

Adding Data Using a Recordset

260

(2)

Deleting Data in a Recordset

262

(1)

Updating Data in a Recordset

263

(3)

Using Transactions to Commit Groups of Records---or Not

266

(2)

Case Study

268

(5)

Using a Recordset Object to Add Items to a Combo Box

268

(5)

Creating Objects with ADOX

273

(14)

What Is ADOX?

273

(1)

Creating Tables

274

(5)

Creating a Table and Columns

275

(2)

Creating Indexes

277

(1)

Creating Relationships

278

(1)

Securing Objects

279

(5)

Creating a New Group

279

(1)

Creating a New User

280

(1)

Changing Object Ownership

281

(1)

Setting Object Permissions

282

(2)

Case Study

284

(3)

Creating a Data Dictionary

284

(3)

Performing Advanced Data Operations

287

(14)

Coding for Concurrency

287

(6)

Understanding Concurrency

287

(2)

Optimistic Locking in ADO

289

(2)

Pessimistic Locking in ADO

291

(2)

Retrieving a User Recordset

293

(1)

Using Other Schema Recordsets

294

(2)

Case Study

296

(5)

Using the Form Error Event to Resolve Locking Errors

296

(5)

IV USING ADVANCED VBA TECHNIQUES IN ACCESS

Working with Data Files

301

(12)

Understanding File I/O

301

(1)

Opening Files

302

(2)

About mode

302

(1)

About access

303

(1)

About locking

303

(1)

A Simple Open Example

303

(1)

Reading from Files

304

(3)

Using Input

304

(2)

Using Line Input #

306

(1)

Using Input #

306

(1)

Writing to Files

307

(2)

Printing to Files

309

(1)

Case Study

310

(3)

Using I/O to Number Lines in a Text File

310

(3)

Automating Other Applications

313

(12)

Understanding Automation

313

(1)

Setting Object References

314

(1)

Creating Objects in an Automation Server

315

(3)

Using CreateObject

316

(1)

Using GetObject

316

(1)

Using Early Binding

317

(1)

Talking to Excel from Access

318

(2)

Talking to Word from Access

320

(2)

Case Study

322

(3)

Using Excel Chart Features from Inside Access

322

(3)

Working with XML Files

325

(12)

An Introduction to XML

325

(1)

Using ExportXML

326

(4)

An Export Example

327

(2)

Exporting a Web-Ready File

329

(1)

Exporting Related Data

329

(1)

Using ImportXML

330

(4)

An Import Example

331

(3)

Case Study

334

(3)

Exporting Up-to-Date Project Information

334

(3)

Using the Windows API

337

(10)

Declaring API Calls

337

(1)

Using API Calls

338

(2)

API Calls That You Can Use From Access

340

(3)

Determining Whether an Application Is Running

340

(1)

Retrieving the Current Username

341

(1)

Getting the Executable for a Data File

342

(1)

Knowing When to Use the Windows API

343

(4)

V APPENDIX

A Review of Access SQL

347

(12)

An Introduction to SQL

347

(1)

SQL Structure and Syntax

348

(2)

Retrieving with SQL Select

350

(4)

The SQL Predicates

350

(1)

The SQL from Clause

351

(1)

The SQL where Clause

352

(1)

The SQL Order by Clause

353

(1)

The SQL Group by Clause

353

(1)

The SQL Having Clause

354

(1)

Modifying with SQL Update

354

(1)

Deleting with SQL Delete

355

(1)

Appending With SQL's Insert Into

355

(1)

Making Tables With SQL Select Into

356

(1)

Creating a Crosstab Query with SQL Transform

357

(2)

Index

359

Please wait while the item is added to your bag...

FREE SHIPPING

Amazon no longer offers textbook rentals. We do!

Automating Microsoft Access with VBA

Summary

Author Biography

Table of Contents

Supplemental Materials