Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003

    Unanswered: Problem updating a recordset

    I used to code below to update the contents of a table in a form. I'm getting a problem at the .EDIT line, it says "Cannot update. Database or object is read-only." Can someone help?

    sqlStatement = "SELECT [Update Tracker].*, [$10 Change Of Address].* FROM [Update Tracker] " _
    & "INNER JOIN ([$10 Change Of Address] INNER JOIN " _
    & "[Main Data] ON [$10 Change Of Address].HU_FAM_ID = [Main Data].HU_FAM_ID) ON ([$10 " _
    & "Change Of Address].HU_FAM_ID = [Update Tracker].HU_FAM_ID) AND ([Update Tracker].HU_" _
    & "FAM_ID = [Main Data].HU_FAM_ID) WHERE " _
    & "[Main Data].HU_FAM_ID = '" & HUtxt & "';"
    MsgBox sqlStatement, vbInformation
    Set rst10Update = dbsHBHCFamilies.OpenRecordset(sqlStatement)

    With rst10Update
    Do While Not .EOF
    !DATE_MOVED = "#" & txtDATEMOVED.Text & "#"
    End With

  2. #2
    Join Date
    Feb 2004
    Chicago, IL
    It is your query. It is not updateable. When you pick data from many joined tables, the query can become read only. The reason being that Access cannot figure out which piece of information in which record you want to update. You will want to set your key information for the tables that are involved in the query. By telling Access that a certain field is a unique (no duplicates) Access will be able to determine what field/record needs to be updated. If you have your keys set up and the relationships setup, then you will have to do it with more than one recordset. Use the original SQL to find the records, then use a second recordset to search and update the records.

  3. #3
    Join Date
    Oct 2003
    I was able to make it updateable but still it does not update. If I copy the string stored in sqlStatement and make a query from it, I was able to change the value of the fields but if the code is executed in VBA, it does not give me an error but it doesn't do anything either.

Posting Permissions

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