Recordset update problem (1 Viewer)

[Acropolis]

Hoping someone can help here as this is driving me crazy now.


Have the below code, top part which is commented out works absolutely fine, is just commented out so it doesn't run for the moment when I am testing the second part.


Every time it runs, if I use .edit with the recordset I get an error about another user trying to update the same information at the same time Runtime Error 3197, of if it change the recordset to dbsnapshot and use docmd.runsql...... I get a message about lock violations.


Any help anyone please?

Sub UpdateSiteData()
Dim ClientID As Integer
Dim SiteID As Integer
Dim dbSites As DAO.Database
Dim rsClient As DAO.Recordset
Dim rsSite As DAO.Recordset
Dim SiteCount As Integer
Dim SiteRef As String
Dim rsUpdate As DAO.Recordset
Dim AMR As Integer
Dim StatusID As Integer
Dim NoMetersReq As Integer
Dim Surveyed As Integer
'SiteCount = 0
'
Set dbSites = CurrentDb
'
''Get all clients
'Set rsClient = dbSites.OpenRecordset("SELECT CustomerID FROM tblClientData")
'
'rsClient.MoveLast
'rsClient.MoveFirst
'
'Do Until rsClient.EOF = True
'    'Get sites for first client
'    ClientID = rsClient("CustomerID")
'    Set rsSite = dbSites.OpenRecordset("SELECT SiteID, ExternalRef FROM site WHERE CustomerID = " & ClientID & "")
'
'    If Not rsSite.RecordCount = 0 Then
'        rsSite.MoveLast
'        rsSite.MoveFirst
'
'        Do Until rsSite.EOF = True
'            If DCount("[ID]", "tblSiteData", "[SiteID] = " & rsSite("SiteID") & "") = 0 Then
'                DoCmd.RunSQL ("INSERT INTO tblSiteData (SiteID, SiteRef) VALUES (" & rsSite("SiteID") & ",'" & rsSite("ExternalRef") & "')")
'                SiteCount = SiteCount + 1
'            End If
'            rsSite.MoveNext
'        Loop
'    End If
'
'    rsClient.MoveNext
'Loop
'Check for number of meters and update record acordingly
'rsSite.Close
'Set rsUpdate = dbSites.OpenRecordset("tblSiteData")

Set rsUpdate = dbSites.OpenRecordset("SELECT SiteID, NoMetersReq, Surveyed, MeterStatusID FROM tblSiteData")
rsUpdate.MoveLast
rsUpdate.MoveFirst
Do Until rsUpdate.EOF
    SiteID = rsUpdate("SiteID")
    NoMetersReq = rsUpdate("NoMetersReq")
    Surveyed = rsUpdate("Surveyed")
    AMR = DLookup("[AMR]", "tblClientData", "[CustomerID] = " & DLookup("[CustomerID]", "site", "[SiteID] = " & SiteID & "") & "")
    If AMR = 1 Then
        If NoMetersReq = 0 Then
            StatusID = 3
        ElseIf DCount("[ID]", "meters", "[SiteID] = " & SiteID & " AND MeterTypeID = 2") = 0 Then
            If Surveyed = 1 Then
                StatusID = 4
            Else
                StatusID = 3
            End If
        Else
            Select Case NoMetersReq - DCount("[ID]", "meters", "[SiteID] = " & SiteID & " AND MeterTypeID = 2")
                Case 0
                    StatusID = 6
                Case Is > 0
                    If Surveyed = 1 Then
                        StatusID = 5
                    Else
                        StatusID = 8
                    End If
                Case Else
                       StatusID = 7
            End Select
        End If
 '           DoCmd.RunSQL "UPDATE tblSiteData SET MeterStatusID = " & StatusID & " WHERE SiteID = " & rsUpdate("SiteID") & ""
            With rsUpdate
                .Edit
                ![MeterStatusID] = StatusID
                .Update
            End With
    End If
    rsUpdate.MoveNext
Loop
rsUpdate.Close
MsgBox "All site data updated. " & SiteCount & " new sites added.", vbInformation
Set rsSite = Nothing
Set rsUpdate = Nothing
Set rsClient = Nothing
Set dbstites = Nothing

End Sub

 

[Ranman256]

1. dbsnapshot does not allow edits. only dynasets can edit.

2. why are you updating using code? instead of an update query?

 

[Acropolis]

I've tried it with dynaset and the rsupdate.edit and it throws an error about me and another user updating the same information at the same time and won't update it, so I tried the code way instead. But that doesn't work either, come up with error about lock violation.