Query update fails

[Guest]

I have a form that a user picks date criteria and then clicks a command
button to update the query, verify that the query returns data and if it does
return data export the results to an Excel workbook and generate a pivot
table.

My problem is that if I set the startup option to hide the database window
the module fails and produces error #2046, when the database window is
visible the module runs w/o any problems. I've verified that the Excel
automation object is correctly addressed in the code and I've not had any
instances of multiple instances of Excel running.

Is it possible that the culprit is the DoCmd.OutputTo statement which is
what I'm using to export the query data to Excel?

Thanks ahead of time...

 

[Guest]

I don't know if the output to is the problem, but in any case, I would
recommend changing it to use the TransferSpreadsheet method instead.

 

[Guest]

This sounds rather wonky, but I've gotten the code to run by redisplaying the
database window, updating the query and creating the pivot table and then
rehiding the database window. With screen updates suppressed the user won't
really notice but it's a duct tape w/chewing gum kind of solution.

If anyone has any information I would appreciate whatever feedback comes my
way.

 

[Guest]

Thanks for the reply. By the time that I had read your post I had used the
TransferSpreadsheet method, which produced the same results. My wonky fix
was to redisplay the database window, run the code and after completion hide
the database window. With screen updates suppressed the user won't see any
of that but it seems to Frankenstein like approach to fixing the problem.

Thanks again for the tip...

 

[Guest]

That should not be necessary. Please post the code of the procedure where
you are doing the Transfer. Maybe there is something there we haven't seen
yet.

 

[Guest]

Thanks, here's the code.

There's are several constants used here, one is for the query, which is used
by several routines and the other is for the name of the Excel output file.

************************************************************
Function UpdatePivotQry(DateStart As Date, DateEnd As Date) As Boolean

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim blnProceed As Boolean

On Error GoTo Err_UpdatePivotQry
'UPdate SQL statement with the 2 date arguments

strSQL = "SELECT tblEmployeeData.EmpID, " & _
"tblEmployeeData.ClerkID, tblErrorTracking.Date, " & _
"tblErrorTracking.Error_Type, " & _
"tblErrorTracking.Error_Description FROM " & _
"tblEmployeeData INNER JOIN tblErrorTracking ON " & _
"tblEmployeeData.EmpID = " & _
"tblErrorTracking.EmpIDMadeError WHERE (((" & _
"tblErrorTracking.Date)>=#" & DateStart & "# And (" & _
"tblErrorTracking.Date)<=#" & DateEnd & "#));"
'Initial database object variables
Set dbs = CurrentDb()
Set qdf = dbs.QueryDefs(conXLQry)

'Update query
With qdf
.SQL = strSQL
.Close
End With

'Run custom function to verify that the query returns data
'and if not display message
blnProceed = HasData(conXLQry)
If Not blnProceed Then
MsgBox "There were no records returned for " & DateStart _
& " through " & DateEnd & "."
GoTo Exit_UpdatePivotQry
Else
'Otherwise update stat bar message and output query data to to Excel
With DoCmd
.Echo False, "Outputing query result to an Excel " & _
"workbook file, please wait..."
.OutputTo acOutputQuery, conXLQry, acFormatXLS, _
conXLSource
End With
End If

Exit_UpdatePivotQry:

'Release object variables and return True/False
'which determines whether or not the pivot table
'module runs
Set dbs = Nothing
Set qdf = Nothing
UpdatePivotQry = blnProceed
Exit Function

Err_UpdatePivotQry:

'Run generic error trap routine
ErrTrap Err.Number, conMod, "UpdatePivotQry", True
Err.Clear
blnProceed = False
Resume Exit_UpdatePivotQry

End Function

 

[Guest]

I don't see anything obvious, but you may try commenting out the Echo
statement to see if that has any effect. If it cures the problem and you
still want to try to use it, I would try the changing it to use
Application.Echo The Docmd. version is only there for backward
compatibility. (see VBA Help for info)

 

[Guest]

Thanks, I'll give it a go...
--
Kevin Backmann

I don't see anything obvious, but you may try commenting out the Echo
statement to see if that has any effect. If it cures the problem and you
still want to try to use it, I would try the changing it to use
Application.Echo The Docmd. version is only there for backward
compatibility. (see VBA Help for info)