Professional Access 2013 Programming, Notas de estudo de Gestão Industrial

Baixe Professional Access 2013 Programming e outras Notas de estudo em PDF para Gestão Industrial, somente na Docsity!

PROFESSIONAL

Access

®

2013 Programming

Teresa Hennig

Ben Clothier

George Hepworth

Dagi (Doug) Yudovich

To my family and my dearest friends — you are my inspirations, role models and

mentors. Whatever I am facing, I can find strength and guidance by thinking of

what my Mom, Papa, Dad, or brother might say or do. Every day I have reasons

to say thank you for teaching us to work, to accept responsibility for our actions,

to do the right thing, and to help others. You taught us to do what we enjoy and

to do our best. What a great formula to be happy, energized, and resilient, and to

appreciate all that we have! You set the foundation for me to embrace the thrills,

lessons, and adventures in life. And, throughout life, to be compassionate and to

give generously — especially laughter and smiles, a hug or a helping hand.

—Teresa Hennig

To Suzanne and Harry, for being just plain awesome. I couldn’t have done it

without you two!

—Ben Clothier

I want to dedicate my part in this book to my mother and father, who not only

brought me into the world, but showed me how to live properly once I got here.

Family comes first, but that is easy when you have a family like mine. To my

siblings, JoAnna, Tom, Sam, Jack, Nancy, and Jane: Your encouragement (and

unusually high tolerance for bad jokes, lies, and war stories) helps keep me going.

Thanks. I promise I’ll update the family website again soon. I also need to make

special mention of my daughter, Lyndsey; you give me reason to be proud every

day. Love ya, sweetie. We’ll make up for the missed weekend dinners soon.

—George Hepworth

To my wife, Lisa, who still patiently picks up the slack as I dive head-first into

new adventures. None of it would have been possible without you. I love you.

—Dagi Yudovich

EXECUTIVE EDITOR Robert Elliott

PROJECT EDITOR Christina Haviland

TECHNICAL EDITORS F. Scott Barker Jerry Dennison Jerry Wood

PRODUCTION EDITOR Christine Mugnolo

COPY EDITOR Nancy Rapoport

EDITORIAL MANAGER Mary Beth Wakefield

FREELANCER EDITORIAL MANAGER Rosemarie Graham

ASSOCIATE DIRECTOR OF MARKETING David Mayhew

MARKETING MANAGER Ashley Zurcher

BUSINESS MANAGER Amy Knies

PRODUCTION MANAGER Tim Tate

VICE PRESIDENT AND EXECUTIVE GROUP PUBLISHER Richard Swadley

VICE PRESIDENT AND EXECUTIVE PUBLISHER Neil Edde

ASSOCIATE PUBLISHER Jim Minatel

PROJECT COORDINATOR, COVER Katie Crocker

PROOFREADER Sarah Kaikini, Word One New York

INDEXER John Sleeva

COVER DESIGNER Wiley

COVER IMAGE © iStockphoto.com/PhekThong Lee

CREDITS

GEORGE HEPWORTH began his Access career by creating tools to track sales and exam results for his business, which created and sold training and exam materials. Realizing that Access databases were more interesting and rewarding than writing training materials, he soon transitioned to full-time Access development and started his company, Grover Park Consulting (www.GPCData.com), specializing in resuscitat- ing “owner-built” Access databases for small- and medium-sized organizations. As a self-taught developer, George believes that any day on which he doesn’t learn at least one new thing is a day wasted.

George is a database developer for Data and Domains, a development organization near Bellevue, Washington, creating Access and Access/SQL Server solutions for clients in the Puget Sound region. George is part of the administrator team, and a regular contributor, at UtterAccess, the most popu- lar Access support site on the Internet. He is the author or contributor to several books on Access, and a presenter at the Pacific Northwest Access Developer Group and Seattle Access Group.

George holds a BS in English and an MA in TESL from Utah State University. And now, George’s daughter and his money go to Seattle University.

DAGI (DOUG) YUDOVICH is the Assistant Director of Departmental Computing for the University of Washington School of Medicine. One of the department’s primary BI responsibilities is developing database solutions using Access and SQL Server. Doug is an Administrator on UtterAccess forums and the UtterAccess wiki. Doug was a contributing author to Microsoft Access Small Business Solutions and the Access 2010 Programmer’s Reference. Additionally, Doug is an active member of the Pacific Northwest Access Developers Group (PNWADG) and a presenter in the Seattle Access Group (SAG). Doug has been awarded the Access MVP award since 2009.

ABOUT THE TECHNICAL EDITORS/

CONTRIBUTORS

JERRY DENNISON has over 20 years of professional experience in the design and development of Microsoft Access database applications. He was one of the coauthors of Microsoft Access 2010 Programmer’s Reference (Wiley, 2010) and a contributing editor on other Access books. Jerry was a four-year recipient of the Microsoft Office Access MVP award and is considered by his peers to be one of the foremost experts on the forms of data normalization. Jerry is the owner of TradeIt! Software Support Services, where he provides database consulting for businesses and other Access developers. He is an automation engineer at Rexam Healthcare Packaging, where he has worked for 22 years providing design, installation, and service support for automated inspection and test systems. Jerry lives with his wife of 24 years in Hixson, Tennessee.

PATRICK WOOD is the founder of Gaining Access Technologies, which provides consult- ing and custom software development. Originally a minister with a bachelor’s degree in biblical studies, he decided one day while he was between churches to develop an application to manage the many tasks involved with small- to medium-sized churches. That sparked his interest in Microsoft Access development, which led to the founding of his company. He learned ASP.NET to build the company website, and began developing SQL Server and Azure SQL databases. Pat’s articles about cutting-edge technology software develop- ment have been featured on several websites. He received the MVP Award for Microsoft Access in 2011. Pat still preaches at a small country church and enjoys taking walks, playing the guitar, hunting and fishing, and spending time with his wife and three young grandsons.

F. SCOTT BARKER is the owner of AppsPlus, where he develops software for industries ranging from government agencies, insurance companies, banks, medical analy- sis fi rms, and even for Microsoft. Scott is a former Microsoft MVP and Microsoft employee, and was a member of the original Access team. He is also the author of a number of books on programming techniques using Microsoft technologies.

LEO (theDBguy™) is an Access MVP and moderator of the UtterAccess (UA) Forums. Since joining UA in 2007, Leo has made over 50,000 posts responding to questions and solving worldwide database dilemmas. Leo is a renowned Access expert with over 17 years of experience creating complex and intuitive Access solutions. He currently works as a Knowledge Manager for the Navy and manages the SharePoint portal for his com- mand, which supports over 1500 sites and more than 5500 users. Leo’s free time is dedicated to his family and to helping others leverage the powers of Access. In addition to being an active member and presenter at the Access User Group of San Diego, he also shares his expertise through his website at www.accessmvp.com/thedbguy and his blog site at www.thedbguy.blog.com.

ACKNOWLEDGMENTS

AS A TEAM, WE WOULD LIKE TO EXPRESS our respect and appreciation for all of the people who contributed to the content, editing, and production of this book. This project truly epitomizes the concept of team work and community — especially the MVP community. We wanted this book to reflect a wide perspective of expertise, but we didn’t anticipate the extent that would become. We reached around the world to leverage the expertise and passion of more than a dozen MVPs and experts from multiple disciplines. They invested an unprecedented amount of time, research, and resources to create this book and all of the supporting fi les and examples. Much like creating a solu- tion for your clients, the team has invested hundreds of hours to create or enhance developer tools that you can incorporate directly into your fi les. Over the course of a number of months, we conquered many hurdles, forged new connections, and helped to expand the functionality for Access web apps. We couldn’t have done it without the timely and invaluable efforts of our editors and technical contributors. These guys stepped up the moment they were called upon — and yes, that often meant at a point of near panic and urgency! Whether it was to resolve a version confl ict, expand into associated technologies, or to share tools and tech- niques, they generously shared their expertise and passion with the community and you, the readers. Please take a moment to read about our contributing editors and technical contributors. Of course, we wouldn’t have anything to write about if it weren’t for the incredible people on the Microsoft Access team. Their passion, dedication, and commitment to continuously expand and enhance Access are truly remarkable and inspiring. And we wouldn’t have a book without all of the editors and teams at Wiley — Bob, Rosemarie, Mary Beth, Nancy, and so many more. Plus a very spe- cial thank you to Chris Haviland for her remarkable skills, attitude, and encouragement while guiding us through months of reviews and production. Hmm, we’ve lost track of all the IOUs, but suffice it to say they should include a spa day, margaritas, chocolate dipped strawberries, and many massages!

—The Authors

FIRST AND FOREMOST, I WANT TO EXPRESS heartfelt appreciation to my family and dearest friends. I am immensely grateful for the opportunities, encouragement, and support that you continue to give me — personally, professionally, and for so many charitable endeavors. From the summit of Mt. Rainier to Uganda, and from fundraisers to books, you help me to stay focused, energized, and positive. Life can take us on quite a roller coaster ride filled with excitement, challenges, accomplishments, and les- sons. People and life have so many facets to relish and cherish; thank you for sharing the adventures. I also want to thank my colleagues, especially my MVP family, and everyone working on this book.

This project has been like no other. When we started planning over 12 months ago, no one could have foreseen the amount of time and effort, or the extensive and ongoing changes, that would be involved. On behalf of myself and the entire Access community, I want to thank Ben and George for their vision, perseverance, and dedication to not only proving that an Access 2013 web app can be a robust business solution, but also providing a model and guide for others to follow. At critical moments, we also gained the invaluable expertise of Greg, Rik, and Jonathan. And to ensure that the traditional Access solutions demonstrate multiple perspectives and signifi cant new approaches, Doug and I incorporated content and tips from several of our MVP colleagues. Their involvement was also instrumental in completing a second round of technical reviews and testing; with many thanks to the Herculean efforts by Leo, who reviewed nearly all of the web chapters!

Over the months, the team has undergone major transitions. Starting with a team of four authors, we added more than a dozen experts with a seeming exponential increase in coordination tasks. (Talk about lessons in project management!) Fortunately, those challenges also enriched my life with new friends as they joined our team. In working countless hours with Leo, Glen, Jack, and Pat, I gained the utmost respect for their expertise and dedication. Similarly, it is always a pleasure to work with my treasured colleagues, Tom, Peter, Jerry, and Scott. Your contributions helped make our book a truly remarkable and invaluable resource for all Access users. I am grateful for being able to share both the opportunity and the credits with so many. In recognition of the invaluable part that they played, we have designated a new role, Technical Contributor. These are remarkable people, so please take a moment to read their bios. This is truly a team effort and it epitomizes the spirit of the MVP community.

With all of the turmoil on the book, I essentially took a 4+ month hiatus from consulting to support the team. So I also want to thank my clients for understanding the delays and for working with me to prioritize and schedule tasks so that we could meet their immediate needs and defer others. THANK YOU!

I must also express very special, heartfelt appreciation to Christina Haviland — for her patience, guidance, humor, and compassionate understanding. Thank you for doing whatever it took to pull all of the pieces together to get everything to production and print! Your entryway should be over- flowing with gift baskets, massage certificates, and a lifetime of gratitude.

This project has tested and strengthened us individually and collectively. We did this for you, the Access community. Our greatest reward is helping you to help others, so it is with great pride that we present this book. As you benefit from this compilation of resources, we hope that you too will realize that it is indeed priceless.

This demonstrates yet again that life is fi lled with opportunities — celebrate them all!

—Teresa Hennig

CONTENTS

INTRODUCTION xxvii

PART I: ACCESS WEB APPLICATION DESIGN AND DEVELOPMENT

xvii

xviii

• AND ARCHITECTURE CHAPTER 1: INTRODUCTION TO ACCESS WEB APPS
  • Deprecated Components
    • Access Data Projects
    • Jet Replication
    • Menus and Toolbars
    • Import/Export/Link to Jet 3.x and dBASE Files
    • PivotTables and PivotCharts
    • Collect Data via E-mail
    • SharePoint Workflow
    • Source Code Control Extension
    • Packaging Wizard
    • Upsizing Wizard
    • Creating Access Web Databases
  • New Components Added
    • Database Compare
    • Audit and Control Management Server
  • What Is an App?
  • How Is an App Hosted?
  • How Is an App Distributed?
    • App Marketplace for Publicly Available Solutions
    • App Catalogs for Internal-Facing Solutions
  • How Are Security and Trust Managed in Apps?
    • Setting Up a SharePoint Site for Your Apps
    • Setting Up an Office 365 Trial Account
  • Solving Business Problems
  • The Maid To Order Work Schedule Database
  • Summary
• CHAPTER 2: DESIGNING TABLES
  • Creating a Blank App
  • Keeping Your Log Ins Straight
  • Creating Tables CONTENTS
    • Creating Tables Using Nouns
    • Creating Tables from Imported Data Sources
    • Creating Tables the Traditional Way
    • Tables and Their Related Views
  • Data Type Changes from Previous Versions
    • Text Data Type
    • Hyperlink Fields
    • Numeric Data Type
    • Date/Time Data Type
    • Image Data Type
    • Lookup Data Type
  • Field Properties
  • Linking SharePoint Lists
  • Summary
• CHAPTER 3: UNDERSTANDING THE NEW USER INTERFACE
  • Web Navigation
    • Navigation Tools
    • The New Approach to Layout
    • The New Ribbon
  • Mobile Devices Support
  • SharePoint and Office
    • Creating a Web App via Team Site
    • Deleting Web Apps
    • Sharing Web Apps with Others
  • Summary
• CHAPTER 4: DESIGNING VIEWS
  • View Design Surface
    • Ribbon
    • Tiles
    • Links and Creating Views
    • Field List
    • Popup Properties
    • Manipulating Control Placement
    • Duplicating a View
  • Action Bar and Action Bar Buttons
    • Action Bar Button Properties
    • Default Action Button
    • Custom Action Buttons
  • Controls CONTENTS
    • Changed Controls
    • New Controls
    • Common Properties
    • Client Controls with No Counterparts
    • Web Browser Control
  • Summary
• CHAPTER 5: CREATING QUERIES AND WRITING EXPRESSIONS
  • Query Architecture
    • SQL Server Views
    • Table-Valued Functions
  • Changes in the Query Designer
    • Creating, Editing, Saving, and Previewing Queries
    • Action Queries
    • Parameterized Queries
    • Aggregates, Unique Values, and Top Values
    • Query Properties
  • Functions and Expressions
    • Delimiters
    • Operator Differences
    • New Constants
    • Data Type Inspection and Conversion
    • String Functions
    • Date and Time Functions
    • Math Functions
    • Other Functions
    • Availability by Context
  • Summary
• CHAPTER 6: Creating Macros
  • Macro Language Why We Need a New Web-Compatible
    • Where We’ve Been
    • Access’s Traditional Role as Tool of First Choice
  • Macro Designer
    • Action Catalog
    • Expressions and Expression Builder
    • Data Macro Tracing
    • Parameter Box
    • Macro Links
    • Interacting with the Macro Designer
  • Different Types of Macros CONTENTS
    • UI Macros
    • Data Macros
    • Data Macro Architecture
    • UI Macro Architecture
    • Block Macro Action
  • Creating and Editing Data Macros
    • Creating a Standalone Data Macro
    • Using the Action Catalog
    • Using the Add New Action Drop-Down
    • Using Program Flow Actions
    • How to Use the LookupRecord Data Block
    • How to Use ForEachRecord and EditRecord
    • How to Use DeleteRecord
  • Creating and Editing UI Macros
    • How to Use SetProperty
    • How to Use ChangeView and OpenPopup
    • How to Use RequeryRecords
    • How to Use Data Entry Operations
  • Using UI and Data Macros Together
    • How to Create and Use Parameters
    • How to Return Values
  • Summary
• CHAPTER 7: DESIGNING THE TABLE STRUCTURE
  • New Data Types
    • Short and Long Text Fields
    • Number Fields
    • Date/Time Fields
    • Currency
    • Yes/No
    • Hyperlink
    • Image
    • Calculated Fields
    • Lookup Fields
  • Validation Rules and Text
    • Field Level Validation
    • Record Level Validation
    • Editing Validation Rules with Existing Data
    • Validation Rules Design Consideration
  • Leveraging Calculated Fields
    • Creating Concatenated Fields