1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Access 2007 / Excel 2007 - Linking to a database

Discussion in 'Business Applications' started by draytond, Jun 5, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. draytond

    draytond Thread Starter

    Joined:
    Jul 20, 2011
    Messages:
    220
    I'm trying to get the results of Access queries to be linked to an Excel workbook. I've been using the Data tab in Excel, clicking the "From Access" button in the upper left, and choosing the Access database. Then it asks what query I want to link.

    The problem arises when I try to link a specific query, which we'll call qry_x. This query is based on several other queries, one of which is qry_z. Frustratingly, when I try to link qry_x to the Excel workbook, I get an error message that says that Access can't find qry_z, and suggests maybe I spelled the name incorrectly.

    However, qry_z definitely exists as part of the database, and when I run qry_x (which is based on qry_z) in Access, the correct results are displayed with no error.

    Note: qry_x is the only query this is happening to. I can link any other query with no problem at all, including qry_z!!!

    What could be going wrong?

    Thanks in advance.
     
  2. draytond

    draytond Thread Starter

    Joined:
    Jul 20, 2011
    Messages:
    220
    Well, I think I found out what the problem is: I can't link queries that display running totals. If anyone can tell me how to fix this, I'd appreciate it, otherwise I think I'm just going to have to resort to copy/pasting the data from Access into Excel every time I need the info...
     
  3. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,686
    Rather than copy and paste have a look at at the Docmd.Output function, which should allow you to send the query results to a specific Excel sheet and Range.
     
  4. draytond

    draytond Thread Starter

    Joined:
    Jul 20, 2011
    Messages:
    220
    Ok, so I can see how it would export the results of a specific Excel sheet, but not a specific range within that sheet. Can you point me to a good reference for this method? None that I can find mention this functionality... or maybe I'm misinterpreting it.
     
  5. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,686
    Access 2007 is CR*P where Help is concerned, plus the fact it defaults to using Macros instead of VBA, it is no wonder so many new users struggle to get any advanced stuff done.
    Access 2003 has a really good VBA Editor Help.
    Having said that I did misdirect you, it should have been
    Docmd.transferspreadsheet if you want to output to a particular range in Excel.
     
  6. draytond

    draytond Thread Starter

    Joined:
    Jul 20, 2011
    Messages:
    220
    Hm, it keeps telling me that the query being exported has to be updatable... what's that mean? I can't find an answer online anywhere. Here's my syntax:

    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "chart_almost_all_docket_data", "S:\Board meetings\Docket Project Graphs.xlsx", True, "Test$A1:M15"

    And here's the query being exported:


    SELECT CHART_new_or_saved_acres_final_cumulative.Year, CHART_new_or_saved_acres_final_cumulative.[Cumulative Marine Acres], CHART_new_or_saved_acres_final_cumulative.[Cumulative Terrestrial Acres], CHART_new_or_saved_acres_final_cumulative.[Cumulative New Acres], CHART_marine_terrestrial_total_by_year_and_running.[Terrestrial Cumulative], CHART_marine_terrestrial_total_by_year_and_running.[Marine Cumulative], CHART_marine_terrestrial_total_by_year_and_running.[Total Projects], CHART_existing_acres_final.[Cumulative Existing Terrestrial Acres], CHART_existing_acres_final.[Cumulative Existing Marine Acres], CHART_existing_acres_final.[Total Existing Acres], CHART_duration_final.[Average Duration], CHART_average_cost_per_acre.[Average Cost Per Terrestrial Acre], CHART_average_cost_per_acre.[Average Cost Per Marine Acre]
    FROM (CHART_marine_terrestrial_total_by_year_and_running INNER JOIN CHART_new_or_saved_acres_final_cumulative ON CHART_marine_terrestrial_total_by_year_and_running.Year = CHART_new_or_saved_acres_final_cumulative.Year) INNER JOIN ((CHART_existing_acres_final INNER JOIN CHART_duration_final ON CHART_existing_acres_final.Year = CHART_duration_final.Year) INNER JOIN CHART_average_cost_per_acre ON CHART_duration_final.Year = CHART_average_cost_per_acre.Year) ON CHART_marine_terrestrial_total_by_year_and_running.Year = CHART_existing_acres_final.Year;


    Note: The reason I set the commend to acImport instead of acExport is because according to Microsoft Support, the command will fail if you specify a range while exporting. The range only works with acImport.
     
  7. draytond

    draytond Thread Starter

    Joined:
    Jul 20, 2011
    Messages:
    220
    I've been playing with this for hours now and I can't even get it to work in any way unless I use acLink, which isn't what I need. I have an existing Excel document to which I need to send my query results to a specific range on a specific worksheet. I'm just going to have to leave it as copy/paste for now, I guess.
     
  8. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,686
    Can't you just transfer spreadsheet in the Excel sheet at Range A1 (default) and adjust your Graph ranges to suit that location?

    Also there is VBA code to place the data directly in the Cells of a Range which you could use.
     
  9. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,686
    Cracked it, you just have to use a Range Name to get it to export to that range, this worked to export a query to an existing Excel workbook.

    DoCmd.TransferSpreadsheet acExport, , "CMSolderQuery", "c:\Users\A C\Desktop\Report.xlsx", True, "accessquery"

    Where accessquery is the named range in the Excel worksheet.
     
  10. draytond

    draytond Thread Starter

    Joined:
    Jul 20, 2011
    Messages:
    220
    It keeps telling me that the range I specified already exists. Here's the code I'm using:

    DoCmd.TransferSpreadsheet acExport, , "chart_almost_all_docket_data", "S:\Board meetings\Docket Project Graphs.xlsx", True, "Test2!D6:J17"

    ...where Test2 is the speadsheet in the Excel document "Document Project Graphs.xlsx". The query being exported is chart_almost_all_docket_data.
     
  11. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,686
    Did you read my post, you name a range in the Excel workbook using Excel and then refer to it using Access
    DoCmd.TransferSpreadsheet acExport, , "CMSolderQuery", "c:\Users\A C\Desktop\Report.xlsx", True, "accessquery"

    You do not use the sheet name as Excel stores the name location independent of the sheet name.
     
  12. draytond

    draytond Thread Starter

    Joined:
    Jul 20, 2011
    Messages:
    220
    Oh I'm sorry, I didn't understand what named ranges were. After looking it up here I was able to do what you were suggesting.

    IT WORKED!!! Thank you so much OBP.
     
  13. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,686
    It took us a while, but we got there in the end. (y)
     
  14. draytond

    draytond Thread Starter

    Joined:
    Jul 20, 2011
    Messages:
    220
    Ok, hold on a second, major problem with this approach (or maybe it's a problem with Excel?).

    So this approach works great if you use it once. But then when the data is exported to the named range, it appears to overwrite the range, because the named ranges disappear, preventing the functionality from working properly again the next time. If I try to run it again, it'll instead make new spreadsheets in my workbook that are named what the named ranges used to be named.
     
  15. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,686
    How about a macro to reset the named range?
    I am not sure if Access VBA can reset the name for you, imagine it can, but I have never tried it.
    Do you need to overwrite the data or can you just update the link to the query?
    Or use a Master Workbook and save the Updated workbook with a new name, I know Access can do that.
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1055957

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice