Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2009
    Posts
    18

    Unanswered: Write Conflict on Requery; SaveRecord doesn't work

    Hi all!

    I've been searching for answers on this problem. Please help! I am currently working on a .mdb file that is an Access 2003 front-end/SQL Server 2005 backend. I have a subform that has its Default view set to "Continuous Forms". On the row of the subform there is a drop down box, the user will use this to select a specific value which I will call "Operation". There is also an OpId field that distinguishes each record as unique that is also contained on each subform's recd. Without going into too much detail, when the Operation changes, the corresponding row should be deleted from the underlying table (Operation Notes Table).

    I currently have code in place to delete the record that corresponds to this operation:
    Code:
    'DeleteOpNotes will be called from Operation's AfterUpdate Event
    'OpId is bound to the underlying [Operation Notes Table]  
    ' and is the primarykey
    
    Private Sub DeleteOpNotes(opID As Integer)
        Dim strQry As String
        
        strQry = "DELETE * FROM [Operation Notes Table] "
        strQry = strQry + "WHERE [OpNotesID] = " + CStr(opID)
        RunQuery (strQry)
    
        Forms![Header Main Form]![Operations Sub Form].Form.Requery
    End Sub
    
    Private Sub RunQuery(strSQL As String)
        DoCmd.SetWarnings False
        DoCmd.RunSQL (strSQL)
        DoCmd.SetWarnings True
    End Sub
    The delete query works fine. However, after it runs I am prompted with the "Write Conflict" box that tells me that the record has been changed by another user since I started editing it. It does not give me the option to save record but does allow me to "Copy to Clipboard" or "Drop Changes". I am currently, the only person in the database. Once I click on "Drop Changes", it gives me an error saying "Run-timer error '3167'. Record is deleted." After this, the controls for that record all change to #DELETED.

    I have tried doing a DoCmd.RunCommand acCmdSaveRecord before Requering but this gave me a couple more error boxes saying the record has already been deleted and that 'SaveRecord' is not available right now.

    What can I do to get the requery to work?

    Please let me know if I need to provide any additional information.

    Thanks!

    g-coding

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    This is strange: I tested your code (see attached file) and it works properly. One reason I can imagine for this problem is that there is something like a trigger or a computed column based on a function in the SQL database. This can lead to very "vicious" problems. Can you check for that?

    Have a nice day!
    Attached Files Attached Files

  3. #3
    Join Date
    Feb 2009
    Posts
    18
    Sinndho,
    There are no triggers or computed columns resulting from a function on the SQL Server side. Is there anything else I can check for?

    Thanks!

    g-coding

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Hi g-coding!

    Did you try the sample file I sent ? Does it work? Is there any code running into your application and, if there is, what is it? Did you try
    Code:
    Currentdb.Execute strSQL, dbSeeChanges
    instead of
    Code:
    DoCmd.RunSQL (strSQL)
    Have a nice day!

  5. #5
    Join Date
    Feb 2009
    Posts
    18
    Hello again.

    Sorry for the delay in response. Yes, I tried Currentdb.Execute... statement and the sample file that you sent.

    After making some slight modifications on your sample so that it could connect with the right fields in my database, I got the same error message.
    The only mods I made other than using my Operation Notes Table were changing OpNotesId to be bound to the OpNotesId field in the linked table and changing the name of the Data field to "Operation" and binding it to the table.

    Could it be a record lock?
    On the original database, I imported the contents of my SQL table into access. I made it so that the form now points to the local table. When the delete statement was executed, the following boxes popped up:
    (1) You are about to delete 1 rows(s) from the specified table.
    My response: YES
    (2) Microsoft Office Access can't delete 0 records(s) in the delete query due to key violations and 1 record(s) due to lock violations
    My response: YES
    (3)You are about to delete 1 rows(s) from the specified table.
    My response: YES
    The subform requery works but the record has not been deleted from the underlying table.

    On the flip side, when connected to the SQL Server table, the record is deleted but I keep running into the "Write Conflict" error box.

    I know access and sql server are different but could it be that they are both triggering errors because the record is still locked? If so, is there some way I can eliminate the lock?

    There is more code. Do you still want to see it? It involves updating another subform based on the operation chosen on the subform we are currently discussing. That part appears to work as it should.

    Please let me know.

    Thanks again for your help!!!

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Yes please. Send a sample of your front-end mdb (at least the offending form and the connection strings of the tables to the server) and some explanation about the data structures related to the form in the SQL server.

    It's hard to guess what happens without having a clear idea of how things are organized.

    Have a nice day!

  7. #7
    Join Date
    Feb 2009
    Posts
    18
    I believe I came up with an appropriate work around.

    Here’s my theory:
    Because my sub form’s controls are bound to the same SQL Server Table and I‘m trying to delete the current record from (in VBA) it’s throwing the write conflict error.

    In the After Update Event of one of the controls, I make a call to delete the current record from the same table using VBA. SQL Server allows me to perform this DELETE on the table. However, when trying to requery the form from Access’ perspective, it sees that something has been changed on a record that Access is currently using. It then triggers the Write Conflict message.

    To work around this, while in the AfterUpdate Event, I capture the OpNotesId I want to eventually delete from the Operation Notes Table. After the “After Update” Event completes, the form will perform the Current Event. It is in this event, that I call the DeleteOpNotes function to delete the targeted record that contains the OpNotesId.

    All of the following code is contained in my Operations Sub Form VBA Code:

    Code:
    Public OpChangedToNull As Boolean
    Public OpIDToDelete As Integer
    
    Private Sub Form_Current()
        If OpChangedToNull Then
           DeleteOpNotes (OpIDToDelete)
           OpChangedToNull = False
        End If
    End Sub
    
    
    'Purpose: Populate the correct UOM when an operation is selected
    '         Also, if the Operation is now blank, delete all
    '         associated records in the Operation Notes Table
    Private Sub Operation_AfterUpdate()
    
        'Operation Drop Down Box
        ' Column 0 - Operation Description
        ' Column 1 - UOM
        ' Column 2 - Item ID (if applicable)
        Dim strSQL As String
        Dim mySQL As String
        Dim rst As DAO.Recordset
        Dim db As DAO.Database
        Set db = CurrentDb
        Dim recd As Integer
        Dim item As String
        Dim heat As String
        Dim OriginalItem As String
        Dim amount As Integer
        Dim diff As Integer
    
        heat = [Heat Number]
        
        'If the operation changes and the resulting operation is null, delete the corresponding item out of the operation notes table
        'Delete any records in the Issue Quantity table that may have been reset to 0 due to the operation removal
        'Make sure this is a record that has been changed and NOT added for the first time
        'Only a pre-existing record would have an OpNotesId
        
        If Not IsNull(OpNotesID.Value) And Not IsNull(Operation.OldValue) Then
            amount = CInt(Nz(Amt.OldValue, 0))
            OriginalItem = GetOldItem(Nz(Operation.OldValue, ""))
            
            'If the operation merely changed from one to another, leave the record
            ' in the Operation Notes Table and allow access to update that record
            If IsNull(Operation.Value) Then
             '   DeleteOpNotes (OpNotesID.Value)
                OpChangedToNull = True
                OpIDToDelete = OpNotesID.Value
            Else
                OpChangedToNull = False
                Amt.Value = ""
                If Not IsNull(Operation.Column(2)) And Operation.Column(2) <> "" Then
                    item = Operation.Column(2)
                End If
                Me.UOM.Value = Operation.Column(1)
            End If
            
            If OriginalItem <> "" And OriginalItem <> "0" Then
                diff = GetAmtDiff(OriginalItem, amount, heat)
                If diff <= 0 Then
                    Call DeleteIssueQty(OriginalItem, heat)
                Else
                    Call UpdateIssueQty(OriginalItem, diff, heat)
                End If
            End If
        Else
            If Not IsNull(Operation.Column(2)) And Operation.Column(2) <> "" Then
                item = Operation.Column(2)
            End If
            Me.UOM.Value = Operation.Column(1)
        End If
    End Sub
    
    Public Function GetOldItem(op As String) As String
        Dim mySQL As String
        Dim rst As DAO.Recordset
        Dim db As DAO.Database
        Set db = CurrentDb
        
        mySQL = "SELECT item FROM [Operations Data Query] WHERE Operation = '" + op + "'"
        Set rst = db.OpenRecordset(mySQL)
        
        If rst.RecordCount <> 0 Then
            rst.MoveFirst
            GetOldItem = Nz(rst![item], 0)
        Else
            GetOldItem = 0
        End If
    End Function
    
    Private Sub RunQuery(strSQL As String)
        DoCmd.SetWarnings False
        DoCmd.RunSQL (strSQL)
        DoCmd.SetWarnings True
    End Sub
    
    Private Sub DeleteOpNotes(opID As Integer)
        Dim strQry As String
        Dim rst As DAO.Recordset
        Dim db As DAO.Database
        Set db = CurrentDb
        Dim total As Integer
        
        strQry = "SELECT * FROM [Operation Notes Table] "
        strQry = strQry + "WHERE [OpNotesID] = " + CStr(opID)
        
        Set rst = db.OpenRecordset(strQry, dbOpenDynaset, dbSeeChanges)
        MsgBox (CStr(rst.RecordCount) + " records found to delete.")
        
        If rst.RecordCount = 1 Then
            rst.Delete
             rst.Close
             Me.Requery
        End If    
    End Sub
    I hope I haven't confused anyone more by this explanation. However, it does appear that I have gotten rid of the "Write Conflict" error. YAY!

    Thanks for all your help!!!

Posting Permissions

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