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