We help IT Professionals succeed at work.

Excel file as ADODB says Read Only

1,821 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

Comment
Watch Question

x-menIT super hero
CERTIFIED EXPERT

Commented:
"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?
Bryce BassettFreelance VBA programmer

Author

Commented:
I did reboot and the problem persists.    Any other suggestions?  Is my syntax otherwise in good shape?

Thanks
Freelance VBA programmer
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Bryce BassettFreelance VBA programmer

Author

Commented:
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.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.