Consulting

Results 1 to 9 of 9

Thread: Database or object is read-only

  1. #1
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location

    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:
    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
    Semper in excretia sumus; solum profundum variat.

  2. #2
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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?

  3. #3
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    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!
    Semper in excretia sumus; solum profundum variat.

  4. #4
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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?

  5. #5
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    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!
    Semper in excretia sumus; solum profundum variat.

  6. #6
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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.

  7. #7
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    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
    Semper in excretia sumus; solum profundum variat.

  8. #8
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    It sounds more and more like an MS update issue, did you have an update prior to the problem with the Goods Table?

  9. #9
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    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

    Cheers for your input
    Semper in excretia sumus; solum profundum variat.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •