Any way to get an execution trace out of Access?

  • Thread starter Thread starter mscertified
  • Start date Start date
M

mscertified

I'm trying to debug a multi-user access feature. The only way I can do this
is to know the execution path taken by each user. Is there any way to get
this out of Access? Setting break points wont work because this code is time
sensitive. Essentially, I am trying to code a system where different users
grab records from a table. So far, after many attempts, I cannot see how this
can be done. Different users are getting the same record but I have no idea
of the execution path the program is taking. Looking at the code it is
impossible that this could happen!
 
If you're doing something like assigning open tasks to users, you may need
to run the sql in a transaction with serial isolation. I had this issue in
SQL Server and the problem was the standard transaction isolation level was
insufficient to protect a single sql statement like this:

Update Task
Set Task.owner = 'thisOwner'
Where
Task.taskID In (Select min(Task.taskID) From Task Where Task.owner Is
Null)

The problem appeared to be that the Select subquery would execute
"simulataneously" for multiple users, so even if the Update portion were
properly controlled, 2 users could be assigned the same task. By increasing
the transaction isolation level, the Select subquery was forced to be part
of the Update transaction. From reading the documentation I thought a single
sql statement would execute as a transaction unit, but this did not appear
to be the case. Not sure how you assign transaction isolation level in
Access. One thought is to use some other locking mechanism to ensure only a
single user at a time can be executing this type of statement, maybe locking
a single record in a table created just for that purpose. In an Access app
the number of users shouldn't be so large as to create long wait times.
 
Thanks for the response.
I'm not using SQL Server - this is all Access.
To single-thread, what I do is to insert a record to a table. Each user uses
the same primary key so only one INSERT can succeed (I hope). Other users go
into a wait and retry loop (1/2 sec delay belween each retry). The lock
record is deleted when the first user finishes processing. Even so, with this
technique, multiple users are somehow grabbing the same record.

-Dorian
 
Back
Top