RTM’d today: Microsoft Access 2010 VBA Programming Inside Out
We’re very pleased to announce that Andrew Couch’s new book, Microsoft Access 2010 VBA Programming Inside Out has shipped to the printer!
Dive right in and learn to build elegant business solutions with Access—using Microsoft Visual Basic for Applications (VBA). This supremely organized reference is packed with hundreds of timesaving solutions, troubleshooting tips, and workarounds. It’s all muscle and no fluff. Discover how the experts use VBA to exploit the power of Access—and challenge yourself to new levels of mastery! Enhance your application with SQL code and VBA built-in functions; make forms and reports interact in your application using the Access Object; manipulate data using SQL, Queries, and Recordsets in program code; create classes for handling form and control events; automate your Access database to integrate data from different sources; broaden your skill set by developing applications linked to SQL Server; build Excel spreadsheets that are dynamically updated from the database; and migrate an on-premise SQL Server database to the cloud using SQL Azure. Sample database files include a Table Relinker, and ADO and DAO documentation utilities.
Andrew Couch is a Microsoft Access MVP, who has been programming with VBA since it was introduced into the Access product. He uses VBA on a daily basis in commercial applications. An experienced instructor, Andrew has also taught VBA programming courses.
Andrew’s book will be available via online retailers around end of July. In the meantime, here’s more information about the book:
Contents at a Glance
Part 1: VBA Environment and Language
Chapter 1: Using the VBA Editor and Debugging Code
Chapter 2: Understanding the VBA Language Structure
Chapter 3: Understanding the VBA Language Features
Part 2: Access Object Model and Data Access Objects (DAO)
Chapter 4: Applying the Access Object Model
Chapter 5: Understanding the Data Access Object Model
Part 3: Working with Forms and Reports
Chapter 6: Using Forms and Events
Chapter 7: Using Form Controls and Events
Chapter 8: Creating Reports and Events
Part 4: Advanced Programming with VBA Classes
Chapter 9: Adding Functionality with Classes
Chapter 10: Using Classes and Events
Chapter 11: Using Classes and Forms
Part 5: External Data and Office Integration
Chapter 12: Linking Access Tables
Chapter 13: Integrating Microsoft Office
Part 6: SQL Server and SQL Azure
Chapter 14: Using SQL Server
Chapter 15: Upsizing Access to SQL Server
Chapter 16: Using SQL Azure
Part 7: Application Design
Chapter 17: Building Applications
Chapter 18: Using ADO and ADOX
Introduction
Visual Basic for Applications (VBA) is an exceptional programming language and environment. The language has grown out of a need to have a programming language which would allow more business focused people to write programs, but equally support the programming features that developers look for in a product. The environment is as important as the language because of its unique features in allowing code to be quickly modified whilst being debugged.
The Access Basic language in early product versions evolved into the VBA language which provided a cross-product language for the Microsoft Office products. This all coincided with the revolution of an event driven approach to programming, which was very important because the emphasis on being a programmer shifted from writing thousands of lines of code, to writing snippets of code in response to events. This also led to a change of emphasis from writing large libraries of code to understanding how to manipulate the object models in the environment; a focus which has progressed forwards with .NET albeit using namespaces instead of object models.
Even with the introduction of Object Oriented Programming, VBA has kept pace with the expectations of modern programming. The two products that have shaped VBA the most are Microsoft Excel and Microsoft Access; Excel introduced VBA and had originally gained VBA programming features in advance of these becoming available within Access.
A significant strength of VBA is that it is universal to the Microsoft Office suite of programs; all the techniques we describe in this book can be applied to varying degrees within the other Office products. A major turning point for these products was the ability through OLE Automation to be able to drive one product from another, and cut and paste code between the different environments with a minimum amount of change to the code. This was a revolutionary feature introduced with the programming language of Access Basic conforming to the new VBA standard established in Excel. VBA suddenly provided the long awaited platform for the simple integration of the Office products and building solutions which could easily exploit the strengths of each component product in the Office suite. The combination of Access and VBA offers an extremely productive environment within which to construct applications.
VBA has often been criticized for its simplicity as a language when compared to languages like C++ and C#, although the big advantage of VBA is that its simplicity leads to more easily maintainable and reliable code, when developed by people with a more business focused orientation to programming. Looking towards the future, the emphasis in modern programming has moved from the language syntax to the intricacies of understanding the objects that the language manipulates; so the emphasis on the specific syntax of languages is starting to blur.
In the .NET world, the conflict between using VB.NET which originates from VBA and C# continues, because even though the objects being manipulated are now common, there are subtle differences in the languages which means that developers moving from VBA to C# can often feel that they are being lead out of their comfort zone; especially when they need to continue to use VBA for other applications.
Access has often been criticized for creating poor performance applications where a prototype turns into a business critical system, propagating a support nightmare for Information Technology departments, and leading to applications which eat up network bandwidth. It has also been stated that the product is never used for mission critical applications. The truth is that both Access and Excel are pivotal to many organizations, but the people answering that mission critical question are often not willing to admit to this; as it is perceived as vulnerability. The problem with using Access and Excel is that Rapid Application Development (RAD) can often come to mean final application without recourse to a more structured over-sight of what is being developed, and as data volumes and user communities grow, so do the inevitable flaws in not having designed a scalable solution are exposed.
This book details how Access and VBA are not a problem; although their success is often their downfall in the hands of those lacking some direction on how to effectively develop applications. The big problem with Access is that the underlying database engine is extremely efficient and can compensate for a design which normally would not scale. So if you convert your Access database data to be located in Microsoft SQL Server, Microsoft SQL Azure or Microsoft SharePoint you might find that the existing application design techniques for searching and displaying data need to be revised. Our advice is to take into account the mantra of Client-Server design, which is to minimize the amount of data being transferred in any operation.
In this book, we would like to make our contribution towards creating a better informed community of developers, and show how to better develop applications with VBA.
Who This Book Is For
This book is aimed at two types of reader. Firstly we want to enable a reader who has worked with Access and developed applications to move to the next level of development; allowing them to more fully develop their applications with a deeper understanding of what it means to program with VBA.
Our second audience is the more experienced VBA programmer, who needs a text which enables them to move up a gear and explore the more advanced aspects of VBA programming. Plus we have devoted a significant amount of our pages to supporting you in developing with both SQL Server and Cloud Computing.
Assumptions About You
We make a basic assumption in this book that you are experienced either in working with Access, or you have a strong programming background that means you can learn VBA programming in Access very quickly. We will spend no time explaining how to create a table, form or report, and if you cannot do this then you would need a book which explains these actions in more detail. We recommend our companion text Microsoft Access 2010 Inside Out by Jeff Conrad and John Viescas.
If you are an experienced Access Developer, you can skim Chapters 1-3; if not, Chapters 1-3 will give you a key appreciation of the power of the VBA development environment.
How This Book Is Organized
This book allows you to either start at the beginning and work through each Chapter, or dip into specific Chapters or topics to investigate a particular feature of VBA. When dipping into the book each part is designed to be self-contained.
Part 1: VBA Environment and Language
In these three chapters we provide a grounding showing you how to program with VBA, we start by showing you how to debug, write and modify code (gaining confidence with the VBA environment is the first step to efficiently developing with the VBA environment). Then we move on to an in-depth exposition of the VBA language which can act both as a reference for coding syntax and a solid introduction to the language.
Part 2: Access Object Model and Data Access Objects (DAO)
These two chapters dig deep into programming with the objects that make up Access. The DAO programming language Chapter is the bread and butter programming technique for any Access VBA developer.
Part 3: Working with Forms and Reports
In these three chapters we take you through how to apply VBA when working with forms, controls and reports. This develops your core techniques in understanding how to apply VBA for building the key interface components in applications.
Part 4: Advanced Programming with VBA Classes
These three chapters are for some developers more esoteric than the rest of this book, but they illustrate how you can exploit VBA to embrace the most advanced concepts of modern computing using object oriented programming. There are a lot of cunning tricks and techniques in these chapters which are worth reading, and many of the ideas in these chapters will take you forward in also handling development with .net.
Part 5: External Data and Office Integration
These two Chapters address the issue of how to link Access to external data and write VBA to communicate both with other Office applications and external data sources such as SQL Server and SharePoint.
Part 6: SQL Server and SQL Azure
These three chapters provide a comprehensive description of how to extend the reach of Access applications by moving the back-end data into SQL Server and then onto SQL Azure. We start with a Chapter dedicated to equipping developers with a solid understanding of how to develop code with SQL Server during which we explain both how to use the SQL Server Management Studio and write programs using TSQL.
Then we move on to look at converting Access Databases to SQL Server using both the Upsizing Wizard and SSMA. The final Chapter discusses how to move your databases into the cloud either using the SQL Server Import and Export Wizard feature in the SQL Server Management Studio from a local SQL Server or SSMA from an Access Database. We discuss how you can exploit the unique features of Office in directly constructing links to Azure, building multi-tenanted solutions and using the soon to be released new Data Sync features in SQL Azure.
Part 7: Application Design
The last part of this book shows you a number of ideas for helping you to create applications, including a discussion of how to design the user interface, building ribbons, utilizing the windows API and working with ADO and ADOX. This chapter on Building Applications ties together and makes references back to other sections in the book.