How To Automate Microsoft Access From Visual Basic .NET (317113)

SUMMARY

This article provides step-by-step samples that show you how to automate Microsoft Access from Visual Basic .NET. The topics and the sample code show you how to do the following:

• Open a database in Access
• Print or preview an Access report
• Show and edit an Access form
• Avoid dialog boxes when you open a password-protected database or when user-level security is turned on
• Automate the Access Runtime

back to the top

Automation vs. ADO.NET

A developer can work with a Microsoft Access database from Visual Basic .NET using two separate technologies: Automation and Microsoft ActiveX Data Objects (ADO.NET).

ADO.NET is the preferred technology if you want to work with data objects, such as tables and queries in an Access database. Use Automation only if you need Microsoft Access application-specific features, such as the ability to print or to preview an Access report, to display an Access form, or to call Access macros.

This article discusses how to automate Access. The article does not discuss ADO.NET. For information regarding ADO.NET, click the article numbers below to view the articles in the Microsoft Knowledge Base:
Automation is a COM technology. Automation allows applications that are written in languages such as Visual Basic .NET to programmatically control other applications. When you automate a Microsoft Office application, you actually run an instance of that application in memory, and then call upon the application's object model to perform various tasks in that application. With Access and other Microsoft Office applications, virtually all of the actions that you can perform manually through the user interface can also be performed programmatically by using Automation.

Access exposes this programmatic functionality through an object model. The object model is a collection of classes and methods that serve as counterparts to the logical components of Access. To access the object model from Visual Basic .NET, you can set a project reference to the type library, which is described in the step-by-step sample in this article. To learn more about this process, or to learn more about object model documentation for Office, click the article number below to view the article in the Microsoft Knowledge Base:
back to the top

Common Automation Tasks

Open a Database in Access

When you automate Microsoft Access, you must open a database before you can perform useful tasks, such as printing reports. To open a database in the instance of Access that you are automating, you use the OpenCurrentDatabase or OpenAccessProject methods of the Application object. You can have only one database opened in Access at a time. To work with a different database, you can use the CloseCurrentDatabase method before opening another.

You may also use the GetObject(<path to database>) function to open a database in an instance of Access. If the database is already open in an instance of Access, GetObject returns the Application object of that instance. Otherwise, GetObject opens the database in a running instance of Access. If no instances of Access are running, GetObject starts a new instance of Access and opens the specified database.

If you use the GetObject function to open a database, you cannot control the instance of Access in which the database is opened. OpenCurrentDatabase is the preferred method to open a database because you specify the instance of Access that you want to automate. You can also provide optional arguments to control how the database is opened, for example:

Dim oAccess As Access.Application

' Start a new instance of Access for Automation:
oAccess = New Access.ApplicationClass()

' Open a database in exclusive mode:
oAccess.OpenCurrentDatabase(filepath:="c:\mydb.mdb", Exclusive:=True)
				

back to the top

Print or Preview an Access Report

To preview or to print an Access report, you call the OpenReport method of the DoCmd object. When you call OpenReport, one of the arguments that you pass determines whether the report is previewed on the screen, or whether it is sent to the printer:

' Preview a report named Sales:
oAccess.DoCmd.OpenReport(ReportName:="Sales", _
   View:=Access.AcView.acViewPreview)
' Print a report named Sales:
oAccess.DoCmd.OpenReport(ReportName:="Sales", _
   View:=Access.AcView.acViewNormal)
				

Notice that the View argument determines whether the report is displayed in Access or whether it is sent to the printer. The OpenReport method has other useful arguments, such as WhereCondition. WhereCondition allows you to limit the report's recordset using a valid SQL WHERE clause (without the word WHERE.)

If you are previewing a report, be sure to set the Visible property of the Application object so that Access is visible on the screen. In this way, the user can view the report in the Access window.

There is another way to print a report or other objects in the database. Use the PrintOut method of the DoCmd object. In this example, you select a report named Employees in the Database window, and then you call PrintOut to print the selected object. The PrintOut method allows you to provide arguments that correspond to the Print dialog box in Access, including PrintRange, PageFrom, PageTo, PrintQuality, Copies, and CollateCopies:

' Select the Employees report in the Database window: 
oAccess.DoCmd.SelectObject(ObjectType:=Access.AcObjectType.acReport, _
    ObjectName:="Employees", InDatabaseWindow:=True)
' Print 2 copies of the selected object: 
oAccess.DoCmd.PrintOut(PrintRange:=Access.AcPrintRange.acPrintAll, _
    Copies:=2, CollateCopies:=False)
				

Or, in some cases, you may want to use both the OpenReport and the PrintOut methods to print a report. Suppose you want to print multiple copies of the Employees report but only for a specific employee. This example first uses OpenReport to open the Employees report in preview mode, using the WhereCondition argument to limit the records to a specific employee. Then, PrintOut is used to print multiple copies of the active object:

' Open the report in preview mode using a WhereCondition: 
oAccess.DoCmd.OpenReport(ReportName:="Employees", _
    View:=Access.AcView.acViewPreview, _
    WhereCondition:="[EmployeeID]=1")
' Print 2 copies of the active object: 
oAccess.DoCmd.PrintOut(PrintRange:=Access.AcPrintRange.acPrintAll, _
    Copies:=2, CollateCopies:=False)
' Close the report preview window: 
oAccess.DoCmd.Close(ObjectType:=Access.AcObjectType.acReport, _
    ObjectName:="Employees")
				

Access 2002 introduces the Printer object. You can use this object to customize Access printer settings more easily than in earlier versions of Access. For an example of using the Printer object in Access to print a report, click the article number below to view the article in the Microsoft Knowledge Base: back to the top

Show and Edit an Access Form

Visual Basic .NET has very powerful form capabilities. However, there may be times when you want the user to view a form that was previously developed in Access. Or, you may have a form in your Access database that provides criteria for a query or for a report, and you must open that form before you can preview or print the report. To open and show an Access form, you call the OpenForm method of the DoCmd object:

' Show a form named Employees:
oAccess.DoCmd.OpenForm(FormName:="Employees", _
   View:=Access.AcFormView.acNormal)
				

You can now edit the controls on the form.

back to the top

Access Security Dialogs

When you automate Access, you may be prompted to enter a user name or password, or both, when you try to open a database. If the user enters the wrong information, an error will occur in your code. There may be times when you want to avoid these dialog boxes and instead to programmatically provide the user name and password so that your Automation code runs uninterrupted.

There are two types of security in Microsoft Access: password-protected databases and user-level security through a workgroup file (System.mdw). If you are trying to open a database that is password protected, you receive a dialog box prompting you for the database password. User-level security is different from a password-protected database. When user-level security is activated, Access displays a logon dialog that prompts for both a user name and a password before the user can open any database in Access. For more information about Access security and the workgroup information file, click the article number below to view the article in the Microsoft Knowledge Base:

back to the top

Avoiding Database Password Dialog Boxes

If you are opening a database that has been protected with a password, you can avoid the dialog box by providing the password to the OpenCurrentDatabase method:

' Open a password-protected database in shared mode:
' Note: The bstrPassword argument is case-sensitive
oAccess.OpenCurrentDatabase(filepath:="c:\mydb.mdb", _
   Exclusive:=False, bstrPassword:="MyPassword")
				

The bstrPassword argument was added to the OpenCurrentDatabase method with the release of Access 2002. It is not available in Access 2000. To avoid the password dialog box when you automate Access 2000 to open a password-protected database, you must first use the OpenDatabase method of the DBEngine object and provide the database password using the Connect argument. Then you use OpenCurrentDatabase to open the database in Access.

In the following example, oAccess has been previously set to an instance of Access and sDBPath is the path of the password-protected database that you want to open. This code provides the password to the database so that a dialog box is avoided:

Dim sDBPassword as String        
Dim oDBEngine As DAO.DBEngine
Dim oDB As DAO.Database
        
sDBPassword = "Mypassword" 'database password 
oDBEngine = oAccess.DBEngine
oDB = oDBEngine.OpenDatabase(Name:=sDBPath, _
   Options:=False, _
   ReadOnly:=False, _
   Connect:=";PWD=" & sDBPassword)
oAccess.OpenCurrentDatabase(filepath:=sDBPath, _
   Exclusive:=False)
oDB.Close()
System.Runtime.InteropServices.Marshal.ReleaseComObject(oDB)
oDB = Nothing
System.Runtime.InteropServices.Marshal.ReleaseComObject(oDBEngine)
oDBEngine = Nothing
				

The oDB.Close does not actually close the database in Access. It only closes the DAO connection to the database that was made through the DBEngine object. The DAO connection is no longer necessary after the OpenCurrentDatabase method is used. Notice the code to release the oDB and oDBEngine objects. You must use these objects so that Access quits correctly after the code is completed.

For more information, click the article number below to view the article in the Microsoft Knowledge Base:
back to the top

Avoiding Access Security Logon Dialog

If user-level security is turned on in Access, the user is prompted with a logon dialog box that prompts for both a user name and a password. A user name and a password cannot be specified using the Access object model. Therefore, if you want to avoid the logon dialog box when you automate Access, you must first start the Msaccess.exe file and provide the /user and /pwd command-line switches to specify the user name and password. Afterward, you can use GetObject to retrieve the Application object of the running instance of Access so that you can then proceed with Automation. For an example of how to do this, click the article number below to view the article in the Microsoft Knowledge Base: For more information about starting Access with command-line switches, click the article number below to view the article in the Microsoft Knowledge Base: back to the top

Automating Access Runtime

The Microsoft Office Developer Edition includes the Microsoft Office Developer Tools (MOD). Using MOD, Access developers can create and distribute Access applications to users who do not have the retail version of Access. When the user installs the Access application on a computer that does not have the retail version of Access, a Runtime version of Access is installed. The Access Runtime is installed and is registered like the retail version. The executable is also called Msaccess.exe. The Access Runtime allows an Access application to run on a client computer, but the Access Runtime does not permit a user to develop new applications or modify the design of existing applications.

The Access Runtime must be started with a database. Because of this requirement, if you want to automate the Access Runtime, you must start the Msaccess.exe and specify a database to open. After you use GetObject to retrieve the Application object, you can proceed with automating the Access Runtime. If you try to automate the Access Runtime with the New keyword or with CreateObject, you will receive an error message such as the following when you try to instantiate the instance: For more information, click the article number below to view the article in the Microsoft Knowledge Base: back to the top

Create the Complete Sample Visual Basic .NET Project

To use the following step-by-step sample, make sure the Northwind sample database is installed.

1. Close any instances of Access that are currently running.
2. Start Microsoft Visual Studio .NET. On the File menu, click New and then click Project. Select Windows Application from the Visual Basic Projects types. By default, Form1 is created.
3. Add a reference to the Microsoft Access Object Library. To do this, follow these steps:
   1. On the Project menu, click Add Reference.
   2. On the COM tab, locate Microsoft Access 10.0 Object Library, and then click Select.NOTE: If you have not already done so, it is recommended that you download and install the Microsoft Office XP Primary Interop Assemblies (PIAs). For additional information about Office XP PIAs, click the article number below to view the article in the Microsoft Knowledge Base:

      328912 INFO: Microsoft Office XP PIAs Are Available for Download

   3. Click OK in the Add References dialog box to accept your selections. If you receive a prompt to generate wrappers for the libraries that you selected, click Yes.
      
      NOTE: If you are referencing the Access 10.0 Object Library and you receive errors when you try to add references, click the article number below to view the article in the Microsoft Knowledge Base:

      317157 PRB: Errors When Referencing the Access 10.0 Type Library

4. On the View menu, select Toolbox to display the toolbox.
5. Add five radio button controls and a button control to Form1.
6. Select all of the radio button controls, and then set the Size property to 150,24.
7. On the View menu, click Code.
8. Insert the following code into the Form class:

   Private m_sAction As String
   
   Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) _
       Handles MyBase.Load
       RadioButton1.Text = "Print report"
       RadioButton2.Text = "Preview report" 
       RadioButton3.Text = "Show form" 
       RadioButton4.Text = "Print report (Security)" 
       RadioButton5.Text = "Preview report (Runtime)" 
       Button1.Text = "Go!"
   End Sub
   
   Private Sub RadioButtons_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
       Handles RadioButton1.Click, RadioButton2.Click, RadioButton3.Click, RadioButton4.Click, RadioButton5.Click
       m_sAction = sender.Text 'Store the text for the selected radio button
   End Sub
   
   Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
       Handles Button1.Click
       ' Calls the associated procedure to automate Access, based
       ' on the selected radio button on the form.
       Select Case m_sAction
           Case "Print report" : Print_Report()
           Case "Preview report" : Preview_Report()
           Case "Show form" : Show_Form()
           Case "Print report (Security)" : Print_Report_Security()
           Case "Preview report (Runtime)" : Preview_Report_Runtime()
       End Select
   End Sub
   
   Private Function ShellGetDB(ByVal sDBPath As String, _
       Optional ByVal sCmdLine As String = vbNullString, _
       Optional ByVal enuWindowStyle As Microsoft.VisualBasic.AppWinStyle _
           = AppWinStyle.MinimizedFocus, _
       Optional ByVal iSleepTime As Integer = 1000) As Access.Application
   
       'Launches a new instance of Access with a database (sDBPath)
       'using the Shell function then returns the Application object
       'via calling: GetObject(sDBPath). Returns the Application
       'object of the new instance of Access, assuming that sDBPath
       'is not already opened in another instance of Access. To ensure
       'the Application object of the new instance is returned, make
       'sure sDBPath is not already opened in another instance of Access.
       '
       'Example:
       'Dim oAccess As Access.Application
       'oAccess = ShellGetDB("c:\mydb.mdb")
   
       ' Enable an error handler for this procedure:
       On Error GoTo ErrorHandler
   
       Dim oAccess As Access.Application
       Dim sAccPath As String 'path to msaccess.exe
   
       ' Obtain the path to msaccess.exe:
       sAccPath = GetOfficeAppPath("Access.Application", "msaccess.exe")
       If sAccPath = "" Then
           MsgBox("Can't determine path to msaccess.exe", _
               MsgBoxStyle.MsgBoxSetForeground)
           Return Nothing
       End If
   
       ' Make sure specified database (sDBPath) exists:
       If Not System.IO.File.Exists(sDBPath) Then
           MsgBox("Can't find the file '" & sDBPath & "'", _
               MsgBoxStyle.MsgBoxSetForeground)
           Return Nothing
       End If
   
       ' Start a new instance of Access using sDBPath and sCmdLine:
       If sCmdLine = vbNullString Then
           sCmdLine = Chr(34) & sDBPath & Chr(34)
       Else
           sCmdLine = Chr(34) & sDBPath & Chr(34) & " " & sCmdLine
       End If
       Shell(Pathname:=sAccPath & " " & sCmdLine, _
           Style:=enuWindowStyle)
       'Note: It is advised that the Style argument of the Shell
       'function be used to give focus to Access.
   
       ' Move focus back to this form. This ensures that Access
       ' registers itself in the ROT, allowing GetObject to find it:
       AppActivate(Title:=Me.Text)
   
       ' Pause to allow database to open:
       System.Threading.Thread.Sleep(iSleepTime)
   
       ' Obtain Application object of the instance of Access
       ' that has the database open:
       oAccess = GetObject(sDBPath)
   
       Return oAccess
   ErrorCleanup:
       ' Try to quit Access due to an unexpected error:
       On Error Resume Next
       oAccess.Quit(Option:=Access.AcQuitOption.acQuitSaveNone)
       System.Runtime.InteropServices.Marshal.ReleaseComObject(oAccess)
       oAccess = Nothing
   
       Return Nothing
   ErrorHandler:
       MsgBox(Err.Number & ": " & Err.Description, _
           MsgBoxStyle.MsgBoxSetForeground, "Error Handler")
       Resume ErrorCleanup
   End Function
   
   Private Function ShellGetApp(Optional ByVal sCmdLine As String = vbNullString, _
       Optional ByVal enuWindowStyle As Microsoft.VisualBasic.AppWinStyle _
           = AppWinStyle.MinimizedFocus) As Access.Application
   
       'Launches a new instance of Access using the Shell function
       'then returns the Application object via calling:
       'GetObject(,"Access.Application"). If an instance of
       'Access is already running before calling this procedure,
       'the function may return the Application object of a
       'previously running instance of Access. If this is not
       'desired, then make sure Access is not running before
       'calling this function, or use the ShellGetDB()
       'function instead. Approach based on Q308409.
       '
       'Examples:
       'Dim oAccess As Access.Application
       'oAccess = ShellGetApp()
       '
       '-or-
       '
       'Dim oAccess As Access.Application
       'Dim sUser As String
       'Dim sPwd As String
       'sUser = "Admin"
       'sPwd = ""
       'oAccess = ShellGetApp("/user " & sUser & "/pwd " & sPwd)
   
       ' Enable an error handler for this procedure:
       On Error GoTo ErrorHandler
   
       Dim oAccess As Access.Application
       Dim sAccPath As String 'path to msaccess.exe
       Dim iSection As Integer = 0
       Dim iTries As Integer = 0
   
       ' Obtain the path to msaccess.exe:
       sAccPath = GetOfficeAppPath("Access.Application", "msaccess.exe")
       If sAccPath = "" Then
           MsgBox("Can't determine path to msaccess.exe", _
               MsgBoxStyle.MsgBoxSetForeground)
           Return Nothing
       End If
   
       ' Start a new instance of Access using sCmdLine:
       If sCmdLine = vbNullString Then
           sCmdLine = sAccPath
       Else
           sCmdLine = sAccPath & " " & sCmdLine
       End If
       Shell(Pathname:=sCmdLine, Style:=enuWindowStyle)
       'Note: It is advised that the Style argument of the Shell
       'function be used to give focus to Access.
   
       ' Move focus back to this form. This ensures that Access
       ' registers itself in the ROT, allowing GetObject to find it:
       AppActivate(Title:=Me.Text)
   
       ' Attempt to use GetObject to reference a running
       ' instance of Access:
       iSection = 1 'attempting GetObject...
       oAccess = GetObject(, "Access.Application")
       iSection = 0 'resume normal error handling
   
       Return oAccess
   ErrorCleanup:
       ' Try to quit Access due to an unexpected error:
       On Error Resume Next
       oAccess.Quit(Option:=Access.AcQuitOption.acQuitSaveNone)
       System.Runtime.InteropServices.Marshal.ReleaseComObject(oAccess)
       oAccess = Nothing
   
       Return Nothing
   ErrorHandler:
       If iSection = 1 Then 'GetObject may have failed because the
           'Shell function is asynchronous; enough time has not elapsed
           'for GetObject to find the running Office application. Wait
           '1/2 seconds and retry the GetObject. If you try 20 times
           'and GetObject still fails, assume some other reason
           'for GetObject failing and exit the procedure.
           iTries = iTries + 1
           If iTries < 20 Then
               System.Threading.Thread.Sleep(500) 'wait 1/2 seconds
               AppActivate(Title:=Me.Text)
               Resume 'resume code at the GetObject line
           Else
               MsgBox("GetObject failed. Process ended.", _
                   MsgBoxStyle.MsgBoxSetForeground)
           End If
       Else 'iSection = 0 so use normal error handling:
           MsgBox(Err.Number & ": " & Err.Description, _
               MsgBoxStyle.MsgBoxSetForeground, "Error Handler")
       End If
       Resume ErrorCleanup
   End Function
   
   Private Function GetOfficeAppPath(ByVal sProgId As String, ByVal sEXE As String) As String
       'Returns path of the Office application. e.g.
       'GetOfficeAppPath("Access.Application", "msaccess.exe") returns
       'full path to Microsoft Access. Approach based on Q240794.
       'Returns empty string if path not found in registry.
   
       ' Enable an error handler for this procedure:
       On Error GoTo ErrorHandler
   
       Dim oReg As Microsoft.Win32.RegistryKey = _
           Microsoft.Win32.Registry.LocalMachine
       Dim oKey As Microsoft.Win32.RegistryKey
       Dim sCLSID As String
       Dim sPath As String
       Dim iPos As Integer
   
       ' First, get the clsid from the progid from the registry key
       ' HKEY_LOCAL_MACHINE\Software\Classes\<PROGID>\CLSID:
       oKey = oReg.OpenSubKey("Software\Classes\" & sProgId & "\CLSID")
   
       sCLSID = oKey.GetValue("")
       oKey.Close()
   
       ' Now that we have the CLSID, locate the server path at
       ' HKEY_LOCAL_MACHINE\Software\Classes\CLSID\ 
       ' {xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxx}\LocalServer32:
       oKey = oReg.OpenSubKey("Software\Classes\CLSID\" & sCLSID & "\LocalServer32")
       sPath = oKey.GetValue("")
       oKey.Close()
   
       ' Remove any characters beyond the exe name:
       iPos = InStr(1, sPath, sEXE, CompareMethod.Text)
       sPath = Microsoft.VisualBasic.Left(sPath, iPos + Len(sEXE) - 1)
       Return Trim(sPath)
   ErrorHandler:
       Return ""
   End Function
   
   Private Sub Print_Report()
       'Prints the "Summary of Sales by Year" report in Northwind.mdb.
   
       ' Enable an error handler for this procedure:
       On Error GoTo ErrorHandler
   
       Dim oAccess As Access.Application
       Dim sDBPath As String 'path to Northwind.mdb
       Dim sReport As String 'name of report to print
   
       sReport = "Summary of Sales by Year"
   
       ' Start a new instance of Access for Automation:
       oAccess = New Access.ApplicationClass()
   
       ' Determine the path to Northwind.mdb:
       sDBPath = oAccess.SysCmd(Action:=Access.AcSysCmdAction.acSysCmdAccessDir)
       sDBPath = sDBPath & "Samples\Northwind.mdb"
   
       ' Open Northwind.mdb in shared mode:
       oAccess.OpenCurrentDatabase(filepath:=sDBPath, Exclusive:=False)
   
       ' Select the report name in the database window and give focus
       ' to the database window:
       oAccess.DoCmd.SelectObject(ObjectType:=Access.AcObjectType.acReport, _
           ObjectName:=sReport, InDatabaseWindow:=True)
   
       ' Print the report:
       oAccess.DoCmd.OpenReport(ReportName:=sReport, _
           View:=Access.AcView.acViewNormal)
   
   Cleanup:
       ' Quit Access and release object:
       On Error Resume Next
       oAccess.Quit(Option:=Access.AcQuitOption.acQuitSaveNone)
       System.Runtime.InteropServices.Marshal.ReleaseComObject(oAccess)
       oAccess = Nothing
   
       Exit Sub
   ErrorHandler:
       MsgBox(Err.Number & ": " & Err.Description, _
           MsgBoxStyle.MsgBoxSetForeground, "Error Handler")
       ' Try to quit Access due to an unexpected error:
       Resume Cleanup
   End Sub
   
   Private Sub Preview_Report()
       'Previews the "Summary of Sales by Year" report in Northwind.mdb.
   
       ' Enable an error handler for this procedure:
       On Error GoTo ErrorHandler
   
       Dim oAccess As Access.Application
       Dim oForm As Access.Form
       Dim sDBPath As String 'path to Northwind.mdb
       Dim sReport As String 'name of report to preview
   
       sReport = "Summary of Sales by Year"
   
       ' Start a new instance of Access for Automation:
       oAccess = New Access.ApplicationClass()
   
       ' Make sure Access is visible:
       If Not oAccess.Visible Then oAccess.Visible = True
   
       ' Determine the path to Northwind.mdb:
       sDBPath = oAccess.SysCmd(Action:=Access.AcSysCmdAction.acSysCmdAccessDir)
       sDBPath = sDBPath & "Samples\Northwind.mdb"
   
       ' Open Northwind.mdb in shared mode:
       oAccess.OpenCurrentDatabase(filepath:=sDBPath, Exclusive:=False)
   
       ' Close any forms that Northwind may have opened:
       For Each oForm In oAccess.Forms
           oAccess.DoCmd.Close(ObjectType:=Access.AcObjectType.acForm, _
               ObjectName:=oForm.Name, _
               Save:=Access.AcCloseSave.acSaveNo)
       Next
       If Not oForm Is Nothing Then
           System.Runtime.InteropServices.Marshal.ReleaseComObject(oForm)
       End If
       oForm = Nothing
   
       ' Select the report name in the database window and give focus
       ' to the database window:
       oAccess.DoCmd.SelectObject(ObjectType:=Access.AcObjectType.acReport, _
           ObjectName:=sReport, InDatabaseWindow:=True)
   
       ' Maximize the Access window:
       oAccess.RunCommand(Command:=Access.AcCommand.acCmdAppMaximize)
   
       ' Preview the report:
       oAccess.DoCmd.OpenReport(ReportName:=sReport, _
           View:=Access.AcView.acViewPreview)
   
       ' Maximize the report window:
       oAccess.DoCmd.Maximize()
   
       ' Hide Access menu bar:
       oAccess.CommandBars("Menu Bar").Enabled = False
   
       ' Hide Report's Print Preview menu bar:
       oAccess.CommandBars("Print Preview").Enabled = False
   
       ' Hide Report's right-click popup menu:
       oAccess.CommandBars("Print Preview Popup").Enabled = False
   
       ' Release Application object and allow Access to be closed by user:
       If Not oAccess.UserControl Then oAccess.UserControl = True
       System.Runtime.InteropServices.Marshal.ReleaseComObject(oAccess)
       oAccess = Nothing
   
       Exit Sub
   ErrorCleanup:
       ' Try to quit Access due to an unexpected error:
       On Error Resume Next
       System.Runtime.InteropServices.Marshal.ReleaseComObject(oForm)
       oForm = Nothing
       oAccess.Quit(Option:=Access.AcQuitOption.acQuitSaveNone)
       System.Runtime.InteropServices.Marshal.ReleaseComObject(oAccess)
       oAccess = Nothing
   
       Exit Sub
   ErrorHandler:
       MsgBox(Err.Number & ": " & Err.Description, _
           MsgBoxStyle.MsgBoxSetForeground, "Error Handler")
       Resume ErrorCleanup
   End Sub
   
   Private Sub Show_Form()
       'Shows the "Customer Labels Dialog" form in Northwind.mdb
       'and manipulates controls on the form.
   
       ' Enable an error handler for this procedure:
       On Error GoTo ErrorHandler
   
       Dim oAccess As Access.Application
       Dim oForm As Access.Form
       Dim oCtls As Access.Controls
       Dim oCtl As Access.Control
       Dim sDBPath As String 'path to Northwind.mdb
       Dim sForm As String 'name of form to show
   
       sForm = "Customer Labels Dialog"
   
       ' Start a new instance of Access for Automation:
       oAccess = New Access.ApplicationClass()
   
       ' Make sure Access is visible:
       If Not oAccess.Visible Then oAccess.Visible = True
   
       ' Determine the path to Northwind.mdb:
       sDBPath = oAccess.SysCmd(Action:=Access.AcSysCmdAction.acSysCmdAccessDir)
       sDBPath = sDBPath & "Samples\Northwind.mdb"
   
       ' Open Northwind.mdb in shared mode:
       oAccess.OpenCurrentDatabase(filepath:=sDBPath, Exclusive:=False)
   
       ' Close any forms that Northwind may have opened:
       For Each oForm In oAccess.Forms
           oAccess.DoCmd.Close(ObjectType:=Access.AcObjectType.acForm, _
               ObjectName:=oForm.Name, _
               Save:=Access.AcCloseSave.acSaveNo)
       Next
       If Not oForm Is Nothing Then
           System.Runtime.InteropServices.Marshal.ReleaseComObject(oForm)
       End If
       oForm = Nothing
   
       ' Select the form name in the database window and give focus
       ' to the database window:
       oAccess.DoCmd.SelectObject(ObjectType:=Access.AcObjectType.acForm, _
           ObjectName:=sForm, InDatabaseWindow:=True)
   
       ' Show the form:
       oAccess.DoCmd.OpenForm(FormName:=sForm, _
           View:=Access.AcFormView.acNormal)
   
       ' Use Controls collection to edit the form:
       oForm = oAccess.Forms(sForm)
       oCtls = oForm.Controls
   
       ' Set PrintLabelsFor option group to Specific Country:
       oCtl = oCtls.Item("PrintLabelsFor")
       oCtl.Value = 2 'second option in option group
       System.Runtime.InteropServices.Marshal.ReleaseComObject(oCtl)
       oCtl = Nothing
   
       ' Put USA in the SelectCountry combo box:
       oCtl = oCtls.Item("SelectCountry")
       oCtl.Enabled = True
       oCtl.SetFocus()
       oCtl.Value = "USA"
       System.Runtime.InteropServices.Marshal.ReleaseComObject(oCtl)
       oCtl = Nothing
   
       ' Hide the Database Window:
       oAccess.DoCmd.SelectObject(ObjectType:=Access.AcObjectType.acForm, _
           ObjectName:=sForm, InDatabaseWindow:=True)
       oAccess.RunCommand(Command:=Access.AcCommand.acCmdWindowHide)
   
       ' Set focus back to the form:
       oForm.SetFocus()
   
       ' Release Controls and Form objects:
       System.Runtime.InteropServices.Marshal.ReleaseComObject(oCtls)
       oCtls = Nothing
       System.Runtime.InteropServices.Marshal.ReleaseComObject(oForm)
       oForm = Nothing
   
       ' Release Application object and allow Access to be closed by user:
       If Not oAccess.UserControl Then oAccess.UserControl = True
       System.Runtime.InteropServices.Marshal.ReleaseComObject(oAccess)
       oAccess = Nothing
   
       Exit Sub
   ErrorCleanup:
       ' Try to quit Access due to an unexpected error:
       On Error Resume Next
       System.Runtime.InteropServices.Marshal.ReleaseComObject(oCtl)
       oCtl = Nothing
       System.Runtime.InteropServices.Marshal.ReleaseComObject(oCtls)
       oCtls = Nothing
       System.Runtime.InteropServices.Marshal.ReleaseComObject(oForm)
       oForm = Nothing
       oAccess.Quit(Option:=Access.AcQuitOption.acQuitSaveNone)
       System.Runtime.InteropServices.Marshal.ReleaseComObject(oAccess)
       oAccess = Nothing
   
       Exit Sub
   ErrorHandler:
       MsgBox(Err.Number & ": " & Err.Description, _
           MsgBoxStyle.MsgBoxSetForeground, "Error Handler")
       Resume ErrorCleanup
   End Sub
   
   Private Sub Print_Report_Security()
       'Shows how to automate Access when user-level
       'security is enabled and you wish to avoid the Logon
       'dialog asking for user name and password. In this 
       'example we're assuming default security so we simply
       'pass the Admin user with a blank password to print the 
       '"Summary of Sales by Year" report in Northwind.mdb.
   
       ' Enable an error handler for this procedure:
       On Error GoTo ErrorHandler
   
       Dim oAccess As Access.Application
       Dim sDBPath As String 'path to Northwind.mdb
       Dim sUser As String 'user name for Access security
       Dim sPwd As String 'user password for Access security
       Dim sReport As String 'name of report to print
   
       sReport = "Summary of Sales by Year"
   
       ' Determine the path to Northwind.mdb:
       sDBPath = GetOfficeAppPath("Access.Application", "msaccess.exe")
       If sDBPath = "" Then
           MsgBox("Can't determine path to msaccess.exe", _
               MsgBoxStyle.MsgBoxSetForeground)
           Exit Sub
       End If
       sDBPath = Microsoft.VisualBasic.Left(sDBPath, _
           Len(sDBPath) - Len("msaccess.exe")) & "Samples\Northwind.mdb"
       If Not System.IO.File.Exists(sDBPath) Then
           MsgBox("Can't find the file '" & sDBPath & "'", _
               MsgBoxStyle.MsgBoxSetForeground)
           Exit Sub
       End If
   
       ' Specify the user name and password for the Access workgroup
       ' information file, which is used to implement Access user-level security.
       ' The file by default is named System.mdw and can be specified
       ' using the /wrkgrp command-line switch. This example assumes
       ' default security and therefore does not specify a workgroup
       ' information file and uses Admin with no password:
       sUser = "Admin"
       sPwd = ""
   
       ' Start a new instance of Access with user name and password:
       oAccess = ShellGetDB(sDBPath, "/user " & sUser & " /pwd " & sPwd)
       'or
       'oAccess = ShellGetApp(Chr(34) & sDBPath & Chr(34) & " /user " & sUser & " /pwd " & sPwd)
   
       ' Select the report name in the database window and give focus
       ' to the database window:
       oAccess.DoCmd.SelectObject(ObjectType:=Access.AcObjectType.acReport, _
           ObjectName:=sReport, InDatabaseWindow:=True)
   
       ' Print the report:
       oAccess.DoCmd.OpenReport(ReportName:=sReport, _
           View:=Access.AcView.acViewNormal)
   
   Cleanup:
       ' Quit Access and release object:
       On Error Resume Next
       oAccess.Quit(Option:=Access.AcQuitOption.acQuitSaveNone)
       System.Runtime.InteropServices.Marshal.ReleaseComObject(oAccess)
       oAccess = Nothing
   
       Exit Sub
   ErrorHandler:
       MsgBox(Err.Number & ": " & Err.Description, _
           MsgBoxStyle.MsgBoxSetForeground, "Error Handler")
       ' Try to quit Access due to an unexpected error:
       Resume Cleanup
   End Sub
   
   Private Sub Preview_Report_Runtime()
       'Shows how to automate the Access Runtime to preview
       'the "Summary of Sales by Year" report in Northwind.mdb.
   
       ' Enable an error handler for this procedure:
       On Error GoTo ErrorHandler
   
       Dim oAccess As Access.Application
       Dim oForm As Access.Form
       Dim sDBPath As String 'path to Northwind.mdb
       Dim sReport As String 'name of report to preview
   
       sReport = "Summary of Sales by Year"
   
       ' Determine the path to Northwind.mdb:
       sDBPath = GetOfficeAppPath("Access.Application", "msaccess.exe")
       If sDBPath = "" Then
           MsgBox("Can't determine path to msaccess.exe", _
               MsgBoxStyle.MsgBoxSetForeground)
           Exit Sub
       End If
       sDBPath = Microsoft.VisualBasic.Left(sDBPath, _
           Len(sDBPath) - Len("msaccess.exe")) & "Samples\Northwind.mdb"
       If Not System.IO.File.Exists(sDBPath) Then
           MsgBox("Can't find the file '" & sDBPath & "'", _
               MsgBoxStyle.MsgBoxSetForeground)
           Exit Sub
       End If
   
       ' Start a new instance of Access. If the retail
       ' version of Access is not installed, and only the
       ' Access Runtime is installed, launches a new instance
       ' of the Access Runtime (/runtime switch is optional):
       oAccess = ShellGetDB(sDBPath, "/runtime")
       'or
       'oAccess = ShellGetApp(Chr(34) & sDBPath & Chr(34) & " /runtime")
   
       ' Make sure Access is visible:
       If Not oAccess.Visible Then oAccess.Visible = True
   
       ' Close any forms that Northwind may have opened:
       For Each oForm In oAccess.Forms
           oAccess.DoCmd.Close(ObjectType:=Access.AcObjectType.acForm, _
               ObjectName:=oForm.Name, _
               Save:=Access.AcCloseSave.acSaveNo)
       Next
       If Not oForm Is Nothing Then
           System.Runtime.InteropServices.Marshal.ReleaseComObject(oForm)
       End If
       oForm = Nothing
   
       ' Select the report name in the database window and give focus
       ' to the database window:
       oAccess.DoCmd.SelectObject(ObjectType:=Access.AcObjectType.acReport, _
           ObjectName:=sReport, InDatabaseWindow:=True)
   
       ' Maximize the Access window:
       oAccess.RunCommand(Command:=Access.AcCommand.acCmdAppMaximize)
   
       ' Preview the report:
       oAccess.DoCmd.OpenReport(ReportName:=sReport, _
           View:=Access.AcView.acViewPreview)
   
       ' Maximize the report window:
       oAccess.DoCmd.Maximize()
   
       ' Hide Access menu bar:
       oAccess.CommandBars("Menu Bar").Enabled = False
   
       ' Release Application object and allow Access to be closed by user:
       If Not oAccess.UserControl Then oAccess.UserControl = True
       System.Runtime.InteropServices.Marshal.ReleaseComObject(oAccess)
       oAccess = Nothing
   
       Exit Sub
   ErrorCleanup:
       ' Try to quit Access due to an unexpected error:
       On Error Resume Next
       System.Runtime.InteropServices.Marshal.ReleaseComObject(oForm)
       oForm = Nothing
       oAccess.Quit(Option:=Access.AcQuitOption.acQuitSaveNone)
       System.Runtime.InteropServices.Marshal.ReleaseComObject(oAccess)
       oAccess = Nothing
   
       Exit Sub
   ErrorHandler:
       MsgBox(Err.Number & ": " & Err.Description, _
           MsgBoxStyle.MsgBoxSetForeground, "Error Handler")
       Resume ErrorCleanup
   End Sub
   					

9. Add the following code to the top of Form1.vb:

   Imports Microsoft.Office.Interop
   					

10. Press F5 to build and run the program.
11. Click Print report, and then click Go!. The Print_Report procedure prints a report from the Northwind database.
12. Click Preview report, and then click Go!. The Preview_Report procedure previews a report from the Northwind database. Close the Access instance when you are ready to continue.
13. Click Show form, and then click Go!. The Show_Form procedure displays the Customer Labels Dialog form from the Northwind database. It also sets the option group on the form to "Specific Country" and selects "USA" from the list. Close the Access instance when you are ready to continue.
14. Click Print report (Security), and then click Go!. The Print_Report_Security procedure shows you how to automate Access and how to avoid the logon dialog box if user-level security is turned on. In this example, assume the default logon by passing the user Admin with a blank password. The code then prints a report in the Northwind database.
15. Click Preview report (Runtime), and then click Go!. The Preview_Report_Runtime procedure shows you how to automate the Access Runtime to preview a report in the Northwind database. If the retail version of Access is installed, the procedure will still work correctly. Close the instance of Access when you are ready to continue.

back to the top

REFERENCES

For more information, see the following Microsoft Developer Network (MSDN) Web site: For additional information, click the article numbers below to view the articles in the Microsoft Knowledge Base: back to the top