D

DAO. See Data Access Objects (DAO)

DAP (data access page) support, 14, 100

data.

See also collecting data via e-mail;

data selection;

validating data

attachment controls for storing complex, 530–533

changing

attributes of, 225–233

and deleting form, 550–551

editing in SharePoint lists, 1168–1172

maximum length of, 230–231

conversion errors in, 231–232

creating book’s sample, 1030–1044

defined, A10

deleting inactive, 510–511

duplicate name checking for, 1071–1073

effect of Format and Decimal Places properties on underlying table, 654

entering in first table, 149–151

event properties for changing, 871–873

events when editing form, 884

exporting, A79-A83

to another Access database, A79-A80

to ODBC database, A82-A83

to SharePoint Services, 1182–1186

to spreadsheet or dBASE or Paradox file, A80

to text file, A81

to Word mail merge document, A81-A82

fetching

for client/server applications, 1321

on dynamic Web pages with ASP, 1158

recordsets, 1548

field independence of, A24-A26

filtering

in PivotTables, 470

in Report view, 747–748

result of search, 405–412

table, 178

fixing errors in imported spreadsheet, 280–282

formatting properties for, 652–661

identifying for new database, A5

input masks for

defining, 170–174

limitations of, 174

storing input mask formatting characters, 173

inserting from another table, 502–507

interacting with report, 745–750

keyboard shortcuts for datasheet entry, 391

macro actions printing, A105-A106

manipulating in RDBMS, 9–11

modifying in forms, 119–120

moving to database software, 15–17

normalizing for joined tables, 414

organizing before constructing applications, A5

partitioning, 447–449

recordset, 686

replacing in Datasheet view, 394

searching for

and filtering, 405–412

macro actions, A111

selecting

and changing in Datasheet view, 393

layout choices in Form Wizard, 697

to perform tasks, All

query data from single table, 349–351

sharing

and controlling in Access, 12–13

with SharePoint Services, 1161–1163

simplifying input, 601–607

toggle buttons, check boxes, and option buttons, 605–607

using combo and list boxes, 601–605

smart tag use incompatible with, 664

sorting in Datasheet view, 401–405

specifying for collection via e-mail, 309–310, 330–331

types of invalid, 928–929

validating with macros, 928–934

viewing queries in various ways, 10–11

Data Access Objects (DAO), 981–985

collections in, 983, 984

commonalities between ADODB and ADOX models and, 987

DBEngine object, 979, 982–984

FindFirst method for locating rows in form recordset, 1104

manipulating complex data types, 997–1000

status of technology, 981–982

working with DAO recordsets, 994–997

data access page (DAP) support, 14, 100

data analysis, A13-A16

choosing database subjects, A13-A16

mapping subjects to database, A16

data collection messages

customizing, 318–319, 334–335

data conversion error, 232

generating field error, 168

managing and resending data collection, 343–345

moving to Outlook Inbox, 325

previewing in Outlook, 321–322

security warnings, 24, 25

sending, 336–337, 340–341

warnings

for field definition deletions, 225

for reserved words or function names, 159

writing for InfoPath forms, 334–335

data control

data sharing and, 12–13

defined, 6,

data definition

Access, 6–9

defined, 5,

data definition queries, 463

data documents (.xml)

about, 1237

embedding schema and presentation information in, 1237

example of, 1238–1239

data entry

automating, 1053–1071

filling in related data, 1053–1057

fixing e-mail hyperlink, 1061–1062

handling NotInList event, 1058–1061

providing graphical calendar, 1063–1068

working with linked photos, 1069–1071

designing forms for, 518

matching closest entry when typing, 605

Data Entry mode, 543–544, 545

data integrity

ensuring business rules in queries for, 417–419

maintaining application’s, 413

presetting field values to ensure, 935–938

Data Link Properties dialog box, 1454–1457

attaching database files to server, 1455

selecting file as database name, 1456

specifying connections, 1454

using Windows NT Integrated Security, 1454, 1455

data manipulation

Access and, 9–11

defined, 5,

data selection

automating in Visual Basic, 1082–1097

filtering one list with another, 1095–1097

multiple-selection list boxes, 1082–1086

providing custom query by form, 1086–1093

selecting from summary list, 1094–1095

criteria for selection

avoiding keywords as, 356

Boolean operators as, 356

building date/time, 356–357

common mistakes using compound, 359

entering for queries, 355–356

filtering, 407–408

noncontiguous

deselecting fields, 579

holding down Ctrl key for, 579, 1082

selecting multiple noncontiguous files in list box, 1082–1086

providing custom query by form, 1086–1093

selecting

all controls in control layout, 639

all controls in vertical or horizontal area, 588, 600

data to perform tasks, All

multiple fields, 579

working with multiple-selection list boxes, 1082–1086

data sources

creating, 255–259

importing into new SharePoint table, 1187–1188

linking SharePoint list to Access, 1191–1195

read permission for secured, 273

record sources

binding to table, 609–610

building multiple-table query to serve as, 686–688

choosing query used as report’s, 1572

creating query on form property’s, 576–578

defined, 566

in-line functions as report, 1513

selecting report’s, 791–792

specifying, 566–567

subquery providing search criteria from underlying, 1093

using field in calculations from specified report, 834

selecting for importing to Access, 267–268

unable to connect to database with multiple, 302

Windows SharePoint Services site as database, 1207

Data tab (Export XML dialog box), 1247–1248, 1251

Data tab (property sheet), 575

Data Type list box, 109

data types.

See also specific data types

assigning for query parameters, 451

chart of Access and SQL parameter, A53

converting, 226–230

action query data conversion errors, 512

conversion effects on field contents, 226

dBASE-to-Access conversions, 264

dealing with conversion errors, 231–232

Hyperlink, 229–230

limitations on data type conversions, 226–229

Paradox-to-Access conversions, 266

SQL-to-Access conversions, 270

Visual FoxPro-to-Access conversions, 270

determining spreadsheet, 274–275

disadvantages of Lookup Wizard, 245

field, 157–161

formatting characters for Text, 655–656

formatting Yes/No, 660

grouping specifications and, 765

limitations on conversions, 226–229

manipulating complex DAO, 997–1000

naming Visual Basic variables with prefix of, 966

selecting SQL Server, 1459

SQL Server column, 1461–1464

sql_variant, 1465

supported in Visual Basic, 961–963

unable to change for fields joining tables, 204

unavailable for e-mail data collection, 311

unavailable in Group, Sort, And Total pane, 763

user-defined, 1465

viewing database’s valid, 108–109

database design concepts, A16–A28

about, A16–A17

developing efficient relationships, A27–A28

creating linking tables, A28

foreign keys, A27

one-to-many and one-to-one relationships, A28

normalization, A19–A27

field independence, A24–A26

field uniqueness, A19–A20

four rules of table design, A27

functional dependence, A21–A23

unique identifier or primary key, A20–A21

waste in single table design, A17–A19

when to break design rules, A28–A31

capturing point-in-time data, A29–A31

creating report snapshot data, A31

improving performance of critical tasks, A29

database diagrams, 1485–1488

Database Documenter, 199–200

database management systems (DBMS), 1159, 1160

database objects, 49, 50

Database Splitter wizard, 1320–1323

database subjects

choosing, A13–A16

mapping to database, A16

Database Tools tab, 24, 44–45

Database window. See Navigation Pane

databases, 102–132.

See also database design concepts;

tables

AutoCorrect options for, 193

backing up tables or full, 205–207

capabilities of RDBMS, 5–6

checking version of, 1327

compacting, 250–251

created in Datasheet view, 148–149

creating

data source as link to ODBC, 255–259

empty, 147–149

tables for, 151–156

customizing Quick Access Toolbar commands for, 28–29

data modifications in Form view, 119–120

data vs. information in designing, A10

DBEngine object in, 979, 982–984

defined, 4, 99

defining

fields, 156–157

form in Design view, 114–117

delivering dynamic query results on Web page, 1158–1160

displaying list of tables, 105–106

distributing execute-only, 1329–1330

enabling untrusted, 35–36

encrypting with password, 1334–1336

entering data in first tables, 149–151

establishing variable pointing to current, 989

exit code for Contacts.aacdb, 1312–1315

exporting data

to another Access, A79–A80

to ODBC, A82–A83

as static HTML file, 1140–1143

formats compatible with Access, 9,

four-digit year format for individual or all, 192, 1489

guidelines for enabling content, 916

importing

Access object from other Access, 271–273

and linking data to, 253

source data into new tables in, 276

improved reliability of Visual Basic project, 950

limitations of, 201

linking

data to, 253

dBASE and Paradox files to Access, 298–299

tables with other Access, 295–298

listing queries by object type, 110–111

loading reference to Access Database Engine Object Library in, 981

macros

defining multiple actions for, 129–130, 895–897

working with in, 128–130

migrating to SharePoint

about, 1207–1208

opening database from SharePoint, 1223–1226

publishing vs., 1207

republishing after changing database, 1207, 1219–1222

steps to move database, 1212–1219

ways SharePoint handles migrated data, 1218–1219

working online with migrated data, 1217

modules in, 130–132

opening, 102–103

existing, 22–25

options for extending Access, 137–138

packaging and signing, 1336–1339

printing table definitions for, 199–200

project

building new SQL Server, 1448–1451

connecting to existing SQL Server, 1452–1457

defining table relationships in database diagrams, 1485–1488

errors creating, 1452

naming conventions for SQL Server, 1451

projects vs. databases, 1450

publishing to SharePoint site, 1208–1211

about, 1208

.accdb file format required, 1210

downloading SharePoint databases to trusted locations, 1225

moving vs., 1207

publishing changes to migrated database, 1217

republishing after changing database, 1207, 1219–1222

selecting and publishing file, 1210–1211

randomly loading data to sample, 1030–1032

read-only, 1224

reasons for modifying, 203–204

relational, 4–5

reports

changing definitions of, 121–124

function of for, 120–121

interactive Report view for, 127–128

previewing before printing, 124–125

viewing in Layout view, 125–127

resolving changes in shared, 1231

role of forms in, 114

running

Performance Analyzer on, 1302–1303

queries in Datasheet view, 113–114

saving changes to, 205

selecting

Layout view for forms, 118–119

SQL Server, 1451

setting options in VBE, 198

spreadsheet or text files linked to Access, 300–301

SQL tables linked to Access, 301–302

startup properties for, 1310–1312

synchronizing after offline changes, 1230–1233

templates

customizing default, 195–199

selecting, 143–145

using to create, 142–147

testing in runtime mode, 1329

using Design view with, 107–108

viewing

module objects in, 942–943

navigation objects in groups, 105

wildcard characters for string comparisons, A49

working offline, 1226–1229

data-driven design, A7

Datasheet category (Access Options dialog box), 90

Datasheet command, 592–593

Datasheet Formatting dialog box, 1146

Datasheet Formatting Dialog Box Launcher button, 1145

Datasheet view

adding records in, 391

buttons in, 385

copying and pasting data in, 394–396

creating and customizing Windows SharePoint Services, 1173–1175

database created in, 148–149

deleting rows in, 396

expressions viewed in, 363, 368

extra table rows in, 111

forms

detecting filters applied to, 876

setting datasheet format for, 1145

hyperlinks

editing, 400–401

working with in, 397–400

keyboard shortcuts in, 386, 391

navigation buttons for records, 386

preventing design changes in applications, 1311

properties for trapped keyboard and mouse events, 876–878

queries

setting datasheet format for, 1145

working with, 384–412

record indicators in, 390

replacing data in, 394

reports, detecting filters applied to, 876

selecting and changing data in, 393

SharePoint

modifying layout in, 1178

similarity between lists and tables in, 1169

sorting data in, 401–405

subforms

defining subdatasheet, 710–713

sizing controls in, 704

using in, 701

viewing, 539, 541

switching to other view

Design view, 109

PivotChart view, 479

tables, setting datasheet format for, 1145

viewing

combo box in, 241–242

crosstab query design in, 445

query field’s properties in, 354

query results run in, 113–114

tables in, 109–110

union queries in, 465

datasheets.

See also subdatasheets

adding records, 391

copying and pasting data in, 394–396

creating quick table, 592–593

deleting rows in, 396

editing

hyperlinks in, 400–401

individual, 1146

making changes affecting all, 1143–1144

expanding subdatasheets, 387–390, 461

exporting to HTML with formatting, 1146–1148

filtering sections of, 407–408

modifying keyboard options for, 392–393

navigating in records, 386

preventing data modification directly on, 413

record indicators on, 390

replacing data in, 394

selecting and changing data in, 393

data-typing characters, defining variable’s data type with, 962

date.

See also Date/Time data type

adding print date to reports, 830–832

checking for overlapping, 1078–1080

date/time field, 1063

defining query parameters relative to current, 495

deleting rows using date query parameter, 507–510

excluding query requests by, 1109

filtering records for append query by, 503–504

formats for, 508

four-digit year values, 191–192, 651, 1489

functions

Date, 503, 830–831

DateDiff, 365–368, 373

time and date, 375–376, A87

limiting records with custom range of, 812–813

predefined date filters, 408–410

querying across date spans, 428

using in selection criteria, 356–357

validation rules set by, 670

years supported in SharePoint, 128

Date And Time dialog box, 832

Date function, 503, 830–831

Date Picker, 727

Date/Time data type

about, 158

contents of, 160

converting, 229

displaying Null values for, 657

field property for, 162

Format property settings for, 657–658

formatting characters for, 658–660

report grouping options for, 818

date/time field, 1063

date/time functions, A87

DateDiff function

building expression with, 366–368

interval settings for, 365–366

multiplying integer with currency fields, 373

DatePart function, 365

dBASE files

exporting Access data to, A80

importing, 260–264

linking to Access databases, 293, 298–299

DBEngine object, 979, 982–984

DBMS (database management system), 1159, 1160

Debug toolbar, 951, 952, 956

debugging

macros, 890

Visual Basic, 951–960

call stack, 959–960

preventing, 1312, 1328

setting breakpoints, 951–952, 957

using Immediate window, 952–956

working with Watch window, 956–959, 960

Decimal Places property, 654

decimals (.)

indicating numbers and currency, 653

using decimal fields in SharePoint Services, 1219

DECLARE statements, 1531

declaring

functions, 1006–1007

parameters

to filter in-line functions, 1515, 1516

in parameter queries, A52–A54

subroutines, 1007–1009

variables and constants

Const statement, 965

Dim statement, 966–969

Enum statement, 969–970

Event statement, 971–972

Private statement, 972–973

public, 963–964

Public statement, 973–974

ReDim statement, 974–975

Static statement, 975–976

Type statement, 977–978

Default Max Records property, 1550, 1555–1557

Default View property, defining for reports, 746

defaults

changing for Output All Fields property, 452–453

creating custom templates for, 195–199

ensuring unique company and contact, 1077–1078

overriding field name, 377

placeholder character, 171

record locking settings for linked data, 294, 295

restoring Quick Access Toolbar, 33

setting form and control, 680–683

changing with Set Control Defaults button, 680–681

defining AutoFormatting for control, 681

defining template form, 682–683

Unique Records property, 456–457

delete queries

about, 485

backing up before running, 510

command on Design contextual tab for, 488

confirming actions of, 510

defined, 485

deleting inactive data with, 510–511

icon for, 490

testing rows affected by, 507–510

using, 510

DELETE statement, A72–A73

deleting.

See also delete queries

check constraints, 1480

columns, 150

control labels, 581

embedded macros, 906–907

field definitions, 225

fields, 225

form data, 550–551

form properties controlling filtering and modifications, 673–674

image path for photograph, 1070–1071

inactive data, 510–511

lists in SharePoint, 1198

message box confirming, 396

objects with macro action, A115

records

in InfoPath form, 340

testing code for related records when, 1074–1075

relationships between tables, 210

rows

in ADO keyset, 1004

by date, 451, 507–510

in datasheets, 396

tables, 209–210

temporary variables, 909–910

watched items, 960

delimited text files

about, 282

setting up data for, 283–284

tab field delimiter for, 288

dependencies

AutoCorrect options for names of, 192–193

checking object, 207–209

Descending button, 402, 409

descending sort order, 378

Description property for fields, 156

deselecting noncontiguous fields, 579

Design contextual tab

editing relationships from, 184

illustrated, 122, 564

Query Type group on, 488, 509

running reports from, 121

design grid of Query window

dragging and dropping fields into, 352

illustrated, 349

design strategies for applications, A7–A13

analyzing tasks, A7–A10

organizing tasks, A11–A13

selecting data to perform tasks, All

types of, A7

Design tab

Form Design Tools

Controls group buttons, 568–572, 583–584, 645–646

Font group buttons, 582–583, 761

Design view

aligning and sizing controls, 609–622

adjusting layout of form controls, 616–617, 626–627

aligning form labels, 619–621

Layout view vs., 623, 642

lining up controls, 619–622

sizing controls to fit content, 612–616, 631

snapping controls to grid, 617–619

bigint data type unsupported in, 1461

event properties unavailable for, 871, 873, 875, 876, 878

field properties on General tab of, 161–165

forms

designing structure of, 114–117

polishing subform design, 698–700

preventing users from opening in, 671

moving between parts of window, 222

opening and closing property sheet in, 576

project tables

creating in, 1457–1459

defining relationships for, 1482–1485

wizards for .accdb applications unavailable, 1457

queries opened in, 112–113, 349, 351

reports

changing report definition in, 121–124

contextual tabs for, 121–122

opening subreport, 742

using Layout vs. Design view, 125–127

viewing page number controls in, 806

showing all columns for SharePoint lists, 1206

switching view

to Datasheet, 109

to PivotChart, 479

tables

creating in, 155–156

defining validation rules for, 176

modifying query table design, 501–502

reviewing in, 107–108

designing.

See also advanced form design;

advanced report design

application prototype, A5

database applications, A3–A31

charting task flow, A4

constructing application, A5

data analysis, A13–A16

database design concepts, A16–A28

designing prototype and user interface, A5

identifying data elements, A5

identifying tasks, A4

organizing data, A5

overview, A3–A4, A6

process-driven and data-driven design, A7

rationale of order-entry examples in, A8

strategies for, A7–A13

testing, reviewing, and refining, A6

understanding work process, A4

when to break design rules, A28–A31

reviewing input form in Form view, 589

subreports, 855–857

switchboard forms, 1306–1310

tables, 142

integrity checks in table design, 413

normalizing data, 414

PivotTables, 473–478

setting design options in Access Options dialog box, 191–195

storing calculated value in table, 1114

using query designer, 415

desktop databases. See .accdb files; applications; databases

desktop shortcuts to Access application, 1331–1334

Detail Fields drop zone, 474

Detail section

form

about, 518–519

grid dots in, 564–565

selecting width and height, 566

report

embedding PivotChart in, 861–863

expression values used with conditional formatting, 849

illustrated, 740

numbering lines in, 846–848

performing calculations on, 834–837

Details view (Navigation Pane), 77

diagram pane (query designer)

adding columns to, 1499–1450

working in, 1497, 1499, 1500–1503

Diagram window

adding table to, 1485–1486

defining relationship between tables in, 1487–1488

saving diagram, 1488

viewing project table relationships in, 1486

Dialog Box Launcher button (Office Clipboard task pane), 395

dialog boxes. See confirmation dialog boxes; and specific Access dialog boxes

digital certificates

packaging signed database with, 1337

purchasing, 1336

self-signing, 1337

digital signatures

about digitally signed files, 1336

omitted from sample databases, 35

responding to security notice about, 1338–1339

signing .accdb files, 1338

Dim statement, 966–969

disabling

Layout view, 1304

Message Bar, 38

Microsoft Office Genuine Advantage confirmation dialog box, 145

Minimize or Maximize button, 675

Module and Class Module button, 1330

single stepping through macros, 895

special keys, 1312

unused Ribbon tabs, buttons, and groups, 1283–1284

Windows Vista’s User Account Control, 197

warning smart tags, 771

Discard My Changes button, 1232

discarding e-mail replies, 314

display labels for PivotCharts, 482–483

displaying

all table fields in field list, 563–564

Caption property, 586, 587

complex information in embedded subforms, 1577

custom Ribbon errors, 1274

document tabs, 85–86

file name extensions, 102

form properties, 586–587

function queries, 1525

HTML behind Web page, 1128

list of database tables, 105–106

list of queries by object type, 110–111

long lines of code, 925

Macro Name and Condition columns in Macro window, 891, 922

messages or beeping, A115

multiple-value fields in crosstab queries, 444

Navigation Pane

custom group names, 62–63

single category, 81

system objects and system tables, 1267

Null values, 657

object shortcuts, 72–75

ScreenTips, 104

SQL pane of query designer, 1498

subdatasheet properties, 388

text in color specified, 654, 656

USysRibbons table, 1266–1267

values in option group, 714–716

Visual Basic Editor modules, 131–132

DISTINCTROW clauses, A34, A58–A59, A65

distributing applications, 1319–1340

creating application shortcut, 1331–1334

encrypting database with password, 1334–1336

execute-only databases, 1329–1330

packaging and signing database, 1336–1339

understanding runtime mode execution, 1328–1329

using linked tables, 1320–1328

splitting tables with Database Splitter wizard, 1320–1323

verifying and correcting linked table connections, 1323–1328

DLookup function, 930–931

Do...Loop statement, 1017–1018

Dockable property, 946

Docking tab (Options dialog box), 950

DoCmd object

about, 979, 980, 1026–1027

executing commands with, 1005

executing macro actions with, 890

document libraries, 1214

Document Lists of Control IDs, 1270–1271

Document Management Server, 1209

Document Window Options section (Access Options dialog box), 84–85, 89

Documenter dialog box, 199–200

documents

displaying or hiding tabs for, 85–86

errors adding Quick Access Toolbar macros for all, 30

exporting data to Word mail merge, A81–A82

options for overlapping or tabbed views, 109

presentation

about, 1237, 1241–1243

Report ML version of, 1250, 1252

presentation layout extension, 1237, 1243

schema, 1237, 1239–1241

static HTML, 1140–1158

customizing appearance of Datasheet view for, 1143–1148

designing and using HTML templates, 1148–1151

exporting database as HTML file, 1140–1143

user interface for single or multiple, 83–86

.xml data

about, 1237

embedding schema and presentation information in, 1237

example of, 1238–1239

dollar sign ($), 653, 655

domain functions in Access, 1057

DoReport macro, 914–915

DoReport macro group, 898

double arrow mouse pointer, 580, 581

double hyphens (--), 1528

double left arrow (<<) button, 593

double quotation marks (" ")

added to single text values, 355

embedding text with, 653, 655

placing delimited object names in, 1460

string constants with, 362

double right arrow (>>) button, 593

downloading

Access 2007 Developer Extensions and Runtime, 1340

databases to trusted location, 1225

online templates, 145–147

SharePoint database in Internet Explorer, 1224

SQL Server 2005 Express Edition, 133, 1350, 1494

SQL Server Books Online, 1460, 1533

Visual Basic 2005 Express Edition, 1261

Visual FoxPro ODBC driver, 269

Drag-And-Drop Text Editing check box (Options dialog box), 948

dragging and dropping

controls to new column in Layout view, 634–635

fields

into Query window, 352

to simple input form, 578–580

objects into custom groups, 66

drawing

border on report, 1118–1121

straight lines while holding down Shift key, 643

drivers required to import Visual FoxPro data, 269

drop zones for PivotTable design window, 475, 476

duplicates

AutoNumber and potential for, 549

checking

keyboard shortcuts for, 1305–1306

report text boxes and fields for names, 836

Visual Basic names for, 1071–1073

eliminating duplicate lookup values, 238

ignoring duplicate key for unique indexes, 1474

primary keys, 512

dynamic fields unsupported in SharePoint Services, 1219

dynamic Web pages

delivering dynamic query results, 1158–1160

processing live data with HTML forms, 1160

using ASP for fetching data on, 1158

dynaset recordsets, 996