Professional Access 2013 Programming

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