the Access Experts: Custom Database Solutions

Home
Services
Products
Links
Support
Portfolio
About Us

 

Automation 101
Many situations occur where you need to transmit data to a person who doesn’t have Access, where Access limitations prevent you from doing exactly what you feel is required, or where you need to perform a function that Access doesn’t support.  Examples include sending data to a user in Excel report format from an Access database, creating a report with complex formatting in Word, and sending email from an Access database.  This tutorial introduces the concept of Automation (formerly known as OLE Automation and before that as ActiveX – all these were a replacement for something called DDE which was a simple, but slow and fragile technology used to communicate between applications – many people still use it for mail merges between Access and Word), and describes some of the power provided by it.

Automation is provided as a feature of COM (Component Object Model) technology.  Applications built using this technology make their objects accessible to VBA and to other applications that are COM compliant.  In this view of the world, there is always an Automation Client and an Automation Server – the distinction is that the client is the host application, and the server is the application the client is controlling.  For example, if you build a report in Excel or Word using Access, Access is the client, and Excel or Word is the server.  On the other hand, if you reach into an Access database to extract data and present it in Word using a Word VBA module, Word is the client and Access is the server.  Earlier versions of Office applications had some constraints, as not all of them could act as servers, but with Office 2000, all mainstream applications could be servers.  However you may encounter Microsoft other vendor applications that are not mainstream that have similar constraints.

How do I create an automation session between two applications?

Let’s suppose you want to create a Word report from Access.  In that case the VBA in and Access module would look something like this:

      Dim wordApp As Object   'Declare object variable

      Set wordApp = CreateObject("word.application")  'Start Word

This code will create an instance of Word if it isn't already running, but it may not be visible so you will probably want a line of code to make it visible:

      wordApp.Visible = True  'make the Word instance visible

Then to convince yourself that you really did something useful, you need to create a simple document:

      wordApp.Documents.Add   ' create a new document

      wordApp.Selection.Text = "My dog has fleas!"  'insert some text

Finally, once you've done what you need to, you must remember to close the application and release the reference (and the memory it uses):

      wordApp.Quit            ' Terminate the Word application

      Set wordApp = Nothing   ' Release the object resources

 

That was a simple example, but how do I deal with real-world problems?

The example of automating Word from Access is simple, but it demonstrates most of the principles of Automation.  Unfortunately, there are a number of issues and situations that need to be considered, but many are beyond the scope of this tutorial.  Examples include "what if Word or whatever application is already open?", "what are the unique aspects of the Automation Server?", "should I use early or late binding (the example uses late binding)?", and "how do I deal with errors that occur along the way?"  A case in point is how to deal with saving the Word document that was created above.  If you run the code as it is, you will notice that you get a prompt asking if you want to save the new document before Word closes, a characteristic of Word and many other applications.  In a real world situation, you would either want to automate the save process completely, or provide a more friendly prompt such as do you want to save the new document.  To see an example of an actual Automation project, visit our tutorial on mail merges.  Dealing with real-world problems invariably requires that you understand the object model of the application you are automating

A first step is setting references to the application(s) you intend to work with.  That can be done with the VBA editor, by using the Tools / References command which brings up the References dialog box.  When you do that, you will typically see references set to things such as Visual Basic, Microsoft Access XX Object Library, and OLE Automation.  You may also see references set to such things as the Microsoft Office XX Object Library, Microsoft DAO or ADO, and other applications.  The references are set on a database by database basis, so each new database must have its own special references set.  Once you have that, the challenge is to explore the object model.

How do I find out about the object model of the application?

One way is to open a VBA module in that application, open VBA help, and search for "object model."  Each of the applications has its own object model, and the help files give a graphical view of that model.  If you want more detail, the object browser is the tool of choice to see all of the objects and methods.  It can be opened from the VBA editor for any referenced application, and is activated by pressing F2, clicking on the Object Browser toolbar button, or by selecting View / Object Browser.  With this tool, you can determine what objects exist and some of the components of the object.  If you highlight a specific member of an object class, you can press the F1 key and VBA Help will be opened to the specific topic for that member.  Unfortunately the help files with Office 2000 and 2002 do contain some errors where no help is displayed, so you may want to check for the latest version of help available by download, or refer to the online version of help files on the Microsoft web site.

 

Where can I get more information about Automation?

There are several places you can get additional details and examples of Automation.  For starters there is the Help for VBA.  If you search on the string “Understanding Automation” you will get some topics – another search string “OLE Automation” will return some different topics.  The following topics are useful in our opinion:

  • Understanding Automation
  • Automation with Microsoft Access
  • CreateObject function
  • GetObject function
  • Object property
  • Set Reference to a Type Library
  • User Control Property
  • Application Object
  • SetOption Method
  • Working across applications

    A second resource that is quite valuable in getting examples and basic concept information is the Microsoft Knowledge Base.  Some of the more useful articles are:

    Microsoft Office 2000 Automation Help File Available (if you are starting an automation project be sure to download the latest version of this help file)

    GetObject and CreateObject Behavior of Office Automation Servers

    Obtain Built-In Constant Values for an Office Application

    Find and Use Office Object Model Documentation

    Using Automation to Create and Manipulate an Excel Workbook

    Using Automation to Transfer Data to Microsoft Excel

    Using Automation to Create a Microsoft Excel Chart

    HOW TO: Call Excel Functions from Within Microsoft Access 2000

    Sending the Current Record to Word 2000 with Automation

    Use Automation to Find a Location in a Word Document in Access 2000

    How to Use Automation to Run Word 2000 Mail Merge from Access

    How to Use Automation to Find Bookmarks in Word 2000 Documents

    How to Use Automation to Create a Word 2000 Merge Document

    Use Automation to Print a Microsoft Word Document using Access 2000

    How to Use Automation to Create a New Contact Item in Microsoft Outlook

    How to Use Automation to Add a Task or a Reminder to Microsoft Outlook

    Use Automation to Send a Microsoft Outlook Message using Access 2000

    How to Use Automation to Add Appointments to Microsoft Outlook

    How to Use Automation to Create a PowerPoint Presentation

    How to Implement Automation to Microsoft Project

    How to Use Visual Basic for Applications to Minimize, Maximize, and Restore Access

     

    You are also likely to find topics related to the Automation of various applications at the Microsoft MVP site www.mvps.org .  In the world of books, an excellent and detailed exploration of Automation can be found in “The Access Developer’s Handbook” by Litwin, Getz, et. al. published by Sybex with versions for 97, 2000 and 2002 of Access.  Other titles that have a significant description of Automation include “Beginning Access VBA Programming” and “Professional Access VBA Programming”, both published by Wrox, “F. Scott Barkers Access 2000 Power Programming”, and “Alison Balter’s Mastering Access 2002 Desktop Development”, both published by SAMS.

  •  

    Copyright © 1998-2011 Wendell Bell & Associates Inc.  Last updated on August 1, 2011