Professional Access 2013 Programming

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