sql - Insert Into Access - Cannot Update. Database or object is read-only

Question

I'm trying to upload a table of values from Excel 2013 into Access 2013 database via the VBA code below. However, I keep getting an error. The error occurs on the cn.execute SQL line.

Run-time error '-2147217911 (80040e09)':

Cannot update. Database or object is read-only.

I have checked permissions on the file and Access database and and it is read and write access. I've even tried to create a new microsoft Access database in a different directory but get the same error.

Another thing that has me stumped is that I've re-used this code before without any problems in a different Excel sheet, but using it with this workbook/database does not work.

Sub uploadToDB()

Dim SQL As String

dbPath = "C:Usersjohn.smithDesktopdatabase.accdb"
wbName = "C:Macros" & ActiveWorkbook.Name & ".xlsm"
wsname = "Staging"
TableName = "dataTable"

Set cn = CreateObject("ADODB.Connection")
scn = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & dbPath

cn.Open scn

'find number of data points to add
row = Sheets(wsname).Range("A1").End(xlDown).row

SQL = "INSERT INTO " & TableName & " ([field1],[field2],[field3],[field4],[field5],[field6],[field7],[field8]) "
SQL = SQL & "SELECT * FROM [Excel 12.0 Macro;HDR=YES;DATABASE=" & wbName & "].[" & wsname & "$A1:H" & row & "]" '" & wsName

cn.Execute SQL

cn.Close
Set cn = Nothing

End Sub

Answer 1

I would try to use a hard-coded SQL statement first, i.e.

SQL = "INSERT INTO " & TableName & " ([field1],[field2],[field3],[field4],[field5],[field6],[field7],[field8]) "
SQL = SQL & "SELECT 'val1', 'val2', etc.

This differentiates the problem between an issue with Access or one with Excel. If the hard-coded statement works, you may need to use IMEX=0 in the reference to Excel. i.e.

Excel 12.0;HDR=Yes;IMEX=0;Readonly=False