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