When I tried to open one of my main Access databases today, I got the alarming message, “The database ‘MyData’ is read-only. You won’t be able to save changes made to data or object definitions in this database.”
I closed it, and tried to open it again, making sure that I hadn’t accidentally selected the Read Only option.
A Google search turned up some dire warnings that this could be a sign of corruption, so I created a new database, imported everything, and all seemed well.
So, I renamed the old version, and gave the the new version the same name as the previous one, so my shortcuts and connections would work. And the problem was back.
I looked for errant ldb files, that were mistakenly telling the database someone else had it open. There was nothing.
Then it dawned on me — connections. I had an Excel file open that queried the database, so maybe that was the problem. I closed it, re-opened the database, and it worked fine. If I tried to open the database when the Excel file was open, the database was read only.
Fix the Connection
Well, I don’t have the two files open simultaneously too often, but I wanted to have that option available. So, I checked the connection strings for the queries and found that they included this setting:
Mode=Share Deny Write
I changed the setting to
and the files now live in harmony (so far).
Where Are the Connection Strings?
To find the connection strings in Excel 2007:
- Click the Data tab on the Ribbon, and then click Connections.
- On the Workbook Connections dialog box, click a connection name in the list
- Click the Properties button, to open the Connection Properties dialog box.
- Click the Definition tab, and edit the Connection String. The strings that I edited were for Provider=Microsoft.ACE.OLEDB.12.0 — the Mode setting might not appear in other types of connections.
Why Share This?
This information is probably of no interest to you, unless you landed here in a Google search for “You won’t be able to save changes made to data or object definitions in this database”. But, one day in the future, I might encounter this error again, and maybe I’ll find my own solution when I do a Google search.
Don’t laugh — it’s happened to me before!