ADO exporting to Excel- read only error

  • Thread starter Thread starter kfguardian
  • Start date Start date
K

kfguardian

I am trying create a recordset using Microsoft.Jet.OLEDB4.0. My connection
seems to be ok but when i try to do anything with the recordset, it gives me
the error: Cannot Update. Database or object is read only." I checked and
the file is not write protected or anything.

Any ideas on what I am missing?

With oConn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Extended Properties").Value = "Excel 8.0"
.Open "C:\temp\cedc\cedc.xls"
End With

oCmd.ActiveConnection = oConn
oCmd.CommandText = "select * from [sheet1$]"

With oRS
.Open oCmd, , adOpenDynamic, adLockOptimistic
' .AddNew 'I commented it out to see what would happen but
still errors on next line
End With
oRS(0).Value = 50 'errors here or on addnew, etc
oRS.Update
oRS.Close

oConn.Close

I am using an adp in access 2000. I have excel 2003 ver 11 and did not see
the correct Excel type to use in my so I chose the latest which was Excel
8.0?? Is this ok?
 
I am trying create a recordset using Microsoft.Jet.OLEDB4.0. My connection
seems to be ok but when i try to do anything with the recordset, it gives me
the error: Cannot Update. Database or object is read only." I checked and
the file is not write protected or anything.

Any ideas on what I am missing?

With oConn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Extended Properties").Value = "Excel 8.0"
.Open "C:\temp\cedc\cedc.xls"
End With

oCmd.ActiveConnection = oConn
oCmd.CommandText = "select * from [sheet1$]"

With oRS
.Open oCmd, , adOpenDynamic, adLockOptimistic
' .AddNew 'I commented it out to see what would happen but
still errors on next line
End With
oRS(0).Value = 50 'errors here or on addnew, etc
oRS.Update
oRS.Close

oConn.Close

I am using an adp in access 2000. I have excel 2003 ver 11 and did not see
the correct Excel type to use in my so I chose the latest which was Excel
8.0?? Is this ok?
Click to expand...

check out the CopyFromRecordset method of the ADO recordset object.
there's an example on Access Web. www.mvps.org/access/modules
somewhere...
 
You cannot update data in a linked Excel spreadsheet. That functionality was
removed because of a lawsuit Microsoft lost. It was removed about 3 years
ago.
Your options are to import the spreadsheet into an Access table, make the
mods, then export the modified table or use Automation to open the
spreadsheet in an instance of Excel and use the Excel object model to make
your changes.
 
Back
Top