asked on
VB.Net COM error on accessing Form or form controls
I am trying to create a program that reads an MDB and identifies the forms and controls on the forms. I have had soome success but I keep getting a weird COM error. I am VERY new to VB but been a programmer for 20 years.
Anyway, I copied code from a microsoft article that does what I want to do. THAT code blows up also!!
I included what I copied from MS and Also the error message. Is there a probelm with my setup or what?? This happens on two different macines, XP professional and Win 7 64 bit.
Here is the error I get:
Unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft.Office.Interop.
__________________________
it errors or at the indicated line:
For Each oForm In oAccess.Forms <<<<<<<<<<<<<<<
oAccess.DoCmd.Close(Object
ObjectName:=oForm.Name, _
Save:=Access.AcCloseSave.a
Next
--------------------------
Below is all the code
Imports Microsoft.Office.Interop
Public Class Form1
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.AppW
= 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.A
If sAccPath = "" Then
MsgBox("Can't determine path to msaccess.exe", _
MsgBoxStyle.MsgBoxSetForeg
Return Nothing
End If
' Make sure specified database (sDBPath) exists:
If Not System.IO.File.Exists(sDBP
MsgBox("Can't find the file '" & sDBPath & "'", _
MsgBoxStyle.MsgBoxSetForeg
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.Sl
' 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:=Acces
System.Runtime.InteropServ
oAccess = Nothing
Return Nothing
ErrorHandler:
MsgBox(Err.Number & ": " & Err.Description, _
MsgBoxStyle.MsgBoxSetForeg
Resume ErrorCleanup
End Function
Private Function ShellGetApp(Optional ByVal sCmdLine As String = vbNullString, _
Optional ByVal enuWindowStyle As Microsoft.VisualBasic.AppW
= AppWinStyle.MinimizedFocus
'Launches a new instance of Access using the Shell function
'then returns the Application object via calling:
'GetObject(,"Access.Applic
'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.A
If sAccPath = "" Then
MsgBox("Can't determine path to msaccess.exe", _
MsgBoxStyle.MsgBoxSetForeg
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:=Acces
System.Runtime.InteropServ
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.Sl
AppActivate(Title:=Me.Text
Resume 'resume code at the GetObject line
Else
MsgBox("GetObject failed. Process ended.", _
MsgBoxStyle.MsgBoxSetForeg
End If
Else 'iSection = 0 so use normal error handling:
MsgBox(Err.Number & ": " & Err.Description, _
MsgBoxStyle.MsgBoxSetForeg
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.
'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.RegistryKe
Microsoft.Win32.Registry.L
Dim oKey As Microsoft.Win32.RegistryKe
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\Softwar
oKey = oReg.OpenSubKey("Software\
sCLSID = oKey.GetValue("")
oKey.Close()
' Now that we have the CLSID, locate the server path at
' HKEY_LOCAL_MACHINE\Softwar
' {xxxxxxxx-xxxx-xxxx-xxxx-x
oKey = oReg.OpenSubKey("Software\
sPath = oKey.GetValue("")
oKey.Close()
' Remove any characters beyond the exe name:
iPos = InStr(1, sPath, sEXE, CompareMethod.Text)
sPath = Microsoft.VisualBasic.Left
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:=Acc
sDBPath = sDBPath & "Samples\Northwind.mdb"
' Open Northwind.mdb in shared mode:
oAccess.OpenCurrentDatabas
' Select the report name in the database window and give focus
' to the database window:
oAccess.DoCmd.SelectObject
ObjectName:=sReport, InDatabaseWindow:=True)
' Print the report:
oAccess.DoCmd.OpenReport(R
View:=Access.AcView.acView
Cleanup:
' Quit Access and release object:
On Error Resume Next
oAccess.Quit(Option:=Acces
System.Runtime.InteropServ
oAccess = Nothing
Exit Sub
ErrorHandler:
MsgBox(Err.Number & ": " & Err.Description, _
MsgBoxStyle.MsgBoxSetForeg
' 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:=Acc
sDBPath = sDBPath & "Samples\Northwind.mdb"
' Open Northwind.mdb in shared mode:
oAccess.OpenCurrentDatabas
' Close any forms that Northwind may have opened:
For Each oForm In oAccess.Forms
oAccess.DoCmd.Close(Object
ObjectName:=oForm.Name, _
Save:=Access.AcCloseSave.a
Next
If Not oForm Is Nothing Then
System.Runtime.InteropServ
End If
oForm = Nothing
' Select the report name in the database window and give focus
' to the database window:
oAccess.DoCmd.SelectObject
ObjectName:=sReport, InDatabaseWindow:=True)
' Maximize the Access window:
oAccess.RunCommand(Command
' Preview the report:
oAccess.DoCmd.OpenReport(R
View:=Access.AcView.acView
' 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
' Hide Report's right-click popup menu:
oAccess.CommandBars("Print
' Release Application object and allow Access to be closed by user:
If Not oAccess.UserControl Then oAccess.UserControl = True
System.Runtime.InteropServ
oAccess = Nothing
Exit Sub
ErrorCleanup:
' Try to quit Access due to an unexpected error:
On Error Resume Next
System.Runtime.InteropServ
oForm = Nothing
oAccess.Quit(Option:=Acces
System.Runtime.InteropServ
oAccess = Nothing
Exit Sub
ErrorHandler:
MsgBox(Err.Number & ": " & Err.Description, _
MsgBoxStyle.MsgBoxSetForeg
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:=Acc
sDBPath = sDBPath & "Samples\Northwind.mdb"
' Open Northwind.mdb in shared mode:
oAccess.OpenCurrentDatabas
' Close any forms that Northwind may have opened:
For Each oForm In oAccess.Forms
oAccess.DoCmd.Close(Object
ObjectName:=oForm.Name, _
Save:=Access.AcCloseSave.a
Next
If Not oForm Is Nothing Then
System.Runtime.InteropServ
End If
oForm = Nothing
' Select the form name in the database window and give focus
' to the database window:
oAccess.DoCmd.SelectObject
ObjectName:=sForm, InDatabaseWindow:=True)
' Show the form:
oAccess.DoCmd.OpenForm(For
View:=Access.AcFormView.ac
' 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.InteropServ
oCtl = Nothing
' Put USA in the SelectCountry combo box:
oCtl = oCtls.Item("SelectCountry"
oCtl.Enabled = True
oCtl.SetFocus()
oCtl.Value = "USA"
System.Runtime.InteropServ
oCtl = Nothing
' Hide the Database Window:
oAccess.DoCmd.SelectObject
ObjectName:=sForm, InDatabaseWindow:=True)
oAccess.RunCommand(Command
' Set focus back to the form:
oForm.SetFocus()
' Release Controls and Form objects:
System.Runtime.InteropServ
oCtls = Nothing
System.Runtime.InteropServ
oForm = Nothing
' Release Application object and allow Access to be closed by user:
If Not oAccess.UserControl Then oAccess.UserControl = True
System.Runtime.InteropServ
oAccess = Nothing
Exit Sub
ErrorCleanup:
' Try to quit Access due to an unexpected error:
On Error Resume Next
System.Runtime.InteropServ
oCtl = Nothing
System.Runtime.InteropServ
oCtls = Nothing
System.Runtime.InteropServ
oForm = Nothing
oAccess.Quit(Option:=Acces
System.Runtime.InteropServ
oAccess = Nothing
Exit Sub
ErrorHandler:
MsgBox(Err.Number & ": " & Err.Description, _
MsgBoxStyle.MsgBoxSetForeg
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.A
If sDBPath = "" Then
MsgBox("Can't determine path to msaccess.exe", _
MsgBoxStyle.MsgBoxSetForeg
Exit Sub
End If
sDBPath = Microsoft.VisualBasic.Left
Len(sDBPath) - Len("msaccess.exe")) & "Samples\Northwind.mdb"
If Not System.IO.File.Exists(sDBP
MsgBox("Can't find the file '" & sDBPath & "'", _
MsgBoxStyle.MsgBoxSetForeg
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
ObjectName:=sReport, InDatabaseWindow:=True)
' Print the report:
oAccess.DoCmd.OpenReport(R
View:=Access.AcView.acView
Cleanup:
' Quit Access and release object:
On Error Resume Next
oAccess.Quit(Option:=Acces
System.Runtime.InteropServ
oAccess = Nothing
Exit Sub
ErrorHandler:
MsgBox(Err.Number & ": " & Err.Description, _
MsgBoxStyle.MsgBoxSetForeg
' 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.A
If sDBPath = "" Then
MsgBox("Can't determine path to msaccess.exe", _
MsgBoxStyle.MsgBoxSetForeg
Exit Sub
End If
sDBPath = Microsoft.VisualBasic.Left
Len(sDBPath) - Len("msaccess.exe")) & "Samples\Northwind.mdb"
If Not System.IO.File.Exists(sDBP
MsgBox("Can't find the file '" & sDBPath & "'", _
MsgBoxStyle.MsgBoxSetForeg
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(Object
ObjectName:=oForm.Name, _
Save:=Access.AcCloseSave.a
Next
If Not oForm Is Nothing Then
System.Runtime.InteropServ
End If
oForm = Nothing
' Select the report name in the database window and give focus
' to the database window:
oAccess.DoCmd.SelectObject
ObjectName:=sReport, InDatabaseWindow:=True)
' Maximize the Access window:
oAccess.RunCommand(Command
' Preview the report:
oAccess.DoCmd.OpenReport(R
View:=Access.AcView.acView
' 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.InteropServ
oAccess = Nothing
Exit Sub
ErrorCleanup:
' Try to quit Access due to an unexpected error:
On Error Resume Next
System.Runtime.InteropServ
oForm = Nothing
oAccess.Quit(Option:=Acces
System.Runtime.InteropServ
oAccess = Nothing
Exit Sub
ErrorHandler:
MsgBox(Err.Number & ": " & Err.Description, _
MsgBoxStyle.MsgBoxSetForeg
Resume ErrorCleanup
End Sub
End Class
Anyway, I copied code from a microsoft article that does what I want to do. THAT code blows up also!!
I included what I copied from MS and Also the error message. Is there a probelm with my setup or what?? This happens on two different macines, XP professional and Win 7 64 bit.
Here is the error I get:
Unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft.Office.Interop.
__________________________
it errors or at the indicated line:
For Each oForm In oAccess.Forms <<<<<<<<<<<<<<<
oAccess.DoCmd.Close(Object
ObjectName:=oForm.Name, _
Save:=Access.AcCloseSave.a
Next
--------------------------
Below is all the code
Imports Microsoft.Office.Interop
Public Class Form1
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.AppW
= 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.A
If sAccPath = "" Then
MsgBox("Can't determine path to msaccess.exe", _
MsgBoxStyle.MsgBoxSetForeg
Return Nothing
End If
' Make sure specified database (sDBPath) exists:
If Not System.IO.File.Exists(sDBP
MsgBox("Can't find the file '" & sDBPath & "'", _
MsgBoxStyle.MsgBoxSetForeg
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.Sl
' 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:=Acces
System.Runtime.InteropServ
oAccess = Nothing
Return Nothing
ErrorHandler:
MsgBox(Err.Number & ": " & Err.Description, _
MsgBoxStyle.MsgBoxSetForeg
Resume ErrorCleanup
End Function
Private Function ShellGetApp(Optional ByVal sCmdLine As String = vbNullString, _
Optional ByVal enuWindowStyle As Microsoft.VisualBasic.AppW
= AppWinStyle.MinimizedFocus
'Launches a new instance of Access using the Shell function
'then returns the Application object via calling:
'GetObject(,"Access.Applic
'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.A
If sAccPath = "" Then
MsgBox("Can't determine path to msaccess.exe", _
MsgBoxStyle.MsgBoxSetForeg
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:=Acces
System.Runtime.InteropServ
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.Sl
AppActivate(Title:=Me.Text
Resume 'resume code at the GetObject line
Else
MsgBox("GetObject failed. Process ended.", _
MsgBoxStyle.MsgBoxSetForeg
End If
Else 'iSection = 0 so use normal error handling:
MsgBox(Err.Number & ": " & Err.Description, _
MsgBoxStyle.MsgBoxSetForeg
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.
'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.RegistryKe
Microsoft.Win32.Registry.L
Dim oKey As Microsoft.Win32.RegistryKe
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\Softwar
oKey = oReg.OpenSubKey("Software\
sCLSID = oKey.GetValue("")
oKey.Close()
' Now that we have the CLSID, locate the server path at
' HKEY_LOCAL_MACHINE\Softwar
' {xxxxxxxx-xxxx-xxxx-xxxx-x
oKey = oReg.OpenSubKey("Software\
sPath = oKey.GetValue("")
oKey.Close()
' Remove any characters beyond the exe name:
iPos = InStr(1, sPath, sEXE, CompareMethod.Text)
sPath = Microsoft.VisualBasic.Left
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:=Acc
sDBPath = sDBPath & "Samples\Northwind.mdb"
' Open Northwind.mdb in shared mode:
oAccess.OpenCurrentDatabas
' Select the report name in the database window and give focus
' to the database window:
oAccess.DoCmd.SelectObject
ObjectName:=sReport, InDatabaseWindow:=True)
' Print the report:
oAccess.DoCmd.OpenReport(R
View:=Access.AcView.acView
Cleanup:
' Quit Access and release object:
On Error Resume Next
oAccess.Quit(Option:=Acces
System.Runtime.InteropServ
oAccess = Nothing
Exit Sub
ErrorHandler:
MsgBox(Err.Number & ": " & Err.Description, _
MsgBoxStyle.MsgBoxSetForeg
' 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:=Acc
sDBPath = sDBPath & "Samples\Northwind.mdb"
' Open Northwind.mdb in shared mode:
oAccess.OpenCurrentDatabas
' Close any forms that Northwind may have opened:
For Each oForm In oAccess.Forms
oAccess.DoCmd.Close(Object
ObjectName:=oForm.Name, _
Save:=Access.AcCloseSave.a
Next
If Not oForm Is Nothing Then
System.Runtime.InteropServ
End If
oForm = Nothing
' Select the report name in the database window and give focus
' to the database window:
oAccess.DoCmd.SelectObject
ObjectName:=sReport, InDatabaseWindow:=True)
' Maximize the Access window:
oAccess.RunCommand(Command
' Preview the report:
oAccess.DoCmd.OpenReport(R
View:=Access.AcView.acView
' 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
' Hide Report's right-click popup menu:
oAccess.CommandBars("Print
' Release Application object and allow Access to be closed by user:
If Not oAccess.UserControl Then oAccess.UserControl = True
System.Runtime.InteropServ
oAccess = Nothing
Exit Sub
ErrorCleanup:
' Try to quit Access due to an unexpected error:
On Error Resume Next
System.Runtime.InteropServ
oForm = Nothing
oAccess.Quit(Option:=Acces
System.Runtime.InteropServ
oAccess = Nothing
Exit Sub
ErrorHandler:
MsgBox(Err.Number & ": " & Err.Description, _
MsgBoxStyle.MsgBoxSetForeg
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:=Acc
sDBPath = sDBPath & "Samples\Northwind.mdb"
' Open Northwind.mdb in shared mode:
oAccess.OpenCurrentDatabas
' Close any forms that Northwind may have opened:
For Each oForm In oAccess.Forms
oAccess.DoCmd.Close(Object
ObjectName:=oForm.Name, _
Save:=Access.AcCloseSave.a
Next
If Not oForm Is Nothing Then
System.Runtime.InteropServ
End If
oForm = Nothing
' Select the form name in the database window and give focus
' to the database window:
oAccess.DoCmd.SelectObject
ObjectName:=sForm, InDatabaseWindow:=True)
' Show the form:
oAccess.DoCmd.OpenForm(For
View:=Access.AcFormView.ac
' 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.InteropServ
oCtl = Nothing
' Put USA in the SelectCountry combo box:
oCtl = oCtls.Item("SelectCountry"
oCtl.Enabled = True
oCtl.SetFocus()
oCtl.Value = "USA"
System.Runtime.InteropServ
oCtl = Nothing
' Hide the Database Window:
oAccess.DoCmd.SelectObject
ObjectName:=sForm, InDatabaseWindow:=True)
oAccess.RunCommand(Command
' Set focus back to the form:
oForm.SetFocus()
' Release Controls and Form objects:
System.Runtime.InteropServ
oCtls = Nothing
System.Runtime.InteropServ
oForm = Nothing
' Release Application object and allow Access to be closed by user:
If Not oAccess.UserControl Then oAccess.UserControl = True
System.Runtime.InteropServ
oAccess = Nothing
Exit Sub
ErrorCleanup:
' Try to quit Access due to an unexpected error:
On Error Resume Next
System.Runtime.InteropServ
oCtl = Nothing
System.Runtime.InteropServ
oCtls = Nothing
System.Runtime.InteropServ
oForm = Nothing
oAccess.Quit(Option:=Acces
System.Runtime.InteropServ
oAccess = Nothing
Exit Sub
ErrorHandler:
MsgBox(Err.Number & ": " & Err.Description, _
MsgBoxStyle.MsgBoxSetForeg
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.A
If sDBPath = "" Then
MsgBox("Can't determine path to msaccess.exe", _
MsgBoxStyle.MsgBoxSetForeg
Exit Sub
End If
sDBPath = Microsoft.VisualBasic.Left
Len(sDBPath) - Len("msaccess.exe")) & "Samples\Northwind.mdb"
If Not System.IO.File.Exists(sDBP
MsgBox("Can't find the file '" & sDBPath & "'", _
MsgBoxStyle.MsgBoxSetForeg
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
ObjectName:=sReport, InDatabaseWindow:=True)
' Print the report:
oAccess.DoCmd.OpenReport(R
View:=Access.AcView.acView
Cleanup:
' Quit Access and release object:
On Error Resume Next
oAccess.Quit(Option:=Acces
System.Runtime.InteropServ
oAccess = Nothing
Exit Sub
ErrorHandler:
MsgBox(Err.Number & ": " & Err.Description, _
MsgBoxStyle.MsgBoxSetForeg
' 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.A
If sDBPath = "" Then
MsgBox("Can't determine path to msaccess.exe", _
MsgBoxStyle.MsgBoxSetForeg
Exit Sub
End If
sDBPath = Microsoft.VisualBasic.Left
Len(sDBPath) - Len("msaccess.exe")) & "Samples\Northwind.mdb"
If Not System.IO.File.Exists(sDBP
MsgBox("Can't find the file '" & sDBPath & "'", _
MsgBoxStyle.MsgBoxSetForeg
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(Object
ObjectName:=oForm.Name, _
Save:=Access.AcCloseSave.a
Next
If Not oForm Is Nothing Then
System.Runtime.InteropServ
End If
oForm = Nothing
' Select the report name in the database window and give focus
' to the database window:
oAccess.DoCmd.SelectObject
ObjectName:=sReport, InDatabaseWindow:=True)
' Maximize the Access window:
oAccess.RunCommand(Command
' Preview the report:
oAccess.DoCmd.OpenReport(R
View:=Access.AcView.acView
' 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.InteropServ
oAccess = Nothing
Exit Sub
ErrorCleanup:
' Try to quit Access due to an unexpected error:
On Error Resume Next
System.Runtime.InteropServ
oForm = Nothing
oAccess.Quit(Option:=Acces
System.Runtime.InteropServ
oAccess = Nothing
Exit Sub
ErrorHandler:
MsgBox(Err.Number & ": " & Err.Description, _
MsgBoxStyle.MsgBoxSetForeg
Resume ErrorCleanup
End Sub
End Class
Microsoft Access.NET Programming
Last Comment
jerrystefaniak
ASKER
I know. This is the code I downloaded form a MS site that shows how to acces and MDB. I didn't change anything because I wanted to make sure that I was not making the code go bad.
I only have Access 2003 installed but some of the machines I work on may have 2003 and 2007.
I am real new to VB.net What is early/ late binding and which should I do?
I am trying to develop this code on a Windows 7 64 bit computer AND an XP professional computer. I move it between the computers.
Is that a problem?
Thank you for your help.
Jerry
I only have Access 2003 installed but some of the machines I work on may have 2003 and 2007.
I am real new to VB.net What is early/ late binding and which should I do?
I am trying to develop this code on a Windows 7 64 bit computer AND an XP professional computer. I move it between the computers.
Is that a problem?
Thank you for your help.
Jerry
ASKER
Dear Learned One.
You correctly identified the problem.
I went out for a walk and realized that the computer that got that error DID have 2 versions of Access - 2003 and 2007. What do I do in that case. How do I make sure I am using the correct references?
Thank you,
Jerry
You correctly identified the problem.
I went out for a walk and realized that the computer that got that error DID have 2 versions of Access - 2003 and 2007. What do I do in that case. How do I make sure I am using the correct references?
Thank you,
Jerry
If you have mixed Access versions, then you can't use add an early-bound reference to the project, and you need to use late-bound references (CreateObject and GetObject). You need to make sure that ProgID that you are using references the correct Access version.
ASKER
Sorry for being such a pain but how do I make sure I am accessing the correct version. And what is early/late binding?
Jerry
Jerry
Early vs. Late Binding
http://word.mvps.orgfAQs/InterDev/EarlyvsLateBinding.htm
"There are two ways to use Automation (or OLE Automation) to programmatically control another application.
Late binding uses CreateObject to create and instance of the application object, which you can then control. For example, to create a new instance of Excel using late binding:
Dim oXL As Object
Set oXL = CreateObject("Excel.Applic
On the other hand, to manipulate an existing instance of Excel (if Excel is already open) you would use GetObject (regardless whether you're using early or late binding):
Dim oXL As Object
Set oXL = GetObject(, "Excel.Application")
To use early binding, you first need to set a reference in your project to the application you want to manipulate. In the VB Editor of any Office application, or in VB itself, you do this by selecting Tools + References, and selecting the application you want from the list (e.g. “Microsoft Excel 8.0 Object Library”).
http://word.mvps.orgfAQs/InterDev/EarlyvsLateBinding.htm
"There are two ways to use Automation (or OLE Automation) to programmatically control another application.
Late binding uses CreateObject to create and instance of the application object, which you can then control. For example, to create a new instance of Excel using late binding:
Dim oXL As Object
Set oXL = CreateObject("Excel.Applic
On the other hand, to manipulate an existing instance of Excel (if Excel is already open) you would use GetObject (regardless whether you're using early or late binding):
Dim oXL As Object
Set oXL = GetObject(, "Excel.Application")
To use early binding, you first need to set a reference in your project to the application you want to manipulate. In the VB Editor of any Office application, or in VB itself, you do this by selecting Tools + References, and selecting the application you want from the list (e.g. “Microsoft Excel 8.0 Object Library”).
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Very helpful in explaining.
Here is what I see:
1) You have a reference to Microsoft Access in the project, which is early-binding.
2) You seem to be starting an instance of Microsoft Access, and using late-binding (GetObject).
3) The QueryInterface error seems to suggest that you have different versions of Access installed on those machines.