Cannot read Database or object read only

Question

I am trying to query an Excel file from another file using VBA and ADO options. When I run this code it's throwing the error "Cannot updata, Database or object Read only":

Public Function fnExecuteXlQuery _
    (ByVal strPath As String,  _
    ByVal strQuery As String) As ADODB.Recordset

Dim rs As ADODB.Recordset
Dim conStr As String

On Error GoTo ErrorHandler

conStr = "Provider=Microsoft.Jet.OLEDB.4.0; " _ 
      & "Data Source=" & strPath & "; Extended Properties=Excel 8.0"

Set rs = New ADODB.Recordset
rs.Open strQuery, conStr, adOpenDynamic

Set fnExecuteXlQuery = rs

Exit Function
ErrorHandler:
Set fnExecuteXlQuery = Nothing
fnDisplayError Error(Err) & "Unable to fetch data from DTS...", ERROR_TYPE_ERROR
End Function

The "strPath" is the source Excel file and the "strquery" has the following SQL code:

Select [Activity],[Name],[Date],[Hours Spent] 
from [Time sheet$] 
where [Activity] = 'Billable Activities' 
Order by Name,date

asking the obvious - is the `strPath` spelled correctly with all folder paths and file name and such? — Scott Holtzman, Sep 21 '15 at 14:16
I can't see any updating going on there. Where are you actually getting the error? — Rory, Sep 21 '15 at 14:25
This might help: http://stackoverflow.com/questions/12002805/update-an-excel-sheet-using-vba-ado http://stackoverflow.com/questions/20998502/vba-adodb-update-recordset — Ralph, Sep 21 '15 at 14:33
@Rory There is no update just a select query but the error shows its read only — Anarach, Sep 21 '15 at 14:52
@Anarach Is it actually an .xls file, or a newer format? Also, you really should enclose the field names in the ORDER BY clause in parentheses too as you're using keywords like `Date` for names. — Rory, Sep 21 '15 at 14:54
@Rory Its .Xlsx , and yes i enlosed them in [] still not workin — Anarach, Sep 21 '15 at 14:56
@Anarach Then your connection string is wrong. You should almost certainly be using `ACE.OLEDB.12.0` and not Jet4 and the extended properties should be `Excel 12 XML`. Your code shouldn't work at all unless the file is open, and it's not a good idea to query open workbooks due to memory leaks. — Rory, Sep 21 '15 at 14:58
@Rory Well i just change it to ACE.OLEDB and i get a new error "" could not find installable ISAM" — Anarach, Sep 21 '15 at 15:01
@Anarach Just ot be sure, you did use: `conStr = "Provider=Microsoft.ACE.OLEDB.12.0; "`? Which version of Excel are you using? — Rory, Sep 21 '15 at 15:17
Yes I am using the exact same code and excel is 2010 version — Anarach, Sep 21 '15 at 15:18

score 1 · Accepted Answer · answered Sep 21 '15 at 16:36

Maybe all of what has been said in the comments just needs to be written out once more to make sure that you really got the solution right:

Option Explicit

Public Sub ConnectionToExcel()
Dim rstResult As ADODB.Recordset
Dim strConnectin As String
Dim strPath As String
Dim strSQL As String

strPath = "C:\Data\YourFile.xlsm"

strConnectin = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source='" & strPath & "';Extended Properties=""Excel 12.0 XML;HDR=YES;IMEX=1"" "
Debug.Print strConnection

strSQL = "SELECT * FROM [Time sheet$] "

Set rstResult = New ADODB.Recordset
rstResult.Open strSQL, strConnectin, adOpenForwardOnly, adLockReadOnly, adCmdText

Sheet1.Range("A1").CopyFromRecordset rstResult

End Sub

Please note, that I am taking it one step at a time: (1) I am using a sub instead of a function. (2) The select has been simplified just to test the connection and can be later expanded upon. (3) Just writing back the result to the first sheet. (4) Further restricting the recordset to be adOpenForwardOnly only and adLockReadOnly.

Also, keep in mind that the above code uses early binding and thus requires you to set a reference to Microsoft ActiveX Data Objects 2.8 Library (or later) in Tools --> References....

Cannot read Database or object read only

1 Answers1