Transfer Spreadsheet Command Not Seeing Query Object Created with Code

[orcinus]

I have included the below code that seems to run fine up until the export of the table. When the code gets to the transfer spreadsheet command, I get a run time error (3011) error saying:

"The Microsoft jet engine database could not find the object "CHK". Make sure the object exists and you spell its name and the path name correctly"

When I click debug, I am sent to the docmd.transferspreadsheet line (as below). Then when I check the Queries tab to ensure that "CHK" exists, it shows as existing. I can even open it and see the results of strSQL (based on the selection of value in a drop down box in a form).

Can anyone assist in what I am missing? Is there some kind of refresh command that I need to complete to make Access see the query I created in the code?

Thanks for any assistance..


Private Sub cmd_get_data_by_req_email_Click()
Dim strReqEmail As String
Dim strSQL As String
Dim tblA As String
Dim tblB As String
Dim strFN As String
Dim strFP1 As String
Dim strFP2 As String
Dim strFE As String
Dim strFQFP1 As String
Dim strFQFP2 As String
Dim str_folder_exp As String
Dim str_msg As String
Dim str_msg_maaps_complete As String
Dim d As Database
Dim qdf As QueryDef
Dim qdfName As String

qdfName = "CHK"
strReqEmail = Me.cbo_req_email
tblA = "tbl_pr_assoc_archive"
tblB = "tbl_job_pr_aa_code_assign"
Set d = CurrentDb

strSQL = "SELECT " & tblA & ".assoc_id, " & tblA & ".assoc_abs_id, " & tblA & ".req_email, " & tblA & ".eff_dt, " & tblA & ".exp_dt, " & tblB & ".hn_job_desc, " & tblA & ".type_of_entry, " _
& tblA & ".update_dt, " & tblA & ".update_by, " & tblA & ".run_dt " _
& "FROM " & tblA & " INNER JOIN " & tblB & " ON " & tblA & ".pr_code_id = " & tblB & ".pr_code_id " _
& "WHERE (((" & tblA & ".type_of_entry)='PR') AND ((" & tblA & ".req_email) = '" & Me.cbo_req_email & "'));"

strFN = "Rpt_Profile_Data_By_Req_Email"
strFE = ".xls"
strFP1 = "i:\"
strFP2 = "i:\"
strFQFP1 = strFP1 & strFN & strFE

Set qdf = d.CreateQueryDef(qdfName, strSQL)

If chkDriveFolderExists Then
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, qdfName, strFQFP1, -1
Else


DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, qdfName, strFQFP2, -1
End If


End Sub

[orcinus]

OK, it turns out that the querydef object was not the issue, even though the error appears to lead to that conclusion. What it was is that for some reason, I cannot create a file to the root of the drive I've listed as "I". I can however create the file to I:\SomeSubFolder\". Very strange and misleading error, although the last part of that error is what got me thinking in that direction: the "Make sure the object exists and you spell its name and the path name correctly" even though the path it refers to does not appear to be the path where you want the file exported, its specifically named as the object path which is I suppose is used when you have a link to an external object as your export source object. Weird stuff..

[RuralGuy]

Thanks for posting back with your success and your solution. I marked this thread as Solved for you.