hide a record while it's being edited in a form (1 Viewer)

[shura30]

Shura30,

You may find this article on queue based applications by Philipp Stiefel helpful for concept/background.
(same author as the Transaction article suggested by ebs17 in #31)

Great resource thanks, this is the exact scenario I need to address and it's literally the article, almost word for word
Looks like the UPDATE statement is the way to go

quotes from the article:

There should be only one table relevant for managing the queue. You can either put a LockUser- and LockTime-Field (more on the latter in a minute) right into the facts table (queue table) or you can create an additional table for managing the locks. That table would need those two fields and the primary key of the locked record. - There is not much difference between these two approaches.

Any number of users can read the same data from a database at the same time. So, when querying the next free record first and then locking the record, it is possible that two users query the same, not yet locked, record. There will be some latency between reading the record and writing the lock, so this might happen more often than you think.


To prevent this, you need to write the lock information first. That forces the database to serialize the operation. No two user will ever be able to lock the same record. Only read the record from the queue after locking it.

 

[Pat Hartman]

how does access engine handle simultaneous update requests?

Updates are always serialized. Only one user can lock a record at one time. The update query I suggested solves the problem. The Where clause is not satisfied for the second update in the queue so you have to trap the error.

If you use DAO/ADO to update the table, you need to use a transaction so the record you intend to update can be controlled by you with an update lock that will prevent any other user from obtaining a lock.

How to use transactions in Microsoft Access VBA

How to use transactions in Microsoft Access VBA when executing multiple queries with VBA code.

codekabinett.com

 

[arnelgp]

Looks like the UPDATE statement is the way to go

but if there are 2 users who update on the same time? problem! you have a Deadlock or even a Lockdown, when 2 or more users compete for the same resources. Deadlock meaning your app now becomes unresponsive and the screen just turned white. the only way out is to Ctrl-Alt-Del!

 

[ebs17]

Note on the frequently mentioned transaction:
Line from #33

Set rsLog = db.OpenRecordset("SELECT ID FROM tblLog", dbOpenDynaset, dbFailOnError)

dbFailOnError represents a small transaction in itself. In the event of an error, a rollback occurs.

 

[shura30]

@ebs17
according to what we did so far, we change me.recordsource with a query from within vba, is there a way to unbind after loading without losing the values?
this is the scenario:
user loads a task, admin deletes that task from the main table
I still wish for the user to complete it, assign an exit status and save the information to the exit table
of course if someone deletes the record, the form will show #deleted and a bunch of nonsense is added to the exit table, is there a simple solution to this?

 

[ebs17]

Naturally. ADODB knows Disconnected Recordsets (your keywords for your own research).
Load the query into an ADODB recordset and assign this to the form as a RecordSource. Later, the recordset can be reconnected to the backend and will introduce its changes there.
With ADODB and OLEDB and T-SQL in the query you could also refer directly to the existing SQL Server and use it directly.

admin deletes that task

Immediately? Why?
As an alternative, you do not load the record into a bound form, but rather into a collection of unbound controls in a form.
After completing an edit, you then load the content into the desired tables using your own measures (append query, Recordset-AddNew).

 

[Pat Hartman]

but if there are 2 users who update on the same time? problem! you have a Deadlock or even a Lockdown, when 2 or more users compete for the same resources.

Deadlocks do not occur when there is only one resource that needs to be locked. User1 gets the lock, does his business, and releases the lock. Then user2 gets the resource. Usually without you knowing there was a delay.

Deadlocks occur when there is more than one resource that an application needs. User1 locks table1, user2 locks table2 then tries to lock table1 but can't, meanwhile, user1 is trying to lock table2 and can't ----- this is a deadlock.

This is handled in code by ensuring that you always lock the multiple resources in the same order. User1 locks table1, user2 tries to lock table1 but can't, user1 locks table2, does his business, releases both locks. User2 continues if his request hasn't timed out.

 

[Pat Hartman]

admin deletes that task from the main table

Why are you deleting tasks? Why are they not marked complete and left in the table?

 

[shura30]

Why are you deleting tasks? Why are they not marked complete and left in the table?

tasks come from an external crm, they often come back with an updated status. Also no point in keeping a huge tblTasks as the one where edited records are stored is worth (for analysis purposes)

anyways, the update method still allows the same record to end to two different users, I really have no idea why it's still possible so I might need to test your solution

if you feel like wasting more time, this is the query that updates the record, I did split it into 3 different strings as I'm hating the double quotes and the readability & _
feel free to stomp on my sql skills :

        contattiQuery = "SELECT codice " & _
                        "FROM tabellaSR " & _
                        "WHERE datalavorazione Is NULL And tipoBacklog = """ & TempVars("backlog") & """ And Substato Like ""*contatto eff"" And DateDiff(""h"", tabellaSR.[Data Aggiornamento], Now()) < 7"
    
        top1query = "SELECT TOP 1 Codice" & _
                    " FROM tabellaSR" & _
                    " WHERE dataLavorazione Is NULL" & _
                        " and codice NOT IN (" & contattiQuery & ")" & _
                        " and tipoBacklog = """ & TempVars("backlog") & """" & _
                    " ORDER BY substato LIKE ""*contatto eff"", substato DESC, DateValue(format(tabellaSR.[Data Aggiornamento], ""dd/mm/yyyy"")), timevalue(format(tabellaSR.[Data Aggiornamento], ""hh:mm:ss""))"

        lockQuery = "UPDATE tabellaSR " & _
                    "SET dataLavorazione = """ & lockTime & """, lockedBy = """ & TempVars("Username") & """, stored = ""no"" " & _
                    "WHERE Codice IN (" & top1query & ")"
        
        dbSR.Execute lockQuery, dbFailOnError
        
        If CBool(dbSR.RecordsAffected) Then
            lockedID = DLookup("Codice", "tabellaSR", "dataLavorazione = """ & lockTime & """ AND lockedBy = """ & TempVars("Username") & """")
        
        Else
            MsgBox "Non ci sono SR da lavorare, torno al login"
            DoCmd.Close acForm, Me.Name, acSaveYes
            DoCmd.OpenForm "Login"
            Exit Sub

 

[arnelgp]

if you feel like wasting more time, this is the query that updates the record,

49 post and still no solution, use IBM (I Better Manual).

 

[ebs17]

@shura30:
You want suggestions, you discuss some of them, but you don't understand them (at least not visibly), and you probably don't really want to try them out either.

You persist in your idea.
In my example in #33, I had determined times of around 5 milliseconds for a local and small table tblTasks for the actual locking (selecting ID and writing ID to log table) and around 10 milliseconds for the overall process before loading the record.
In the first mentioned time of 5 milliseconds, a theoretical risk of double selection remains.

Now compare with your variant, which you can add a rough time measurement to:

'...
Dim sngTime As Single
sngTime = Timer
dbSR.Execute lockQuery, dbFailOnError
MsgBox Timer - sngTime & " seconds were needed for this"
' ...

Your WHERE part with subqueries and complicated criteria is obviously very complex and will take a lot of time, especially if you access a backend table via the network instead of a local table and the table is also slightly larger. This is the time window where multiple selection is possible. Please tell us what time you determined.