Is it possible to add a data connection to an existing table in Excel?

2

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).

Stephen Jennings

Posted 2012-12-28T23:28:36.180

Reputation: 21 788

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 – 2012-12-31T20:43:18.557

@CharlieRB It's not an ODBC connection, the connection type is "Microsoft Access Database" (the provider is "Microsoft.ACE.OLEDB.12.0") – Stephen Jennings – 2013-01-04T22:46:55.507

Answers

-1

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.

Source

user474423

Posted 2012-12-28T23:28:36.180

Reputation: 1

How does this relate to the question? What is ODC? – suspectus – 2015-07-28T17:52:35.800

Asked: 2012-12-28T23:28:36.180

Viewed: 4 154 times

Active: 2015-07-28T21:17:17.640