TransferSpreadsheet Cannot Find Source Query?

[PeteCresswell]

The following line of code:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qryWhatever", "C:\Temp\Whatever.xls"

is throwing:

Run-time error '3011': The Microsoft Jet dtabase engine could not find
the object 'qryWhatever". Make sure the object exists and that you
spell it's name and the path name correctly."


But the zinger is that qryWhatever exists and can be opened a-ok. I
copied it's name and pasted it under the line of code to compare
letter-for-letter... then I just pasted it right on top of the code's
reference.... so spelling is definately not the problem.

But what is? How come TransferSpreadsheet cannot find that query,
but my eyeballs can?

Anybody been here?

 

[PeteCresswell]

I should add five observations:
----------------------------------------------------------------
1) Needless to say, the problem app has been decompiled/recompiled/
compacted-repaired a number of times to no avail.

2) This isn't happening with just one query.
It's happening with two.

3) Both were working in a prior verison of the app.

4) I just ran the prior version of the app (after re-booting...)
and it ran a-ok.... and the problem version threw the same error.

5) If I open up one of the problem queries and then do "Analyize It
With Microsoft Excel" from the little Excel tool dropdown, it
comes up in Excel a-ok.
----------------------------------------------------------------

My kneejerk is something weird in the DB that Compact/Repair isn't
fixing.

All I can think of is to export each and every object in the problem
DB into a text file, create an empty DB, and then import from all
the text files.

Past experience suggests this will be a half-day project...

Meanwhile, I thought I'd troll for a miracle.... -)

 

[PeteCresswell]

Last one in this wretched excess of replies to my own post.

#6) I created an empty DB, then imported all objects from the problem
DB into it.
No improvement.

 

[Bonnie]

I know your frustrated - been there.

Take a step back and try a couple of things -

Make sure the folder is there and you have access. Try changing the folder
in the code.

Try creating a new query and put that name in the code. Run it and see if
you get the same error.

Try using the table name instead of a query.


Open the query and do export from the FILE Menu item and see if you have the
same problem.

 

[PeteCresswell]

... been there.

Do you recall what the issue was for your own code?

I tried most of what you suggested, but the problem seems tb a moving
target: One query works, then another does not.

My workaround so far has been to disable my error trapping for
the .TransferSpreadsheet commands.

viz:
'
-------------------------------------------------------------------------------------
' Create a second new worksheet in the C:\Temp .XLS that contains
info on securities
' that do not have loss ratio information

2940 If okToProceed = True Then
2941 okToProceed = False

On Error Resume Next
2942 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qrySecurities_LossInfo_Lack_FinalResult", myTempXlsPath
If Err > 0 Then
BugAlert True, ""
End If
On Error GoTo ProjectedCashFlows_Process_err

2943 okToProceed = True
2949 End If

 

[Bonnie]

Were you able to run TrasferSpreadsheet without error against the table (or
each of the tables) without error? Did you recreate the query and try again?

I guess if you must you can ignore the error and go on as your code
indicates but that error is indicative of corruption somewhere so be careful
is doesn't come back to bite you in the butt.

Best -

Bonnie Hicks
http://www.dataplus-svc.com