Access connection in Excel no longer refreshes

[willhouston]

I don't know if this belongs in the Excel or Access forum, so I will start here.

I have numerous queries in an Access database that I have linked to Excel (versions 2013). This workbook was created about 1 month ago. When I add data in Access such that a new row shows up in the query, that addition is not reflected in the Excel tables. More troubling, when I right click on the tables in Excel, the "Refresh" option is grayed out. Hitting Data->Refresh All does nothing. When I select the connection in Data->Connections and click the "refresh" drop down menu, the same issue is seen: "Refresh All" is the only available option, with all the other options grayed out. In fact, when my active cell is within the table, all the usual options on the Data and Design tabs are grayed out (refresh, export, properties, etc.).

I have linked the same Access query to the same Excel sheet in the exact same way (Data->From Access), and the new row shows up in the new query. I can then copy all the properties from the new connection to the old, broken connection, and still... nothing works.

I have even copied the Excel workbook and Access database to a new location, relinked the connection, and the options are still grayed out.

I do not have a good revision control to see what changes have been made to the database since the Excel sheet was created. I do know I have made a few changes, none of which affected the queries in Access. The query I am looking at in particular does still work within Access. There are probably a dozen other connections in the Excel workbook, and none of them work either.

On the bright side, I built the workbook in such a way that all I have to do is clear and re-establish the connections, but I really don't want to have to do that every time I add or modify data.

Any ideas?

 

[Micron]

I find your terminology confusing, thus cannot be sure what you're saying.
- You have what linked to Excel - tables in Access as linked tables to spreadsheets?
- don't know what Excel "tables" are - maybe linked tables as noted above?
- when my active cell is within the table - Access tables don't have "cells", just fields and rows
- When I add data in Access such that a new row shows up in the query add to what? Tables linked to spreadsheets? You're not supposed to do that.
- I have linked the same Access query to the same Excel sheet You are using Automation to open Excel to get data? Or again, is this a table linked to a spreadsheet?
Maybe this will help: tables in Access linked to spreadsheets only pull in the data, and you shouldn't alter it within Access. You can query it of course, and you should be able to refresh manually. However, just making the linked table in Access the active window will refresh the data even if the workbook has not bee saved (as long as cells are not being edited). If in Access you are not seeing changes in tables that are linked to spreadsheets, then I suspect there is an issue with your connection. Check the link path in Access and ensure there have been no changes in the workbook location or network permissions that don't allow data transfer (assuming you're saying this used to work).

I don't know if this belongs in the Excel or Access forum, so I will start here.

Also, if you cross-post, be sure to declare it in each thread. It's annoying to spend time answering posts that have activity elsewhere at the same time.

 

[willhouston]

Thanks for your response. I went through the Excel workbook and redid all the connections, so this issue has been resolved for now. If it happens again, I will probably post it in the Excel forum with a link to this thread, as it seems to be more of an Excel issue. I'll do my best now to explain my terminology so that if you do recognize the problem, perhaps you can explain.

- You have what linked to Excel - tables in Access as linked tables to spreadsheets?

I have Queries on Tables in Access. When linking to those tables in Excel, they are inserted into "Tables" - an Excel object. For example, I have a table of Zip Codes in Access that I have run a query on to pull out certain Zip Codes (ones being mailed to). When I link to that Query in Excel it is inserted as a Table (Excel Object), as all Access connections within Excel are.

- when my active cell is within the table - Access tables don't have "cells", just fields and rows

This is referring to the Excel Table as mentioned before - whoops. When the Excel active cell is within the Table (Excel Object) that contains the data pulled from Access, I am not getting any useful options - like refresh or connection properties.

- When I add data in Access such that a new row shows up in the query add to what? Tables linked to spreadsheets? You're not supposed to do that.

The Excel workbook I am referring to here has nothing to do with data entry; it pulls data from an Access Database and analyzes it. All entry is done in Access. For example, imagine I add a new zip code to the Access Table (and Query, which is what I am actually connecting to in Excel). If I were to refresh the connection in Excel, that new zip code would not show up. What I am saying here is just a symptom of the bigger problem - Excel connections are not refreshing.

- I have linked the same Access query to the same Excel sheet You are using Automation to open Excel to get data? Or again, is this a table linked to a spreadsheet?

The other way around. I added a new read-only connection in Excel to the same Access query (the one with zip codes mentioned before). With the new connection, everything was working fine. I could add a zip code in Access, go to Excel, hit refresh, and the new record would show up.

Thanks for taking the time to look at this thread,

Will

 

[Micron]

Ahh, OK. I think Excel is driving the process, so my opinion (FWIW) would be to post in Excel because it sounds like you want someone who is proficient in that software as opposed to Access. You could get lucky here and get help from someone who's a guru in both, but I'd hedge my bet on the Excel part of the forum. I don't tend to look at those posts too much because I think my expertise doesn't lie there, although it's not entirely foreign to me either.