?
Solved

Excel file as ADODB says Read Only

Posted on 2014-01-09
4
Medium Priority
?
1,518 Views
Last Modified: 2014-01-23
Hi

Working in Word 2010 VBA.  using the method below to read from, and in this case, write to an Excel file using ADODB.  I've used this method countless times with no problem.  But tonight all of a sudden when I reaches the objRecSet.AddNew command, it gives me the error message "Cannot update.  Database or object is read only."  The Excel file is not read only, is not open in Excel, and was properly shut down by my last ADOBD routine.  

What could be causing this?

Set objConnection = New ADODB.Connection
objConnection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DataFile & ";Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=0"";"  'HDR=YES means has col headings
objConnection.Open
Set objRecSet = New ADODB.Recordset

objRecSet.Open TableName, objConnection, adOpenKeyset, adLockOptimistic 

    For myrow = 0 To SaveSystem.ListBox3.ListCount - 1
        objRecSet.AddNew
        objRecSet.Fields(0) = Me.TextBox1.Text
        For mycol = 0 To 4
            objRecSet.Fields(mycol + 1) = SaveSystem.ListBox3.List(myrow, mycol)
        Next mycol
    Next myrow

objRecSet.UpdateBatch
     
objRecSet.Close
objConnection.Close
Set objRecSet = Nothing
Set objConnection = Nothing

Open in new window

0
Comment
Question by:Bryce Bassett
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 18

Expert Comment

by:x-men
ID: 39771361
"The Excel file is not read only, is not open in Excel, and was properly shut down by my last ADOBD routine.  "...still, sounds like it is in use, some how. can you reboot?
0
 

Author Comment

by:Bryce Bassett
ID: 39772556
I did reboot and the problem persists.    Any other suggestions?  Is my syntax otherwise in good shape?

Thanks
0
 

Accepted Solution

by:
Bryce Bassett earned 0 total points
ID: 39791420
I finally realized what was going on here.  I had my installer put files in the "Program Files" folder.  Then when my code tried to update the file there, it was not allowing me to write to that location.  Solution is to keep all files you need to write to in the user's AppData area.
0
 

Author Closing Comment

by:Bryce Bassett
ID: 39802533
This wasn't really an issue with the code itself, but the installation environment.  Not fair to expect the experts to know what was going on with that.
0

Featured Post

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question