DoCmd.TransferSpreadsheet - ACCESS doesn't see my query

[bifteki]

I have the following line in my code:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"sp_export_persons_to_excel", "W:\DOCS\DRAFTS\eortazontes.xls", True, "A1:
G12"

sp_export_persons_to_excel is a stored procedure.
However Access tells me that it "can't find the object
'sp_export_persons_to_excel' ".
I tried replacing the string "sp_export_persons_to_excel" with the names of
other queries in my DB, but I get the same message.

Does anyone know why this may occur?

 

[MSAccessRookie]

I have the following line in my code:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"sp_export_persons_to_excel", "W:\DOCS\DRAFTS\eortazontes.xls", True, "A1:
G12"

sp_export_persons_to_excel is a stored procedure.
However Access tells me that it "can't find the object
'sp_export_persons_to_excel' ".
I tried replacing the string "sp_export_persons_to_excel" with the names of
other queries in my DB, but I get the same message.

Does anyone know why this may occur?

I am not an expert in the DoCmd.TransferSpreadsheet command, but the parameter "sp_export_persons_to_excel" is expected to be a "Table". I assume that means it is also supposed to be an object that you have Write Access to. Is it even possible to use a Query, Function, or Procedure in that context?

 

[DCrake]

In Access, you can't use an SQL statement to specify data to export when you arer using the TransferSpreadsheet action. Instead of using an SQL statement, you must first create a query and then specfiy the name of the query in the Table Name argument.

CodeMaster:

 

[MSAccessRookie]

In Access, you can't use an SQL statement to specify data to eport when you arer using the TransferSpreadsheet action. Instead of using an SQL statement, you must first create a query and then specfiy the name of the query in the Table Name argument.

CodeMaster:

So you can use a query? I am not familiar with write accessible query results, only insert and append queries, which I assumed did not apply here since the datatype was table.

 

[bifteki]

Thank you both for your answers.

to DCrake:
sp_export_persons_to_excel is a stored query, not an SQL statement.
Despite that, Access tells me that it can't find it.

 

[DCrake]

Can you send a sample of the mdb please.

 

[MSAccessRookie]

Thank you both for your answers.

to DCrake:
sp_export_persons_to_excel is a stored query, not an SQL statement.
Despite that, Access tells me that it can't find it.

Isn't that because of the fact that the function wants the name of a table and you are supplying the name of a procedure? I think it is saying that it cannot find a table with that name.

Of course, I stand corrected in the event that they are interchangeable for this purpose, although so far I have found no useful examples showing how they would be.

 

[chergh]

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"sp_export_persons_to_excel", "W:\DOCS\DRAFTS\eortazontes.xls", True, "A1:
G12"

You can't specify a range when using TransferSpreadsheet to export to excel, it's an import only argument.