You receive “Can’t update. Database or object is read-only” error message when you export to Excel in Access 97

It isn’t clear why this error happened. I blame a MS update.

What started happening is I would get that error when exporting Select Query results to Excel 97 format under Access 97.  It didn’t do it for every query in the database, and I could make a table with the query and then export the table to Excel, but the fact that I couldn’t do it direct from the query was a little annoying since I have always done it that way.

I checked and double-checked the read-only settings on the drive, file, etc and nothing was wrong.  So I started troubleshooting.  I did alot of things in the course of my investigation so it’s difficult to know what combo of things did the trick exactly, but if I was going to try one thing to get it working, the first things I would check are these registry keys:

HKLM / Software / Microsoft / Jet / 3.5 / Excel / DisabledExtensions (key)
HKLM / Software / Microsoft / Jet / 4.0 / Excel / DisabledExtensions (key)

These two keys each have a value of !xls on my system, so I deleted it from each.  Things worked after that.

Here are some other things I did which you’re free to try as well:
1. UnInstalled and ReInstalled “Data Access”, “Office Tools”, “Converters & Files” from the CD Setup program – This resulted in Excel being missing from the list of Save As file types.  When I went back to the Setup program it said that the Excel option was “Installed” and the option itself was greyed out and unselectable.
2. A MS webpage led me to try installing the Data Access Pack by itself (goto Office97CD/ValuPack/VALUPK8.HLP and run it, then select Data Access Pack and follow instructions).  Didn’t do anything.
3. A solution for this is to register the dll:  START–>RUN: regsvr32 msexcl35.dll
4. That makes it appear in the list to Save As, but when you save the file, you will get Error 3170: Couldn’t find Installable ISAM.
5. Tried unregistering the dll, registering the msexcl40.dll, changing the DisabledExtensions key for 3.5 to !xlp, reinstalled Data Pack, nothing worked.

6. Completely uninstalled Access 97 and reinstalled into a new directory.  Registered the msexcl35.dll and then ran the Office Setup program again with some switches:  setup /y/r
7. Deleted the values of the Keys listed above and it worked again.

Note:  Another tip that I never had to try is to uninstall Access, rename the Excel dlls and then reinstall it.

Update 10/8/09: I noticed that I could not import a Text file, which I fixed by registering those DLLs… regsvr32 mstext35.dll and regsvr32 mstext40.dll