Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a table in Excel which has a data connection to an Access database. When the data changes, I can click "Refresh" to re-query the data and update the table.

However, Excel 2008 for Mac doesn't understand this data connection, so if a Mac user edits and saves the file, it strips the data connection from the table. The previous data is still stored in the table, but I can no longer refresh it even from Windows.

Is it possible to re-connect the data connection to the table?

  • I can't just re-create the table because all the references to the table would be lost.
  • I tried converting the whole table to a range, then using "Get external data from Access" on the entire range of cells, but Excel simply shifts the existing data to the right (and all the references in the spreadsheet still reference the original data instead of the new table).
share|improve this question
Does the other person have the same data connection to the database set up? Typically you need to setup an ODBC with MS Access drivers to make the connection. If they don't, it won't work and will drop the connection when they save the file. – CharlieRB Dec 31 '12 at 20:43
@CharlieRB It's not an ODBC connection, the connection type is "Microsoft Access Database" (the provider is "Microsoft.ACE.OLEDB.12.0") – Stephen Jennings Jan 4 '13 at 22:46

I find answer by Treb Gatte @tgatte under

*What you are seeing is a warning that the cached internal ODC no longer matches the external ODC. In an attempt to be "helpful", Excel is giving you the option to break the link to the external ODC. This is usually not what you intended to do. To update an external ODC, make your changes in the SQL query as you've done but click Export Connection File instead of OK on the tab where you updated the query. It will prompt you for a location and name. You can overlay the existing ODC at this point. Once complete, close the Excel file without saving it. Reopen the Excel file. Excel does a version check on refresh between the external ODC and the internal cached ODC information. If the external ODC has changed, it will automatically update the internal ODC cache with the external updated ODC information.


share|improve this answer
How does this relate to the question? What is ODC? – suspectus Jul 28 '15 at 17:52

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .