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