Results 1 to 6 of 6
  1. #1
    mantooth29 is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Aug 2012
    Posts
    21

    DAO Recordset Error - Cannot Update. Database or object is read-only

    I have looked around a lot, and this seems to be an error that can be hard to pinpoint. I am hoping that someone can provide me with some insights as to why I am having this problem. I am new to programming with DAO (used ADO a fair amount), so maybe its an obvious thing I am missing.

    I am testing a desktop database. No linked tables, and everything is local. I am attempting to edit records in a code produced dataset with DAO.

    I have a top level function that creates two recordsets.
    1) The fist recordset is created and returned with a private function. I use a QueryDef to create the set, and It combines two local tables. (Works fine)
    2) The second recordset is created by another private function, but takes the recordset from step 1) and attempts to manipulate it. The recordset comes over with 4 fields, only one of which is populated. The rest are NULL by default. These NULL values are meant to be updated within this function by referencing another table. It is when I try to alter a record that I receive the cannot update error. I have tried various ways of manipulating the set. First I tried creating a temporary recordset based off the incoming set, then I tried manipulating the set passed by argument directly. I am currently trying to use the .Edit method for updating a record.

    Here is all the code, error producing code comes last.

    Top Level Function


    Code:
    Function GetProposedAliases() As Boolean
    
        GetProposedAliases = False
        
        Dim rs1 As DAO.Recordset
        Set rs1 = CheckForNewAliases
        
        Dim rs2 As DAO.Recordset
        Set rs2 = MaintainEmployeeAliases(rs1, "Originator") 'Error Producing Function
    'Return logic not yet completed
    End Function
    Step 1)
    Code:
    Private Function CheckForNewAliases() As DAO.Recordset
        
        Set CheckForNewAliases = Nothing
        
        Dim db As DAO.Database
        Set db = CurrentDb
        
        Dim sSQL As String
        sSQL = "SELECT Deals.Originator, Aliases.Creator, NULL As FirstName, NULL As LastName, NULL As Email " & _
                "FROM tbl_Deals AS Deals LEFT JOIN tbl_EmployeeAliases as Aliases " & _
                "ON Deals.Originator = Aliases.Creator " & _
                "WHERE Deals.Originator Is Not Null  AND Deals.Originator <> '' AND Aliases.Creator Is Null " & _
                "GROUP BY Deals.Originator, Aliases.Creator;"
        
        Dim qDef As DAO.QueryDef
        Set qDef = db.CreateQueryDef("AliasCheck", sSQL)
      
        Set CheckForNewAliases = qDef.OpenRecordset
    
    End Function
    Step 2)

    Code:
    Private Function MaintainEmployeeAliases(AliasComparisonRS As DAO.Recordset, FieldChecked As String) As DAO.Recordset
    
    'Purpose is to compare database editor credentials to a standard list of employee IDs.  
    Dim db As DAO.Database
    Set db = CurrentDb
    
    Dim rs As DAO.Recordset
    Set rs = db.OpenRecordset("tbl_EmployeeCredentials")
    
        With AliasComparisonRS
           .MoveFirst
            Do Until .EOF
            
            Dim CheckedCleared As Boolean
                CheckedCleared = False
                        
                With rs
                    .MoveFirst
                    Do Until .EOF
                  
                        If CStr(AliasComparisonRS(FieldChecked).Value) = CStr(![EmpNum]) Then 
                                                    
                            With tempSet 
                                .Edit 'This is where error occurs
                                .Fields(1).Value = rs![EmpNum]
                                .Fields(2).Value = rs![FName]
                                .Fields(3).Value = rs![LName]
                                .Fields(4).Value = rs![Email]
                                .Update
                            End With
                            
                            CheckedCleared = True
                        
                        ElseIf CStr(AliasComparisonRS(FieldChecked).Value) = CStr(![LName]) Then
    With tempSet
    .Edit 'This is where error occurs .Fields(1).Value = rs![EmpNum] .Fields(2).Value = rs![FName] .Fields(3).Value = rs![LName] .Fields(4).Value = rs![Email] .Update End With CheckedCleared = True Else End If If FieldCheckedCleared Then Exit Do .MoveNext Loop If FieldCheckedCleared = False Then Debug.Print "Not Found: " & AliasComparisonRS(FieldChecked).Value End With .MoveNext Loop End With rs.Close Set rs = Nothing End Function
    Anyone have ideas why I can't edit the records in this set?

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I suspect that the recordset is built on a read only query.

    http://allenbrowne.com/ser-61.html
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    mantooth29 is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Aug 2012
    Posts
    21
    Thank you very much for the link! Turns out I was violating at least a handful of those listed. GROUP BY, UNION, and stacked! Instead I made a table with the preceding queries, and update that table.

    If I were to go disconnected recordset with ADO, do you know if could I accomplish what I was trying to do without making a table, or do these rules extend to ADO as well?

    Thanks again!

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I think you'd run into the same problem. I'm not clear on the overall goal, but typically execute an update query/SQL would be more efficient than looping a recordset.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    mantooth29 is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Aug 2012
    Posts
    21
    Ok thanks. I have to reconcile several data sources that display employee names differently, and tie them back into an EmployeeID type table. Its a pretty complicated process, and uses a few different algorithms to approximate a match. I didn't want to make a bunch of different query tables in the database to accomplish this (DB is already getting cluttered), so I went with the programming approach (a bonus is that I can the keep matching logic centralized in code). I was hoping to keep everything in code except for the final table, but that is essentially what I have now except that I pass that table to the function that makes alterations. Not much of a sacrifice, so no big deal.

    Thanks again for the link.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Error Handling with ADODB Recordset Object
    By Aaron5714 in forum Programming
    Replies: 3
    Last Post: 12-27-2012, 04:02 PM
  2. Cannot update. Database or object is read-only ERROR
    By Namibia in forum Import/Export Data
    Replies: 2
    Last Post: 06-05-2012, 05:32 PM
  3. Can Not Update. Object is Read Only
    By mrfixit1170 in forum Programming
    Replies: 3
    Last Post: 01-03-2012, 03:35 PM
  4. Replies: 1
    Last Post: 07-17-2010, 08:55 PM
  5. Runtime Error '3027': Database or object is read only
    By 4x4Masters in forum Programming
    Replies: 4
    Last Post: 06-08-2010, 08:02 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums