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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
Sub MakeExternalDataReadOnly() 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") End If End Sub |
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
I also set the PersistentConnection property to False.
I want it to refresh the data, then drop the connection.
“Excel can’t write data back to Access via the user-interface.”
Actually it can
Try this : ALT+D+D+N (make sure “Use Query Editor to edit/create queries” is ticked)
Choose MS Access Database
Navigate to the folder containing the database select it and say open (make sure “Read only” and “Exclusive” are un-ticked”)
Select the Table/Query from which you want to pull the data
Say Next, Next, Next, in the final step of the dialog choose “View Data…”
In the Query Editor – in the Menu “Records” – Choose – “Allow Editing”
Any change that you now do in the records in the data preview section of the Query editor are written back in the the database
Notwithstanding Sam’s point (which was pretty interesting) I think the original observation is valid: given that we generally use data connections to get data OUT of Access it seems obtuse that the default connection mode should be ‘Share Deny Write’ instead of ‘Read’.
That said, I’ve noticed that if I have some other process in the spreadsheet (say a macro) that writes data from Excel to the database, Excel will sometimes ‘re-set’ the connection mode back to ‘Share Deny Write’ with the corresponding problem of the database opening in read-only mode. This can be annoying. A possible solution – not fully tested yet – is to set the mode to ‘Share Deny None’ which still permits writing back to the DB but doesn’t lock it up.