Multi user enviroment, findfirst method and update

[MacW]

Hi,
need help please
I have a problem in multi user enviroment, with finding and locking a record in database.


When one user finds, blocks and edit record ... another user in the same time can find the same record and overwrite it (User and Status values).
It is just like he didn't see the changes making by another user.

Each user have a copy of the front-end, back-end is on the shared disk.
Here is the code to find new record:

...
Set rs = db.OpenRecordset("tblTable1", dbOpenDynaset)
rs.FindFirst "[Status]='New' And [User] Is Null"
        ws.BeginTrans
                rs.LockEdits = True
                rs.Edit
                    rs![User] = username
                    rs![Status] = "Assigned"
                rs.Update
        ws.CommitTrans
rs.Close
...

I don't know where I do mistake ...

[ItsMe]

rs.LockEdits = True is giving you a pessimistic lock. However, this is only lasting for a split second until after rs.Update is executed.

If you want a lock to last longer than it takes for a single procedure to execute, you will need to use additional columns within a given recordset to indicate to Access record(s) are unavailable.

I will typically employ a SessionID field where a unique value is generated by the User's machine and placed in every record for that given session.

[MacW]

Thanks for the quick answer, it make sense.
But how to mark the recordset with this unique SessionID ...

I thought that when I edit my recodset and update with username, another user can no longer find this record...
You said that it is pessimistic lock and it takes until rs.Update ... so why when record edit was between rs.LockEdits and rs.Update, another user who was searching for the record with Status=New - found this record...

[ItsMe]

...so why when record edit was between rs.LockEdits and rs.Update...

Not sure how you would test for this since the entire process is completed in under a second.

You can manage which records are retrieved by using queries. If use criteria within a query to exclude records that have a value within the SessionID field then, that dataset will not "see" the records in the table with a value in the SessionID field.

[MacW]

... I still don't understand ...
for example
I'm looking for a recordset with value Status="new", in the same time another user is looking for the same record ...
I change this value "new" to "assigned"
one of us found this record first ... so why when I found it first and edit it to new value and record was locked untill rs.update,
another user found it, despite he have criteria to look only for Status="new" ...
It's just like I change the value and he found the same record but don't see that I take (he don't see changes), the value in the database is not refreshed ...

any help will be appreciated

[orange]

You may get some insight from the code examples here. I've read the syntax etc and samles but can't say I understand it enough to comment.
If you look at his Pessimistic code, he has some error trapping (but I also see the on Error goto 0?????). You might be able to test/trap, but the issue may be related to workload and what else is happening on the system.
Good luck.

Let us know if you find something.

[ItsMe]

The way I understand it and you can google it to verify...

When you retrieve records via a query the SQL determines the dataset. What is held in memory and displayed via the form is the Recordset. I view a recordset as a single record/row but, that is a simplistic viewpoint. When you navigate to a new or different record/row within the dataset, the record will be committed to the table via the form's Recordset. You can do a similar thing via VBA and a DAO Recordset.

If I recall correctly, a form's Recordset will represent many records but does not have to include all records from the original dataset. A form has properties, like Filter, that can affect the Recordset independent from the Original query object.

What I am trying to get at is that criteria in a query object can act independent of a form's recordset. If you open a form and adjust it's Recordet, that adjustment will not necessarily be reflected in the table. It is a matter of timing and whether or not the Recordset has been committed to the table.

Methods like Update, Requery, etc will have an impact on both the table and the form's Recordset. Use these methods in a specific and determined order/manner.