Access Database Design and Programming

Microsoftr AccessTM is the industry's leading desktop and departmental database product. More complex than the other applications in the Microsoft Office suite, Access requires careful database planning and custom programming using the VBA language to bring out its true power and flexibility. For programmers who prefer content to frills, Access Database Design & Programming is a sure bet. Succinct and straightforward, it may be the most information packed 400 pages in print for putting Access to its full, individually tailored use. This expanded introduction to design and programming with the most common database operations will satisfy those who want to go beyond step-by-step learning to grasp the principles behind well built databases.

Preface

xi

Part I. Database Design

Introduction

3

(8)

Database Design

3

(6)

Database Programming

9

(2)

The Entity-Relationship Model of a Database

11

(7)

What Is a Database?

11

(1)

Entities and Their Attributes

11

(4)

Keys and Superkeys

15

(1)

Relationships Between Entities

16

(2)

Implementing Entity-Relationship Models: Relational Databases

18

(17)

Implementing Entities

18

(3)

A Short Glossary

21

(1)

Implementing the Relationships in a Relational Database

22

(5)

The Library Relational Database

27

(4)

Index Files

31

(2)

Null Values

33

(2)

Database Design Principles

35

(16)

Redundancy

35

(2)

Normal Forms

37

(1)

First Normal Form

38

(1)

Functional Dependencies

39

(1)

Second Normal Form

40

(1)

Third Normal Form

41

(1)

Boyce-Codd Normal Form

42

(1)

Normalization

43

(8)

Part II. Database Queries

Query Languages and the Relational Algebra

51

(30)

Query Languages

52

(1)

Relational Algebra and Relational Calculus

53

(2)

Details of the Relational Algebra

55

(26)

Access Structured Query Language (SQL)

81

(34)

Introduction to Access SQL

81

(1)

Access Query Design

81

(2)

Access Query Types

83

(1)

Why Use SQL?

84

(1)

Access SQL

85

(1)

The DDL Component of Access SQL

86

(4)

The DML Component of Access SQL

90

(25)

Part III. Database Architecture

Database System Architecture

115

(14)

Why Program?

117

(1)

Database Systems

117

(1)

Database Management Systems

118

(1)

The Jet DBMS

119

(2)

Data Definition Languages

121

(1)

Data Manipulation Languages

122

(1)

Host Languages

123

(1)

The Client/Server Architecture

124

(5)

Part IV. Visual Basic for Applications

The Visual Basic Editor, Part I

129

(8)

The Project Window

129

(3)

The Properties Window

132

(1)

The Code Window

132

(2)

The Immediate Window

134

(1)

Arranging Windows

135

(2)

The Visual Basic Editor, Part II

137

(9)

Navigating the IDE

137

(1)

Getting Help

138

(1)

Creating a Procedure

139

(1)

Run Mode, Break Mode, and Design Mode

139

(1)

Errors

140

(3)

Debugging

143

(3)

Variables, Data Types, and Constants

146

(19)

Comments

146

(1)

Line Continuation

146

(1)

Constants

147

(2)

Variables and Data Types

149

(15)

VBA Operators

164

(1)

Functions and Subroutines

165

(8)

Calling Functions

165

(1)

Calling Subroutines

166

(1)

Parameters and Arguments

167

(4)

Exiting a Procedure

171

(1)

Public and Private Procedures

171

(1)

Fully Qualified Procedure Names

172

(1)

Built-in Functions and Statements

173

(18)

The MsgBox Function

174

(2)

The InputBox Function

176

(1)

VBA String Functions

177

(3)

Miscellaneous Functions and Statements

180

(3)

Handling Errors in Code

183

(8)

Control Statements

191

(10)

The If Then Statement

191

(1)

The For Loop

192

(1)

The Exit for Statement

193

(1)

The For Each Loop

194

(1)

The Do Loop

194

(1)

The Select Case Statement

195

(1)

A Final Note on VBA

196

(5)

Part V Data Access Objects

Programming DAO: Overview

201

(40)

Objects

201

(6)

The DAO Object Model

207

(2)

The Microsoft Access Object Model

209

(2)

Referencing Objects

211

(4)

Collections Are Objects Too

215

(5)

The Properties Collection

220

(5)

Closing DAO Objects

225

(1)

A Look at the DAO Objects

226

(8)

The CurrentDb Function

234

(7)

Programming DAO: Data Definition Language

241

(13)

Creating a Database

241

(2)

Opening a Database

243

(1)

Creating a Table and Its Fields

243

(3)

Creating an Index

246

(2)

Creating a Relation

248

(2)

Creating a QueryDef

250

(4)

Programming DAO: Data Manipulation Language

254

(15)

Recordset Objects

254

(1)

Opening a Recordset

255

(2)

Moving Through a Recordset

257

(3)

Finding Records in a Recordset

260

(2)

Editing Data Using a Recordset

262

(7)

Part VI. ActiveX Data Objects

ADO and OLE DB

269

(57)

What Is ADO?

269

(1)

Installing ADO

270

(2)

ADO and OLE DB

272

(2)

The ADO Object Model

274

(30)

Finding OLE DB Providers

304

(5)

A Closer Look at Connection Strings

309

(13)

An Example: Using ADO over the Web

322

(4)

ADOX: Jet Data Definition in ADO

326

(11)

The ADOX Object Model

326

(11)

Part VII. Programming Problems

Some Common Data Manipulation Problems

337

(76)

Running Sums

337

(3)

Overlapping Intervals I

340

(1)

Overlapping Intervals II

341

(3)

Making Assignments with Default

344

(2)

Time to Completion I

346

(1)

Time to Completion II

347

(2)

Time to Completion III--A MaxMin Problem

349

(3)

Vertical to Horizontal

352

(2)

A Matching Problem

354

(1)

Equality of Sets

355

(6)

Part VIII. Appendixes

A. DAO 3.0/3.5 Collections, Properties, and Methods

361

(17)

B. The Quotient: An Additional Operation of the Relational Algebra

378

(3)

C. Open Database Connectivity (ODBC)

381

(20)

D. Obtaining or Creating the Sample Database

401

(10)

E. Suggestions for Further Reading

411

(2)

Index

413

What is included with this book?

The New copy of this book will include any supplemental materials advertised. Please check the title of the book to determine if it should include any access cards, study guides, lab manuals, CDs, etc.

The Used, Rental and eBook copies of this book are not guaranteed to include any supplemental materials. Typically, only the book itself is included. This is true even if the title states it includes any access cards, study guides, lab manuals, CDs, etc.

FREE SHIPPING

Amazon no longer offers textbook rentals. We do!

Access Database Design and Programming

Summary

Table of Contents

Supplemental Materials