Knowledge Base

ACC: Using Microsoft Access as an Automation Server

Article ID: 147816

Article Last Modified on 1/19/2007

APPLIES TO

Microsoft Access 95 Standard Edition
Microsoft Access 97 Standard Edition

This article was previously published under Q147816

SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article provides information and examples on how to use Microsoft Access 7.0 or 97 as an Automation server. With Visual Basic for Applications, you can manipulate the functionality of Microsoft Access from a controller (or client) that supports Automation, such as Microsoft Excel, Microsoft Project, or Microsoft Visual Basic. For example, you can create an application in Microsoft Excel that uses Automation to print a report in a Microsoft Access database.

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.

MORE INFORMATION

This article discusses the following topics:

Creating a Reference to Microsoft Access
Using GetObject() and CreateObject() Functions
Understanding the UserControl and Visible Properties
Viewing an Instance of Microsoft Access
Closing an Instance of Microsoft Access
Bypassing Startup Settings When Opening a Database
Calling Microsoft Access Functions
Calling Custom Procedures
Using a Run-Time Application
Using a Secured Workgroup
Examples:

- Previewing or Printing a Report
- Calling a Report Wizard to Create a New Report

Creating a Reference to Microsoft Access

Automation enables you to control or manipulate Microsoft Access objects from other applications using Visual Basic code. To make this possible, information about Microsoft Access objects is stored in a type library called Msaccess.tlb. If you want optimal performance for your OLE Automation code, you can create a reference to the Microsoft Access type library.

NOTE: Many of the sample procedures demonstrated in this article require a reference to Microsoft Access. Be sure to follow the steps below to create a reference in your Automation controller if you plan to use the sample code.

To create a reference:

Open a module in your Automation controller.
On the Tools menu, click References.
In the References box, select Microsoft Access 8.0 Object Library (or Microsoft Access for Windows 95 in version 7.0), and then click OK.

Creating a reference to Microsoft Access is not required to manipulate its objects using Automation. However, creating a reference does have the following advantages:

Your code will run faster.
You can use the Object Browser to view all Microsoft Access objects, properties, and methods. This makes it easy to determine what properties and methods are available to each object.
You can use Microsoft Access constants or call Microsoft Access functions directly in your Visual Basic code.
You can declare a variable as an application-defined object type, for example:
```
      Dim objAccess As Access.Application
                        
```
This type of declaration is called early binding, which is fastest. However, it is not supported by all Automation controllers. For this reason, the sample code in this article uses late binding instead of early binding. For, example, it declares a variable as an object rather than as Access.Application:
```
      Dim objAccess As Object
                        
```
To find out if an application supports early or late binding, please see your Automation controller documentation.

Using GetObject() and CreateObject() Functions

The GetObject() and CreateObject() functions enable you to open or activate an instance of Microsoft Access and control its functionality from an Automation controller application. When you use these functions in a Visual Basic module, you activate Microsoft Access as an Automation object and assign the object to a variable. For example, here are several different ways to use the GetObject() and CreateObject() functions.

Method 1

You can use the GetObject() function to activate or open an instance of Microsoft Access and a specific database with the following syntax:

   Dim objAccess as Object
   Set objAccess = GetObject("C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb")

Note that the objAccess variable can refer to different instances of Microsoft Access depending on the following conditions when the code is run:

If the specified database (Northwind.mdb) is already open in an instance of Microsoft Access, then objAccess refers to the open instance.
If the specified database is open in multiple instances of Microsoft Access, then objAccess refers to one of those instances at random.
If Microsoft Access is not running when the code is run, then a new instance is opened and objAccess refers to that new instance.

Method 2

You can use the GetObject() function to activate an instance of Microsoft Access with the following syntax:

   Dim objAccess as Object
   Set objAccess = GetObject(,"Access.Application")

Note that the objAccess variable can refer to different instances of Microsoft Access depending on the following conditions when the code is run:

If an instance of Microsoft Access is running at the time the code is run, then objAccess refers to the open instance.
If multiple instances of Microsoft Access are running, then objAccess refers to one of those instances at random.
If Microsoft Access is not running when the code is run, then an error is generated.

Method 3

You can use the CreateObject() function to open a new instance of Microsoft Access with the following syntax:

   Dim objAccess as Object
   Set objAccess = CreateObject("Access.Application")

The objAccess variable refers to the Application object of the new instance.

NOTE: Some Automation controllers, such as Microsoft Visual Basic 4.0, support the keyword "New" as an alternative method for opening a new instance of an Automation server, for example:

   Dim objAccess as New Access.Application

See your Automation controller documentation to determine if it supports the New keyword.

Understanding the UserControl and Visible Properties

The UserControl and Visible properties of the Application object are important for controlling the on-screen behavior of Microsoft Access. The UserControl property enables you determine whether an instance of Microsoft Access was started by the user or by another application using Automation. The Visible property enables you determine whether an instance of Microsoft Access is visible or minimized.

When you open or activate an instance of Microsoft Access, the UserControl and Visible properties are set automatically, depending on whether Microsoft Access is already running when an Application object is activated in Visual Basic. For example, the following table illustrates these settings in different scenarios:

   Method for Activating MS Access      UserControl   Visible
   ----------------------------------------------------------

   GetObject() function after an          True        True
     instance of Microsoft Access
     is opened

   Shell() function                       True        True

   GetObject() function when no           False       False
     instance of Microsoft Access
     is open yet

   CreateObject() function                False       False

UserControl Property
--------------------

UserControl Property

The UserControl property is always read-only; therefore, you cannot set it using Automation. However, the UserControl property can change automatically if a user intervenes while your Automation code is idle. For example, the UserControl property is changed to False when the following events occur:

The user creates an instance of Microsoft Access, which sets the UserControl property to True.
You run Automation code in the controller application, which uses the GetObject() function to activate the previously opened instance of Microsoft Access. The object variable that you use for the instance is a Public or module-level variable.
The user restores Microsoft Access using the Windows taskbar (or Task List in Windows NT).
The user tries to close Microsoft Access by clicking the Close box. The instance does not close as expected because the Automation controller has a Public or module-level object variable referring to that instance of Microsoft Access. Instead, the instance is minimized, which sets the UserControl and Visible properties to False.

Similarly, the UserControl property is changed to True if the following events occur:

You create a new instance of Microsoft Access using Automation. The UserControl property is False. The Visible property is also False; therefore, the instance is minimized.
The user restores the instance using the Windows taskbar (or Task List in Windows NT). Or, you call the ShowWindow() API function in Visual Basic to restore the instance using code. In both cases, the UserControl and Visible properties are changed to True.

If the UserControl property is True, it can affect your ability to control the on-screen behavior of Microsoft Access. Specifically, you should watch out for the following limitations:

You may receive an error message in your Automation code when you try to set the Visible property to True; the Visible property is read-only when the UserControl property is True.
You cannot trap or suppress an error message generated by Microsoft Access as the OLE Server application. If you execute a bad command, such as trying to open a form that does not exist in the current database, an error message is displayed.
An instance of Microsoft Access does not close automatically when the object variable referring to the instance (objAccess) is set to Nothing or it loses scope.

Visible Property

The Visible property is read-only or read-write in different situations. It is read-only when the UserControl property is True or the instance of Microsoft Access is originally created by the user (and not Automation code). The Visible property is read-write when the UserControl property is False. In rare cases, however, setting the Visible property to True may fail to bring Microsoft Access into view if the instance's Visible property is already True and it has been minimized by the user.

Viewing an Instance of Microsoft Access

There are several ways to view an instance of Microsoft Access using Automation, depending on whether Microsoft Access is open at the time the Application object is activated.

Method 1

When you create a new instance of Microsoft Access using Automation, the instance is minimized because the Application object's Visible property is automatically set to False. To bring Microsoft Access into view, you can set the Visible property to True. For example:

   Dim objAccess as Object
   Set objAccess = CreateObject("Access.Application")
   objAccess.Visible = True

This method works because the CreateObject() function always opens a new instance of the specified application. If you set the Visible property immediately after running CreateObject(), then the user cannot intervene by restoring or minimizing the application window, which resets the UserControl and Visible properties.

However, if you use the GetObject() function instead of CreateObject()and set the Visible property, your code may fail in the following situations:

If Microsoft Access is already running when the code is run. In this case, the UserControl property is set to True, which makes the Visible property read-only.
If an instance of Microsoft Access is opened with Automation, is restored by the user using the taskbar, and is then minimized by the user.

NOTE: To avoid these two limitations, you can use Method 2 for bringing an instance of Microsoft Access into view.

Method 2

If you want to view an instance of Microsoft Access regardless of user intervention, you can use the ShowAccess() procedure listed below. This sample function makes three Windows API calls to control an instance of Microsoft Access, regardless of its current UserControl and Visible properties settings.

   '----------------------------------------------------------------------
   'DECLARATIONS
   '----------------------------------------------------------------------

   Option Explicit

   Declare Function SetForegroundWindow Lib "User32" _
     (ByVal hWnd As Long) As Long
   Declare Function IsIconic Lib "User32" _
     (ByVal hWnd As Long) As Long
   Declare Function ShowWindow Lib "User32" _
     (ByVal hWnd As Long, ByVal nCmdShow As Long) As Long
   Const SW_NORMAL = 1     'Show window in normal size
   Const SW_MINIMIZE = 2   'Show window minimized
   Const SW_MAXIMIZE = 3   'Show window maximized
   Const SW_SHOW = 9       'Show window without changing window size

   Dim objAccess As Object 'module-level declaration

   '----------------------------------------------------------------------
   'This procedure brings the instance of Microsoft Access referred to
   'as "instance" into view. The instance's window size can be SW_NORMAL,
   'SW_MINIMIZE, SW_MAXIMIZE, or SW_SHOW. If size is omitted, the window is
   'not changed (SW_SHOW). To call this function, use this syntax:
   '   ShowAccess instance:=objAccess, size:=SW_SHOW
   '----------------------------------------------------------------------

   Sub ShowAccess(instance As Object, Optional size As Variant)
     Dim hWnd As Long, temp As Long

     If IsMissing(size) Then size = SW_SHOW
     On Error Resume Next
         If Not instance.UserControl Then instance.Visible = True
         On Error GoTo 0 'turn off error handler
         hWnd = instance.hWndAccessApp
         temp = SetForegroundWindow(hWnd)
         If size = SW_SHOW Then 'keep current window size
              If IsIconic(hWnd) Then temp = ShowWindow(hWnd, SW_SHOW)
         Else
              If IsIconic(hWnd) And size = SW_MAXIMIZE Then _
                temp = ShowWindow(hWnd, SW_NORMAL)
              temp = ShowWindow(hWnd, size)
         End If
   End Sub

Method 3

If you want to view a specific database using an instance of Microsoft Access that is already running when your automation code runs, you can use the ShowAccess() procedure (described above in Method 2) along with the following sample code:

   '----------------------------------------------------------------------
   'This procedure opens the sample database Northwind.mdb in a new or
   'existing instance of Microsoft Access (if one is already open).
   '
   'NOTE: This procedure uses the ShowAccess() procedure (listed above for
   'Method 2). You must enter this procedure into the same module as
   'ShowAccess() for the code to run properly.
   '----------------------------------------------------------------------

   Sub OpenNorthwind()
     Dim path as String

     On Error Resume Next 'temporary error handling
     Set objAccess = GetObject(,"Access.Application")

       If Err <> 0 Then 'no existing instances of Access
         Set objAccess = CreateObject("Access.Application")
       End If

       On Error GoTo OpenNorthwind_ErrHandler 'normal error handler
       ShowAccess instance:=objAccess, size:=SW_MAXIMIZE

       With objAccess
         path = .SysCmd(Access.acSysCmdAccessDir) & "Samples\Northwind.mdb"
         If .DBEngine.Workspaces(0).Databases.Count = 0 Then
            .OpenCurrentDatabase filepath:=path
         ElseIf LCase(Right(.CurrentDb.Name, Len("northwind.mdb"))) _
             <> "northwind.mdb" Then
            .CloseCurrentDatabase

            .OpenCurrentDatabase filepath:=path
         End If
         .DoCmd.OpenForm FormName:="Main SwitchBoard"
       End With
     Exit Sub

   OpenNorthwind_ErrHandler:
     MsgBox Error$(), , "Open Northwind"
   End Sub

Closing an Instance of Microsoft Access

Normally, an instance of Microsoft Access closes automatically when the object variable referring to the instance is set to Nothing or loses scope in the controller application. However, if any Microsoft Access objects are open in the following views, then the instance does not close automatically as expected:

   Object   View
   ----------------------
   Table    Datasheet
            Design
   Query    Datasheet
   Form     Form
   Report   Print Preview

When a Microsoft Access object is open, the instance does not close until the objects are closed and the Application object's UserControl property is False. You can, however, force an instance to close by using the Quit method of an Application object. For example, the following sample code uses the Quit method to close all instances of Microsoft Access.

   '----------------------------------------------------------------------
   'DECLARATIONS
   '----------------------------------------------------------------------

   Option Explicit

   '----------------------------------------------------------------------
   'This procedure closes all open instances of Microsoft Access. Once all
   'instances are closed, the error handler is run and the procedure ends.
   '----------------------------------------------------------------------

   Sub CloseAllAccess()
     Dim objAccess As Object
     On Error GoTo CloseAllAccess_ErrHandler
       Do
         Set objAccess = GetObject(,"Access.Application")
         objAccess.Quit
       Loop
   CloseAllAccess_ErrHandler:
     Set objAccess = Nothing
   End Sub

WARNING: You should not use the Quit method to close an instance created by directly calling a Microsoft Access function if your code makes additional calls to Microsoft Access functions. For more information about using Microsoft Access functions in your Automation code, please see the "Calling Microsoft Access Functions" section later in this article.

NOTE: You can prevent an instance of Microsoft Access from closing when its object variable (objAccess) loses scope by making the object variable a module-level or public variable rather than a procedure-level variable.

For example, if objAccess is declared within a procedure, it is available for use only while the procedure is running. When the procedure ends, objAccess loses scope and Microsoft Access can close automatically.

However, if objAccess is declared in the Declarations section of a standard module, then it is a module-level variable, which is available to all procedures in that module. If objAccess is declared as a Public variable in a standard module, then it is available to all procedures in the database. In these two situations, objAccess does not lose scope when your Automation code is idle. As a result, a connection remains open to Microsoft Access until you use the Quit method or you close the Automation controller.

Bypassing Startup Settings When Opening a Database

When using Microsoft Access as an Automation Server, there are different methods for opening a particular database. For example, you can use the GetObject() function and specify a database. Or, you can use the Application object's OpenCurrentDatabase method to open an existing database as the current database.

If a database has custom Startup settings or an AutoExec macro, these features are run when you use an OpenCurrentDatabase method. However, you can use a SendKeys statement to simulate holding down the SHIFT key when opening the database, which bypasses the Startup settings and AutoExec macro. For example, you can use the following sample code:

   Dim objAccess as object
   Set objAccess = CreateObject("Access.Application")

      ShowAccess instance:=objAccess, size:=SW_MAXIMIZE
      SendKeys "+"
      'Simulates holding down the SHIFT key as the database is being opened
      objAccess.OpenCurrentDatabase filepath:= _
         "C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"

For the SendKeys statement to work properly, your code should meet the following conditions:

The code should use late binding, which means the variable referring to an instance of Microsoft Access is declared as an object, for example:
```
      Dim objAccess as Object
      Set objAccess = CreateObject("Access.Application")
                        
```
You should not use early binding, which declares the variable using an application-defined object type, for example:
```
      Dim objAccess as Access.Application
      Set objAccess = CreateObject("Access.Application")
```
The code ensures that Microsoft Access is in view and the mouse pointer is located in the Microsoft Access window. If Microsoft Access is not the active window, run a procedure such as the ShowAccess() function (described earlier in the "Viewing an Instance of Microsoft Access section").
The database that you are controlling with Automation has its AllowBypassKey property set to True (the default setting).

The OpenCurrentDatabase method appears immediately after the SendKeys statement and contains no calculations. For example, instead of using the following code

      objAccess.OpenCurrentDatabase filepath:= _
      objAccess.SysCmd(Access.acSysCmdAccessDir) &"Samples\Northwind.mdb"
      SendKeys "+"

use this sample code:

      path = objAccess.SysCmd(Access.acSysCmdAccessDir) & _
        "Samples\Northwind.mdb"
      SendKeys "+"
      objAccess.OpenCurrentDatabase filepath:=path

Calling Microsoft Access Functions

In your Automation code, you can call built-in Microsoft Access functions such as Eval(), SysCmd(), or Dlookup() as long as the procedure is exposed to the Application object. To find out if a function is available, follow these steps:

Open a module.
On the Tools menu, click References.
In the References box, select Microsoft Access 8.0 Object Library (or Microsoft Access for Windows 95 in version 7.0), and then click OK.
On the View menu, click Object Browser.
In the Object Browser box, under Libraries/Databases, select "Access" (or "Access - Microsoft Access for Windows 95" in version 7.0).
Under Classes (or Modules/Classes in version 7.0), click Application. Note that the functions listed for the Application object appear in the Members box (or the Methods/Properties box in version 7.0). You can use any of these in your Automation code.

When you use a Microsoft Access function in Automation code, you can call the function directly, using the Application object, or indirectly, using an object variable set to an instance of Microsoft Access. Both techniques are described below in more detail.

Directly Calling a Microsoft Access Function

To call a Microsoft Access function directly, you must first have a reference to the Microsoft Access 8.0 Object Library (or Microsoft Access for Windows 95 in version 7.0) in your Automation controller. For more information about creating a reference, please see the "Creating a Reference to Microsoft Access" section in this article.

Once you have a reference to Microsoft Access, you can use the "Access" Application object to call a Microsoft Access function, for example:

   MsgBox Access.Eval("2+2") 'displays "4"
   MsgBox Access.SysCmd(Access.acSysCmdAccessDir) 'displays the path

The first time an Automation controller directly calls a Microsoft Access function, a new, minimized instance of Microsoft Access is created. The controller maintains a connection to this instance in case your code makes additional calls to Microsoft Access functions. This connection remains in effect until the controller application is closed.

NOTE: You should not use the Application object's Quit method to close an instance created by directly calling a Microsoft Access function. This can cause an Automation error in your controller if you later make a call to a Microsoft Access function. To avoid a potential error, you can let the controller close the minimized instance automatically at the time the controller is closed.

Indirectly Calling a Microsoft Access Function

To call a Microsoft Access function indirectly, you do not need to create a reference to the object library (unlike when you call one directly). In your Automation code, you can use an object variable set to an instance of Microsoft Access for calling a Microsoft Access function, for example:

   Dim objAccess as Object
   On Error Resume Next
     Set objAccess = GetObject(,"Access.Application")
       If Err <> 0 Then 'no instance of Access is open
         Set objAccess = CreateObject("Access.Application")
       End If
     MsgBox objAccess.Eval("2+2") 'displays 4
     MsgBox objAccess.SysCmd(Access.acSysCmdAccessDir) 'displays the path

Calling Custom Procedures

In your Automation code, you can call a custom Visual Basic procedure stored in a Microsoft Access database by using the Run method of the Application object. The custom procedure must be declared as Public and located in a standard module (not a form or report module). For example, you can add the following function to a new module in the Northwind.mdb sample database:

   Public Function MyDateAdd(interval As String, number As Integer, _
     startdate As Date) As Date
     MyDateAdd = DateAdd(interval, number, startdate)
       'Calls the Microsoft Access built-in DateAdd function.
   End Function

To run the above function, add the following sample code to your Automation controller:

   Dim objAccess as Object, newdate as Date
   Set objAccess = GetObject _
      ("C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb")
   newdate = objAccess.Run("MyDateAdd", "m", 1, Date)
   MsgBox newdate,,"MyDateAdd returned"

Using a Run-Time Application

If you want to control a run-time Microsoft Access application using Automation, there are several code changes you may need to make especially if a full, retail version of Microsoft Access is not installed on the user's computer:

Because a run-time version of Microsoft Access cannot start without a database, you should first verify if an instance is already running. If it is not, you should use the Shell() function to open the run-time version and specify a path to Msaccess.exe and a database.
After your code starts a run-time instance of Microsoft Access, use the GetObject()to refer to the instance.
Make sure your code does not bring a run-time instance into view before a database is open. Otherwise, the instance will briefly display on screen, and then become minimized.
Use the Quit method of the Application object if you want to close a run-time instance of Microsoft Access, for example:
```
      objAccess.Quit
```

If you want to open a run-time instance of Microsoft Access, you can use the following sample procedure:

   '----------------------------------------------------------------------
   'DECLARATIONS
   '----------------------------------------------------------------------

   Option Explicit
   Dim objAccess as Object

   '----------------------------------------------------------------------
   'This procedure sets a module-level variable, objAccess, to refer to
   'an instance of Microsoft Access. The code first tries to use GetObject
   'to refer to an instance that might already be open and contains the
   'specified database (dbpath). If the database is not already open in
   'an instance of Microsoft Access, a new instance of the full version of
   'Microsoft Access is opened. If the full version of Microsoft Access is
   'not installed, the Shell() function starts a run-time instance of
   'Microsoft Access. Once the instance is opened, you can use the
   'CloseCurrentDatabase and OpenCurrentDatabase methods to work with other
   'databases.
   '----------------------------------------------------------------------

   Sub OpenRunTime()
     Dim accpath As String, dbpath As String
     On Error Resume Next
     dbpath = "C:\My Application\MyApp.mdb"
     Set objAccess = GetObject(dbpath)
     If Err <> 0 Then
       If Dir(dbpath) = "" Then 'dbpath is not valid
         MsgBox "Couldn't find database."
         Exit Sub
       Else  'The full version of Microsoft Access is not installed.
         accpath = "C:\Program Files\Common Files\Microsoft Shared" & _
              "\Microsoft Access Runtime\MSAccess.exe"
         If Dir(accpath) = "" Then
            MsgBox "Couldn't find Microsoft Access."
            Exit Sub
         Else
            Shell pathname:=accpath & " " & Chr(34) & dbpath & Chr(34), _
              windowstyle:=6
            Do 'Wait for shelled process to finish
              Err = 0
              Set objAccess = GetObject(dbpath)
            Loop While Err <> 0
         End If
       End If
     End If
   End Sub

Using a Secured Workgroup

If the Microsoft Access application you want to control uses a secured workgroup (System.mdw), you may want to bypass the logon box, which asks for a user name and password. The following sample code uses the Shell() function to start Microsoft Access and pass a user name and password to the application:

   '----------------------------------------------------------------------
   'DECLARATIONS
   '----------------------------------------------------------------------

   Option Explicit
   Dim objAccess as Object

   '----------------------------------------------------------------------
   'This procedure sets a module-level variable, objAccess, to refer to
   'an instance of Microsoft Access. The code first tries to use GetObject
   'to refer to an instance that might already be open. If an instance is
   'not already open, the Shell() function opens a new instance and
   'specifies the user and password, based on the arguments passed to the
   'procedure.
   '
   'Calling example: OpenSecured varUser:="Admin", varPw:=""
   '----------------------------------------------------------------------

   Sub OpenSecured(Optional varUser As Variant, Optional varPw As Variant)
      Dim cmd As String
      On Error Resume Next
      Set objAccess = GetObject(, "Access.Application")
      If Err <> 0 Then 'no instance of Access is open
        If IsMissing(varUser) Then varUser = "Admin"
        cmd = "C:\Program Files\Microsoft Office\Office\MSAccess.exe"
        cmd = cmd & " /nostartup /user " & varUser
        If Not IsMissing(varPw) Then cmd = cmd & " /pwd " & varPw
        Shell pathname:=cmd, windowstyle:=6
        Do 'Wait for shelled process to finish.
          Err = 0
          Set objAccess = GetObject(, "Access.Application")
        Loop While Err <> 0
      End If
   End Sub

Examples

This section contains two sample procedures for controlling Microsoft Access functionality from an Automation controller such as Microsoft Excel, Microsoft Project, or Microsoft Visual Basic. The two procedures perform the following tasks: previewing or printing a report; and calling a Report Wizard to create a new report.

NOTE: These sample procedures require a reference to the Microsoft Access object library in the Automation controller application. For more information about creating a reference, please see the "Creating a Reference to Microsoft Access" section earlier in this article.

Previewing or Printing a Report

You can use the following sample procedure for printing or previewing a report in Microsoft Access from an Automation controller:

   '----------------------------------------------------------------------
   'DECLARATIONS
   '----------------------------------------------------------------------

   Option Explicit

   '----------------------------------------------------------------------
   'This procedure prints or previews a report, and then closes the current
   'instance of Microsoft Access (because objAccess is a procedure-level
   'variable). To call this procedure, use the following syntax:
   '  PrintAccessReport _
   '  dbname:= _
   '    "C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb",
   '    rptname:="Sales by Category", preview:=True
   '----------------------------------------------------------------------
   Sub PrintAccessReport(dbname As String, rptname As String, _
     preview As Boolean)
     Dim objAccess As Object
     On Error GoTo PrintAccessReport_ErrHandler
     Set objAccess = CreateObject("Access.Application")
     With objAccess
       .OpenCurrentDatabase filepath:=dbname
       If preview Then 'Preview report on screen.
         .Visible = True
         .DoCmd.OpenReport reportname:=rptname, _
            view:=Access.acPreview
       Else 'Print report to printer.
         .DoCmd.OpenReport reportname:=rptname, _
            view:=Access.acNormal
          DoEvents 'Allow report to be sent to printer.
       End If
     End With
     Set objAccess = Nothing
     Exit Sub

   PrintAccessReport_ErrHandler:
     MsgBox Error$(), , "Print Access Report"
   End Sub

Calling a Report Wizard to Create a New Report

You can use the following sample procedure start the Report Wizard in Microsoft Access from an Automation controller:

   '----------------------------------------------------------------------
   'DECLARATIONS
   '----------------------------------------------------------------------

   Option Explicit
   Dim objAccess as Object

   '----------------------------------------------------------------------
   'This procedure starts the Report Wizard in Microsoft Access using a
   'specified database and table (or query) as the record source. This
   'procedure does not close the instance of Microsoft Access because
   'objAccess is a module-level variable. To call this procedure, use the
   'following syntax:
   '  CallReportWizard _
   '    dbname:= _
   '    "C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb",
   '    sourcetype:="table", sourcename:="Employees"
   '----------------------------------------------------------------------

   Sub CallReportWizard(dbname As String, sourcetype As String, _
     sourcename As String)
     Dim objtype As Integer
     On Error GoTo CallReportWizard_ErrHandler
     Set objAccess = CreateObject("Access.Application")
     With objAccess
       .Visible = True
       .OpenCurrentDatabase filepath:=dbname
       If LCase(sourcetype) = "table" Then
         objtype = Access.acTable
       Else
         objtype = Access.acQuery
       End If
       .DoCmd.SelectObject objecttype:=objtype, _
          objectname:=sourcename, inDatabaseWindow:=True

       'Although the following line of code works in Microsoft Access 97,
       'DoMenuItem exists only for backward compatibility. In Microsoft
       'Access 97, you should use the following RunCommand method instead:
       '.DoCmd.RunCommand (acCmdNewObjectReport)
       .DoCmd.DoMenuItem MenuBar:=1, MenuName:=3, Command:=3, _
          Version:=Access.acMenuVer70
          'Database menubar, Insert menu, Report command
     End With
     Exit Sub

   CallReportWizard_ErrHandler:
     If Err <> 2501 Then 'Error did not occur by canceling Report Wizard.
       MsgBox Error$(), , "Call Report Wizard"
     End If
   End Sub

REFERENCES

For more information about using Microsoft Access as an Automation server, search the Help Index for "Automation," or ask the Microsoft Access 97 Office Assistant.

For additional information, click the article numbers below to view the articles in the Microsoft Knowledge Base:

145707 ACC: How to Use Automation to Print Microsoft Access Reports

192919 HOWTO: Automate a Secured Access Database Using Visual Basic

Additional query words: runtime

Keywords: kbfaq kbinfo kbprogramming KB147816

Microsoft KB Archive/147816

Contents

ACC: Using Microsoft Access as an Automation Server

SUMMARY

MORE INFORMATION

Creating a Reference to Microsoft Access

Using GetObject() and CreateObject() Functions

Method 1

Method 2

Method 3

Understanding the UserControl and Visible Properties

UserControl Property

Visible Property

Viewing an Instance of Microsoft Access

Method 1

Method 2

Method 3

Closing an Instance of Microsoft Access

Bypassing Startup Settings When Opening a Database

Calling Microsoft Access Functions

Directly Calling a Microsoft Access Function

Indirectly Calling a Microsoft Access Function

Calling Custom Procedures

Using a Run-Time Application

Using a Secured Workgroup

Examples

Previewing or Printing a Report

Calling a Report Wizard to Create a New Report

REFERENCES