?
Solved

Having issue with locking records

Posted on 2011-10-20
3
Medium Priority
?
244 Views
Last Modified: 2012-05-12
I have a app that is being ran by several computers. There are processing data, no intervention besides starting up

What I have is a list of items to be processed.
On each system, I fill a listview of the items that meet the sql.
They are supposed to go down the list for each item, and mark the recorded "in work" in the progress field  and UPDATE and then process the devices.
If when wanting to mark the record "IN WORK" if it is already marked, it is to return a fail, and then get the next listview item and test it.

Here is the calling code and the locking code

Calling:
Function ProcessMAC(MyWorkingMac As Long, rs As Recordset, ByRef mac As String) As String
      'Find MAC in table and mark as "inwork"
          Dim Z As Long
          Dim CSGResults As Integer
          Dim tmpZ As Long
          Dim strSQL2 As String
          Dim rst2 As Recordset
          Dim I As Long
          Dim BTSAccount As String

' moves 6 webpages to a default page
5600      ResetBTS  

'Check to see if record is in use or not
5610      If MarkMACInWork(MyWorkingMac, rs, mac) Then
'Record was placed in work successfully
              'CurrMac.ID = MyWorkingMac
              'CurrMac.mac = mac
5620          For Z = 1 To lvwDepartments.ListItems.Count
5630              If lvwDepartments.ListItems(Z) = MyWorkingMac Then
5640                  lvwDepartments.ListItems(Z).EnsureVisible
5650                  tmpZ = Z
5660                  CSGResults = submitSNtoCSG(mac)
                      '7860            CSGResults = ProcessCSG(mac)
5670                  strSQL2 = "SELECT * FROM EMTAs WHERE (((EMTAs.ID)=" & MyWorkingMac & "));"
5680                  Set rst2 = CurrentDb.OpenRecordset(strSQL2, dbOpenDynaset)
5690                  rst2.MoveLast
5700                  rst2.MoveFirst
5710                  With rst2
                          ' debug.print .Fields(2)
5720                      .Edit
5730                      .Fields(3) = CSGResults
5740                      .Update
5750                  End With
                      'CurrMac.CSG = CSGResults

5760                  If CSGResults = 2 Then    ' Found
5770                      lvwDepartments.ListItems(tmpZ).ListSubItems(2).ReportIcon = RedX
5780                      lvwDepartments.ListItems(tmpZ).ListSubItems(3).ReportIcon = NotProcessed
5790                      With rst2
                              ' debug.print .Fields(2)
5800                          .Edit
5810                          .Fields(4) = 4    'BACC
5820                          .Fields(5) = 4    'FRSNCA03PS0
5830                          .Fields(6) = 4    'FTFRCAAMPS0
5840                          .Fields(7) = 4    'HYWRCAZRPS0
5850                          .Fields(8) = 4    'RTPKCABTPS0
5860                          .Fields(9) = 4    'SNFGCAHBPS0
5870                          .Fields(10) = 4    'SNJSCABAPS0
5880                          .Fields(11) = "Complete"    'Progress
5890                          .Fields(12) = gsUserID    'ProcessorID
5900                          .Fields(13) = Now    'CompleteDate
5910                          .Update
5920                          For I = 1 To 6
5930                              lvwDepartments.ListItems(tmpZ).ListSubItems(I + 3).ReportIcon = NotProcessed
5940                          Next
5950                      End With
5960                  Else    'Not Found - We will continue to process!
5970                      lvwDepartments.ListItems(tmpZ).ListSubItems(2).ReportIcon = GreenChk
5980                      Call ProcessMACWEB(mac, tmpZ)
5990                      For I = 1 To 6
                              ' debug.print rst2.Fields(i + 3).Name
6000                          If bTSResults(I) > 1 Then
                                  'MsgBox "Need to Delete!" & rst2.Fields(i + 3)
6010                              BTSAccount = ""
6020                              Call bTSArray(I).GetTextBox("acctno", BTSAccount)
6030                              Call DeleteEMTAs(bTSArray(I), BTSAccount, aSwitchname(I))
6040                          End If

6050                          lvwDepartments.ListItems(tmpZ).ListSubItems(I + 3).ReportIcon = bTSResults(I)
6060                      Next
6070                      On Error GoTo ReLock
retryLock:
6080                      With rst2
                              ' debug.print .Fields(2)
6090                          .Edit
6100                          .Fields(5) = bTSResults(1)    'FRSNCA03PS0
6110                          .Fields(6) = bTSResults(2)    'FTFRCAAMPS0
6120                          .Fields(7) = bTSResults(3)    'HYWRCAZRPS0
6130                          .Fields(8) = bTSResults(4)    'RTPKCABTPS0
6140                          .Fields(9) = bTSResults(5)    'SNFGCAHBPS0
6150                          .Fields(10) = bTSResults(6)    'SNJSCABAPS0
6160                          .Fields(11) = "Complete"    'Progress
6170                          .Fields(12) = gsUserID    'ProcessorID
6180                          .Fields(13) = Now    'CompleteDate
6190                          .Update
6200                      End With
6210                      On Error GoTo 0
6220                  End If
6230                  refreshListView lvwDepartments


6240                  Exit For
6250              End If
6260          Next

6270      End If
Exitme:
6280      Exit Function

ReLock:
6290      Sleep 100
6300      GoTo retryLock
End Function

Open in new window




 Here is the locking code:
Option Compare Database
Option Explicit
Function MarkMACInWork(MacID As Long, rs As Recordset, ByRef mac As String) As Boolean
          Dim blnFound As Boolean
15290     With rs
15300         .MoveFirst
              ' Check each record
15310         While Not .EOF
                  ' Check the name of the column
                  ' If the current column holds the item number that the user entered
15320             If .Fields(0).Value = MacID Then
                      ' ... then get the record and display its values in the controls
                      ' Set the found flag to true (we will use it later)


15330                 If IsNull(.Fields(2)) Then    ' The record is not in work yet! 

15340                     On Error GoTo FailedLock  ' if we fail to lock and update, dont try again, fail it!
15350                     blnFound = True
15360                     mac = .Fields(1).Value
15370                     .Edit
15380                     .Fields(2) = 3
15390                     .Update
15400                     MarkMACInWork = blnFound
15410                     Exit Function
15420                 Else  ' The record is already in work, return false
15430                     MarkMACInWork = False
15440                     Exit Function
15450                 End If
15460             End If
                  ' In case you didn't find it, move to the next record
15470             .MoveNext
15480         Wend
15490     End With

FailedLock:  ' Fail and return FALSE, we will get the next record!
15500     MarkMACInWork = False
15510     Exit Function
End Function

Open in new window



sOMETIME, IT ENDS UP THAT 2 OR MORE MACHINES ARE WORKING ON THE SAME RECORD.

What am I doing wrong???

Thanks!
Bruce
0
Comment
Question by:Bruj
  • 2
3 Comments
 
LVL 22

Accepted Solution

by:
8080_Diver earned 2000 total points
ID: 37006593
When the second machine encounters the record that the first machine is already working on, what happens?

In general, if the first machine accesses the record and updates it to In Work but is still making changes to the record, then that record is still locked and the second machine won['t be able to access it to find out that In Work has been set.  You may need to extract the data from the columns in the record into variables and then save the record.  Once you have the changes completed, against the values in the variables, you would then execute an UPDATE query to modify the values in the record.
0
 

Author Comment

by:Bruj
ID: 37021615
In general, if the first machine accesses the record and updates it to In Work but is still making changes to the record, then that record is still locked and the second machine won['t be able to access it to find out that In Work has been set.
>> I am editing the record, then changing to "in work" and right away updating. I would think that this would allow the second machine to see the record. ( and it looks like you are implying that as well, becuase I am NOT keeping the record locked.)


You may need to extract the data from the columns in the record into variables and then save the record.  Once you have the changes completed, against the values in the variables, you would then execute an UPDATE query to modify the values in the record.
>> I am not sure I follow this one...
What I am doing is that once the record is put "in work, All I need from that record is one field (MAC), then my program is processing data about that item (takes about 20 seconds) and then I want to edit that record, and then with the new data update.
Both times I am touching the record,, it is opened only for a minimum amount of time. If I cannot lock it on the LAST contact, then I will sleep and then try to relock and then update.

Thanks
Bruce
0
 

Author Closing Comment

by:Bruj
ID: 37245031
I was updating query, but I found I had put 1 line in wrong position. Tweeaked and is now working.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses
Course of the Month15 days, 12 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question