ACC: Automation Object Disappears When Code Finishes Executing

• Microsoft Access versions 7.0, 97

SYMPTOMS

Moderate: Requires basic macro, coding, and interoperability skills.

When you use Automation to control another application, such as Microsoft Word or Microsoft Excel, the application quits as soon as your procedure finishes executing.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

CAUSE

The application quits because its object variable loses scope.

RESOLUTION

If you do not want the Automation server application to close when your code finishes executing, use one of the following methods.

Method 1

Declare the Object Variable in the Declarations Section of Your Code Module:

1. Open the sample database Northwind.mdb.

2. Create a module and type the following line in the Declarations section:

         Dim xlApp as Object
   

3. Type the following procedure:

         Sub LeaveXLOpen1()
         Set xlApp = CreateObject("Excel.Application")
         xlApp.Visible = True
         End Sub
   
   

4. To test this function, type the following line in the Debug window, and then press ENTER.

          LeaveXLOpen1
   

NOTE: If you declare your object variable in the Declarations section of a form or report module, Microsoft Excel remains open only as long as the form or report remains open.

Method 2

Declare the Object Variable as a Static Variable at the Procedure Level:

1. Open the sample database Northwind.mdb.

2. Create a module and type the following procedure:

         Sub LeaveXLOpen2()
         Static xlApp as Object
         Set xlApp = CreateObject("Excel.Application")
         xlApp.Visible = True
         End Sub
   
   

3. To test this function, type the following line in the Debug window, and then press ENTER.

          LeaveXLOpen2
   

NOTE: If you declare the Static variable as part of a procedure in a form or report module, Microsoft Excel remains open only as long as the form or report remains open.

MORE INFORMATION

Where and how you declare a variable in a code module determines its scope and its lifetime. Scope is defined as the availability of a variable, constant, or procedure for use by another procedure. Lifetime is defined as the time during which a variable retains its value.

When you declare an object variable within a procedure, the variable only retains its value as long as the procedure is running, as shown in the example in the "Steps to Reproduce Behavior" section.

Steps to Reproduce Behavior

1. Open the sample database Northwind.mdb.

2. Create a module and type the following procedure:

         Sub OpenAndClose()
         Dim xlApp as Object
         Set xlApp = CreateObject("Excel.Application")
         xlApp.Visible = True
         End Sub
   
   

3. To test this function, type the following line in the Debug window, and then press ENTER.

          OpenAndClose
   

   Note that Microsoft Excel starts and then quits. That is because its object variable, xlApp, loses scope as soon as the procedure stops running.

REFERENCES

For more information about the scope and lifetime of variables, search the Help Index for "variables, scope," or "variables, lifetime."

For more information about using the CreateObject function, search the Help Index for "CreateObject function."

For more information about declaring Static variables, search the Help Index for "Static statement."

For information about using Microsoft Access as an Automation server, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q147816
   TITLE     : ACC: Using Microsoft Access as an Automation Server

   ARTICLE-ID: Q145770
   TITLE     : ACC: Automation Does Not Close Microsoft Excel

Keywords          : AutoGnrl kbinterop IntpOleA
Version           : 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbprb
Solution Type     : kbcode