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
Mode=Read
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!
___________
All I can say is that I appreciate that you shared this. I’m explaining to my client why a database may be what they’re looking for (they’re on the learning fast track as about two weeks ago they had never heard of pivottables), and as part of that I prepared an tiny Access DB with a couple of tables allong with a MSQuery and an Excel based query. What you wrote may help me prevent unnecessary frustration and time.
Thanks Bob! I hope you don’t encounter the problem (only affects some types of queries), but at least you’ll have a solution to try if you do hit it.
Ok this is great doing the train application between the Access dB and the Excel I now see why we have had to close the Excel sheet when we wanted to add some more data to the dB. However, sorry I’m in a Duh mode, how do you change or find the “Mode=Share Deny Write” – I changed the setting to – “Mode=Read” string or info.
Fred, I added another section to the blog post, to show where the connection string information is located.
Hi Debra — I am trying to create a very basic Access DB for a new role I have inherited “Domain Management” and my friend Andy Pope recommended that I reach out to you for direction. The main categories would be: Domain Name, Registrar (name, URL, address/phone), Region, Domain Extension, Purchase/Renew Date, Expiry Date, Costs, Invoice Date, Invoice Number, Purchase Order Number, Payment Date, and Domain Regional Register (DRR), Primary DNS#, Secondary DNS#, Tertiary DNS#, Hosted at (Registrar or Monsanto) check box may be?) do you have something basic for these simple items? Thank you so much! Flo
Hi Flo,
I don’t have a sample database that’s similar to what you described, but you could take a look at the sample databases and templates that come with Microsoft Access. Even if there’s nothing for domain management, you’ll get some ideas on how to set it up. There are Microsoft forums where you can ask questions if you get stuck, and someone will probably be able to help.
Hi Debra,
I have this problem, but am using Excel 2003. I can’t seem to find the connection strings in 2003. Please help
Mim, you could try Ron Coderre’s Pivot Play add-in, to see and edit the connection strings:
http://www.contextures.com/xlPivotPlayPLUS01.html
Thank you! I had 5 connections in one workbook and just one of them was marked as Share Deny Write, and I couldn’t figure out what the problem was! Your post helped save me hours of frustration.
You’re welcome! Thanks for letting me know that it helped.
Cheers Debra, you just help me fix a problem. Thanks for sharing.
Jason
I had pretty much the same issue you had. I was suspecting that the mode in the connection string was the culprit. Thanks for confirming that it works with mode=read.
Saved me some more frustration.
Mike
You are an ANGEL! I love posts like this. We have a database that is accessed constantly when customers log in and download content. When I created an Excel file that connected to use my Access, it locked out the database so it wouldn’t log customer downloads anymore. I saw the mode in the connection string and had no idea what value I wanted in there. This was the solution to my problem. Thanks a million!
Hi Deb,
I just encountered this “read-only” problem with my access 2007 database, while i was working on my visual basic, but at the same time, i had my microsoft word opened and it’s been opened for a couple days.
by the way, i don’t seem to find the “data tab on the ribbon” you wrote about. where’s that located please?
Thanks for your help in advance!
Tom