ID: Q147816
The information in this article applies to:
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.
This article discusses the following topics:
- Previewing or Printing a Report
- Calling a Report Wizard to Create a New Report
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:
1. Open a module in your Automation controller.
2. On the Tools menu, click References.
3. 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.
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.
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.
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")
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")
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")
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
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
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.
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.
List in Windows NT).
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.
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.
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.
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.
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.
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
However, if you use the GetObject() function instead of CreateObject()and set the Visible property, your code may fail in the following situations:
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
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
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
'----------------------------------------------------------------------
'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
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.
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"
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")
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
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 8.0 Object Library (or
Microsoft Access for Windows 95 in version 7.0), and then click OK.
5. In the Object Browser box, under Libraries/Databases, select "Access"
(or "Access - Microsoft Access for Windows 95" in version 7.0).
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.
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
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.
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
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
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"
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:
objAccess.Quit
'----------------------------------------------------------------------
'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
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
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.
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
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
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.
Additional query words: runtime
Keywords : kbole IntpOlea kbfaq
Version : 7.0 97
Platform : WINDOWS
Hardware : x86
Issue type : kbinfoLast Reviewed: November 22, 1998