.accdb files
about, 6
backing up, 205–207
building database queries in query designer for, 1491
components of application files, 978
creating compiled version of, 272
DAO model with desktop applications, 981
databases vs. projects, 1450
packaging and signing, 1338
publishing or moving to SharePoint Services, 1210, 1212
return DAO recordset for Recordset property, 1104
user-level security unsupported for, 12
viewing database recordsets for action queries, 1523
.accde files, 1330
.accdr files, 1329
Access.
See also applications;
projects;
user interface (UI)
ActiveX controls available in, 7–8
application development with, 13–15
architecture
application architecture, 978–981
overview, 99–101
backward compatibility with .mdb format, 979
building client/server applications with, 137–138
changes to user interface, 21
checking for updates, 97
color names in, A93-A99
converting from previous release, 1358–1360
compatibility with earlier versions, 1358–1359
conversion issues, 1359–1360
creating SharePoint list from within, 1198–1207
adding record to table, 1200–1201
creating custom list, 1203–1207
using existing list template, 1199–1203
data access pages, 14
data formats compatible with, 9
data types for, 158
domain functions, 1057
event-driven applications in, 867–869
examples of installed ActiveX controls, 726–727
executing commands, 1027–1028
exporting data to SharePoint Services, 1182–1186
file formats for, 6
importing SharePoint list to, 1186–1191
integration with Windows SharePoint Services, 1162–1163
linking SharePoint list to, 1191–1195
macro actions not trusted in, 916–918
managing queries in, 417
newsgroups for, 17
ODBC standard and, 253–255
opening for first time, 19–20
processing objects in, 560–562
RDBMS features of, 6–13
relationship between Visual Basic and, 942, 950
smart tags in, 664
spreadsheet data compatible with, 273
support for existing DAPs, 14, 100
switching to database software, 15–17
use of SQL in
brackets or parentheses added by Access, A34, A61
for database commands, A33
IN clause, A34, A46
parameter data types and equivalents, A53
using XML data in, 1136–1137
Web development environment in, 1127
working with Visual FoxPro tables in, 268
Access 2007 Developer Extensions and Runtime, 1328, 1340
Access applications. See applications
Access Database Engine (ACE)
DAO and, 982
no SQL modifications by, A61
Access desktop databases. See .accdb files; databases
Access Options dialog box, 87–97
automatic display of Macro Name and Condition columns, 891
categories
Add-Ins, 95
Advanced, 93
Current Database, 88–89
Customize, 94
Datasheet, 90
Object Designers, 90–91
Popular, 87–88
Proofing, 92
Resources, 96–97
Trust Center, 96
checking for keyboard shortcut duplicates, 1306
Compact On Close check box, 251
control IDs for built-in controls, 1277
Document Window Options section of, 84–85, 89
enhancing performance of linked data, 293–294
Layout view
disabling, 1304
enabling for database, 671
making changes affecting all datasheets, 1143–1144
modifying
global settings in, 87–97
keyboard options for datasheets and forms, 392–393
opening, 27, 84
Perform Name AutoCorrect options, 192–193
Quick Access Toolbar
customizing commands on, 28–31
revising command and macro order, 32
setting
database startup properties, 1310–1312
overlapping or tabbed document views, 109
project form options, 1549–1551
Show Add-In User Interface Errors check box, 1274
table design options
for projects, 1488–1490
setting, 191–195
Use Windows-Themed Controls On Forms check box, 559, 596, 692
year formatting options, 191–192
Access projects. See projects
.accfl files, 153
ACE (Access Database Engine)
DAO and, 982
no SQL modifications by, A61
Action Failed dialog box, 912–913, 914
action queries, 485–514.
See also append queries;
delete queries;
make-table queries;
update queries
about, A33, A71
appending data, 485, 502–507
creating append queries, 503–506
running append queries, 506–507
SQL overview for append queries, A73-A75
using stored procedures for, 1519–1522
cautions about viewing database or project recordsets for, 1523
defined, 347
deleting data
about delete queries, 485
testing rows affected by delete queries, 507–510
using delete query, 510–511
fields discarded in conversion from select queries, 490
icons for, 490
make-table queries, 485, 495–502
about, 495–496
creating, 496–500
defined, 485
limitations of, 501
running, 500–502
syntax variants for, A34
troubleshooting, 512–514
types of, 485
updating data, 486–495
about update queries, 485
converting select to update query, 488
multiple fields, 491–493
running update queries, 489–491
testing data updates, 486–487
actions. See macro actions
activating
Date Picker for text box controls, 727
hyperlinks, 398
Active Server Pages. See ASPs (Active Server Pages)
ActiveX controls
Calendar, 729–730
defined, 726
examples of installed, 726–727
selecting, 727–728
using in distributed applications, 1068
ActiveX Data Objects (ADO)
architecture of, 985–987
Find method in, 1104
introduction of, 981
ODBC and, 254
recordsets
difficulties using, 982
working with, 1001–1005
using with dynamic Web pages, 1159, 1160
using as interface for ODBC databases, 255
ActiveX objects.
See also ActiveX Data Objects (ADO)
displaying in forms, 533–535
methods for controls, 7–8
size limitations of, 201
using with dynamic Web pages, 1159, 1160
Add A Group option, 794
Add A Sort option, 794
Add Generated Key button (Table Analyzer Wizard), 237
Add Table dialog box, 1486, 1496
Add Watch dialog box, 957
add-in security restrictions, 38
Add-Ins category (Access Options dialog box), 95
addresses
finding in associated table, 317–318
selecting source for, 315–316, 333–334
selecting table containing, 316–317, 332–333
entering
individual, 321–322
or selecting in form, 548
ADO. See ActiveX Data Objects (ADO)
ADODB data model, 985, 986–987
ADOX data model, 985, 986–987
.adp files, 981.
See also Contacts.adp project file;
projects
about, 132–133, 1448
application engine used by, 978–979
cautions viewing project recordsets for action queries, 1523
connecting to new SQL Server database, 1448–1451
installing SQL Server 2005 Express Edition to build, 1349
projects vs. databases, 1450
using with SQL Server, 6,
views, functions, and stored procedures in, 135–137
Advanced category (Access Options dialog box), 93
Advanced Filter button, 385, 402
Advanced Filter Design window, 403–405
Advanced Filter/Sort window, 556
advanced form design
basing on multiple-table query, 686–691
conditional formatting, 716–719
creating multiple-page forms, 723–726
example of, 520
illustrated, 725, 726
page break control, 724, 725
planning, 724
setting Cycle property, 726, 1080
displaying values in option group, 714–716
embedded subforms, 692–713
creating, 703–706
creating main form, 707–710
defining subdatasheet subform, 710–713
designating main form source, 706–707
designing innermost, 696–701
displaying complex information with, 1577
editing controls in inner forms, 709
setting up first level subform, 701–702
sizing controls in Form view, 704
specifying query data source for, 693–696
uses for, 692–693
PivotChart forms, 730–734
building, 731–732
designing, 538–539, 730
embedding linked PivotChart, 730, 733–734
PivotTable forms, 538–539
tab controls, 719–723
about, 530
property settings for, 723
setting Page Index property, 721
types of, 719
working with, 720–722
advanced report design, 811–863
adding PivotChart to report, 860–863
adding values across group, 837–838
building queries for complex reports, 812–813
calculating
grand totals, 839–840
percentages, 840, 843–844
totals on expressions, 838
concatenating text strings, 841–842
conditional formatting, 848–851
custom Ribbon for sample reports, 811
defining grouping and sorting criteria, 816–818
embedding subreports, 741–744, 851–859
hiding redundant values, 840–841, 842
numbering report lines, 845–848
performing calculations on detail line, 833–837
print date and page numbers on, 830–833
section properties, 819–822
structuring basics in Report Wizard, 813–815
AfterUpdate events, 931, 937
aggregate expressions, 838
aggregate functions
about, A35
available as Group By options in query designer, 1506
defining in totals queries, 435–436, 437
limitations updating, 1508
using in SELECT queries, A35
aggregate queries, 1505
alerts
Message Bar, 35
notifying if SharePoint list data modified, 1196
turning off warning smart tags, 771
warning messages for reserved words or function names, 159
aliases
assigning to query field lists, 496, 497
specifying for column with ORDER BY clause, A62
alignment
control layouts for column, 637–638
effect of screen resolution on, 611
Font group options for, 583
forcing left, 653, 655
formlabel, 619–621
snapping items to grid, 618, 619
tabbing across page boundary with correct, 1080–1082
All tab (Property Sheet window)
listing of control properties, 666–669
listing of form properties, 676–680
Allow Additions property, 674, 1554–1555
Allow Deletions property, 674, 1554–1555
Allow Design Changes property, 589
Allow Edits property, 674, 1554–1555
Allow Layout view, 589
ALTER PROCEDURE statement, 1527
Always Use Event Procedures check box (Access Options dialog box), 906
American National Standards Institute (ANSI), 253
ampersand (&) character
concatenating expressions with, A40
concatenating fields or strings with, 362
displaying available characters in this position, 655
anchoring controls in Layout view, 623–624
AND operator
constructing multiple constraint expressions with, 1481
expressing in query designer, A55
results using OR vs., 357–359
truth tables for, A56
ANSI (American National Standards Institute), 253
Append dialog box, 505
append queries, 502–507
command on Design contextual tab for, 488
creating, 503–506
defined, 485
icon for, 490
make-table vs., 502
running, 506–507
SQL overview for, A73-A75
stored procedures for appending data, 1519–1522
append values queries
inserting parameters in, 1520–1522
syntax of, 1519–1520
application design worksheet
subjects, A14, A15, A16
tasks, A9, A10, A12
application engine in Access, 978, 979
application shortcuts, 1331–1334
adding parameters to target file name, 1332
command-line options for, 1333–1334
setting properties for, 1331–1332, 1334
applications.
See also .accdb files;
automating
Active X controls in, 1068
building client/server, 137–138
compiling
before production, 1316–1317
errors with procedures within procedures, 947
flagging undeclared variables during, 963
setting VB options for, 949–950
version of .mdb or .accdb files, 272
controlling flow with forms, 517–518
custom Ribbons for, 1266–1279, 1283–1302
adding built-in groups to tabs, 1276–1279
building well-formed XML for, 1270–1271
creating VBA callbacks, 1296–1297
customizing tabs, 1274–1275
disabling unused tabs, buttons, and groups, 1283–1284
displaying errors, 1274
dynamically updating elements, 1297–1299
hiding existing elements, 1271–1272
hiding options on Microsoft Office Button, 1300–1301
loading images into custom controls, 1299–1300
loading XML for, 1284–1287
setting focus to tab, 1301–1302
testing, 1270, 1272–1273
using RibbonX attributes, 1287–1296
working with USysRibbons table, 1266–1269
XML code listing for, 1292–1294
designing database, 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 for, A8
strategies for, A7-A13
testing, reviewing, and refining, A6
understanding work process, A4
when to break design rules, A28-A31
developing in Access, 13–15
disabling Layout view, 1304
distributing, 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 in desktop database, 1320–1328
working with Access 2007 Developer Extensions and Runtime, 1328, 1340
event-driven Access, 867–869
execute-only
distributing databases as, 1329–1330
preventing users switching to Design view for, 671
property settings for, 1310–1312
runtime mode and, 1328–1329
fine-tuning with Performance Analyzer, 1302–1303
keeping Connect property current for, 297
macro action running another, A118
macros in, 887–940
actions not trusted, 916–918
automating tasks with, 918–938
brackets inserted in reference to control on form, 901
conditional expressions in, 900–901
converting to Visual Basic, 938–940
defining multiple actions, 895–897
embedded, 901–907
grouping, 897–900
Hourglass automatically restored by, 897
recommendations about, 890
saving, 893–894
temporary variables, 907–910
testing, 894–895
trapping errors, 910–915
uses of, 887, 888–889
working with Macro window, 890–893
maintaining data integrity, 413
previewing reports for sample, 738
starting and running, 1310–1316
intercepting Ctrl+F4 with AutoKeys macro, 1315–1316
setting database startup properties, 1310–1312
starting and stopping application, 1312–1315
switchboard forms for, 1305–1310
checking for duplicate shortcuts, 1305–1306
designing in Switchboard Manager, 1306–1310
illustrated, 1305
upsizing to projects, 533
architecture
Access, 99–101
Access application, 978–981
ADO, 985–987
DAO, 981–985
form, 560–561
RibbonX, 1287, 1301
Web, 1137–1139
archiving data
with append query, 503–506
deleting inactive data with delete queries, 510–511
argument boxes in Macro window, 892–893
arguments
AskEdit macro, 933–934
DatePart function interval, 365
entering macro, 890–891
SyncWeddingAndCity macro, 926
arithmetic expressions, 364–370
concatenating Null values in, 364, 420
operator precedence in, 367
operators used in, 364–365
using DateDiff function in, 365–366
arithmetic functions, A85-A86
Arrange contextual tab, 564, 618
arrays declared with ReDim statement, 974–975
arrow keys, 392
Ascending button, 402, 409
ascending sort order, 378
ASP.NET, 1160–1161, 1162
ASPs (Active Server Pages)
creating dynamic Web pages with, 1158
designed for IIS, 1159
exporting XML data as, 1249, 1250
Assets template, 152, 153
assignment statements, 952–953
asterisk (*)
all fields indicator, 352, 507
designating next character as fill character, 653, 655
multiplying expressions with, 365
operator precedence for, 367
record indicator icon, 390
as wildcard, 169, 406, A49
asterisk icon, 390
attachment controls, 530–533
Attachment data type, 8–9, 158, 160–161
attachment fields
indexes undefinable using, 189
inserting files in, 546
Attachments dialog box, 532
attributes
associated with Ribbon controls and callbacks, 1290–1292
defined, 5,
list of Ribbon control, 1288–1289
authentication errors using SQL Server 2005 Express with Vista, 258
Authentication Mode page (Microsoft SQL Server Installation Wizard), 1353–1354
Auto Expand property, 605
Auto Indent check box (Options dialog box), 948–949
Auto Resize property, 622
Auto Syntax Check check box (Options dialog box), 948
AutoCalc button, 475
AutoCorrect feature, 92, 193
AutoCorrect smart tag, 233
AutoExec macro
about, 890
starting applications with, 1310, 1312
using, 896
AutoFilter button, 475
AutoFormat
applying to reports in Layout view, 807–809
defining control defaults as, 681
AutoFormat Wizard, 681
AutoKeys macro, 1052, 1315–1316
automatically processing e-mail replies, 313, 314
automating
applications
controlling tabbing on multiple-page form, 1080–1082
linking to data in other form or report, 1098–1094
complex tasks, 1105–1114
calculating stored value in table, 1114
linking to related task, 1098–1099, 1109–1114
triggering data task from related form, 1105–1109
data entry assistance, 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
data selection, 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
data validation, 1071–1080
checking for overlapping data, 1078–1080
checks for possible duplicate names, 1071–1073
maintaining special unique value constraint, 1077–1080
testing for related records when deleting record, 1074–1075
verifying prerequisite before saving record, 1075–1077
form processes, 561, 562
with macros, 918–938
Hourglass automatically restored after running, 897
including subform and subreport references, 920–921
referencing form and report objects, 919
referencing properties for forms and reports, 919–920
tasks using, 887, 888–889
using references to form and report controls and properties, 920
reports, 1114–1124
drawing border on, 1118–1121
filtering dynamically on opening, 1121–1124
using up partial page of mailing labels, 1115–1118
AutoNumber data type
about, 158, 160
converting, 228
unavailable for data collection if primary key, 310, 311
AutoNumber fields
query update limitations for, 469
using as primary keys, 549
Avery label formats, 754, 1117
Avg function, 437
axis for PivotCharts, 481