Report opening and refreshing inconsistencies through VBA

[TheZZman]

Too anyone willing to help me out, here is my issue.

I've written VBA code to automatically login to BO, open a report/document, refresh it and send the results via the ExportAsText method. (I've posted the code at the bottom)

My issue is this: The opening and refreshing of the report is not only inconsistent, but sometimes errors out. The companies BO software is used to manage data from an Oracle Data Warehouse. There have been some know performance issues, but I don't believe it's what is causing this issue.

The most glaring situation is a user attempts to the pull the data, the report takes extremely long to open, and then when it refreshes, the error:

Error #: -2147417856 Automation error, System call failed

I am just having trouble identifying the reason...the only two lines of code that run are the open and refresh, so it must be one of them. Thing is it doesn't happen every time.

My thought is A) determine why this is happening and fix the problem or B) find a way to schedule the reports nightly (they don't have BCA, by the way)

This is all being done using Access 97. (I had no choice)

One more thing, i don't neccessarily need a solution here, just some thoughts on why this is happening, so that I can effectively direct and advise my client.

Thanks a bunch in advance.

Brad

--------------------------------------------------
Dim Obj_BOApp As busobj.Application
Dim Obj_BODocOrders As busobj.Document
Dim Obj_BORepOrders As busobj.Report

On Error GoTo ErrorHandle
'On Error Resume Next
Set Obj_BOApp = New busobj.Application
Obj_BOApp.Visible = False
Obj_BOApp.Interactive = False
Call Obj_BOApp.LoginAs("MSA1818", "testing", False)

Me!DataUpdateStatus = "Running Orders Report"
Me.Refresh
Me.Repaint

' Run Orders Report
Set Obj_BODocOrders = Obj_BOApp.Documents.Open(OrdersReportName, False, False)
Obj_BODocOrders.Refresh
Me!DataUpdateStatus = "Writing IncomingOrders.txt to network drive."
Me.Refresh
Me.Repaint

' Export Orders Report
Set Obj_BORepOrders = Obj_BODocOrders.ActiveReport
Obj_BORepOrders.ExportAsText ("\\IncomingOrders.txt&quot
'Obj_BORepOrders.ExportAsText ("\\IncomingOrders_" + VBA.CStr(VBA.Month(VBA.Date)) + "_" + VBA.CStr(VBA.Day(VBA.Date)) + "_" + VBA.CStr(VBA.Year(VBA.Date)) + "_" + VBA.CStr(VBA.Hour(VBA.Time)) + VBA.CStr(VBA.Minute(VBA.Time)) + ".txt&quot
Set Obj_BORepOrders = Nothing
Obj_BODocOrders.Close
Set Obj_BODocOrders = Nothing
Obj_BOApp.Quit

Set Obj_BOApp = Nothing

Exit Sub

ErrorHandle:
Call MsgBox("Orders Error#: " + CStr(Err.Number) + Chr(13) + "The following error has occured after attempting to run the Business Object Reports: " + Chr(13) + Err.Description + Chr(13) + "Please try again later and remove the BUSOBJ.EXE from your PC's current processes.", vbCritical)
Obj_BOApp.Quit
Set Obj_BOApp = Nothing
Me!ProcessCheck = "Nothing"
Exit Sub

 

[TheZZman]

Just wanted update this in case anybody else runs into this issue.

It was suggested that I eliminate Access from the refreshing of the reports. I set the report to refresh on open, and put some code in the AfterRefresh even to export the data to text. I'll update this after i've tested it all out...

 

[funkmonsteruk]

I do something very similar to this, but i've got no where near the amount of code your using, dunno if it's any use but here is what i'm using:

Private Sub Document_Open()
Application.BreakOnVBAError = False
Application.Interactive = False

Dim doc As Document
Dim rep As Report

Set doc = Application.Documents.Item(1)
Set rep = doc.Reports.Item(1)

rep.ExportAsText ("File path goes here&quot
Application.Quit
End Sub

I run this overnight and never gat any error issues