Export details from an Access 2003.adp database to an Excel spreadsheet - using DoCmd.Transferspreadsheet
Posted on 2007-07-30
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.