Humming and harring whether this post should be in the Excel or Access forum, but i plumped for this one!
I have been struggling with this for a while and posted here 3 weeks ago with no answer.
I'm writing to Access from Excel with this code:
Sub Goods2DB()
RstLog
On Error GoTo Oops
frmWriting.Show
frmWriting.BackColor = Sett.Cells(1, 2)
DoEvents
With CreateObject("ADODB.Connection")
.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=K:\KKDB.accdb;"
.Execute "INSERT INTO TestOpen (TestField) VALUES ('" & Sett.Cells(42, 2) & "') "
.Execute "DROP TABLE GoodsBackup;"
.Execute "SELECT * INTO GoodsBackup FROM Goods"
.Execute "DELETE FROM Goods;"
.Execute "INSERT INTO Goods SELECT * FROM [Excel 8.0;HDR=YES;DATABASE=" & ThisWorkbook.Name & "].[DBGoods$]"
End With
Unload frmWriting
Xit:
Exit Sub
Oops:
strErrSource = Err.Source
strErrDesc = Err.Description
strErrDetail = "The Goods list has not been updated, please try again."
frmOops.Show
Logger "Err: Goods2DB", Err.Source, Err.Description
Unload frmWriting
End Sub
The line in red errors with "Cannot update. Database or object is read-only." but it does delete the data in the Goods table.
I've also tried
.Execute "DROP TABLE Goods;"
.Execute "SELECT * INTO Goods FROM [Excel 8.0;HDR=YES;DATABASE=" & ThisWorkbook.Name & "].[DBGoods$]"
but get the same error, even though it deletes the Goods table
Can anyone help me please?
Many thanks in anticipation