If you use the From Access button on the Data Ribbon to create External Data, it uses OLEDB to link the data. In the bad ol’ days it used ODBC. Almost always when I bring data from Access to Excel, I need to open Access to fix the query or do something different. If the Excel workbook is still open, I get this unfriendly message:
This database has been opened read-only. You can only change data in linked tables. To make design changes, save a copy of the database
For some reason, the connection string created by Excel includes Mode=Share Deny Write and that causes the read-only message.
As far as I know, Excel can’t write data back to Access via the user-interface. I’d be happy to be proven wrong on that point – and very surprised. So why not just make the connection string read-only?
Fortunately, you can edit the connection string right there in the Connection Properties dialog. I changed the Mode to Mode=Read, and it fixes the problem.
If the database is open in Access at the time I create the external data table, it prompts me for data link properties. On the Advanced tab, I can change the mode. So I guess that’s easier than going through the connection properties after the fact.
Sadly, there’s no specific event for adding an external data table. If there were, I could change that setting for every new table added. I could use the Worksheet_Change event, but I don’t want that code running for every change in every worksheet. That would be crazy. So I’m stuck fixing it manually. And by manually, I mean clicking a button.
Dim qt As QueryTable
On Error Resume Next
Set qt = ActiveCell.ListObject.QueryTable
If qt Is Nothing Then Set qt = ActiveSheet.ListObjects(1).QueryTable
On Error GoTo 0
If Not qt Is Nothing Then
qt.Connection = Replace(qt.Connection, "Mode=Share Deny Write", "Mode=Read")
If the active cell is in a listobject that has external data, assume that’s the one to change. If not, assume we want to change the first one on the active sheet. If neither of those things work, don’t do anything, otherwise replace the Mode section of the connection string. Here’s the workflow:
- Create an external data table
- Open the database in Access
- Curse yourself for forgetting to change the connection string
- Run the above macro
- Re-open the database in Access