asked on

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.

ASKER CERTIFIED SOLUTION

Stephen Daugherty

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Stephen Daugherty

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Stephen Daugherty

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

MalicUK

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Adlerm

ASKER

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.

Stephen Daugherty

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.