Access Database Design and Programming, Second Edition

By Steven Roman, Ph.D.

Cover | Table of Contents | Colophon

---

Index

[ Symbols ], [ Numbers ], [ A ], [ B ], [ C ], [ D ], [ E ], [ F ], [ G ], [ H ], [ I ], [ J ], [ K ], [ L ], [ M ], [ N ], [ O ], [ P ], [ Q ], [ R ], [ S ], [ T ], [ U ], [ V ], [ W ],

Symbols[ Top ]
! (bang) operator, 222-223
. (dot) operator, 222
* ("many") symbol, 18
# (number) sign, 154
q-joins (theta-joins), 74, 79, 96

Numbers[ Top ]
1NF (first normal form), 40
2NF (second normal form), 42
3NF (third normal form), 43-45

A[ Top ]
AbsolutePage property, 301
AbsolutePosition property (ADO), 301
AbsolutePosition property (DAO), 272
ac symbolic constants, 155
Access
      collections, 226
      CurrentDb function, 246-252
      forms, 137
      housekeeping in, 227
      Object data types, 161
      object model, 220
Access IDE
      major components of, 135-143
      starting, 135
access modes, 295
access permissions, 240
Access SQL, 58, 85-89
      data types, 91
      DDL component, 91-95
      DML component, 95-117
      query types, 87
      reasons for using, 88
      syntax conventions, 90
action queries, 87, 263
ActiveConnection property, 301, 312
ActualSize property, 318
Add method, 227
adding
      records to recordsets, 278
      table columns, 93
AddNew method, 278, 304
address, memory, 213
ADO (ActiveX Data Objects), 283
      commands, 297
      DAO and, 283
      installation, 284-286
      object model, 289
            treelike view of, 290
      objects, 294-320
      OLE DB and, 286-289
      Recordset objects, 292
ADODB prefix, 292
algebraic query languages, 55-57
ALL option
      SELECT statement, 98
      UNION statement, 109
AllowZeroLength property, 256
ALTER TABLE command, 93
anomalies
      deletion anomalies, 9
      insertion anomalies, 9
      update anomalies, 8
apostrophe, for comments, 153
Append method, 226
append queries, 87
AppendChunk method, 318
application-defined properties, 233
application modal dialog box, 183
arguments, 175-179
      named, 177
      optional, 176
      vs. parameters, 175
      positional, 177
arrays, 163-164
As Object declaration, 162
atomic attributes, 40
attribute dependency, 39
attribute names (see table schemes)
attributes, 13-15, 22
      atomic, 40
      environment attribute (ODBC), 381
      fully qualified attribute name, 65
      indivisible, 40
      of ODBC drivers, 381-383
      for Relation objects, 260
      renaming, 59
      scalar, 40
      strictly informational, 38
      structured, 40
      superkeys, 15
      table schemes (see table schemes)
      types of, 37
Attributes property, 315, 318
automatic syntax checking, 148

B[ Top ]
backing up databases, 392
bang (!) operator, 222-223
base tables, 55
BCNF (Boyce-Codd normal form), 45
Beep statement, 192
BOF property (ADO), 302
BOF property (DAO), 270
Bookmark property, 302
bookmarks, 145, 300
Boolean data types, 159
Boyce-Codd normal form (BCNF), 45
break mode, 146
breakpoint, 147
bugs (see errors)
built-in properties (ADO), 314
built-in properties (DAO), 232
buttons, 183-185
      Button types, 183
      buttons pressed, 185
      default button, 184
ByRef parameters, 177
ByVal parameters, 177

C[ Top ]
CacheSize property, 302
call stack, 195
calling (see declaring)
calling procedure, error-handling in, 194
candidate keys, 16
Cartesian product of tables, 65-66
cascading updates/deletions, 28
Case Else part, 205
case-insensitive comparison, 188
characters, repeated, 188
class modules, 138
classes, 13
      DAO, list of, 342-361
      entity classes, 12
client side, 299
client/server architecture, 130
Clone method, 305
Close method (ADO), 296, 298, 305, 308
Close method (DAO), 237
closing objects, 237
code
      bookmarking, 145
      debugging, 150-152
      error handling, 193-199
      examples (ODBC), 365
      executing, 141
      executing in break mode, 152
      long lines of, handling, 153
      stepping (see stepping)
Code window, 139-141
      keyboard shortcuts, 144
collections, 217-220, 226-231
      Collection objects, 343-345
      DAO collections, 237-246
      default, 224
      Properties collection, 231-236
      refreshing, 230, 249
      for specific objects, 342-361
      types of, 226
      user-defined, 227
columns
      adding/deleting, 93
      column definitions, 91
      column headings, 21
      constraint clauses for, 92
      order of, 21
      projection and, 67
COM (Component Object Model), 286
Command objects, 311-314
      methods, 313
      properties, 312
commands
      compound, 307
      executing, 141
      in ADO, 297
CommandText property, 312
CommandTimeout property, 295, 312
CommandType property, 312
comments, 153
compile errors, 149
compiling, 149
compound commands, 307
conceptual database, 124
Connection objects, 295-298
connection strings, 325-338, 375
ConnectionFunction attribute, 383
ConnectionString property, 295
ConnectionTimeout property, 295
constants, 154
      enumeration structure, 155
      scope of, 166
      for Type property, 234, 256
      VBA string functions, 186-189
constraints
      CONSTRAINT clauses, 92
      referential, 27
      table scheme (see normal forms)
containers, 219
      Container objects, 242-243, 345
continuation, 153
control statements, 200-206
Controls collection, 226
Count property
      DAO collections, 226
      Microsoft Access collections, 226
      user-defined collections, 227
counter, variable loop, 201-204
CREATE INDEX command, 94
CREATE TABLE command, 91-93
CreateDatabase method, 253-255
CreateField method, 256
CreateIndex method, 258
CreateParameter method, 313
CreateQueryDef method, 262
CreateRelation method, 260
CreateTableDef method, 255
crosstab queries, 87, 112-115
current position/record in recordsets, 270
CurrentDb function, 246-252
cursors, 299
      CursorLocation property, 295, 299, 302
      CursorType property, 302

D[ Top ]
dangling references, 9, 27
DAO (Data Access Objects), 121-123, 125, 211
      classes, list of, 342-361
      closing objects, 237
      collections, 226-231
      creating QueryDefs objects, 262-266
      creating relations, 260-262
      creating tables, 255-257
      creating/opening databases, 253-255
      DDL component, 253-266
      DML component, 267-279
      object data types, 161
      object model, 217-220
      objects, 237-246, 343-361
      Properties collection, 231-236
      referencing objects, 221-226
data
      editing using recordsets, 276-279
      loss of, 9
      manipulating in database, 128
      NULL values, 35
      persistent data, 11
      viewing, 9
data consumers, 288
data definition language (see DDL)
data dictionary/catalog, 125
data manipulation language (see DML)
data providers, 286-288, 291, 296
data source name (see DSN)
data sources, 286, 368-376
      data source types, 368
      examples, 370-374
      existing on system, 377-380, 383
      functions for connecting to, 374
      (see also DSN)
data stores, 286, 288
data types (see types)
database management system (see DBMS)
Database objects, 218, 237, 240, 345-347
Databases container, 242
database programming
      ADO, 283-286
      benefits of learning, 10
      DAO DDL, 253-266
      DAO DML, 267-279
      VBA programming language, 121
databases, 11
      backing up, 392
      creating
            in DAO, 253-255
            host languages for, 126
            sample database, 385-394
      CurrentDb function, 246-252
      database design, 3-10
      database systems, 123
      deleting, 254
      duplicates, 126, 254
      flat databases, 5
      new databases, defining, 127
      normalizing, 46-50
      opening, 255
      relational (see relational databases)
      resources for further reading, 395
data-definition queries, 87
date constant, 154
Date data type, 161
date-related functions, 207
dates, formatting, 207
DBEngine object, 238, 347-349
DBMS (database management system), 3, 85, 124
      client/server architecture, 130
      (see also Jet DBMS)
DBMS-based drivers, 367
DBQ parameter, 332
DDL (data definition language)
      Access SQL commands, 91-95
      DAO, 253-266
      for Jet DBMS, 127
debugging, 142, 150-152
      commenting out code, 153
      debug mode, exiting from, 152
      (see also run-time errors)
declaration statements
      location of in procedures, 159
      Require Variable Declaration option, 159
declaring
      functions, 173
      subroutine, 174
      variables, 157, 159
decomposition of table schemes, 47-50
default
      button, 184
      collections, 224
      recordset types, 269
DefaultDatabase property, 295
DefaultValue property, 256
DefinedSize property, 318
degree, table, 21, 60
deleting
      cascading deletions, 28
      databases, 254
      Delete method (ADO), 305
      Delete method (DAO), 226, 277
      delete queries, 87, 110
      DELETE statement, 110
      deletion anomalies, 9
      records, 277
      table columns, 93
      tables, 95
dependency
      attribute, 39
      functional, 42
      loss of, 48
      trivial/nontrivial, 42
dependency-preserving decomposition, 50
derived tables, 55
design mode, 146
      Step Into and, 151
design-time errors, 148
dialog boxes, 183
difference of tables, 63-65
dimensions of an array, 163
directories, housekeeping in, 206
DISTINCT option, 98-100, 104-105
DISTINCTROW option, 98-105
DistinctCount property, 260
DLL (dynamic link library), 125, 365
DML (data manipulation language)
      Access SQL component, 95-117
      DAO, 267-279
      for Jet DMBS, 128
Do loop statement, 203
docked windows, 143
Document objects, 219, 244, 349
domains, 21
dot (.) operator, 222
double quotation marks, 154
DriverODBCVersion attribute, 383
drivers
      ODBC Driver Manager, 366
      ODBC drivers, 367, 376, 381-383
DROP statement, 95
DSN (Data Source Name), 328, 336, 368-374
      connection strings and, 376
      creating, 370
      DSN types, 370
      examples, 370-374
duplicate data (see redundancy)
duplicate Recordset objects, 305
duplicate rows, 67
dynamic arrays, 163
dynamic cursors, 299
dynamic properties, 314
dynaset-type Recordset objects, 267, 274

E[ Top ]
E/R diagram, 17
editing
      editing buffer, 303
      with recordsets, 276-279
EditMode property, 303
ElseIf parts, 200
empty strings, 35
End statement, 179
Enforce Referential Integrity box, 31
entities, 12-15, 22
      E/R diagram, 17
      implementing, 19-22
      relationships between, 16
entity classes, 12, 15, 22, 26
      attributes of, 13-15
      implementing, 20
      superkeys, 15, 23
entity sets, 12, 22
      implementing, 20
      superkeys and, 16
entity-relationship diagram, 17
entity-relationship model, 11
enum structure, 155
environment handle, 381
EOF property (ADO), 302
EOF property (DAO), 270
equi-joins, 70-72, 77
errors, 148-150
      break mode, 146
      compile errors, 149
      design-time errors, 148
      error dialog boxes, 147
      error messages
            Data source name not found, 336
            Invalid use of Null, 192
            Item not found, 232
            Project Unviewable, 137
      error object (Err), 196
      Error objects, 238, 349
      handling in code, 193-199
      logical errors, 149
      resources for further reading, 193
      run-time errors, 149
      trapping, 194
Errors property, 295
event procedures, 195
example DSNs, 370-374
Excel (see Microsoft Excel)
Execute method, 297, 313
Exit Do statement, 204
Exit For statement, 202
exiting
      debug mode, 152
      procedures, 179
expressions, query (see views)
external level of database, 124
externally creatable objects, 289

F[ Top ]
fields, 33
      creating for tables, 255-257
      Field objects, 245, 256, 318-320, 350
      Fields property, 303
      properties of, 256
file-based drivers, 367
file data sources, 369
FileExtns attribute, 383
files
      creating text files, 206
      housekeeping, 206
      (see also tables)
FileUsage attribute, 383
Filter property, 303
Find methods, 274
first normal form, 40
fixed-length string variables, 160
flat databases, 5
floating windows, 143
For Each loop statement, 203
For-Next statement (For loop), 201
foreign keys, 23-26
      referential integrity and, 27
forms (see Access, forms)
Forms collection, 226
Forms container, 243
forward-only cursors, 300
FROM clause, 107
frontends, 130
full-module view, 139
fully qualified attribute names, 65
fully qualified object names, 222, 224
fully qualified procedure names, 180
functional dependency, 41
      decomposition and, 48
functions
      connection functions, 366
      for data source connections, 374
      date- and time-related, 207
      declaring, 173
      file-related, 206
      format functions, 207
      Immediate If function (IIf function), 190-191
      information functions, 366
      Is functions, 189
      miscellaneous VBA functions, 189-192
      SQLDrivers function, 381
      SQLSetEnvAttr function, 381
      Switch functions, 191
      built into VBA, 181-183
      VBA string functions, 186-189

G[ Top ]
GetChunk method, 318
GetRows method, 305
global variables, 168
glossary of database terms, 22
GROUP BY option, 107
Group objects, 240, 351

H[ Top ]
HAVING option, 108
help, 145
      for ODBC drivers, 376
host applications, 130
host languages, 126, 130
housekeeping
      of files and directories, 206
      in Microsoft Access, 227

I[ Top ]
Icon types, 184
IDE (Integrated Development Environment), 135
If-Then statement, 200
IgnoreNulls property, 260
Immediate If function (IIf function), 190-191
Immediate window, 141
implementing
      entities, 19-22
      relationships, 23-28
index files, 33-35
Index property, 271
indexes, 258-260
      Index objects, 246, 352
      table column, 93-95
Indexes dialog box, 34
indivisible attributes, 40
information, loss of, 48
Inherited property, 233
initialized variables, 171
in-line error checking, 197
INNER JOIN clause, 96
inner joins, 70-75, 96
      outer joins and, 75-77
      semi-joins, 81-83
InputBox function, 185
INSERT INTO statement, 110
insertion anomalies, 9
Instr function, 187
Integrated Development Environment (see IDE)
integrity (see referential integrity)
Internal ISAM Component, 126
internal level of database, 123
intersection of tables, 62
ISAM (Indexed Sequential Access Method), 126
IsDate function, 189
IsEmpty function, 190
IsMissing function, 176
IsNull function, 190-191
IsNumeric function, 190
Item method, 227

J[ Top ]
Jet Database Engine, 125
Jet DBMS, 125-131
      DLLs and, 125
      host languages for, 126
      integration with Access and Visual Basic, 130
      Replication Engine, 126
Jet Query Engine, 126
JOIN statement, 96
joins, table, 70-77, 95-97
      implementing in Access, 77-81
      nested joins, 96
      self-joins, 97
      semi-joins, 81-83

K[ Top ]
keyboard shortcuts, 144
keys, 23
      candidate keys, 16
      constraint clauses for, 92
      foreign, 23-26
      functional dependency and, 42
      NULLs appearing in, 35
      primary keys, 16
      referenced, 26
      superkeys, 15, 23
      unique indexes, 34
keyset cursors, 300
keywords, 175-180

L[ Top ]
labels, 193
languages
      algebraic query, 55-57
      data definition (see DDL)
      data manipulation (see DML)
      host (see host languages)
      structured query language (SQL), 58, 88
late binding, 162
LCase function, 186
Left function, 186
left outer joins, 63, 75, 80
left semi-joins, 81
Len function, 186
lifetime, 168-170
      vs. scope, 168-169
Like operator function, 188
line-continuation character, 154
ListDPs procedure, 320-326
lists
      of DAO objects, 343-361
      of data sources, 377-380, 383
      of database tables, 328
      naming conventions for variables, 165
      of ODBC drivers, 377-381
      VBA functions and statements, 181-183
literal constants, 154
local constants/variables, 166
LockType property, 300, 303
logical errors, 149
logical pages, 301
logical structure of database, 124
long integers, 159
looping, 201-204
loss of data, 9
lossless decomposition, 50
LTrim function, 187

M[ Top ]
machine data sources, 369
Macros container, 243
make-table queries, 55, 87, 111
many-to-many relationships, 18, 26
MaxRecords property, 303
.mdb file, 237, 254
memory, data types and, 157
metadata, 125
methods
      DAO collections, 226
      for objects, 216, 218
      recordset available methods, 338
      for Recordset objects, 304-311
      for specific objects, 342-361
      user-defined collections, 227
Microsoft Access
      collections, 226
      CurrentDb function, 246-252
      housekeeping in, 227
      object model, 220
      SQL for (see Access SQL)
      (see also entries at Access)
Microsoft Excel, 284
      connecting to Microsoft OLE DB provider for ODBC, 332-334
      example data sources, 370
Microsoft Jet OLE DB provider, 326-328
      connection test, 328
Microsoft OLE DB provider for ODBC, 328
      connecting to Microsoft Excel, 332-334
      connecting to text files, 334-337
Mid function, 186
Mode property, 295
module-level constants/variables, 167
modules
      class, 138
      Modules container, 243
      standard, 137
Move methods, 270-271, 306
moving through recordsets, 270-273
MsgBox function, 183-185
multicolumn constraints, 93
multiple-value problems, 8

N[ Top ]
Name property (ADO), 314, 319
Name property (DAO), 233, 313
named arguments, 177
names
      fully qualified object names, 222, 224
      fully qualified procedure names, 180
      for projects, 137
      for variables, 215
natural joins (nat-joins), 72-74, 77
navigating recordsets, 270-273
nested joins, 96
NextRecordset method, 307
nodes, 135, 137
NoMatch property, 273
nonprocedural languages, 55
normal forms, 39-46
      functional dependency, 41
normalization, 40, 46-50
NULL values, 9, 35
number sign (#), 154
numbers
      converting to strings, 187
      formatting, 207
      in InputBox, 186
Numeric data types, 159
NumericScale property, 319

O[ Top ]
Object box, 140
Object Browser utility, 341
object data types, 161, 212
object properties, 219
object variables, 212-216, 223
      naming, 215
      vs. standard variables, 212
objects, 211-217
      ADO objects, 294-320
      As Object declaration, 162
      closing, 237
      collections (see collections)
      DAO object model, 217-220
      DAO objects, 237-246
      default collections for, 224
      error object (Err), 196
      externally creatable, 289
      fully qualified names for, 222
      Microsoft Access object model, 220
      Properties collection, 231-236
      referencing, 216, 221-226
      Set statement, 162
      viewing, 140
ODBC (Open Database Connectivity), 328, 365-384
      drivers, 367, 381-383
            attributes of, 381-383
            existing on system, 377-381
            help for, 376
            obtaining list of, 381
      ODBC Administrator, 328, 370
      ODBC behavior, 381
      ODBC Driver Manager, 366
      Visual Basic and, 377-384
      (see also Microsoft OLE DB provider for ODBC)
OLE DB providers, 325, 328-338
      finding in Windows registry, 320-325
OLE DB (technology), 283, 286-289
On Error statements, 193, 197
one-to-many relationships, 18, 23-26
one-to-one relationships, 18, 26
Open method, 298, 307
Open statement, 206
OpenDatabase method, 255
opening
      databases, 255
      recordsets, 268-270
OpenRecordset method, 268
OpenSchema method, 298, 328
operators, VBA, 172
optimization, table, 83
Option Explicit, 159
Options parameter, 297-298
ORDER BY option, 98, 108
order of columns, 21
order of table rows, 21
OriginalValue property (ADO), 319
outer joins, 75-77, 80, 96

P[ Top ]
PageCount/PageSize property, 301
pages, 301
parameters, 175
      vs. arguments, 175
      to a function, 174
      Parameter objects, 245, 352
      parameterized queries, 88, 117
Parameters property, 313
PARAMETERS statement, 117
pass-through queries, 87
pattern matching, 188
PercentPosition property, 272
persistent data, 11
physical database, 123
      Internal ISAM component, 126
pointer variables, 213
positional arguments, 177
Precision property (ADO), 319
predicate, 98
Prepared property, 313
primary index, 34
primary keys, 16, 34
Primary property, 260
private constants/variables, 167
Private/Public procedures, 180
procedural languages, 55
Procedure list box, 140
procedure-level constants/variables, 166
procedures, 138, 145-147, 179-180
      break mode, 146
      creating, 145
      executing, 146
      exiting, 179
      Private/Public procedures, 180
      run mode, 146
      stepping and, 152
      viewing, 139
programmatic ID, 325
programming (see database programming)
programs
      readability, 164
      VBA, 394
Project Explorer, 135-138
projection, 67
      (see also SELECT statement)
projects, 136
      project names, 137
properties, 218
      application-defined, 233
      built-in (ADO), 314
      built-in (DAO), 232
      of DAO collections, 226
      editing, 258
      of fields, 256
      of indexes, 260
      of Microsoft Access collections, 226
      Properties collection (ADO), 314
      Properties collection (DAO), 231-236
properties (continued)
      Properties property, 314
      Properties window, 138
      Property objects (ADO), 314-318
      Property objects (DAO), 231, 233, 353
      of QueryDef objects, 264
      recordset available properties, 337
      of Recordset objects, 301-304
      referencing, 216
      for specific objects, 342-361
      types of, 232
      user-defined, 235
      of user-defined collections, 227
      (see also normal forms)
Provider property, 296
public constants/variables, 167

Q[ Top ]
qualified attribute name, 65
queries, 54, 263
      Access design of, 86
      Jet Query Engine, 126
      make-table queries, 55
      parameterized, 88, 117
      query languages, 54
      subqueries, 88, 115-117
      types of, 87
      updatable, 87, 95, 109
Query Design window, 58, 86
query expressions (see views)
QueryDef objects, 241, 353-354
      creating, 262-266
      properties, 264
quotation marks
      adjacent, 160
      double, 154
quotients, 83, 362-364

R[ Top ]
read-only properties, 233
read/write properties, 233
readability
      improved by named arguments, 177
      improved by object variables, 224
RecordCount property, 303
records (see rows)
RecordsAffected parameter, 297
RecordsAffected property, 265
Recordsets collection, 268
recordset cursors, 299
recordsets
      adding records to, 278
      Do loops and, 204
      navigating, 270-273
      Recordset objects, 237, 241, 267-279, 299-311, 354-357
            creating, 299
            duplicates, 305
            methods, 304-311
            obtaining in ADO, 292
            opening, 268-270
            properties, 301-304
            searching, 273-275
redimensioning arrays, 164
redundancy, 5-8, 37-39
referenced keys, 26
referenced table schemes, 26
referencing methods and properties, 216
referencing objects, 213, 221-226
referential constraints, 27
referential integrity, 9, 27, 36
      NULL in keys and, 36
Refresh method, 226
refreshing collections, 230, 249
Relations collection, 241
Relations container, 242
Relation objects, 260-262, 357
relational algebra, 55-59
      table operations, 57-84
relational calculus, 55-57
relational databases, 5-10, 23
      entity-relationship model, 11
relations, VBA, 172
relationships
      between entities, 16
      between tables, 96
      implementing, 23-28
      integrity of (see referential integrity)
      object-collection relationship, 219
      types of, 18
Relationships view, 30
remote database model, 131
Remove method, 227
renaming table attributes, 59
Replication Engine, 126
Reports collection, 226
Reports container, 243
Requery method, 308
Require Variable Declaration option, 159
Required property, 256, 260
restriction of tables (selection), 67-70
result tables, 54
Resume statements, 198
Resync method, 308
Right function, 186
right outer joins, 75, 81
right semi-joins, 81
rows, 33
      duplicate, 67, 98
      inserting, 110
      order of, 21
      selection and, 67-70
RTrim function, 187
run mode, 146
Run to Cursor feature, 152
run-time errors, 149, 193-199

S[ Top ]
sample database, obtaining, 385-394
scalar attributes, 40
schema.ini, 373
scope, 166
      vs. lifetime, 168-169
SDI (single document interface), 137
searching recordsets, 273-275
second normal form, 42
secondary index, 34
Seek method, 273
Select Case statement, 205
SELECT- INTO statement, 111
select queries, 87
SELECT statement, 97-108
      DISTINCT option, 98-100, 104-105
      DISTINCTROW option, 98-100, 104-105
      for subqueries, 115-117
selection, 67-70
      criterion for, 68
      select queries, 263
      (see also SELECT statement)
self-joins, 97
semantic models, 17
semi-joins, 81-83
semiqualified names, 222
SEQUEL, 85
server side, 299
service providers, 288
Set Next Statement feature, 152
Set statement, 162
shortcuts, keyboard, 144
single document interface (SDI), 137
single-column constraints, 92
size, table, 21
snapshot-type Recordset objects, 268, 274
Source property, 304
Space function, 188
spaces
      removing, 187
      string comprising, 188
split-screens, 143
SQL Server, 284
SQL (Structured Query Language), 58, 88
      DAO and, 122
      SQL queries, 87
      (see also Access SQL)
SQL View, 86
SQLBrowseConnect function, 383
SQLConnect function, 375, 383
SQLDataSources function, 383
SQLDriverConnect function, 376, 383
SQLDrivers function, 381
SQLLevel attribute, 383
SQLSetEnvAttr function, 381
square brackets (optional parameters), 183
standard modules, 137
State property, 296, 304
statements
      Beep statement, 192
      changing order of execution, 152
      control statements, 200-206
      error-handling statements, 193-199
      miscellaneous VBA statements, 189-192
      stepping and, 151
      built into VBA, 181-183
static cursors, 300
static variables, 169-171
stepping
      Step Into feature, 151
      Step Out feature, 152
      Step Over feature, 152
Stop statement, 147
Str function, 187
StrCmp function, 188-189
strictly informational attributes, 38, 42
strings
      comparing, 188-189
      converting to number, 187
strings (continued)
      formatting, 207
      string constants, 154
      String data types, 160
      String function, 188
structured attributes, 40
subqueries, 88, 115-117
subroutines, declaring, 174
subschemes (see views)
subsets of tables
      projection and, 67
      selection (restriction) of, 67-70
suffixes, type-declaration, 158
superkeys, 15, 23
      functional dependency and, 42
Supports method, 309
Switch function, 191
symbolic constant, 154
syntax conventions, 90
syntax errors, checking for, 148
system data sources, 369
system modal dialog box, 183

T[ Top ]
table schemes, 20, 122
      decomposition, 47-50
      normal forms for, 39-46
TableDef objects, 229, 241, 358
tables, 20, 23
      ALTER TABLE command, 93
      base tables, 55
      Cartesian product of, 65-66
      CREATE TABLE command, 91-93
      creating in DAO, 255-257
      degree of, 60
      deleting or deleting from, 95, 110
      derived tables, 55
      difference of, 63-65
      indexes, 258-260
      inserting data into other, 111
      inserting rows, 110
      intersection of, 62
      joins, 70-81, 95-97
      optimization, 83
      projection of, 67
      quotients, 362-364
      relationships between, 96
      result tables, 54
      selection (restriction) of, 67-70
      semi-joins, 81-83
      union of, 59-62, 87, 109
      updating, 109
      virtual (see views)
Tables container, 242
table-type Recordset objects, 267, 273
tabular format, 287, 299
test, connection (Microsoft Jet OLE DB provider), 328
text files
      connecting to ODBC provider, 334-337
      example data source, 373
theta-joins, 74, 79, 96
third normal form, 43-45
three-tier structure, 123
time, 161, 207
TOP option (SELECT statement), 98
tracing, 150-152
      (see also stepping)
TRANSFORM statement, 112-115
Trim function, 187
trivial/nontrivial dependency, 42
type conversion functions, 187
Type property (ADO), 315, 320
type-declaration suffixes, 158
types
      Boolean data types, 159
      Button types, 183
      data source types, 368
      data types, 91, 157-163
      Date data types, 161
      driver types, 367
      DSN types, 370
      Icon types, 184
      Numeric data types, 159
      object data types, 161, 212
      of properties, 232
      query types, 87
      recordset types, 269, 337-338
      String data types, 160
      Type property, 233, 256
      Variant data types, 158, 161
      VBA data types, list of, 157

U[ Top ]
UBound function, 164
UCase function, 186
UnderlyingValue property (ADO), 319
underscore, for line continuation, 154
union of tables, 59-62, 87, 109
      union queries, 87
UNION statement, 109
unique index, 34
Unique property, 260
Update method, 276, 278, 311
UPDATE statement, 109
updates
      anomalies, 8
      cascading, 28
      record, 276
      updatable queries, 87, 95, 109
user data sources, 369
user-defined collections, 227
user-defined properties, 235
User objects, 240, 359
users
      beeping, 192
      displaying message to, 183-185
      event procedures and, 195
      getting input from, 185, 375-376

V[ Top ]
Val function, 187
ValidationRule and ValidationText properties, 257
Value property (ADO), 315, 319
Value property (DAO), 234
variable-length string variables, 160
variables, 157
      changing value of, 178
      declaring, 157, 159
      initialized, 171, 190
      IsNull function, 190
      lifetime of, 168-170
      naming conventions for, 164
      object variables, 212-216, 223
            naming, 215
            vs. standard variables, 212
      pointer variables, 213
      scope of, 166
      VBA string functions for, 186-189
Variant data types, 161
variants, 158-159, 161
vb symbolic constants, 155
VBA (Visual Basic for Applications), 121, 125
      built-in functions and statements, 181-183
      control statements, 200-206
      data types, list of, 157
      further study using help files, 206
      help files, 145
      miscellaneous functions and statements, 189-192
      operators and relations, 172
      procedures, 138
      resources for further reading, 206
      rules for initializing variables, 171
      running programs, 394
      string functions, 186-189
      variable naming conventions, 215
Version property, 296
viewing
      objects, 140
      output, 141
      procedures, 139
      split-screen, 143
views, 9, 55, 124
virtual tables, 55
Visual Basic
      resources for further reading, 138
      using to obtain ODBC information, 377-384
Visual Basic Editor, 135
      keyboard shortcuts, 144

W[ Top ]
watch statement, 147
WHERE clause
      SELECT statement, 98, 107
      UPDATE statement, 110
windows
      docking, 143
      floating, 143
Workspace objects, 218, 237, 239, 360

Return to Access Database Design and Programming