Export details from an Access 2003.adp database to an Excel spreadsheet - using DoCmd.Transferspreadsheet

I have an Access 2003 .adp frontend database that links to a SQL 2003 backend database. I'm attempting to extract some statistics from the SQL 2003 tablles and export these statistics to an Excel 2003 spreadsheet which in turn are use as a data source for several graphs in a separate worksheet  within the spreadsheet.
I'm using the DoCmd.TransferSpreadsheet, eg
     DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "vw1234_sel_ExportStatistics", _
                                                     "c:\MS\Excel\Statistics.xls", True, "A1:G2"
The vw1234_sel_ExportStatistics is a view on the SQL 2003 backend database but each time I execute the DoCmd.TransferSpreadsheet I keep getting an error message indicating the vw1234_sel_ExportStatistics cannot be found.
I have experimented with using a table on the SQL 2003 backend database holding the desired statistics and replacing the table name for the view name in the DoCmd and everying thing works ok .
Can anyone explain why the view won't work for the DoCmd.Spreadsheet while using a table name works correctly and is there a work around I can use as I really don't want to create a temporty table to hold the statistics I need to export  to the Excel spreadsheet.
 
AdlermAsked:
Who is Participating?
 
Stephen DaughertyCommented:
Try using DoCmd.Output to. Specify the format (acFormatXLS) and the path to the file (including filename. Each time you output it will overwrite the previous file.
0
 
Stephen DaughertyCommented:
Sorry, that is DoCmd.OuputTo
0
 
Stephen DaughertyCommented:
DoCmd.OutputTo acOutputQuery, "YOUR_QUERY_NAME", acFormatXLS,"c:\MS\Excel\Statistics.xls"
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
MalicUKCommented:
Hi Adlerm,

Excel has a function where you can bypass the need for Access altogether. In Excel choose "Data" -> "Get External Data" -> "Create New Database Query" and follow the wizard.

If you right click on the data when it has imported and choose "Query Properties" you can set it to auto refresh when you open the Excel file, amongst other things.

Cheers,
MalicUK.
0
 
AdlermAuthor Commented:
Im not sure the DoCmd.OutputTo will achieve what I want. The excel spreadsheet I want to export my statistics to is madel up of several worksheet - which I need to keep. For example on one worksheet are various graphs which I must keep but which inturn are linked to the statistics in the worksheet I want to export to. I'm under the impression - and I may be wrong - that the DoCmd.OutputTo method deletes the entire original spreadsheet and creates a new spreadsheet which as I said above is not acceptable as I want to maintain my worksheet graphs.
While the method suggesred by MalicK may work it is not the preferred method I wish to adopt for a variety of reasons but I thank you for your suggestion.
I did read a number of responses in the EE knowledge base to an number of similar questions and one response clearly indicated that using a query (or view) in the DoCmd.TransferSpreadsheet was not available for an  .adp database. It suggested using a table instead of using a query (view). I did this and the statistics were exported to the spreadsheet as required.
0
 
Stephen DaughertyCommented:
Understood. For future references, it may be helpful to those offering assistance to have such inforamtion up front. Based on your initial posting, OutputTo seemed adequate. Anyway, Thanks for the C.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.