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

Posted on 2007-07-30
Last Modified: 2013-12-05
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.
Question by:Adlerm
    LVL 4

    Accepted Solution

    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.
    LVL 4

    Assisted Solution

    Sorry, that is DoCmd.OuputTo
    LVL 4

    Assisted Solution

    DoCmd.OutputTo acOutputQuery, "YOUR_QUERY_NAME", acFormatXLS,"c:\MS\Excel\Statistics.xls"
    LVL 23

    Assisted Solution

    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.


    Author Comment

    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.
    LVL 4

    Expert Comment

    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.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
    Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now