Tips, techniques, and tools to enhance your Access 2013 experience

The new Access 2013 boasts significant new features aimed at improving your experience of building web-based and traditional database applications. Written by a team of Microsoft Access MVPs, this book shows you everything you need to immediately start developing new solutions, upgrading existing projects, and planning future enhancements. Placing special emphasis on creating cost-effective solutions based on ...

See more details below
Professional Access 2013 Programming

Available on NOOK devices and apps  
  • NOOK Devices
  • Samsung Galaxy Tab 4 NOOK 7.0
  • Samsung Galaxy Tab 4 NOOK 10.1
  • NOOK HD Tablet
  • NOOK HD+ Tablet
  • NOOK eReaders
  • NOOK Color
  • NOOK Tablet
  • Tablet/Phone
  • NOOK for Windows 8 Tablet
  • NOOK for iOS
  • NOOK for Android
  • NOOK Kids for iPad
  • PC/Mac
  • NOOK for Windows 8
  • NOOK for PC
  • NOOK for Mac
  • NOOK for Web

Want a NOOK? Explore Now

NOOK Book (eBook)
BN.com price
(Save 42%)$54.99 List Price
Note: This NOOK Book can be purchased in bulk. Please email us for more information.


Tips, techniques, and tools to enhance your Access 2013 experience

The new Access 2013 boasts significant new features aimed at improving your experience of building web-based and traditional database applications. Written by a team of Microsoft Access MVPs, this book shows you everything you need to immediately start developing new solutions, upgrading existing projects, and planning future enhancements. Placing special emphasis on creating cost-effective solutions based on real-world scenarios, the authors show how to enhance and manage the features of Access 2013 and cover critical changes that may affect legacy programs. Ultimately, this book serves as a guide, a reference, and a resource for expanding your Access expertise.

Professional Access 2013 Programming:

  • Starts with the fundamentals of the new Access 2013 web app and then takes you step by step through building a robust application
  • Provides examples and techniques to create and deploy professional solutions for both traditional and web applications
  • Demonstrates how to automate other programs using macros, VBA, API calls, and more
  • Shares techniques for using macros to create solutions that will run in a web browser and perform tasks on SQL Server®
  • Features helpful advice for making informed decisions and investing in solutions that will support your current and future needs


Programmer Forums

Join our Programmer to Programmer forums to ask and answer programming questions about this book, join discussions on the hottest topics in the industry, and connect with fellow programmers from around the world.

Code Downloads

Take advantage of free code samples from this book, as well as code samples from hundreds of other books, all ready to use.

Read More

Find articles, e-books, sample chapters, and tables of contents for hundreds of books, and more reference resources on programming topics that matter to you.

Read More Show Less

Product Details

  • ISBN-13: 9781118789612
  • Publisher: Wiley
  • Publication date: 8/2/2013
  • Sold by: Barnes & Noble
  • Format: eBook
  • Edition number: 1
  • Pages: 888
  • Sales rank: 455,686
  • File size: 73 MB
  • Note: This product may take a few minutes to download.

Meet the Author

Teresa Hennig, an Access MVP and business owner of 15years, creates innovative and effective Access solutions for herclients. She is the president of two Access User Groups, the authorof six books on Access, and an acclaimed presenter and trainer.

Ben Clothier, an Access MVP since 2009, is an Access andSQL Server expert at the forefront of Access web apps. Ben is anadministrator at UtterAccess.

George Hepworth, an Access MVP, is an Access/SQL Serverdeveloper and consultant. He has written numerous books on Accessand Access web apps, and is an administrator on UtterAccess.

Dagi Yudovich, an Access MVP, develops Access SQL Serversolutions to support BI needs. He is an administrator onUtterAccess.

Wrox Professional guides are planned and written byworking programmers to meet the real-world needs of programmers,developers, and IT professionals. Focused and relevant, theyaddress the issues technology professionals face every day. Theyprovide examples, practical solutions, and expert education in newtechnologies, all designed to help programmers do a better job.

Read More Show Less

Read an Excerpt

Click to read or download
Read More Show Less

Table of Contents




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


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


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


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


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


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


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


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


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


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


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


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


Deploying Access Web Apps 540

Web Apps and App Catalogs 540

On-Premises SharePoint Server 541

Versioning Web Apps 551

Summary 562



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


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 Window613

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


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


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


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


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


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


Read More Show Less

Customer Reviews

Be the first to write a review
( 0 )
Rating Distribution

5 Star


4 Star


3 Star


2 Star


1 Star


Your Rating:

Your Name: Create a Pen Name or

Barnes & Noble.com Review Rules

Our reader reviews allow you to share your comments on titles you liked, or didn't, with others. By submitting an online review, you are representing to Barnes & Noble.com that all information contained in your review is original and accurate in all respects, and that the submission of such content by you and the posting of such content by Barnes & Noble.com does not and will not violate the rights of any third party. Please follow the rules below to help ensure that your review can be posted.

Reviews by Our Customers Under the Age of 13

We highly value and respect everyone's opinion concerning the titles we offer. However, we cannot allow persons under the age of 13 to have accounts at BN.com or to post customer reviews. Please see our Terms of Use for more details.

What to exclude from your review:

Please do not write about reviews, commentary, or information posted on the product page. If you see any errors in the information on the product page, please send us an email.

Reviews should not contain any of the following:

  • - HTML tags, profanity, obscenities, vulgarities, or comments that defame anyone
  • - Time-sensitive information such as tour dates, signings, lectures, etc.
  • - Single-word reviews. Other people will read your review to discover why you liked or didn't like the title. Be descriptive.
  • - Comments focusing on the author or that may ruin the ending for others
  • - Phone numbers, addresses, URLs
  • - Pricing and availability information or alternative ordering information
  • - Advertisements or commercial solicitation


  • - By submitting a review, you grant to Barnes & Noble.com and its sublicensees the royalty-free, perpetual, irrevocable right and license to use the review in accordance with the Barnes & Noble.com Terms of Use.
  • - Barnes & Noble.com reserves the right not to post any review -- particularly those that do not follow the terms and conditions of these Rules. Barnes & Noble.com also reserves the right to remove any review at any time without notice.
  • - See Terms of Use for other conditions and disclaimers.
Search for Products You'd Like to Recommend

Recommend other products that relate to your review. Just search for them below and share!

Create a Pen Name

Your Pen Name is your unique identity on BN.com. It will appear on the reviews you write and other website activities. Your Pen Name cannot be edited, changed or deleted once submitted.

Your Pen Name can be any combination of alphanumeric characters (plus - and _), and must be at least two characters long.

Continue Anonymously

    If you find inappropriate content, please report it to Barnes & Noble
    Why is this product inappropriate?
    Comments (optional)