Database or object is read-only

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:

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

Code:

---

.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

Personally I think this should be in the Excel Forum as the code is in Excel.
As an Access programmer I would work from Access to import the data in to Access.
I assume that in your error line you are trying to export DBGoods$ to an Access table.
Have you made sure that a Field is not an Autonumber field that you are trying to write to?
You can use an Access Recordset in Excel VBA to write data directly to a table, basically it is the same code as used in Access.
Could you explain what type fo data and howm you are tryign to transfer?

Hi and thanks for reply. I'll check the autonumber as soon as I've finished typing!

Your assumption is correct, exporting Excel tables to Access. I do use recordsets when updating and adding job and customer records to the database and I guess I could do the same with the goods list, but this routine is used to backup the tables in excel to the db. It works with all the other excel tables (which are all on separate sheets, as is the Goods table) and is a little faster than looping through the thousands of records!

Again, thanks for reply, will check that autonumber... ah! I tried dropping the table then creating a new one with SELECT * INTO Goods FROM the Excel table and that wouldn't work, so I guess it's not an AutoNumber field!

There must be something about the table that is different to the ones that work.
Either in the Excel Data or the Access Table design.
Can you provide a copy of both?

I've just created new Access and Excel files with just the goods tables in to upload smaller files (the current DB is 18MB and WkBk is 6MB) and it works!

I'll create a new DB and try it on that before anything else. It maybe tomorrow now as I have to go out shortly.

Thanks for your help so far... I can see a light!

That is good news.
I would also suggest Compacting and repairing the Access database, unless it has a millions of records I doubt it should be that big.
If the small database works I would then Import all the other data from the big one in to it.
Job done.

Update:

Created a new database and imported the tables and hey presto... none of the tables would update from the Excel tables, same error Database or object is read-only!

So rather than faff about looking I have changed all the sub procedures to write the RecordSets instead, like this for the Goods:

Code:

---

Sub SaveGoods()
    Dim i As Long, j As Long, Arr As Variant
    Dim cn As ADODB.Connection, rs As ADODB.Recordset
    RstLog
    On Error GoTo Oops
    frmWriting.Show
    frmWriting.BackColor = Sett.Cells(1, 2)
    DoEvents
    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=K:\KKDB.accdb;"
    cn.Execute "DROP TABLE GoodsBackup;"
    cn.Execute "SELECT * INTO GoodsBackup FROM Goods"
    cn.Execute "DELETE * FROM Goods"
    rs.Open "Goods", cn, adOpenKeyset, adLockOptimistic, adCmdTable
    Arr = DBGds.Range("A2").CurrentRegion.Value2
    For i = 2 To UBound(Arr)
        rs.AddNew
        For j = 0 To 38
            rs(j) = Arr(i, j + 1)
        Next
        rs.Update
    Next
    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 execution time is averaging 2.6 seconds for 6700 records 39 fields long which is fine when backing up at the end of the day.

I run a scheduled task at 3:00AM every day to compact and repair the DB, but thanks for suggesting it.

And once again, many thanks for your assistance, I'm 'good to go' as some say :thumb

It sounds more and more like an MS update issue, did you have an update prior to the problem with the Goods Table?

That's a good point, although I can't track updates as the system is in use on four of my clients PC's. I've updated them all now and they're happy so it's a grand weekend for me :whistle:

Cheers for your input :beerchug: