Professional Access 2013 Programming

Teresa Hennig, an Access MVP and business owner of 15 years, creates innovative and effective Access solutions for her clients. She is the president of two Access User Groups, the author of six books on Access, and an acclaimed presenter and trainer. Ben Clothier, an Access MVP since 2009, is an Access and SQL Server expert at the forefront of Access web apps. Ben is an administrator at UtterAccess. George Hepworth, an Access MVP, is an Access/SQL Server developer and consultant. He has written numerous books on Access and Access web apps, and is an administrator on UtterAccess. Dagi Yudovich, an Access MVP, develops Access SQL Server solutions to support BI needs. He is an administrator on UtterAccess. Wrox Professional guides are planned and written by working programmers to meet the real-world needs of programmers, developers, and IT professionals. Focused and relevant, they address the issues technology professionals face every day. They provide examples, practical solutions, and expert education in new technologies, all designed to help programmers do a better job.

INTRODUCTION xxvii PART I: ACCESS WEB APPLICATION DESIGN AND DEVELOPMENT

CHAPTER 1: INTRODUCTION TO ACCESS WEB APPS AND ARCHITECTURE 3

Deprecated Components 4

Access Data Projects 5

Jet Replication 6

Menus and Toolbars 6

Import/Export/Link to Jet 3.x and dBASE Files 7

PivotTables and PivotCharts 8

Collect Data via E-mail 8

SharePoint Workflow 8

Source Code Control Extension 9

Packaging Wizard 10

Upsizing Wizard 10

Creating Access Web Databases 11

New Components Added 12

Database Compare 12

Audit and Control Management Server 13

What Is an App? 13

How Is an App Hosted? 15

How Is an App Distributed? 16

App Marketplace for Publicly Available Solutions 16

App Catalogs for Internal-Facing Solutions 16

How Are Security and Trust Managed in Apps? 18

Setting Up a SharePoint Site for Your Apps 20

Setting Up an Office 365 Trial Account 21

Solving Business Problems 24

The Maid To Order Work Schedule Database 24

Summary 24

CHAPTER 2: DESIGNING TABLES 25

Creating a Blank App 26

Keeping Your Log Ins Straight 27

Creating Tables 29

Creating Tables Using Nouns 29

Creating Tables from Imported Data Sources 31

Creating Tables the Traditional Way 36

Tables and Their Related Views 37

Data Type Changes from Previous Versions 38

Text Data Type 38

Hyperlink Fields 39

Numeric Data Type 39

Date/Time Data Type 40

Image Data Type 40

Lookup Data Type 40

Field Properties 40

Linking SharePoint Lists 41

Summary 42

CHAPTER 3: UNDERSTANDING THE NEW USER INTERFACE 45

Web Navigation 46

Navigation Tools 46

The New Approach to Layout 51

The New Ribbon 55

Mobile Devices Support 60

SharePoint and Office 365 65

Creating a Web App via Team Site 67

Deleting Web Apps 69

Sharing Web Apps with Others 69

Summary 70

CHAPTER 4: DESIGNING VIEWS 73

View Design Surface 74

Ribbon 75

Tiles 76

Links and Creating Views 78

Field List 79

Popup Properties 84

Manipulating Control Placement 88

Duplicating a View 92

Action Bar and Action Bar Buttons 93

Action Bar Button Properties 93

Default Action Button 94

Custom Action Buttons 95

Controls 96

Changed Controls 96

New Controls 103

Common Properties 108

Client Controls with No Counterparts 109

Web Browser Control 110

Summary 114

CHAPTER 5: CREATING QUERIES AND WRITING EXPRESSIONS 115

Query Architecture 116

SQL Server Views 116

Table-Valued Functions 117

Changes in the Query Designer 119

Creating, Editing, Saving, and Previewing Queries 119

Action Queries 125

Parameterized Queries 125

Aggregates, Unique Values, and Top Values 126

Query Properties 127

Functions and Expressions 128

Delimiters 129

Operator Differences 129

New Constants 131

Data Type Inspection and Conversion 132

String Functions 135

Date and Time Functions 139

Math Functions 141

Other Functions 145

Availability by Context 146

Summary 148

CHAPTER 6: Creating Macros 149

Why We Need a New Web-Compatible Macro Language 150

Where We’ve Been 150

Access’s Traditional Role as Tool of First Choice 151

Macro Designer 152

Action Catalog 153

Expressions and Expression Builder 153

Data Macro Tracing 154

Parameter Box 155

Macro Links 155

Interacting with the Macro Designer 155

Different Types of Macros 157

UI Macros 157

Data Macros 157

Data Macro Architecture 158

UI Macro Architecture 159

Block Macro Action 160

Creating and Editing Data Macros 160

Creating a Standalone Data Macro 162

Using the Action Catalog 162

Using the Add New Action Drop-Down 162

Using Program Flow Actions 162

How to Use the LookupRecord Data Block 165

How to Use ForEachRecord and EditRecord 165

How to Use DeleteRecord 166

Creating and Editing UI Macros 166

How to Use SetProperty 168

How to Use ChangeView and OpenPopup 169

How to Use RequeryRecords 171

How to Use Data Entry Operations 171

Using UI and Data Macros Together 172

How to Create and Use Parameters 172

How to Return Values 174

Summary 175

CHAPTER 7: DESIGNING THE TABLE STRUCTURE 177

New Data Types 178

Short and Long Text Fields 178

Number Fields 182

Date/Time Fields 183

Currency 187

Yes/No 188

Hyperlink 188

Image 189

Calculated Fields 190

Lookup Fields 191

Validation Rules and Text 193

Field Level Validation 193

Record Level Validation 195

Editing Validation Rules with Existing Data 195

Validation Rules Design Consideration 196

Leveraging Calculated Fields 197

Creating Concatenated Fields 197

Confi guration Tables 199

Tally Table 199

Utility Table 200

Summary 200

CHAPTER 8: DESIGNING THE USER INTERFACE 203

Naming Convention for Web Apps 204

Planning the User Interface 205

Reusing Create, Read, Update, and Delete Views 205

Effective Navigation Design 206

Traditional Navigation Patterns 206

Web Design Principles 211

Tile-Bound View and Popup Views 215

Creating a Splash Screen 216

Designing an Index Form 219

List View: Searching on a fi eld 219

Summary View: Searching on an Aggregated Value 223

Datasheet: Filtering Cumulatively 230

Creating a Query By View 237

SQL Construction 237

Techniques for Filtering 241

Design Requirements 246

Creating Filter Tables 248

Creating Data Macro to Clear Selections 249

Creating Query By View 249

Filtering Query 252

Building Data Macros to Populate Filter Tables 257

Building a Popup View to Display the Filter Results 260

Building a Click Event Handler 261

Summary 263

CHAPTER 9: SOLVING BUSINESS PROBLEMS WITH MACROS 265

Macro Design Considerations 266

When to Use Data Macros 266

When to Use UI Macros 268

On Start Macro 269

Using Freestanding Macros 270

Cloning a Record 270

Generating a Set of Records with Variable Parameters 278

Generating Records in Diff erent Tables 290

Updating and/or Deleting Several Records 294

Using Table Events 296

Performance Considerations 297

Preventing Deletions of Completed Records 298

Acquiring Default Values from Other Tables 300

Maintaining a History of Change 301

Summary 303

CHAPTER 10: EXTENDING WEB APPS 305

Integration Options 306

Security Considerations 307

Introduction to Web Services 310

Linking a Web App for Additional Functionality 311

Locating and Adding the Locations Mapper App 311

Configuring and Using the Locations Mapper App 315

Inlining a Web App in Access Web App 322

Adding a PayPal Button 333

Getting the PayPal HTML 333

Authoring a Custom Page in SharePoint Designer 336

Allowing Framing of Custom Page 338

Adding the PayPal HTML 339

Showing a PayPal Button on an Access Web App 340

Adding a Site Mailbox 341

Confi guration for a Site Mailbox 342

Setting Up the Site Mailbox 343

Team Site Mailbox as a Shared Tool 347

Using APIs with a Web Browser Control 362

Building a Page to Handle JavaScript Code 363

Development Experience 366

Adding the Charting Page to Access Web App 368

Consuming ZIP-Lookup Web Services in the Client 371

Creating a User Account for the Web Service 372

Library References and Code 373

Summary 394

CHAPTER 11: CONNECTING TO YOUR WEB APP 397

Info Backstage 398

Connections 399

Enable/Disable Connection 400

Getting Connection Details 401

Reset Passwords 401

Using the Access Client 401

Adding VBA Code to Relink 406

Best Practices 409

Using Excel 412

Creating an ODC Connection 412

Creating an Excel Table 415

Creating an Excel PivotChart 416

Creating an Excel PivotTable 420

External Data Considerations 421

Sharing Excel Workbooks on the Web 425

Using SSMS 430

Connecting to a Web App Database 430

Adding a Linked Server on the Web App Database 433

Querying Data in a Linked Server 437

Using Linked Server Programmability Objects 442

Summary 446

CHAPTER 12: WEB APPS IN THE ENTERPRISE 449

Creating a Document Library 450

Creating a Custom Content Type 452

Importing Image Files 455

Customizing a Library 456

Customizing a Library Ribbon 458

Version Control 460

Customizing an App Package 463

Linking to a Template File 467

Synchronizing Data Between App Databases 468

Local Differential Backup 468

Choosing Approaches 479

Summary 479

CHAPTER 13: IMPLEMENTING SECURITY MODELS FOR THE ACCESS WEB APP 481

Security Considerations 482

File-Based Security 482

Agent-Based Security 482

Securing Web Apps on SharePoint 483

Security in the Application Layer 484

SharePoint Security 485

Team Sites and Personal Storage 486

Managing User Accounts 486

Extending Permissions 490

External User Accounts 491

Anonymous Access 492

Securing Web Apps in the Web Browser 494

Using Subsites to Restrict Users to Specific Apps 498

Sites and Subsites 499

Traditional Methods for Security in Client Solutions 509

Web App Linked File Security 513

DSN-Less Linking and Relinking 514

Password Storage/Non-Storage 515

Local SQL Server 532

Summary 537

CHAPTER 14: DEPLOYING ACCESS WEB APPS 539

Deploying Access Web Apps 540

Web Apps and App Catalogs 540

On-Premises SharePoint Server 541

Versioning Web Apps 551

Summary 562

PART II: CLIENT-SERVER DESIGN AND DEVELOPMENT

CHAPTER 15: MANAGING DATA SOURCES 565

Normalization 566

First Normal Form: Eliminate Repeating Groups 566

Second Normal Form: Eliminate Duplicate Data 567

Third Normal Form: Eliminate Fields That

Do Not Depend on the Key 567

Other Normalization Forms 568

Normalization Examples 568

Un-Normalized Table 568

First Normal Form: Eliminate Repeating Groups 568

Second Normal Form: Eliminate Duplicate Data 569

Third Normal Form: Eliminate Fields That

Do Not Depend on the Key 569

Primary Keys 570

Overview of Access Files and the Database Engine 572

Other Data Sources 572

Overview of ODBC Linking 573

Managing Linked Objects 576

Querying External Data Effectively 579

Linked Object Performance and Query Optimization 579

Passthrough Query and T-SQL 580

Comparing Access SQL and T-SQL 581

Summary 582

CHAPTER 16: PROGRAMMING USING VBA, APIS, AND MACROS 585

VBA 586

Procedures 586

User-Defi ned Functions 586

Error Handling 591

Debugging 596

Leveraging Queries 597

Enhancing Query Techniques 597

Query by Form 604

Creating a Query On the Fly Using VBA 606

API 608

Obtaining Documentation for API Functions 609

Mapping Data Types 609

VBA User-Defi ned Types and C-Style Structs 610

Pointers and Handles 611

32-Bit vs. 64-Bit 611

Putting It All Together: Create and Manage an Explorer Window 613

API Declaration 617

Declaring the Enumeration Function 618

Declaring the Callback Function 619

Determining the Class Name of a Window 619

Preparing the EnumChildProc for Two Different Uses 620

Creating the Main Procedure 620

Retrieving Window Information 621

Tips and Techniques 622

Introduction to Data Macros 624

Why Use Data Macros? 625

Diff erences in Client and Web Data Macros 625

Use Cases for Data Macros 626

Creating Data Macros 628

Maintaining Calculated Values to Support Indexing 628

Maintaining Quantity On Hand to Support Business Logic 631

Data Macros and VBA 640

Summary 641

CHAPTER 17: CREATING INTUITIVE FORMS 643

Creating Intuitive Forms 644

Clean Layout 645

Guiding the User Through the Process 645

Showing and Verifying Data in a Timely Manner 646

User-Friendly Messages and Tips 646

Leveraging Built-in Functionality 647

Textbox 647

Label 648

Command Buttons 649

Split Forms 650

Pop-up, Modal, or Dialog Forms 652

The Demo Forms 653

Tag Property 653

Displaying Images 654

List and Combo Boxes 656

Datasheet View Search Forms 657

Multiple Instances of a Form 660

Multi-Value Fields 663

Appending MVFs 664

Appending Attachments 665

Report Runner 668

Creating the Foundation 668

Setting Up the Report and Its Criteria Fields 669

Selecting the Criteria and Running the Report 673

Summary 685

CHAPTER 18: CREATING POWERFUL REPORTS 687

Introduction to Reports 688

Creating Reports 689

Fundamentals 689

Creating and Customizing Reports 693

SubReports 696

Drill Down Reports 698

Report Examples 700

Grouping Data 700

Reports with Simple Criteria 701

Reports with Simple Groupings 702

One Flexible Report 702

Calling the Criteria Form from the Report 706

Reports that Compare Values 706

Professional Polish 709

Report Criteria 709

Confidentiality Statement 711

Page Numbers and Report Date 711

Report Name 711

Using Work Tables 711

Filling Out PDF Forms Using Access 712

Using Reports 712

Using an XFDF fi le 713

Summary 715

CHAPTER 19: AUTOMATING AND INTEGRATING WITH OTHER PROGRAMS 717

Overview of Interoperability 717

Getting Started with Automation 719

Declare and Instantiate Variables 721

Early Binding Versus Late Binding 722

Automating Office Programs 724

Microsoft Excel Integration 725

Integration with Excel Using Ribbons, Menus, and Macros 725

Referencing the Excel Object Library 728

Working with the Excel Object Model 728

Using Automation to Send Data to Excel 728

Creating an Excel PivotTable from Access 731

Generating an Excel Chart from Access 734

Word Integration 737

Integration with Word Using Ribbons, Menus, and Macros 737

Referencing the Word Object Library 737

Working with the Word Object Model 738

Sending Access Data to Word with Automation 738

Using Access Automation to Create Word Tables 741

PowerPoint Integration 744

Setting a Reference to the PowerPoint Object Library 745

Working with the PowerPoint Object Model 745

Creating a Presentation from an Access Table 745

Outlook Integration 749

Sending Outlook Mail Using a Macro Action 750

Referencing the Outlook Object Model 751

Sending Mail Using VBA and Automation 751

Reading and Moving Mail Using VBA and Automation 753

Creating Other Outlook Items Using VBA and Automation 755

Integrating Access with Other Applications 759

Summary 762

CHAPTER 20: SECURING, DEPLOYING, AND MAINTAINING ACCESS APPLICATIONS 763

User Level Security and Audit Trail 764

Local User Table 765

Network Identity 766

Network Identity with a User Table in Access Database 766

Diff erent Front-end Files for Diff erent User Roles 767

Security Summary 767

Converting from .mdb with User Level Security to .accdb 768

Audit Trail 769

Using VBA 770

Using Data Macros 771

Remote Query 772

Deployment Considerations 772

Deploying Front-end Files 773

Auto-Updating the Front End 775

Deploying Back-end Changes 776

Maintaining Different Environments 778

Development, Test, and Production Environments 778

Promoting Files from Test to Production 784

Version Control 785

Data Maintenance 786

Automating Backups 786

Automating Maintenance 789

How to Kick Users Out of the Application 791

Ownership of Code 791

Intellectual Property Rights 791

Trial Version 792

Summary 794

CHAPTER 21: MAXIMIZING SQL SERVER CAPABILITIES 795

Upsizing 795

When to Upsize 796

The Upsizing Process 802

Things to Watch for When Upsizing 805

Working with SQL Server as the Back End 807

SQL Server Management Studio 807

SQL Server Objects 814

Troubleshooting 817

Summary 820

INDEX 823