Microsoft KB Archive/210111

= How to use Access 2000 as an Automation Server =

Article ID: 210111

Article Last Modified on 3/4/2005

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q210111





For a Microsoft Access 97 version of this article, see 147816.

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



SUMMARY
This article applies only to a Microsoft Access database (.mdb).

This article provides information and examples on how to use Microsoft Access 2000 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.



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
 * 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 Msacc9.olb. 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:


 * 1) Open a module in your Automation controller.
 * 2) On the Tools menu, click References.
 * 3) In the References box, select Microsoft Access 9.0 Object Library, 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 to 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
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:


 * 1) The user creates an instance of Microsoft Access, which sets the UserControl property to True.
 * 2) 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.
 * 3) The user restores Microsoft Access using the Windows taskbar (or Task List in Windows NT).
 * 4) 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:


 * 1) 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.
 * 2) 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 the CreateObject function 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:

<pre class="fixed_text">  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.

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:


 * 1) Open a module.
 * 2) On the Tools menu, click References.
 * 3) In the References box, select Microsoft Access 9.0 Object Library, and then click OK.
 * 4) On the View menu, click Object Browser.
 * 5) In the Object Browser box, under Libraries/Databases, select Access.
 * 6) Under Classes, click Application.

Note that the functions listed for the Application object appear in the Members box. 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 9.0 Object Library 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.</li> After your code starts a run-time instance of Microsoft Access, use the GetObject function to refer to the instance.</li> 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.</li>  Use the Quit method of the Application object if you want to close a run-time instance of Microsoft Access, for example: objAccess.Quit </li></ul>

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 'Database menubar, Insert menu, Report command .DoCmd.RunCommand (acCmdNewObjectReport) 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

<div class="references_section">