Automate Microsoft Office Access 2003 from Microsoft Visual Basic .NET

Frank C. Rice
Microsoft Corporation

November 2003

Applies to:
    Microsoft® Office Access 2003
    Microsoft Visual Basic® .NET

Summary: Automating Microsoft Office Access 2003 from another application provides an easy and efficient way to take advantage of the features and capabilities of the Access object model. In this article, you will learn some of the common tasks that you can accomplish by automating Access. (32 printed pages)

Introduction
Automation versus ADO.NET
About Automation
Common Automation Tasks
Access Security Dialogs
Automating Access Runtime
Create the Complete Sample Visual Basic .NET Project
Conclusion

While Microsoft® Visual Basic® for Applications (VBA) continues to be the main programming language for Microsoft Office Access 2003 and earlier versions, it is limited in its ability to provide a relatively easy environment for you to perform certain tasks. For example, the tools available from VBA to develop Web applications are not necessarily intuitive or easy to use. On the other hand, Microsoft Visual Basic .NET, when used with the Microsoft .NET Framework, provides a number of tools and related technologies that help you create Web applications for Access databases much more easily than VBA or even Visual Basic.

The .NET Framework is a software development environment that blends very well with Access and other databases. You don't interface with the .NET Framework directly but instead use a favorite programming language. The .NET Framework works through Microsoft Visual Studio® .NET to make available several templates that you can use for creating different types of solutions. Most Access developers create solutions that take advantage of a local area network (LAN) or individual workstation. The Windows Application template is well suited for this type of application.

While a discussion of the .NET Framework or Visual Basic .NET is beyond the scope of this article, there are a variety of sources for more information on these technologies such as books, Knowledge Base articles, and a support center as with the following:

Introduction to Developing with the .NET Framework

Visual Basic .NET Support Center

There are two main technologies that you can use to work with Access databases from Microsoft Visual Basic .NET: Automation and Microsoft ActiveX® Data Objects (ADO.NET).

Automation allows you to create programmable applications that you can control by using a program or script running in another application. Use automation only if you need 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 looks at automation in more detail later.

ADO .NET is provided as a part of the .NET Framework to handle data. While you are most likely using ActiveX Data Object or Data Access Objects (DAO) with Visual Basic or VBA to connect to your Access databases, ADO .NET is now available for this task. ADO.NET is the preferred technology if you want to work with data objects, such as tables and queries in an Access database.

This article discusses how to automate Access. The article does not discuss ADO.NET. For more information about ADO .NET, see Introduction to Data Access with ADO.NET.

This article provides step-by-step samples that show you how to automate Microsoft Access 2003 from Visual Basic .NET.

Note   For information on how to automate Microsoft Access 2002 with Microsoft Visual Basic .NET (2002), see the Microsoft Knowledge Base article — 317113: 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

Automation is a Component Object model (COM) technology. Automation allows applications that you write 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 in your application 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.

The following sections describe common automation tasks that you may perform programmatically.

When you automate 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.

application.OpenCurrentDatabase dbname[, exclusive]

Table 1. A list of arguments and descriptions for using the OpenCurrentDatabase method

expression.OpenAccessProject(filepath, Exclusive)

Table 2. A list of arguments and descriptions for using the OpenAccessProject method

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. To summarize, you use the OpenCurrentDatabase and CloseCurrentDatabase methods to open and close an existing .mdb-type database. You use the OpenAccessProject method to open an existing .adp-type database.

You may also use the GetObject(<path to database>) function to open a database in an instance of Access.

GetObject([pathname] [, class])

Table 3. A list of arguments and descriptions for using the GetObject function

If you already opened the database in an instance of Access, the GetObject function 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 an 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)
...

To preview or to print an Access report, you call the OpenReport method of the DoCmd object.

DoCmd.OpenReport(ReportName, View, FilterName, WhereCondition, WindowMode, OpenArgs)

Table 4. A list of arguments and descriptions for using the OpenReport method

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.

expression.PrintOut(PrintRange, PageFrom, PageTo, PrintQuality, Copies, CollateCopies)

Table 5. A list of arguments and descriptions for using the PrintOut method

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, you can use PrintOut 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 first introduced 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, see Microsoft Knowledge Base Article — (284286) ACC2002: How to Reset Changes to the Application.Printer Object.

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.

The full syntax is as follows:

expression.OpenForm(FormName, View, FilterName, WhereCondition, DataMode, WindowMode, OpenArgs)

Table 6. A list of arguments and descriptions for using the OpenForm method

When you automate Access, you may be prompted to provide a user name or password, or both, when you try to open a database. If the user types the wrong information, an error occurs 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 Access: password-protected databases and user-level security through a workgroup file (System.mdw). You can use passwords to restrict access to the database but once you provide the correct password, the user has unrestricted access to all Access data and database objects. 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. With user-level security, you define a workgroup composed of user accounts and group accounts. You can assign permissions to users, groups, or both. 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, see the Microsoft Knowledge Base article — (305542) ACC2002: The Role of the Workgroup Information File in Security.

You can also find more information in Access Help in the following topics: "Manage user and group accounts," and "Types of permissions."

If you open a database that is 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. In the following example, the oAccess method was 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 = "My_password" '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 method does not actually close the database in Access. It only closes the DAO connection to the database that is made through the DBEngine object. The DAO connection is no longer necessary after you use the OpenCurrentDatabase method. 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.

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. You cannot specify a user name and a password 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 method 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, see the Microsoft Knowledge Base article — (192919) HOWTO: Automate a Secured Access Database Using Visual Basic.

For more information about starting Access with command-line switches, see the Microsoft Knowledge Base article — (209207) How to Use Command-Line Switches in Microsoft Access.

The Microsoft Office Access 2003 Developer Edition contains features that allow you to 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 installs and registers similar to 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 receive an error message such as the following when you try to instantiate the instance:

-2147023186 The object universal unique identifier (UUID) was not found.

For more information, see the Microsoft Knowledge Base article — (295179) ACC2002: Can't Use OLE Automation with Access Runtime.

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

Note   By default, Access 2003 installs the sample databases in the following path: C:\Program Files\Microsoft Office\Office11\Samples. Using Access 2003, you can make sure that the Northwind sample database is installed. Click the Help menu, click Sample Databases, and then click Northwind Sample Database.

1. Close any instances of Access that are currently running.

2. Start Microsoft Visual Studio .NET and on the File menu, click New and then click Project.

3. Select Windows Application from the Visual Basic Projects types. By default, Form1 is created.

4. Add a reference to the Microsoft Access Object Library. To do this, follow these steps:

   • On the Project menu, click Add Reference.
   • On the COM tab, locate Microsoft Access 11.0 Object Library, and then click Select. Note that 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, see the Microsoft Knowledge Base article — (328912) INFO: Microsoft Office XP PIAs Are Available for Download.
   • In the Add References dialog box click OK to accept your selections. If you receive a prompt to generate wrappers for the libraries that you selected, click Yes.

5. On the View menu, select Toolbox to display the toolbox.

6. Add five radio button controls and a button control to Form1.

7. Select all of the radio button controls, and then set the Size property to 150,24.

8. On the View menu, click Code.

9. 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 = "user_name"
       'sPwd = "my_password"
       '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 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 = "user_name"
       sPwd = "my_password"
   
       ' 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
   

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

    ...
    Imports Microsoft.Office.Interop
    ...
    

11. Press F5 to build and run the program.

12. Click Print report, and then click Go!. The Print_Report procedure prints a report from the Northwind database.

13. 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.

14. 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.

15. 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.

16. 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 still works correctly. Close the instance of Access when you are ready to continue.

Automation enables one application to control another. The controlling application interacts with the controlled application, in this example, Access, by manipulating its exposed properties, methods, and responding to its events. In this article, we learned how to open a database in Access, print or preview an Access report, show and edit an Access form, and other common tasks. Using automation, you have a simple way to make Access more extensible.