Database or object is read-only

[paulked]

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 (https://www.mrexcel.com/forum/excel-questions/1113374-adodb-error-cannot-update-database-object-read-only.html) 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

[OBP]

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?

[paulked]

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!

[OBP]

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?

[paulked]

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!

[OBP]

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.

[paulked]

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:



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

[OBP]

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

[paulked]

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: