ADO in MS Access to Query Excel File

[Joe]

Hi

I'm trying to access an Excel workbook through ADO in an Access Database
(both in the 2003 version).

My code is the following:
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cnn = New ADODB.Connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=C:\Databases_JLP\BD_TXT\Clientes_ADO.xls;" _
& "Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';"
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM [Clientes$]", cnn, _
adOpenKeyset,
adLockOptmistic

Data is showed in a form. The buttons to navigate (rs.MoveLast,
rs.MoveFirst, rs.MovePrevious and rs.MoveNext) runs as expected.

But, when I try to modify some field and click to Update, I receive the
message "Database or the Object is read only".

The workbook isn't read only and isn't protected. The folder has no
restrictions.

Thank you for your help.

Joe

 

[Douglas J. Steele]

Try playing with the CursorLocation property when you open the Connection.
(Sorry, I know that the CursorLocation property can impact whether or not
you can do updates, but I don't remember what it needs to be!)

 

[Alex Dybenko]

Hi,
have you tried to update this recordset in code using .Update method, will
it work?

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com