S

sample files

location of, 23, 103

modifying table design for, 204

working with project files, 1494–1495

sample reports

look of printed, 736

previewing, 738

SampleMacro macro object, 129

Save As dialog box, 151, 893–894

Save button, 151

saving

changes to databases, 205

complex query results in temporary table, 496

database diagrams, 1488

embedded macros with form or report control, 904

filters, 412

import steps for Get External Data-SharePoint Site wizard, 1189–1190

macros, 893–894

new or retaining old data for subdatasheets, 389

objects with macro action, A116

project queries in SQL Server before running, 1510

specific printer settings for reports, 753

tables, 151

template files in appropriate folders, 197–199

scalar functions

defined, 1493

including in parentheses, 1542

table-valued functions vs., 1544

text table-valued vs., 1543

schema documents (.xsd)

about, 1237, 1239–1241

importing into Access, 1255

Schema tab (Export XML dialog box), 1247, 1248, 1249

scientific notation, 654

scope of variables and constants, 963–964

screen fonts, 649

Screen object of Access Application object, 979, 980

screen resolution

effect on alignment commands, 611

Print Preview display and, 737

ScreenTips

displaying, 104

hyperlink, 397, 398, 547

viewing filter icons with, 408

scripts

about, 1128

ASP script generated in VBScript, 1158

browsers executing on client computers, 1138

scroll bars

combo box, 244

form, 661

Scroll Bars property, 671–672

scrolling through Ribbon tabs, 41

SDI. See single-document interface (SDI)

Search Bar

filtering objects with, 79–80

finding database objects from, 78–82

Search box, 553–554

Search Fields As Formatted check box (Find And Replace dialog box), 552, 553

Search-Condition clause, A55-A57

searching

ADO recordset for rows, 1003–1004

data

with Find and Replace, 405–406

with macro actions, A111

using wildcards, 406

database objects, 78–82

forms, 551–554

maximizing search for all objects, 82

for states, 410–411

secondary forms

opening with macro, 921–924

synchronizing with macro, 924–928

section properties.

See also Detail section

list of available, 820–822

property sheet for, 819

viewing, 819

security.

See also trusted locations

allowing blocked content in browsers, 1245

blocking harmful content, 35–36

database encryption and, 1336

digital certificates

packaging signed database with, 1337

purchasing, 1336

self-signing, 1337

digital signatures

about files with, 1336

omitted from sample databases, 35

responding to security notice about, 1338–1339

signing .accdb files, 1338

improving SQL Server 2005, 1356

linked data, 293

Message Bar alerts for, 35

SQL Server connections

establishing, 1450–1451

using Windows NT Integrated Security, 1455

Trust Center, 34–35

SELECT...INTO statement (make-table query), A75-A76

Select Case statement, 1023–1024

Select Certificate dialog box, 1337

SELECT clause, 464

Select Data Source dialog box, 267–268

Select Field box (Group, Sort, And Total pane), 763

select queries

action queries distinguished from, 490

converting

fields discarded while, 490

to make-table query, 500

to update query, 488

defined, 347

testing

data updates with, 486–487

rows affected by delete query with, 507–510

text table-valued functions and, 1543

update limitations for fields in, 468–469

verifying data for make-table queries with, 496

Select SQL Server Database File dialog box, 1455

SELECT statements

about, A33, A34

assigning variables with, 1531

DISTINCTROW clauses in, A34, A58-A59, A65

order of major clauses in, A33

overview of, A57-A63

syntax variants for, A34

Selected Fields list, copying fields to, 593

selecting

data, 1082–1097

by filtering, 407–408

choosing all controls in control layout, 639

filtering one list with another, 1095–1097

multiple fields, 579

multiple-selection list boxes, 1082–1086

noncontiguous, 579, 1082–1086

to perform tasks, All

providing custom query by form, 1086–1093

selecting all controls in area, 588, 600

from summary list, 1094–1095

fields, 578

all in list, 578

all in table, 352, 353

in Form Wizard, 593–594

multiple, 579

as report group, 762–763

in Report Wizard, 778

as source for stored e-mail address, 315–316

selection criteria

avoiding keywords as, 356

Boolean operators as, 356

building date/time, 356–357

common mistakes using compound, 359

entering for queries, 355–356

excluding query requests by date, 1109

self-signing certificates, 1337

SendKeys macro actions, A110

<Separator> option, 33

Server Filter By Form property, 1559

Server Filter property, 1570, 1571

server filters, 1558–1561

about, 1558

example using report, 1570–1572

getting help constructing, 1559

input parameters vs., 572, 1572

reducing network traffic with, 1572

using

in embedded subforms and subreports, 1577

project report, 1570

separate queries with filters vs., 1563

servers

attaching/detaching project files to local, 1494–1495

choosing server connections, 1450–1451, 1454

conserving resources when downloading recordsets, 1551

embedded subform and subreport design for efficiency, 1577

errors connecting to SQL Server, 1452

server-side data-only files

designing data fetching for, 1321

setting up applications with, 1319, 1320

splitting tables to create, 1320–1323

Service Packs

Access bug fixes

error message when saving in-line function, 1516

incorrect positioning of fields on form grid, 627

mailto: protocol prefix, 1062

missing background images in Form Wizard, 595, 690

opening properties affecting color from Custom Properties dialog box, 729

operating system-required, 1341

required for SQL Server 2005 Express Edition, 258

Set Control Defaults button, 680–681

Set Database Password dialog box, 1335

Set Hyperlink ScreenTip dialog box, 547

Set Maximum Record Count button, 1548, 1549, 1568

Set Maximum Record Count dialog box, 1549

SET NOCOUNT ON statement, 1528, 1529

SET statements (SQL), 1532

Set statements (Visual Basic), 991–993

Set Unique Identifier button (Table Analyzer Wizard), 237

Setup Progress page (Microsoft SQL Server Installation Wizard), 1356–1357

SharePoint List button, 1183, 1184, 1187, 1203, 1204

SharePoint Services. See Windows SharePoint Services (version 3)

SharePoint List group (Access), 1206

sharing data.

See also publishing data on Web;

publishing database to SharePoint site

Access for, 12–13

using databases for, 16

Shift key, 643

shortcut menus

accessing table’s commands from, 106–107

displaying single Navigation Pane categories with, 81

Shortcut tab (Properties dialog box), 1331, 1332

shortcuts.

See also keyboard shortcuts

application, 1331–1334

adding parameters to target file name, 1332

command-line options for, 1333–1334

properties for, 1331–1332, 1334

object

creating and grouping, 63–67

hiding and renaming, 69–72

renaming, 70–72

revealing hidden, 72–75

Show Add-In User Interface Errors check box (Access Options dialog box), 1274

Show As button, 476

Show Date Picker property, 1063

Show Details button, 476

Show System Objects check box (Navigation Options dialogbox), 1267,1269

Show Table dialog box, 183, 350

Show Top/Bottom button, 475

showing. See displaying

Shutter Bar Open/Close button, 47

simple input form, 578–589

customizing colors and reviewing design, 587–589

dragging multiple fields to, 578–580

font options for, 582–583

label properties for, 585–586

moving and sizing controls, 580–581

setting and displaying properties of, 586–587

text box properties for, 584–585

simple queries, 347–412

adding records in Datasheet view, 391

assigning field properties, 353–355

Between, In, and Like comparison operators in, 360–361

building complex expressions for, 371–376

criteria selection with AND and OR, 357–360

data selection

and editing in Datasheet view, 393

from single table, 349–351

dates and times in selection criteria, 356–357

entering selection criteria, 355–356

expressions in, 362–370

field names in, 377–378

limiting returned records, 410

methods of building, 348

record indicators in Datasheet view, 390

specifying

fields, 351–353

sorting criteria, 380

subdatasheets in, 387–390

validation rules

checking new field, 381–382

testing new table, 382–384

working in Datasheet view, 384–412

simple reports

adding new grouping level on grid, 764

changing grouping and sorting priorities, 768–769

choosing to use or omit headers, 767

column labels for, 770–771

data types unavailable in Group, Sort, And Total pane, 763

defining groupings, 761, 762–763

designing, 759–760

group interval options, 765–766

line control in, 771, 773

opening Group, Sort, And Total pane for, 761–762

page numbers for, 773–774

printing options, 768

selecting totals options in, 766

sizing text box controls for, 772–773

sort order and options in, 764–765

titles

entering, 766–767

formatting, 769

using or omitting footers, 767–768

simplifying data input, 601–607

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

using combo and list boxes, 601–605

single quotation marks (''), 362

single stepping through macros

disabling, 895

enabling, 894

limited debugging with, 951

single-document interface (SDI), 83–86

about, 84

defined, 83

single-field indexes, 188–189

Size To Fit command

limitations of, 614

sizing controls with, 612–616

sizing

combo box columns, 603

controls and moving, 580–581

controls to fit form content, 612–616

Form window to fit form with Auto Resize property, 622

individual controls to fit content, 613

subform controls, 704

text box controls for reports, 772–773

smart tags

adding to controls, 663–665

defined, 663

turning off warning, 771

Smart Tags dialog box, 665

Smart Tags field property, 165

SMTP/POP3 service, 1341

Snap To Grid (Control Layout group), 617–620

snapshot data for reports, A31

Snapshot Recordset Type property, 1554–1555

software requirements for Microsoft Office, 1341–1342

Solstice form style, 594–595, 598, 600

Sort & Filter group on Home tab, 554

Sort Ascending button, 385

Sort By options (Navigation Pane), 76–77

Sort Descending button, 385

sorting

adding to report in Layout view, 793–795

applying multiple sorts in reverse order, 403

changing row sequence by cutting or, 396

columns in project queries, 1503–1504

data in Datasheet view, 401–405

defining criteria

in Group, Sort, And Total pane, 769, 816–817

for queries, 380

in Report Wizard, 780

form field data, 551, 554

multiple fields, 403–405

number values of Lookup properties, 381

objects

automatically in Navigation Pane, 76–77

manually in Navigation Pane, 77–78

rows in recordsets, 378–380, 421

Soundex

checking for potential duplicates with, 549

generating procedure for, 1073

Source Connect Str property, 462

source data. See data sources

Source Database property, 462

spaces

as character in format string, 653, 655

eliminating when concatenating Null values, 364, 420

omitting in field names, 159, 216

XML field names with, 1263

Special Effect button, 583, 645–646

special effects for highlighting form, 645–646

spelling checks

checking typing errors as possible duplicate lookup values, 238

settings for, 92

split forms, 522, 590–591

split tables, 237

splitting databases, 1320

spreadsheets, 273–282

database systems vs., 13

designing databases from, 17

exporting Access data to, A80

fixing errors in imported data from, 280–282

importing, 275–279

linking to Access databases, 300–301

moving data to temporary table first, 275

preparing for import to Access, 274–275

reasons to convert to Access from, 15–17

selecting fields to index and primary key, 278–279

SQL (Structured Query Language).

See also SQL Server 2005;

Transact-SQL

about, 10

action queries

about, A33, A71

DELETE statement, A72–A73

INSERT statement, A73–A75

SELECT...INTO statement, A75–A76

basic clauses in, 464

brackets or parentheses added by Access, A34

commands used in data definition queries, 463

learning to write in SQL pane, 1498

linking tables to Access databases, 301–302

name separators in, 373

no modifications by ACE to, A61

ODBC standards for, 253–254

queries converted into, 417

query designer’s use of, 463

SELECT queries, A34–A71

aggregate functions in, A35

BETWEEN predicate, A35

Column-Name clause, A35–A37

comparison predicate, A37–A38

DISTINCTROW clauses in, A34, A58–A59, A65

EXISTS predicate, A38–A39

Expression clause, A39–A41

FROM clause, A41–A44

GROUP BY clause, 464, 1506, A44, A65–A66

HAVING clause, A45

IN clause, A34, A46–A47

IN predicate, A47–A48

LIKE predicate, A48–A49

NULL predicate, A50

ORDER BY clause, 1510, 1511, A50-A52, A62, A63, A69

PARAMETERS declaration, A52–A54

quantified predicate, A54–A55

Search-Condition clause, A55–A57

SELECT statement, A57–A63

Subquery clause, A64–A67

syntax variants for, A34

TRANSFORM statement, A67–A68

UNION query operator, A68–A70

stored procedures, 1519–1524, 1526–1541

about, 1493, 1519

defined, 136

displaying in text editors, 1525

filtering, 1493

naming parameters for, 1517

using append and append values queries, 1519

TOP n? PERCENT clause, 1510, 1511

underlying Access database commands, A33

SQL action queries

about, A33, A71

DELETE statement, A72–A73

INSERT statement (append query), A73–A75

SELECT...INTO statement (make-table query), A75–A76

UPDATE statement, A77–A78

SQL designer, 1530

SQL pane (query designer), 1498

SQL SELECT queries, A34–A71

about, A33

aggregate functions in, A35

BETWEEN predicate, A35

Column-Name clause, A35–A37

comparison predicate, A37–A38

EXISTS predicate, A38–A39

Expression clause, A39–A41

FROM clause, A41–A44

GROUP BY clause

overview of, A44–A45

selecting Group By options in query designer, 1506

uses of, 464, A65–A66

HAVING clause, A45

IN clause, A34, A46–A47

IN predicate, A47–A48

LIKE predicate, A48–A49

NULL predicate, A50

ORDER BY clause

overview, A50–A52

specifying calculated column alias name with, A62

TOP n PERCENT clause and, 1510, 1511

using column names of clause in UNION queries, A52, A63, A69

order of major clauses in, A33

PARAMETERS declaration, A52–A54

quantified predicate, A54–A55

Search-Condition clause, A55–A57

SELECT statement, A57–A63

Subquery clause, A64–A67

syntax variants for, A34

TRANSFORM statement, A67–A68

UNION query operator, A68–A70

WHERE clause, A70–A71

SQL Server 7.0 column properties not visible, 1470

SQL Server 2005.

See also Microsoft SQL Server Installation Wizard;

SQL Server 2005 Express Edition

access to Create authority in, 113

adding project indexes, 1471–1475

checking Allow Nulls property first, 1482

column data types, 1461–1464

creating

data source to, 256–258

user-defined data types, 1465

databases

buildingnew, 1448–1451

connecting to existing, 1452–1457

errors creating, 1452, 1457

editing Access database tables in, 134–135

importing data from, 266–270

linking tables to Access databases, 301–302

names

databases vs. project files, 1450

embedded spaces in, 159, 216

ODBC Driver Manager and, 254

queries supported in, 1525

Rules for Identifiers, 1460

running Access databases in, 137, 138

saving project queries before running, 1510

selecting data types in, 1459

starting Configuration Manager, 266–267

storing attachment fields as OLE Object data types, 533

table column properties, 1467–1470

Transact-SQL, 1525

upsizing Multi-Value Lookup Fields unavailable for, 248

version compatibility with projects, 1448

SQL Server 2005 Express Edition.

See also Microsoft SQL Server Installation Wizard

authenticating with Windows Vista, 258

Create Database permissions, 1452

disk space required for, 1341

downloading, 133, 1350, 1494

installing, 1349–1358, 1494

licensing agreements for, 1350

Service Pack 2, for, 258

SQL Server Books Online, 1460, 1533

SQL Server Configuration Manager, 266–267, 1357–1358

SQL statements

declaring, assigning values to, and testing variables, 1531–1532

definingblockwithBEGIN/END, 1531, 1532

using in text stored procedures, 1526

SQL view, 463–468

SQL-to-Access data type conversions, 270

sql_variant data type, 1465

Stacked button, 635–636

stacked control layouts

converting, 798–800, 801

defined, 626, 797

removing, 632–635, 800

selecting controls into, 635–638

standard modules

defined, 943

Private statement in, 972–973

Public statement in, 973–974

starting and running applications, 1310–1316

intercepting Ctrl+F4 with AutoKeys macro, 1315–1316

loading USysRibbons table at startup, 1267–1268

setting database startup properties, 1310–1312

AutoExec macro for starting applications, 1310, 1312

hiding Navigation Pane on application startup, 1311

starting and stopping application, 1312–1315

startup from application shortcuts, 1331–1334

verifying and correcting linked table connections, 1323–1328

states

abbreviations as selection criteria, 356

searching for with Filter By Form, 410–411

static HTML documents, 1140–1158

customizing appearance of Datasheet view for, 1143–1148

designing and using HTML templates, 1148–1151

exporting database as HTML file, 1140–1143

Static statement, 975–976

static Web pages

understanding, 1137–1139

viewing, 1139–1140

status bar, 25, 1311

StDev function, 437

Stop All Macros button, 913

Stop Refresh button, 1548, 1568

Stop statement, 1024

StopAllMacros action, 932, 933

stored parameters, 1561–1565

stored procedures, 1519–1524

aboutSQL, 1493, 1519

choosing combo box values resolved with, 1564, 1575–1576

defined, 136

defining properties for columns, 1523–1524

displaying in text editors, 1525

filtering, 1493

inserting parameters in append values queries, 1520–1522

naming parameters for, 1517

server filters unavailable for, 1558

text, 1526–1541

about, 1526–1527

adding control-of-flow statements, 1531–1535

comment blocks in, 1528

grouping multiple statements with transactions, 1535–1541

starting new, 1527–1529

user-defined, 1533

using append and append values queries, 1519

Stored Procedures tab (Properties window), 1523–1524

Stretch Across Top anchoring option, 624, 625

string constants, 362

string functions, A89-A90

strings

comparing

case insensitivity of, 170

wildcard characters for, A49

concatenating

with & character, 362

text, 841–842

string constants, enclosing text in double or single quo- tation marks for, 362

string functions, A89-A90

zero-length, 166

Structured Query Language. See SQL; SQL Server 2005

style of Form Wizard forms, 594–596

style sheets. See presentation (layout) document (.xsl)

Sub statement, 947, 1007–1009

Subdatasheet menu, 388

subdatasheet properties

about, 178

performance with large tables, 179

Subdatasheet Expanded property, 180,460

Subdatasheet Height property, 180,460

subdatasheets, 387–390

defining

for complex queries, 458–462

subform for, 710–713

expanding, 387–390, 461

opening and filtering, 407

properties

about, 178

performance with large tables, 179

Subdatasheet Expanded property, 180,460

Subdatasheet Height property, 180,460

subfolders as trusted location, 37–38

subforms, 522–523

automatic linking to record source, 706

defined, 560

embedded, 692–713

creating, 703–706

creating main form, 707–710

defining subdatasheet subform, 710–713

designating main form source, 706–707

designing innermost first, 696–701

displaying complex information with, 1577

editing controls in inner forms, 709

setting up first-level subform, 701–702

specifying query data source for, 693–696

uses for, 692–693

embedding linked PivotChart in, 730, 733–734

illustrated, 561

linking

to form with filter, 1098–1099

records between forms and, 548

polishing design of, 698–700

rules for referencing, 920–921

situations impractical for using, 921

sizing controls in Form view, 704

subreports vs., 851

triggering data task from related, 1105–1109

using in Datasheet view, 701

viewing in Continuous Forms view, 700–701

submenus

Bitmap Image Object, 534

view by, 76

viewing categories of, 72

subqueries

correlated, A39, A66, A67

knowledge of SQL needed to define, A33

providing search criteria from underlying record source, 1093

testing existence of row in, A38, A39

Subquery clause, A64-A67

subreports, 851–859

building query for, 855

designing, 855–857

embedding, 741–744, 857–859

repeating report headers on, 851–854

rules for referencing, 920–921

subroutines

about, 1005

Call statement to transfer control to, 1017

declaring, 1007–1009

defining as method, 1005

Subtotal button, 475

Sum control, 787, 788

Sum function

about, 437

returning Null then converted to zero, 446–447

summary lists, 1094–1095

Summary Options dialog box, 781

sunken effect for text box controls, 596–597, 610

Surface Area Configuration Tool, 1356, 1357

switchboard forms, 1305–1310

checking for duplicate keyboard shortcuts, 1305–1306

command buttons on sample, 535–536, 537

designing, 1306–1310

function of, 560

Switchboard Manager, 1306–1310

building additional switchboard pages, 1307–1308

illustrated, 1308

starting, 1307

SwitchboardSample form, 1307

synchronizing

between Access and Visual Basic, 950

changes in SharePoint Services after working offline, 1230–1233

resolving changes, 1231

related forms with macro, 924–928

two forms with class event, 1101–1104

syntax

append values queries, 1519–1520

assigning object variable with Set statement, 991–993

BETWEEN predicate, A35

Call statement, 1017

calling functions, 1542

class modules

Property Get procedure, 1010

Property Let procedure, 1012

Property Set procedure, 1014

Column-Name clause, A35

comparison predicate, A37

declaring variables and constants, 965–978

Const statement, 965

Dim statement, 966–969

Enum statement, 969–970

Event statement, 971–972

Private statement, 972–973

Public statement, 973–974

ReDim statement, 974–975

Static statement, 976

Type statement, 977–978

DELETE statement, A72

Do...Loop statement, 1018

DoCmd object, 1026

domain functions, 1057

executing Access commands, 1027–1028

EXISTS predicate, A38

Expression clause, A39

For...Next statement, 1019

For Each...Next statement, 1019

FROM clause, A41-A42

Function statement, 1006–1007

GoTo statement, 1020–1021

GROUP BY clause, A44

HAVING clause, A45

If...Then...Else statement, 1021

ImportXML command, 1256

IN clause, A46-A47

IN predicate, A47

input mask, 171

INSERT statement, A73

LIKE predicate, A48

LoadCustomUI method, 1285

NULL predicate, A50

On Error statement, 1029

Open method of ADO recordset, 1001

OpenRecordset method, 994

ORDER BY clause, A50

PARAMETERS declaration, A52

quantified predicate, A54

RaiseEvent statement, 1022

Search-Condition clause, A55

SELECT…INTO statement, A75-A76

Select Case statement, 1023

SELECT statement, A57-A58

Stop statement, 1024

Sub statement, 1007–1009

Subquery clause, A64

temporary variables, 909

text table-valued function, 1544

transaction batching, 1535

TRANSFORM statement, A67

UNION query operator, A68

UPDATE statement, A77

variants for SELECT statement and action queries, A34

WHERE clause, A70

While…Wend statement, 1025

With…End statement, 1025

System Button Face property, 647

system colors, 647

System Configuration Check page (Microsoft SQL Server Installation Wizard), 1351–1352

system functions, A90-A91

system objects

displaying in Navigation Pane, 1267

recommended settings for, 1269

system tables

displaying in Navigation Pane, 1267

protecting, 1267, 1269