You are currently offline, waiting for your internet to reconnect

ACC: Using Microsoft Access as an Automation Server

This article was previously published under Q147816
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
SUMMARY
Advanced: Requires expert coding, interoperability, and multiuser skills.

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

This article assumes that you are familiar with Visual Basic forApplications and with creating Microsoft Access applications using theprogramming tools provided with Microsoft Access. For more informationabout 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 objectsfrom other applications using Visual Basic code. To make this possible,information about Microsoft Access objects is stored in a type librarycalled Msaccess.tlb. If you want optimal performance for your OLEAutomation code, you can create a reference to the Microsoft Access typelibrary.

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

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.
Creating a reference to Microsoft Access is not required to manipulate itsobjects using Automation. However, creating a reference does have thefollowing 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 activatean instance of Microsoft Access and control its functionality from anAutomation controller application. When you use these functions in a VisualBasic module, you activate Microsoft Access as an Automation object andassign the object to a variable. For example, here are several differentways to use the GetObject() and CreateObject() functions.

Method 1

You can use the GetObject() function to activate or open an instance ofMicrosoft 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 ofMicrosoft Access depending on the following conditions when the code isrun:

  • 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 MicrosoftAccess with the following syntax:
   Dim objAccess as Object   Set objAccess = GetObject(,"Access.Application")				
Note that the objAccess variable can refer to different instances ofMicrosoft Access depending on the following conditions when the code isrun:

  • 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 MicrosoftAccess with the following syntax:
   Dim objAccess as Object   Set objAccess = CreateObject("Access.Application")				
The objAccess variable refers to the Application object of the newinstance.

NOTE: Some Automation controllers, such as Microsoft Visual Basic 4.0,support the keyword "New" as an alternative method for opening a newinstance of an Automation server, for example:
   Dim objAccess as New Access.Application				
See your Automation controller documentation to determine if it supportsthe New keyword.

Understanding the UserControl and Visible Properties

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

When you open or activate an instance of Microsoft Access, the UserControland Visible properties are set automatically, depending on whetherMicrosoft Access is already running when an Application object is activatedin Visual Basic. For example, the following table illustrates thesesettings 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       FalseUserControl Property--------------------				

UserControl Property

The UserControl property is always read-only; therefore, you cannot set itusing Automation. However, the UserControl property can changeautomatically if a user intervenes while your Automation code is idle. Forexample, the UserControl property is changed to False when the followingevents 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 followingevents 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 controlthe on-screen behavior of Microsoft Access. Specifically, you should watchout 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. Itis read-only when the UserControl property is True or the instance ofMicrosoft Access is originally created by the user (and not Automationcode). The Visible property is read-write when the UserControl property isFalse. In rare cases, however, setting the Visible property to True mayfail to bring Microsoft Access into view if the instance's Visible propertyis 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 usingAutomation, depending on whether Microsoft Access is open at the time theApplication object is activated.

Method 1

When you create a new instance of Microsoft Access using Automation, theinstance is minimized because the Application object's Visible propertyis automatically set to False. To bring Microsoft Access into view, you canset 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 newinstance of the specified application. If you set the Visible propertyimmediately after running CreateObject(), then the user cannot intervene byrestoring or minimizing the application window, which resets theUserControl and Visible properties.

However, if you use the GetObject() function instead of CreateObject()andset 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 aninstance of Microsoft Access into view.

Method 2

If you want to view an instance of Microsoft Access regardless of userintervention, you can use the ShowAccess() procedure listed below. Thissample function makes three Windows API calls to control an instance ofMicrosoft Access, regardless of its current UserControl and Visibleproperties 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 MicrosoftAccess that is already running when your automation code runs, you can usethe ShowAccess() procedure (described above in Method 2) alongwith 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 theobject variable referring to the instance is set to Nothing or loses scopein the controller application. However, if any Microsoft Access objects areopen in the following views, then the instance does not close automaticallyas 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 untilthe objects are closed and the Application object's UserControl property isFalse. You can, however, force an instance to close by using the Quitmethod of an Application object. For example, the following sample codeuses 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 bydirectly calling a Microsoft Access function if your code makes additionalcalls to Microsoft Access functions. For more information about usingMicrosoft Access functions in your Automation code, please see the "CallingMicrosoft Access Functions" section later in this article.

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

For example, if objAccess is declared within a procedure, it is availablefor 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 standardmodule, then it is a module-level variable, which is available to allprocedures in that module. If objAccess is declared as a Public variable ina standard module, then it is available to all procedures in the database.In these two situations, objAccess does not lose scope when your Automationcode is idle. As a result, a connection remains open to Microsoft Accessuntil 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 differentmethods for opening a particular database. For example, you can use theGetObject() function and specify a database. Or, you can use theApplication object's OpenCurrentDatabase method to open an existingdatabase as the current database.

If a database has custom Startup settings or an AutoExec macro, thesefeatures are run when you use an OpenCurrentDatabase method. However, youcan use a SendKeys statement to simulate holding down the SHIFT key whenopening the database, which bypasses the Startup settings and AutoExecmacro. 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 thefollowing 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 functionssuch as Eval(), SysCmd(), or Dlookup() as long as the procedure is exposedto the Application object. To find out if a function is available, followthese 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.
  4. On the View menu, click Object Browser.
  5. In the Object Browser box, under Libraries/Databases, select "Access" (or "Access - Microsoft Access for Windows 95" in version 7.0).
  6. 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 cancall the function directly, using the Application object, or indirectly,using an object variable set to an instance of Microsoft Access. Bothtechniques are described below in more detail.

Directly Calling a Microsoft Access Function

To call a Microsoft Access function directly, you must first have areference to the Microsoft Access 8.0 Object Library (or Microsoft Accessfor Windows 95 in version 7.0) in your Automation controller. Formore information about creating a reference, please see the "Creating aReference 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 Accessfunction, a new, minimized instance of Microsoft Access is created. Thecontroller maintains a connection to this instance in case your code makesadditional calls to Microsoft Access functions. This connection remains ineffect until the controller application is closed.

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

Indirectly Calling a Microsoft Access Function

To call a Microsoft Access function indirectly, you do not need to create areference to the object library (unlike when you call one directly). Inyour Automation code, you can use an object variable set to an instance ofMicrosoft 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 procedurestored in a Microsoft Access database by using the Run method of theApplication object. The custom procedure must be declared as Public andlocated 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.mdbsample 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 Automationcontroller:
   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 usingAutomation, there are several code changes you may need to make especiallyif a full, retail version of Microsoft Access is not installed on theuser'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 usethe 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 securedworkgroup (System.mdw), you may want to bypass the logon box, which asksfor 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 theapplication:
   '----------------------------------------------------------------------   '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 MicrosoftAccess functionality from an Automation controller such as Microsoft Excel,Microsoft Project, or Microsoft Visual Basic. The two procedures performthe following tasks: previewing or printing a report; and calling a ReportWizard to create a new report.

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

Previewing or Printing a Report

You can use the following sample procedure for printing or previewing areport 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 inMicrosoft 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 Automationserver, search the Help Index for "Automation," or ask the MicrosoftAccess 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
runtime
Properties

Article ID: 147816 - Last Review: 01/19/2007 17:42:08 - Revision: 3.5

  • Microsoft Access 95 Standard Edition
  • Microsoft Access 97 Standard Edition
  • kbfaq kbinfo kbprogramming KB147816
Feedback