Records from the query are loaded into a form where I disabled record navigation, on "form_load" the record is set "inUse=true" and a DoCmd.RunCommand acCmdSaveRecord is run, if another FE instance is open, this record should never be loaded from the query and so the form.
why not use XML to create specific menus for users, I strongly do not think what you are suggesting is workable.
@shura30
If you understand the code in #14, you know what's going on. A record is randomly selected from a pool of tasks to be completed (timestamp field GoForEdit is empty, i.e. NULL). Random to further minimize an overlap with another user. A timestamp is set in this record using the recordset so that it is no longer considered for further selection by others. The record ID of this record is remembered via the myID variable. I can't see anything about a user ID that Pat is talking about.
Finally, this record is loaded into the bound form and can now be edited. A record is now a very filtered RecordSource.
Now how can a process be:
buttons the form gets reopened every time with a quick succession of DoCmd.Close acForm and DoCmd.OpenForm
That's not necessary. That's too much effort.
A button can complete saving the existing record. In the event procedure for this:
Code:
If Me.Dirty Then Me.Dirty = False
NewTask
The NewTask procedure is then called in the event procedure. This selects and loads a new record; filtering on the record ID causes the edited record to disappear. Of course, the form should remain open.
So that an entire table is not loaded when the form is first started, the standard RecordSource is set to empty quantity.
Code:
SELECT * FROM tblTasks WHERE False
In Form_Open you can now also call NewTask and get a new record.
What still remains open:
- Completing the processing of the last record in conjunction with closing the form.
- In the NewTask procedure, for a tiny millisecond period (selecting the record and setting the timestamp), there remains the risk that two users will make the call at the same time and accidentally address the same record.
So if you have a lot of hard-working users who work extremely intensively, you would have to install a mechanism so that an error is triggered in the event of double access and this then leads to one of the users simply making a new record selection again.
the file has been modified quite a bit since I opened the post,
I can't really select randomly as tasks need to follow a FIFO pipeline. Since they have a opened date and time stamp I might randomize at least the time but I'd rather a more clean solution that I can't think of rn.
that's going to be the next update.
the form loads the record with the query you posted then adds a note field and an exit status
along with with the record, the save button adds that note and exit field into another table with an update query
in this case, the:
Code:
If Me.Dirty Then Me.Dirty = False
NewTask
would still work or do I need to clean fields manually before loading the next task?
Code:
SELECT * FROM tblTasks WHERE False
is it better to put this in the code itself or in the form bound field?
right not the form field is left empty and all the fields are bound to the query in vba
So if you have a lot of hard-working users who work extremely intensively, you would have to install a mechanism so that an error is triggered in the event of double access and this then leads to one of the users simply making a new record selection again.
I could add a 'locked by user field' then an if statement that checks the right user locked the record then loads it into the me.recordsource
but this still wouldn't cover the double access right when:
Code:
.Fields("GoForEdit") = Now
happens
another thing that's missing is a release record piece of code for when access itself gets (properly) closed
handling a crash might be a little bit tricky tho
If you want to know more precisely, you would have to measure this time under exactly your possible conditions in order to judge how likely it is that two users will trigger the selection at the same time within this time period.
Code:
Set rs = CurrentDb.Openrecordset( _
"SELECT TOP 1 * FROM tblTasks WHERE GoForEdit Is NULL ORDER BY ID", _
dbOpenDynaset)
With rs
.Edit
.Fields("GoForEdit") = Now
myID = .Fields("ID")
.Update
.Close
End With
GoForEdit and ID should of course be indexed in order to run the query efficiently.
I'll put your other questions aside. You obviously haven't worked with a bound form yet.
Do that first, gain knowledge and experience, read tutorials, watch videos, read posts here in the forum. Then we can continue talking.
My attitude: A forum offers help for self-help. Self-help requires the presence of certain basics.
I feel overwhelmed by writing an instruction manual for completely clueless people and I won't do it.
I have this database in order to assign a task to each logged on user. The db is split between be and fe already and everything is on a network drive.
Admins import tasks on the main table where there is a 'inUse' boolean column defaulting as false.
A query reads a few fields from the table and filters out the 'inUse=true' records.
Records from the query are loaded into a form where I disabled record navigation, on "form_load" the record is set "inUse=true" and a DoCmd.RunCommand acCmdSaveRecord is run, if another FE instance is open, this record should never be loaded from the query and so the form.
There's a 'save and continue' button, on_click it simply closes the form and reopens it with a new record where 'inUse=false'
What could go wrong in a network environment between each FE database?could the query and form slow down and show the same record to two or more users?
Is this logic viable?I searched around the web and couldn't find anything like this, I've seen the record lock options but it's not really what I need, 'inUse=true' records should never appear to anyone else beside the one who gets it first.
There is no need to reopen the form when the Record is saved.
In the underlying record source to the Form, simply filter out those records that are in use. When the User on other FE's click the save but, simply requery the form, or apply a new filter. A lot of resources are wasted to close and reopen a form. Use the simplest built-in features that are available.
Sub NewTask()
Dim rs As DAO.Recordset
Dim myID As Long
Set rs = CurrentDb.Openrecordset( _
"SELECT TOP 1 * FROM tblTasks WHERE GoForEdit Is NULL ORDER BY ID", _
dbOpenDynaset)
With rs
If Not .EOF Then
.Edit
.Fields("GoForEdit") = Now
myID = .Fields("ID")
.Update
.Close
Me.RecordSource = "SELECT * FROM tblTasks WHERE ID = " & myID ' this line not in measurement
Else
' no record available
End If
End With
End Sub
The procedure still needs a check for EOF to avoid errors.
For interest's sake, I measured the time required for record selection. On my computer with a local table it's around 4 milliseconds. So this is the problematic period where a double selection of the same record could occur.
This residual risk could be avoided by using an additional log table in which the selected ID is written into a field with a unique index. As is well known, an error occurs with a duplicate.
Now you can encapsulate the entire action in a transaction; in the event of an error, a complete reset is carried out via rollback.
Now you just have to make a new selection.
How many records can a user look at at one time? One. How many records are in your table? 10,000, 100,000, 500,000. Do you think it makes sense to bring 500,000 rows from the server into memory on your local PC when the user can't possibly work with more than one at a time? That is exactly what happens when you bind a form to a table or to a query that has no where clause. This is how many samples you see are coded. Once all the data has been brought across the LAN to memory on your PC, then you can use the Access filter methods to allow the user to find what he wants. Each time the user closes the form and reopens it, all those records are brought across the network again. So, depending on how the users use the application, This could easily be 20 times per hour or more. The bigger your tables are, the slower this process is and the larger the burden it places on your network. If you have ever read any questions from an OP who decided to convert to SQL Server to solve a slowness issue with Access only to find his problem has become much worse after the conversion due to the inefficiency of using this technique.
Open a form, bound to your largest table. Keep in mind if you are working with tiny tables you can use seriously poor techniques and nothing matters. Did the x show up immediately in the 1 of x in the navigation bar? It didn't because Access is fetching the rows a few at a time from the server. The form is displayed as soon as enough records to populate it have been downloaded. The rest are sucked down a little at a time behind the scenes.
sadly this is what most tutorials teach right from the start, in my case, I moved away from closing and reopening the form each time and every query has where clause.
Best practices aren't really explained anywhere without digging.
Tested with a few users working on the file and in the span of a few minutes around 10 records were simultaneously locked by 2 different users.
No errors or warnings on anyone's end, checked the table where an update query logs the result of their editing and the records had the same exact ID and lockedBy value which came from the Now() assignment
I thought of adding the username who got to the record first:
Code:
Sub NewTask()
Dim rs As DAO.Recordset
Dim myID As Long
Dim lockTime as date
Dim locked as Long
locktime = Now()
Set rs = CurrentDb.Openrecordset( _
"SELECT TOP 1 * FROM tblTasks WHERE GoForEdit Is NULL ORDER BY ID", _
dbOpenDynaset)
With rs
If Not .EOF Then
.Edit
.Fields("GoForEdit") = lockTime
.Fields("lockedBy") = TempVar("username")
myID = .Fields("ID")
.Update
.Close
locked = dlookup("ID", "tblTasks", "ID = " & myID & " and GoForEdit = " & locktime & " and lockedBy = & TempVar("username"))
if myID = locked then
Me.RecordSource = "SELECT * FROM tblTasks WHERE ID = " & myID
Else
NewTask
End if
Else
' no record available
End If
End With
End Sub
I think there's still the possibility of a record getting assigned to 2 users but the probability of this happening should lower drastically
So that an entire table is not loaded when the form is first started, the standard RecordSource is set to empty quantity.
With a unique index?
How do you add new records?
In addition, you did not notice the reference to the transaction. You should code this yourself to understand how it works.
The full quote mentioned the GoForEdit field, I'm not using another table to store an indexed row as in my opinion it would wield the same results
the table I mentioned is part of the database design, users get a record from tblTasks, add a note and an exit value then that record is saved to another table for reporting purposes. Keep in mind these ID are searched on an external CRM, some users found themselves working the same external record as another colleague then the reporting table showed saved records with identical IDs and GoForEdit values in the reporting table. Of course ID is not a primary key in the reporting table as these tasks are exported from the CRM and can come back to tblTasks after some time.
trying to think how to apply a transaction negotiation on that code,
the main issue is the fact that once we store "myID = .Fields("ID")" as a variable, without an additional field, there's no way to tell if we locked the correct ID,
if two or more users simultaneously store the same myID value, of course that's valid in their code but an issue for the logic I want
But the ID should be clear (unique index), otherwise you don't have to complain about multiple selections.
Below I have a modified suggestion. An ID (Long) field with a unique index and a LockedDT (Date) field with an index are expected in the tblTasks table. To protect against multiple selections, there is a table tblLog with the ID (Long) field, with unique index.
The same record can be selected by different users in a short period of time. In no case can the same ID be written twice in tblLog. Here the error is used directly for program control.
For additional clarity I have included a measurement, the stopwatch class comes from...
The attached access file contains two tables, three queries, a module and a stopwatch class. If these elements are transferred to your own application, you will be able to carry out speed tests and speed comparisons for a large number of tasks without great effort. This solution is not as broad...
www.access-programmers.co.uk
Code:
Sub NewTask_2()
Dim QSU As New QpcStoppuhr
Dim db As DAO.Database
Dim rsTasks As DAO.Recordset
Dim rsLog As DAO.Recordset
Dim myID As Long
Dim i As Long
Dim xTime As Double
QSU.StartT
Set db = CurrentDb
Set rsLog = db.OpenRecordset("SELECT ID FROM tblLog", dbOpenDynaset, dbFailOnError)
Set rsTasks = db.OpenRecordset( _
"SELECT TOP 3 * FROM tblTasks WHERE LockedDT Is NULL ORDER By ID", _
dbOpenDynaset)
With rsTasks
Do While Not .EOF
myID = .Fields("ID")
i = i + 1
On Error Resume Next
rsLog.AddNew
rsLog.Fields("ID") = myID
rsLog.Update
If Err.Number > 0 Then
i = i - 1
Err.Clear
End If
If i = 1 Then Exit Do
QSU.StopT
xTime = QSU.time
QSU.RestartT
.MoveNext
Loop
.Edit
.Fields("LockedDT") = Now
.Fields("UserID") = 5 ' representative of the registration
.Update
.Close
End With
rsLog.Close
QSU.StopT
MsgBox "ID " & myID & " was selected" & vbCrLf & _
QSU.time + xTime & " milliseconds the process took time" & vbCrLf & _
xTime & " milliseconds the blocking of the record took time"
'Me.RecordSource = "SELECT * FROM tblTasks WHERE ID = " & myID
End Sub
But that's not the issue, I'm trying to gather ideas on how to automatically assign a task exclusively to a user preventing as many issues as I can.
Once a task is inUse no one else should be able to load it in the form
I use a different method than what you are currently discussing. It does continue to show the task as long as it is incomplete and people can view the record. No one else can update it though. When the task is selected I run an update query that updates the selected client record if it is not already locked. I added LockedByID and LockedDT to the client record and it is displayed when the record is viewed so you can see who has the record locked.
I my case, users have no added value but wasted time in seeing a record they can't use/edit
they get their task, work on it, release and move to the next
how does access engine handle simultaneous update requests?
I was thinking to change the way we look at this by using an update query instead of a select then edit
so we straight up update the first record that meets the criteria with "LockedDT" and "UserID" then query back via the same parameters which would represent a unique combination as "UserID" is also unique
I think we're allowing the issue by picking and storing locally an ID regardless of a strong WHERE clause
With a unique index, the first entry is accepted, the second entry throws an error. With the code presented, if an error occurs, the next record is selected and is checked.
What happens if there is a double entry at the identical time? I don't know if this is technically possible. If in doubt, you will then generate a double error. The record remains unselected during this access and is available for reselection.
I see it this way: Before updating the table, the content must first be read, i.e. what corresponds to the filter (where lockedDT is null and lockedby is null). This takes some time. If a second frontend initiates exactly the same process during this time, it can address exactly the same record and would then overwrite the entries if no real lock on the record triggers an error.
What can happen: I don't know exactly. Something like this would have to be determined empirically using mass testing. Until then, I believe that anything can happen: Both users get the same record, only one user gets the record while the other one gets nothing, or in the event of an error, both get nothing.
Therefore, my approach was to trigger a controllable and evaluable error in the event of double access as early as possible and with the least additional effort.
As a second safeguard, I increasingly like the measure of writing the UserID into the table with selection (this is already done in my code) and then comparing this UserID with the frontend user login after loading the record into the form. If there is no agreement, your own entries in the table must be withdrawn and then a new election is simply triggered.
see post #11 again, using semaphore/lock file.
only one user can have a table locked.
once locked, immediately fetch the record, and update it's "flag" field, then unlock the semaphore table so other user can do the same.
the users will go to loops if the table is locked. it will not be locked forever since, automatically, via code it will update the "flag" field and issue an unlock. this will prevent users contending on same record or possible lock-up of system since they are contending on same record.
