Code running slow or not at all

[mpm32]

Hi, I have a module that runs when a user clicks a button on a form. They have to choose a begin date and end date to run a report. Lately they have been getting an error "The command or action 'OutputTo' isn't available now. When I run the report it works for me. They run the DB off of citrix.

I started looking at the code to figure out what is causing it to run so slow and to generate the error. If you look at the code, I have an update query do a calc and add this value to a table. I think this is slowing down the code as it is going through all 65,000 records and recalculating for that field. I know this is not good practice but I'm not sure how to fix it.

Any thoughts?

 ExportMonthly
'
'------------------------------------------------------------
Function ExportMonthly()
On Error GoTo ExportMonthly_Err

    DoCmd.SetWarnings False
    DoCmd.OpenQuery "TimeCalcQuery", acNormal, acEdit
    DoCmd.OutputTo acQuery, "MonthlyQuery1", "", "", False, ""
    DoCmd.Close acQuery, "MonthlyQuery1"
    DoCmd.OpenForm "MainMenuForm", acNormal, "", "", , acNormal


ExportMonthly_Exit:
    Exit Function

ExportMonthly_Err:
    MsgBox Error$
    Resume ExportMonthly_Exit

End Function

In advance, thanks for any guidance you can provide.

 

[vbajock]

OutputTo is real flaky, it has different parameter orders for different versions of Access, IIRC. I'd first try making sure I've got all my data flushed to disk, it might be a timing issue since you are running it over Cytix. To do so add these line at the beginning of your function:

Dim wrkDefault As Workspace
Set wrkDefault = DBEngine.Workspaces(0)

I'd then bracket this query with something that forces a disk flush, like so:

wrkDefault.BeginTrans

DoCmd.OpenQuery "TimeCalcQuery", acNormal, acEdit

wrkDefault.ComitTrans

That way VBA would not try to execute your OutputTo command until all data has been successfully flushed to disk.

 

[mpm32]

Thanks, I'll give it a try.