Trying to export to Excel

[nicknameoscar]

Lets forget about the code right now. Can you open the query by double clicking on its name in the Access 2007 user interface? If it opens does it give any records?



Can you zip up a version of your database that doesn't contain any private or personal info (but the query will still return data) and attach it here?

[BigCat]

I am able to view the query with records in it when I double click it.

[nicknameoscar]

Sorry, I am totally unfamiliar with ADP. My apologies if I have wasted your time.

[BigCat]

Here is my final solution that finally works in case anyone else is having this problem and they come across this thread.

Private Sub ExportMPS_Click()
DoCmd.RunSavedImportExport "Export-MonthlyProductionSummary"
 
End Sub

[NoellaG]

Hi,

what's the purpose of the statement DoCmd.OpenQuery stDocName?

Is the query "Export-MonthlyProductionSummary" an Access query (then you're working with an mdb or accdb file) or a procedure written on the back-end SQL server (then you're working with an .adp project)?
In the first case you don't have to open it prior to exporting the report based on it, just use the TransferSpreadsheet method.

In the latter case you run the procedure on the backend using the EXEC command and the OutputTo acStoredProcedure methode as in my code example. (Transferspreadsheet could work there, but in adp projects I always use the OutputTo method which I'm sure works fine)

So all depends if you're working in a standard .mdb/.accdb Access file linked through ODBC to the back-end, or an .adp project Access on SQL server.

greetings
NG