Transfer spreadsheet error

[anna_717717]

Hi All,

I've tried the following code from KDSnell to try and export some data
(substituting my own query and field names):

Dim dbs As DAO.Database
Dim qdfTemp As DAO.QueryDef
Dim strSQL As String, strQDF As String
Set dbs = CurrentDb

' Replace NameOfTableOrQuery with the real name of the table or query,
' replace NameOfForm with the real name of the form, and replace
' ADateControlOnForm and AnotherDateControlOnForm with the real names
' of the controls on that form
strSQL = "SELECT NameOfTableOrQuery.* FROM NameOfTableOrQuery " & _
"WHERE NameOfTableOrQuery.FieldName >= " & _
Format(Forms!NameOfForm!ADateControlOnForm.Value,"\#mm\/dd\/yyyy\#") & _
" And NameOfTableOrQuery.FieldName <=" & _

Format(Forms!NameOfForm!AnotherDateControlOnForm.Value,"\#mm\/dd\/yyyy\#") &
"';"

strQDF = "_TempQuery_"
Set qdfTemp = dbs.CreateQueryDef(strQDF, strSQL)
qdfTemp.Close
Set qdfTemp = Nothing

' Replace C:\MyFolderName\MyFileName.xls with the real path and filename for
the
' EXCEL file that is to contain the exported data
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strQDF,"C:\MyFolderName\MyFileName.xls"

dbs.QueryDefs.Delete strQDF
dbs.Close
Set dbs = Nothing



I can the see that the query has been created however i get this error
message:
The Microsoft Jet database engine could not find the object <_TempQuery_>.
Make sure the object exists and that you spell its name and the path name
correctly. (Error 3011)

What am i doing wrong?
Any help much appreciated.

 

[Roger Carlson]

Well, this looks like Ken's code and it looks fine to me. What does your
code look like?

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[anna_717717]

Hi, this is my code:

Dim RetValue As Integer
RetValue = MsgBox("Do you want to export this data?", vbOKCancel,
"Question")
If RetValue = 1 Then

Dim dbs As DAO.Database
Dim qdfTemp As DAO.QueryDef
Dim strSQL As String, strQDF As String
Set dbs = CurrentDb

strSQL = "SELECT qryPSetFinalAndBatchResults2.* FROM
qryPSetFinalAndBatchResults2 " & _
"WHERE qryPSetFinalAndBatchResults2.TestDate >= " & _
Format(Forms!frmPSetFinalAndBatchResults!txtStartDate.Value,
"\#dd\/mm\/yyyy\#") & _
" And qryPSetFinalAndBatchResults2.TestDate <=" & _
Format(Forms!frmPSetFinalAndBatchResults!txtEndDate.Value,
"\#dd\/mm\/yyyy\#") & ";"

strQDF = "_TempQuery_"
Set qdfTemp = dbs.CreateQueryDef(strQDF, strSQL)
qdfTemp.Close
Set qdfTemp = Nothing

' Replace C:\MyFolderName\MyFileName.xls with the real path and
filename for the
' EXCEL file that is to contain the exported data
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strQDF, "C:\Documents and Settings\My Documents\Test1.xls"

dbs.QueryDefs.Delete strQDF
dbs.Close
Set dbs = Nothing

End If

 

[Roger Carlson]

I don't see anything wrong here either. You say you can see the query is
created. Can you open it?

Maybe Access is confused by the underscores in the name. It shouldn't, but
it's possible. Try naming the query something else like just plain
"TempQuery". That's a long shot.

It's also possible that there is some corruption in your database and this
is just how it's manifesting. Try compacting and repairing. Make a backup
first.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[anna_717717]

Thanks for the prompt response - looked like it was corruption in the
database - compacting and repairing worked!