Results 1 to 5 of 5

Thread: Dynaset Update

  1. #1
    Join Date
    Jul 2010

    Unanswered: Dynaset Update

    Hi guys,

    I just upsized my access database to SQL server 2000. The only problem I am having is the "Cannot update. Database or object is read-only". I know its not the back end, I've given full permission to this account. Also tested it through query analyzer create, insert, update etc. to make sure permissions weren't the case.

    I m getting the data off of an excel sheet. Then looping each row depending on the number of Quantity(each row must be 1 item), then updating into "barinv" table. The part I cannot get to work is the Update. The code works fine when I m not using Linked Tables to SQL Server.

    Would greatly appreciate any help. Here is my code.

    'Import the Excel file
        filename = "C:\" & Me![filename]
        If Right(filename, 4) <> ".xls" Then filename = filename & ".xls"
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tbllabels", filename
        Dim db As DAO.Database
        Dim qry As DAO.QueryDef
        Dim rs As DAO.Recordset
        Dim labelstd As DAO.TableDef
        Dim addrs As DAO.Recordset
        Set labelstd = db.TableDefs("tbllabels")
        Set rs = labelstd.OpenRecordset
        'skip the table headings row - remove this if tableheadings are removed from excel sheet
        'DAO Hack to quickly add a record - barinv key will never be 0
        Set qry = db.QueryDefs("qry_add_barinv")
        qry.Parameters(0) = 0
        Set addrs = qry.OpenRecordset
        Dim loopqty As Long
        While Not rs.EOF
            loopqty = rs!F3
            While Not loopqty = 0
                addrs.AddNew   <------------ this is where I get read-only error
                addrs!dept = rs!f1
                addrs!sku = rs!F2
                addrs!qty = rs!F3
                addrs!desc1 = rs!F4
                addrs!desc2 = rs!F5
                addrs!desc3 = rs!F6
                addrs!desc4 = rs!F7
                addrs!partno = rs!F8
                addrs!loaddate = rs!F9
                addrs!customer = rs!F10
                addrs!custPO = rs!F11
                addrs!NFRC = rs!F12
                addrs!Wrap = rs!F13
                addrs!printed = False
                loopqty = loopqty - 1
    'Close all dao objects we used for initial data population
        Set rs = Nothing
        Set addrs = Nothing
        Set qry = Nothing
        Set labelstd = Nothing

  2. #2
    Join Date
    May 2010
    Does the table tbllabels in have a primary key defined?

    When you created the linked table, did you select the correct Primary key field?

    Also your OpenRecordset is missing required parameters for SQL Server.

    I normally do not use the TableDef collection to open a recordset.

    There two lines:
        Set labelstd = db.TableDefs("tbllabels")
        Set rs = labelstd.OpenRecordset
    could be written with this one line:

        Set rs = db.OpenRecordset ("tbllabels", dbOpenDynaset, dbSeeChanges)
    Boyd Trimmell aka HiTechCoach (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  3. #3
    Join Date
    Feb 2004
    Chicago, IL
    The easiest way to tell if there is Primary Key on the table (that Access can use) is to link to the table. If Access prompts you with a list of fields that are in the table then it doesn't know or can't use the current Primary Key.

    If you have a Primary Key in SQL Server, what is the data type? When I started using SQL Server as a backend I selected BigInt as the data type (SQL 2005). I later found out that Access cannot handle a BigInt variable and therefore the table becomes Read Only becuase Access cannot tell SQL Server which record to update because the Primary Key field gets corrupted.

    Another way to approach the problem is to use and ADO connection and recordset to connect directly with SQL Server. Are you familiar with ADO?

  4. #4
    Join Date
    Jul 2010
    tbllabels does not have a primary key. That table is not linked to to SQL Server. It is created at the beginning of the function, then deleted. I've created a primary key in tbllabels table, but I m getting the same error. I would love to use ADO in this circumstance but I m afraid of starting over again.

  5. #5
    Join Date
    Feb 2004
    Chicago, IL
    OK, what is the query qry_add_barinv doing? Are joining any tables in the query. After looking at it more closely it seems to me that you shouldn't be able to add any records to qry_add_barinv. If you open it directly in Access can you add anything?

Posting Permissions

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