Late binding error - running Ms Access Report in VB.Net

[Maciej Franciszkowski]

Hi,
I would like to make vb.net code which print a raport from MS Access.
Found some example in vb6 but they do not comply with .net.
My question is how to update my code.
Now I get late binding by MS Access'es DoCmd.SendObject.

Private Declare Sub Sleep Lib "Kernel32" (ByVal dwMS As
Long)
Private Sub accAuth()
Dim accObj As Object, Msg As String
Dim application As String, dbs As String, workgroup As
String
Dim user As String, password As String, cTries As
Integer
Dim x

'Try

' This is the default location of Access
application = "C:\Program Files\Microsoft Office
\Office11\MSACCESS.EXE"
' Use the path and name of a secured MDB on your
system
dbs = "C:\P\P_WU_2003.mdb"
' This is the default workgroup
workgroup = "C:\P\System.mdw "
user = "user" ' Use a valid username
password = "pass" ' and correct password

x = Shell(application & " " & Chr(34) & dbs & Chr(34)
& " /nostartup /user " & user & _
" /pwd " & password & " /wrkgrp " & Chr(34) &
workgroup & Chr(34), vbMinimizedFocus)

On Error GoTo WAITFORACCESS
accObj = GetObject(, "Access.Application")


' Turn off error handling
On Error GoTo 0

' You can now use the accObj reference to automate
Access

Dim stReport As String
Dim stWhere As String
Dim stEmailadd As String
Dim stSubject As String
Dim stEmailMessage As String

'accObj.DoCmd.OpenReport(ReportName:="rptDoDost")
accObj.DoCmd.SendObject(, "rptDoDost",
"PDFFormat(*.pdf)", "(e-mail address removed)", , , "Test", "TestMsg",
True, "")
'accObj.DoCmd.OutputTo(, "rptDoDost",
"PDFFormat(*.pdf)", "C:\Plan\1.pdf")




'stEmailMessage = "Please see the attached Inspection
Reports(PDF) for " & Me.RCnumber '
'stSubject = "Inspection for " & Me.RCnumber '
'stReport = "rptInspection"
'stWhere = "ISP_ID = " & Me.ISP_ID "'"
'stEmailadd =
"(e-mail address removed);[email protected];person3@email .com"

'accObj.OpenReport(stReport, acViewPreview, "",
stWhere, acWindowNormal, "")
'DoCmd.SetProperty stReport,
acPropertyCaption, "Inspection for " & Me.RCnumber [/QUOTE]

'accObj.SendObject(, stReport, "PDFFormat(*.pdf)",
stEmailadd, , , stSubject, stEmailMessage, True, "")

Msg = "Access is now open. You can click on Microsoft
Access "
Msg = Msg & "in the Taskbar to see that your database
is open."
Msg = Msg & vbCrLf & vbCrLf & "When ready, click OK to
close."
Console.WriteLine(Msg)

accObj.CloseCurrentDatabase()
accObj.Quit()

accObj = Nothing
Console.WriteLine("All Done!")

Exit Sub

WAITFORACCESS: ' <--- This line must be left-aligned.
' Access isn't registered in the Running Object Table
yet, so call
' SetFocus to take focus from Access, wait half a
second, and try
' again. If you try five times and fail, then
something has probably
' gone wrong, so warn the user and exit.
'SetFocus()
'If cTries < 5 Then
'cTries = cTries + 1
'Sleep(500) ' wait 1/2 seconds
'Resume
'Else
'Console.WriteLine("Access is taking too long. Process
ended.")
'End If


'Finally
'End Try
End Sub

 

[Jason Keats]

Hi,
I would like to make vb.net code which print a raport from MS Access.
Found some example in vb6 but they do not comply with .net.
My question is how to update my code.
Now I get late binding by MS Access'es DoCmd.SendObject.

Private Declare Sub Sleep Lib "Kernel32" (ByVal dwMS As
Long)
Private Sub accAuth()
Dim accObj As Object, Msg As String
Dim application As String, dbs As String, workgroup As
String
Dim user As String, password As String, cTries As
Integer
Dim x

'Try

' This is the default location of Access
application = "C:\Program Files\Microsoft Office
\Office11\MSACCESS.EXE"
' Use the path and name of a secured MDB on your
system
dbs = "C:\P\P_WU_2003.mdb"
' This is the default workgroup
workgroup = "C:\P\System.mdw "
user = "user" ' Use a valid username
password = "pass" ' and correct password

x = Shell(application& " "& Chr(34)& dbs& Chr(34)
& " /nostartup /user "& user& _
" /pwd "& password& " /wrkgrp "& Chr(34)&
workgroup& Chr(34), vbMinimizedFocus)

On Error GoTo WAITFORACCESS
accObj = GetObject(, "Access.Application")


' Turn off error handling
On Error GoTo 0

' You can now use the accObj reference to automate
Access

Dim stReport As String
Dim stWhere As String
Dim stEmailadd As String
Dim stSubject As String
Dim stEmailMessage As String

'accObj.DoCmd.OpenReport(ReportName:="rptDoDost")
accObj.DoCmd.SendObject(, "rptDoDost",
"PDFFormat(*.pdf)", "(e-mail address removed)", , , "Test", "TestMsg",
True, "")
'accObj.DoCmd.OutputTo(, "rptDoDost",
"PDFFormat(*.pdf)", "C:\Plan\1.pdf")




'stEmailMessage = "Please see the attached Inspection
Reports(PDF) for "& Me.RCnumber '
'stSubject = "Inspection for "& Me.RCnumber '
'stReport = "rptInspection"
'stWhere = "ISP_ID = "& Me.ISP_ID "'"
'stEmailadd =
"(e-mail address removed);[email protected];person3@email .com"

'accObj.OpenReport(stReport, acViewPreview, "",
stWhere, acWindowNormal, "")
'DoCmd.SetProperty stReport,
acPropertyCaption, "Inspection for "& Me.RCnumber

'accObj.SendObject(, stReport, "PDFFormat(*.pdf)",
stEmailadd, , , stSubject, stEmailMessage, True, "")

Msg = "Access is now open. You can click on Microsoft
Access "
Msg = Msg& "in the Taskbar to see that your database
is open."
Msg = Msg& vbCrLf& vbCrLf& "When ready, click OK to
close."
Console.WriteLine(Msg)

accObj.CloseCurrentDatabase()
accObj.Quit()

accObj = Nothing
Console.WriteLine("All Done!")

Exit Sub

WAITFORACCESS: '<--- This line must be left-aligned.
' Access isn't registered in the Running Object Table
yet, so call
' SetFocus to take focus from Access, wait half a
second, and try
' again. If you try five times and fail, then
something has probably
' gone wrong, so warn the user and exit.
'SetFocus()
'If cTries< 5 Then
'cTries = cTries + 1
'Sleep(500) ' wait 1/2 seconds
'Resume
'Else
'Console.WriteLine("Access is taking too long. Process
ended.")
'End If


'Finally
'End Try
End Sub
[/QUOTE]


The VB6 example at http://www.a1vbcode.com/vbtip-132.asp works okay.

When you open the above VB6 project in VS2008 it will convert it for you
as follows:

Option Strict Off
Option Explicit On
Friend Class Form1
Inherits System.Windows.Forms.Form

Dim objAccess As Object

Private Sub Command1_Click(ByVal eventSender As System.Object, ByVal
eventArgs As System.EventArgs) Handles Command1.Click
Dim dbName As String
Dim rptName As String
Dim Preview As Integer
Const acNormal As Short = 0
Const acPreview As Short = 2

dbName = My.Application.Info.DirectoryPath & "\db1.mdb"
rptName = "MyReportName"
Preview = acPreview 'acNormal

With objAccess
.OpenCurrentDatabase(filepath:=dbName)
If Preview = acPreview Then
.Visible = True
.DoCmd.OpenReport(rptName, Preview)
Else
.DoCmd.OpenReport(rptName)
End If
End With
End Sub

Private Sub Form1_Load(ByVal eventSender As System.Object, ByVal
eventArgs As System.EventArgs) Handles MyBase.Load
objAccess = CreateObject("Access.Application")
End Sub

Private Sub Form1_FormClosed(ByVal eventSender As System.Object, ByVal
eventArgs As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed
On Error Resume Next
objAccess.Quit()
On Error GoTo 0
objAccess = Nothing
End Sub
End Class


Just make sure your .mdb file is in the bin folder (with your .exe) and
the above will also work.