Access 2007 Interop and Outputto

[smthomas]

I have a project i am upgrading from the Access XP to Access 2007 interop.

When i change the reference the line containing docmd.outputto shows an error.


'OutputTo' is ambiguous because multiple kinds of members with this name
exist in interface 'Microsoft.Office.Interop.Access.DoCmd'.

When i try to retype the line the outputto command is not even available in
the list of options.

Any idea why it would not be available? The documentation on Microsoft shows
it is still available to use.

Thanks for any help.

[Mary Chipman [MSFT]]

Do you have a link to the online documentation? Generally speaking,
DoCmd was designed to access Access UI objects, mirroring macro
commands, although it has evolved considerably in Access 2007. What
are you attempting to use outputto command for? There might perhaps be
a better way to tackle the problem that doesn't use outputto.

--Mary

Post by smthomas
I have a project i am upgrading from the Access XP to Access 2007 interop.
When i change the reference the line containing docmd.outputto shows an error.
'OutputTo' is ambiguous because multiple kinds of members with this name
exist in interface 'Microsoft.Office.Interop.Access.DoCmd'.
When i try to retype the line the outputto command is not even available in
the list of options.
Any idea why it would not be available? The documentation on Microsoft shows
it is still available to use.
Thanks for any help.

[smthomas via DotNetMonster.com]

Thanks for the Reply Mary,
I have a VB application that generates reports in batch process as a windows
service. It opens an Access DB and runs a report against either an AS400 or
MSSQL database. Then it saves the results of the report to a snapfile. I
want to eventually turn that into a PDF file. However i just need to get the
current code updated and running. Here is the funciton that does the work.


Public Sub CAccessSnapShot()

Dim vSQLText As String = ""
Dim sAccessError As String = ""

Dim strMDBName, strSNPName As String

Dim oCmdOutput As Access.DoCmd
Try
Dim dtSQLText As DataTable
' GET THE SQL ASSOCIATED WITH THIS REPORT
dtSQLText = cFunctions.GetSQL(pReportRefNum, pQConnectionString)
If dtSQLText.Rows.Count > 0 Then
vSQLText = CStr(dtSQLText.Rows(0).Item("SQLText"))
Else
vSQLText = ""
End If
' GET THE NAME OF THE TEMPORARY MDB AND MAKE A COPY TO WORK WITH
'vStep = 1
'cFunctions.WriteEventLog("Step " & vStep)
strNwFileNM = cFunctions.getLongRptName(pQConnectionString,
pReportName) + "--" + cFunctions.GetNewFileName()
If CStr(strNwFileNM).Length > 60 Then
strNwFileNM = CStr(strNwFileNM).Substring(0, 60)
End If
'vStep = 2
'cFunctions.WriteEventLog("Step " & vStep)
strMDBName = strNwFileNM + ".mdb"
strMDBPath = pInputFileDir + strNwFileNM + ".mdb"
'vStep = 3
'cFunctions.WriteEventLog("Step " & vStep)
Try
objAccess.CloseCurrentDatabase() ' SMT 6-20
Catch ex As Exception
'cFunctions.WriteEventLog("Close Current DB: " &
pReportRefNum & " --- " & ex.Message.ToString)
End Try
'vStep = 4
'cFunctions.WriteEventLog("Step " & vStep)
' OPEN THE NEW COPY OF THE MDB
Dim copymdb As Boolean = False
Dim copycnt As Integer = 0
While copymdb = False And copycnt < 5
Try
cFunctions.copyfile(pInputFileDir + pInputFileName,
strMDBPath)
copymdb = True
'vStep = 5
'cFunctions.WriteEventLog("Step " & vStep)
Catch errorVariable As Exception
'cFunctions.deletefile(strMDBPath)
'Exit While
Thread.Sleep(3000)
Finally
copycnt += 1
End Try
End While
'MOVE THE RETURN TO Q FROM COPY MDB OUTSIDE OF THE LOOP
If copymdb = False Then
cFunctions.UpdateReportReturnToQueue(pReportRefNum,
pQConnectionString)
objAccess.CloseCurrentDatabase()
cFunctions.WriteEventLog("return to queue :" & pReportRefNum
& " error at copy mdb: ")
Else
Dim openmdb As Boolean = False
Dim opencnt As Integer = 0
'vStep = 6
'cFunctions.WriteEventLog("Step " & vStep)
While openmdb = False And opencnt < 5
Try
'cFunctions.WriteEventLog("Step Try to Open DB: " &
pReportRefNum)
objAccess.OpenCurrentDatabase(strMDBPath, False)
openmdb = True
'cFunctions.WriteEventLog("Opened DB: " &
pReportRefNum)
' GET THE NEW NAME OF THE SNAP FILE
Catch ex As Exception
'cFunctions.WriteEventLog("Step Did not Open DB: " &
pReportRefNum)
cFunctions.WriteEventLog("OpenDB Error -- rptrefnum:
" & pReportRefNum & " cnt: " & CStr(opencnt) & "-" & ex.Message & " -- " &
strMDBPath)
Thread.Sleep(3000)
Try
'cFunctions.WriteEventLog("OpenDB Error try to
close")
objAccess.DoCmd.Close(Access.AcObjectType.
acReport, pReportName, Access.AcCloseSave.acSaveNo)
objAccess.CloseCurrentDatabase()
Catch ex1 As Exception
cFunctions.WriteEventLog("Close Current DB 2: " &
pReportRefNum & " --- " & ex1.Message.ToString)
End Try
Finally
opencnt += 1
End Try
End While
'cFunctions.WriteEventLog("outside openmdb: " & pReportRefNum)

If openmdb = False Then
'cFunctions.WriteEventLog("failed to open and return to
queue" & pReportRefNum)
Try
cFunctions.UpdateReportReturnToQueue(pReportRefNum,
pQConnectionString)
cFunctions.WriteEventLog("Could not Open MDB Returned
to Queue : " & pReportRefNum & " --- ")
Catch ex As Exception
cFunctions.UpdateReportError(pReportRefNum, CStr
(vStep) + " == " + ex.Message.ToString, pQConnectionString)
End Try
Else
' RUN THE FUNCTION FROM ACCESS TO CREATE THE REPORT
SNAPSHOT
Try

'cFunctions.WriteEventLog("new snp name: " &
strSNPName)
sAccessError = objAccess.Run("SetReportParams", CStr
(vSQLText), CStr(pConnectString), CStr(pReportName))
cFunctions.WriteEventLog("strSNPName: " & strSNPName)

objAccess.DoCmd.OpenReport(pReportName, Access.AcView.
acViewPreview)

cFunctions.WriteEventLog("Access OpenRpt: " &
pReportRefNum)
'If pReportType = "SNP" Then
oCmdOutput = objAccess.DoCmd

'**** THIS IS WHERE THE ERROR OCCURS

oCmdOutput.OutputTo(Access.AcOutputObjectType.
acOutputReport, pReportName, "Snapshot Format", pOutputFileDir & strSNPName,
False)

'***********
cFunctions.WriteEventLog("Access Output: " &
pReportRefNum)
cFunctions.UpdateReportComplete(pReportRefNum,
strSNPName, pQConnectionString)
Catch ex As Exception
cFunctions.WriteEventLog("Access Rpt Error: " & ex.
Message)
End Try

End If
' IF AN ERROR OCCURED MARK THE REPORT AS ERRORED
Thread.Sleep(5000)
If sAccessError <> "" Then
cFunctions.UpdateReportError(pReportRefNum, CStr(vStep) +
" == " + sAccessError + " Access Error", pQConnectionString)
cFunctions.WriteEventLog("GPReportServer.NET-Access-Error
(" & pReportRefNum & " errored at step " & CStr(vStep) & "): " & sAccessError)

cFunctions.UpdateReportReturnToQueue(pReportRefNum,
pQConnectionString)
cFunctions.WriteEventLog("return to queue (" &
pReportRefNum & " Access Error ")

Else
'If writesnpfile = True Then
'cFunctions.UpdateReportComplete(pReportRefNum,
strSNPName, pQConnectionString)
'Else
'cFunctions.UpdateReportError(pReportRefNum, CStr(vStep)
+ " == " + sAccessError + " Access Error", pQConnectionString)
'cFunctions.WriteEventLog("Could not write snp file (" &
pReportRefNum)
'cFunctions.UpdateReportReturnToQueue(pReportRefNum,
pQConnectionString)
'cFunctions.WriteEventLog("return to queue (" &
pReportRefNum & " write snp file Error ")
End If
End If
' REPORT IS COMPLETE CLOSE MDB
vStep = 5
objAccess.DoCmd.Close(Access.AcObjectType.acReport, pReportName,
Access.AcCloseSave.acSaveNo)
objAccess.CloseCurrentDatabase()
'End If
'End If

Catch errorVariable As System.Runtime.InteropServices.COMException
' ERROR HANDLING

'GC.Collect()
'Thread.Sleep(5000)
' WRITE ANY ERROR TO EVENT LOG
objAccess.DoCmd.Close(Access.AcObjectType.acReport, pReportName,
Access.AcCloseSave.acSaveNo)
objAccess.CloseCurrentDatabase()
cFunctions.WriteEventLog("ReportServer.NET-Access-COMError (" &
pReportRefNum & ") errored at step " & CStr(vStep) & "): " & errorVariable.
Message.ToString & " mdb name: " & strMDBPath)
cFunctions.UpdateReportError(pReportRefNum, "COM Exc == " +
errorVariable.Message.ToString, pQConnectionString)
cFunctions.UpdateReportReturnToQueue(pReportRefNum,
pQConnectionString)
cFunctions.WriteEventLog("return to queue (" & pReportRefNum & ")
InteropServices.COMException ")

Catch errorVariable As System.Threading.ThreadAbortException
objAccess.DoCmd.Close(Access.AcObjectType.acReport, pReportName,
Access.AcCloseSave.acSaveNo)
objAccess.CloseCurrentDatabase()
cFunctions.WriteEventLog("ReportServer.NET-Access-
ThreadAboutException (" & pReportRefNum & " ThreadAbortException " & CStr
(vStep) & "): " & errorVariable.Message.ToString)
cFunctions.UpdateReportError(pReportRefNum, "ThreadAbort == " +
errorVariable.Message.ToString, pQConnectionString)
cFunctions.UpdateReportReturnToQueue(pReportRefNum,
pQConnectionString)
cFunctions.WriteEventLog("return to queue (" & pReportRefNum & "
ThreadAbortException ")
Exit Sub

Catch errorVariable As Exception
' ERROR HANDLING
objAccess.DoCmd.Close(Access.AcObjectType.acReport, pReportName,
Access.AcCloseSave.acSaveNo)
objAccess.CloseCurrentDatabase()
cFunctions.WriteEventLog("ReportServer.NET-Access-Error (" &
pReportRefNum & " errored at step " & CStr(vStep) & "): " & errorVariable.
Message.ToString)
cFunctions.UpdateReportError(pReportRefNum, "LastAccessError == "
+ errorVariable.Message.ToString + " Exception" + CStr(vStep),
pQConnectionString)
cFunctions.UpdateReportReturnToQueue(pReportRefNum,
pQConnectionString)
cFunctions.WriteEventLog("return to queue (" & pReportRefNum & "
NET-Access-Error ")
Finally
' WAIT FOR 5 SECONDS TO GIVE ACCESS TIME TO CLOSE MDB AND THEN
DELETE THE WORKING FILE
'Thread.Sleep(8000)
cFunctions.deletefile(strMDBPath)
End Try

End Sub

Post by Mary Chipman [MSFT]
Do you have a link to the online documentation? Generally speaking,
DoCmd was designed to access Access UI objects, mirroring macro
commands, although it has evolved considerably in Access 2007. What
are you attempting to use outputto command for? There might perhaps be
a better way to tackle the problem that doesn't use outputto.
--Mary

Post by smthomas
I have a project i am upgrading from the Access XP to Access 2007 interop.

[quoted text clipped - 10 lines]

Post by smthomas
Thanks for any help.

[Mary Chipman [MSFT]]

Have you considered using SQL Server Reporting Services instead of
Access? It's designed to handle this sort of business problem. As far
as troubleshooting goes, the only thing I can think of at the moment
is to isolate the point of failure and try to simplify a repro case
with OutputTo. You need to figure out if the problem is in Access
itself or some other issue with interop.

--Mary

On Thu, 01 Oct 2009 14:43:24 GMT, "smthomas via DotNetMonster.com"

Post by smthomas via DotNetMonster.com
Thanks for the Reply Mary,
I have a VB application that generates reports in batch process as a windows
service. It opens an Access DB and runs a report against either an AS400 or
MSSQL database. Then it saves the results of the report to a snapfile. I
want to eventually turn that into a PDF file. However i just need to get the
current code updated and running. Here is the funciton that does the work.
Public Sub CAccessSnapShot()
Dim vSQLText As String = ""
Dim sAccessError As String = ""
Dim strMDBName, strSNPName As String
Dim oCmdOutput As Access.DoCmd
Try
Dim dtSQLText As DataTable
' GET THE SQL ASSOCIATED WITH THIS REPORT
dtSQLText = cFunctions.GetSQL(pReportRefNum, pQConnectionString)
If dtSQLText.Rows.Count > 0 Then
vSQLText = CStr(dtSQLText.Rows(0).Item("SQLText"))
Else
vSQLText = ""
End If
' GET THE NAME OF THE TEMPORARY MDB AND MAKE A COPY TO WORK WITH
'vStep = 1
'cFunctions.WriteEventLog("Step " & vStep)
strNwFileNM = cFunctions.getLongRptName(pQConnectionString,
pReportName) + "--" + cFunctions.GetNewFileName()
If CStr(strNwFileNM).Length > 60 Then
strNwFileNM = CStr(strNwFileNM).Substring(0, 60)
End If
'vStep = 2
'cFunctions.WriteEventLog("Step " & vStep)
strMDBName = strNwFileNM + ".mdb"
strMDBPath = pInputFileDir + strNwFileNM + ".mdb"
'vStep = 3
'cFunctions.WriteEventLog("Step " & vStep)
Try
objAccess.CloseCurrentDatabase() ' SMT 6-20
Catch ex As Exception
'cFunctions.WriteEventLog("Close Current DB: " &
pReportRefNum & " --- " & ex.Message.ToString)
End Try
'vStep = 4
'cFunctions.WriteEventLog("Step " & vStep)
' OPEN THE NEW COPY OF THE MDB
Dim copymdb As Boolean = False
Dim copycnt As Integer = 0
While copymdb = False And copycnt < 5
Try
cFunctions.copyfile(pInputFileDir + pInputFileName,
strMDBPath)
copymdb = True
'vStep = 5
'cFunctions.WriteEventLog("Step " & vStep)
Catch errorVariable As Exception
'cFunctions.deletefile(strMDBPath)
'Exit While
Thread.Sleep(3000)
Finally
copycnt += 1
End Try
End While
'MOVE THE RETURN TO Q FROM COPY MDB OUTSIDE OF THE LOOP
If copymdb = False Then
cFunctions.UpdateReportReturnToQueue(pReportRefNum,
pQConnectionString)
objAccess.CloseCurrentDatabase()
cFunctions.WriteEventLog("return to queue :" & pReportRefNum
& " error at copy mdb: ")
Else
Dim openmdb As Boolean = False
Dim opencnt As Integer = 0
'vStep = 6
'cFunctions.WriteEventLog("Step " & vStep)
While openmdb = False And opencnt < 5
Try
'cFunctions.WriteEventLog("Step Try to Open DB: " &
pReportRefNum)
objAccess.OpenCurrentDatabase(strMDBPath, False)
openmdb = True
'cFunctions.WriteEventLog("Opened DB: " &
pReportRefNum)
' GET THE NEW NAME OF THE SNAP FILE
Catch ex As Exception
'cFunctions.WriteEventLog("Step Did not Open DB: " &
pReportRefNum)
" & pReportRefNum & " cnt: " & CStr(opencnt) & "-" & ex.Message & " -- " &
strMDBPath)
Thread.Sleep(3000)
Try
'cFunctions.WriteEventLog("OpenDB Error try to
close")
objAccess.DoCmd.Close(Access.AcObjectType.
acReport, pReportName, Access.AcCloseSave.acSaveNo)
objAccess.CloseCurrentDatabase()
Catch ex1 As Exception
cFunctions.WriteEventLog("Close Current DB 2: " &
pReportRefNum & " --- " & ex1.Message.ToString)
End Try
Finally
opencnt += 1
End Try
End While
'cFunctions.WriteEventLog("outside openmdb: " & pReportRefNum)
If openmdb = False Then
'cFunctions.WriteEventLog("failed to open and return to
queue" & pReportRefNum)
Try
cFunctions.UpdateReportReturnToQueue(pReportRefNum,
pQConnectionString)
cFunctions.WriteEventLog("Could not Open MDB Returned
to Queue : " & pReportRefNum & " --- ")
Catch ex As Exception
cFunctions.UpdateReportError(pReportRefNum, CStr
(vStep) + " == " + ex.Message.ToString, pQConnectionString)
End Try
Else
' RUN THE FUNCTION FROM ACCESS TO CREATE THE REPORT
SNAPSHOT
Try
'cFunctions.WriteEventLog("new snp name: " &
strSNPName)
sAccessError = objAccess.Run("SetReportParams", CStr
(vSQLText), CStr(pConnectString), CStr(pReportName))
cFunctions.WriteEventLog("strSNPName: " & strSNPName)
objAccess.DoCmd.OpenReport(pReportName, Access.AcView.
acViewPreview)
cFunctions.WriteEventLog("Access OpenRpt: " &
pReportRefNum)
'If pReportType = "SNP" Then
oCmdOutput = objAccess.DoCmd
'**** THIS IS WHERE THE ERROR OCCURS
oCmdOutput.OutputTo(Access.AcOutputObjectType.
acOutputReport, pReportName, "Snapshot Format", pOutputFileDir & strSNPName,
False)
'***********
cFunctions.WriteEventLog("Access Output: " &
pReportRefNum)
cFunctions.UpdateReportComplete(pReportRefNum,
strSNPName, pQConnectionString)
Catch ex As Exception
cFunctions.WriteEventLog("Access Rpt Error: " & ex.
Message)
End Try
End If
' IF AN ERROR OCCURED MARK THE REPORT AS ERRORED
Thread.Sleep(5000)
If sAccessError <> "" Then
cFunctions.UpdateReportError(pReportRefNum, CStr(vStep) +
" == " + sAccessError + " Access Error", pQConnectionString)
cFunctions.WriteEventLog("GPReportServer.NET-Access-Error
(" & pReportRefNum & " errored at step " & CStr(vStep) & "): " & sAccessError)
cFunctions.UpdateReportReturnToQueue(pReportRefNum,
pQConnectionString)
cFunctions.WriteEventLog("return to queue (" &
pReportRefNum & " Access Error ")
Else
'If writesnpfile = True Then
'cFunctions.UpdateReportComplete(pReportRefNum,
strSNPName, pQConnectionString)
'Else
'cFunctions.UpdateReportError(pReportRefNum, CStr(vStep)
+ " == " + sAccessError + " Access Error", pQConnectionString)
'cFunctions.WriteEventLog("Could not write snp file (" &
pReportRefNum)
'cFunctions.UpdateReportReturnToQueue(pReportRefNum,
pQConnectionString)
'cFunctions.WriteEventLog("return to queue (" &
pReportRefNum & " write snp file Error ")
End If
End If
' REPORT IS COMPLETE CLOSE MDB
vStep = 5
objAccess.DoCmd.Close(Access.AcObjectType.acReport, pReportName,
Access.AcCloseSave.acSaveNo)
objAccess.CloseCurrentDatabase()
'End If
'End If
Catch errorVariable As System.Runtime.InteropServices.COMException
' ERROR HANDLING
'GC.Collect()
'Thread.Sleep(5000)
' WRITE ANY ERROR TO EVENT LOG
objAccess.DoCmd.Close(Access.AcObjectType.acReport, pReportName,
Access.AcCloseSave.acSaveNo)
objAccess.CloseCurrentDatabase()
cFunctions.WriteEventLog("ReportServer.NET-Access-COMError (" &
pReportRefNum & ") errored at step " & CStr(vStep) & "): " & errorVariable.
Message.ToString & " mdb name: " & strMDBPath)
cFunctions.UpdateReportError(pReportRefNum, "COM Exc == " +
errorVariable.Message.ToString, pQConnectionString)
cFunctions.UpdateReportReturnToQueue(pReportRefNum,
pQConnectionString)
cFunctions.WriteEventLog("return to queue (" & pReportRefNum & ")
InteropServices.COMException ")
Catch errorVariable As System.Threading.ThreadAbortException
objAccess.DoCmd.Close(Access.AcObjectType.acReport, pReportName,
Access.AcCloseSave.acSaveNo)
objAccess.CloseCurrentDatabase()
cFunctions.WriteEventLog("ReportServer.NET-Access-
ThreadAboutException (" & pReportRefNum & " ThreadAbortException " & CStr
(vStep) & "): " & errorVariable.Message.ToString)
cFunctions.UpdateReportError(pReportRefNum, "ThreadAbort == " +
errorVariable.Message.ToString, pQConnectionString)
cFunctions.UpdateReportReturnToQueue(pReportRefNum,
pQConnectionString)
cFunctions.WriteEventLog("return to queue (" & pReportRefNum & "
ThreadAbortException ")
Exit Sub
Catch errorVariable As Exception
' ERROR HANDLING
objAccess.DoCmd.Close(Access.AcObjectType.acReport, pReportName,
Access.AcCloseSave.acSaveNo)
objAccess.CloseCurrentDatabase()
cFunctions.WriteEventLog("ReportServer.NET-Access-Error (" &
pReportRefNum & " errored at step " & CStr(vStep) & "): " & errorVariable.
Message.ToString)
cFunctions.UpdateReportError(pReportRefNum, "LastAccessError == "
+ errorVariable.Message.ToString + " Exception" + CStr(vStep),
pQConnectionString)
cFunctions.UpdateReportReturnToQueue(pReportRefNum,
pQConnectionString)
cFunctions.WriteEventLog("return to queue (" & pReportRefNum & "
NET-Access-Error ")
Finally
' WAIT FOR 5 SECONDS TO GIVE ACCESS TIME TO CLOSE MDB AND THEN
DELETE THE WORKING FILE
'Thread.Sleep(8000)
cFunctions.deletefile(strMDBPath)
End Try
End Sub

Post by Mary Chipman [MSFT]
Do you have a link to the online documentation? Generally speaking,
DoCmd was designed to access Access UI objects, mirroring macro
commands, although it has evolved considerably in Access 2007. What
are you attempting to use outputto command for? There might perhaps be
a better way to tackle the problem that doesn't use outputto.
--Mary

Post by smthomas
I have a project i am upgrading from the Access XP to Access 2007 interop.

[quoted text clipped - 10 lines]

Post by smthomas
Thanks for any help.

[smthomas via DotNetMonster.com]

Thanks Mary,
I would consider SQL reporting except that this architecture we build has
been in place for a number of years and is very successful. It also supports
about 800 reports that would have to be rewritten. Not something we have the
time to do at this point.

Thanks for your efforts.

Post by Mary Chipman [MSFT]
Have you considered using SQL Server Reporting Services instead of
Access? It's designed to handle this sort of business problem. As far
as troubleshooting goes, the only thing I can think of at the moment
is to isolate the point of failure and try to simplify a repro case
with OutputTo. You need to figure out if the problem is in Access
itself or some other issue with interop.
--Mary

Post by smthomas via DotNetMonster.com
Thanks for the Reply Mary,
I have a VB application that generates reports in batch process as a windows

[quoted text clipped - 259 lines]

Post by smthomas via DotNetMonster.com

Post by smthomas
Thanks for any help.

[Mary Chipman [MSFT]]

Gotcha -- If it does end up looking like it's an Access issue, I'd try
posting in one of the Access newsgroups where there are more Access
experts hanging out. I can definitely understand not wanting to
rewrite 800 reports :)

--Mary

On Fri, 02 Oct 2009 17:34:10 GMT, "smthomas via DotNetMonster.com"

Post by smthomas via DotNetMonster.com
Thanks Mary,
I would consider SQL reporting except that this architecture we build has
been in place for a number of years and is very successful. It also supports
about 800 reports that would have to be rewritten. Not something we have the
time to do at this point.
Thanks for your efforts.

Post by Mary Chipman [MSFT]
Have you considered using SQL Server Reporting Services instead of
Access? It's designed to handle this sort of business problem. As far
as troubleshooting goes, the only thing I can think of at the moment
is to isolate the point of failure and try to simplify a repro case
with OutputTo. You need to figure out if the problem is in Access
itself or some other issue with interop.
--Mary

Post by smthomas via DotNetMonster.com
Thanks for the Reply Mary,
I have a VB application that generates reports in batch process as a windows

[quoted text clipped - 259 lines]

Post by smthomas via DotNetMonster.com

Post by smthomas
Thanks for any help.