Re: Any way to get an execution trace out of Access?

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.

"mscertified" <rupert@xxxxxxxxxxxxx> wrote in message news:B68960B5-8EC0-40A7-92DA-9481E19FA830@xxxxxxxxxxxxxxxx
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!

.

Relevant Pages

  • Re: Any way to get an execution trace out of Access?
    ... I'm not using SQL Server - this is all Access. ... SQL Server and the problem was the standard transaction isolation level was ... The problem appeared to be that the Select subquery would execute ...
    (microsoft.public.access.formscoding)
  • Newbie help with binary fields and locks
    ... If I were not doing this in a SQL database, I would set up an array of bits and simply look for the first zero bit. ... and a column to indicate the length of the varbinary field (which is really my maximum number of generateable strings). ... I am assuming that I can execute a Begin Transaction with the SqlCommand.ExecuteNonQuery method. ...
    (microsoft.public.sqlserver.server)
  • Re: Urgent problem: Any help greatly appreciated
    ... Can you combine those store procedures to derive a new one in your SQL ... > I am trying to execute a number of SQL stored procedures in a single ... > transaction. ... > executing DataAccessProvider.executeBatchTransaction(ArrayList cmds", ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Urgent problem: Any help greatly appreciated
    ... Can you combine those store procedures to derive a new one in your SQL ... > I am trying to execute a number of SQL stored procedures in a single ... > transaction. ... > executing DataAccessProvider.executeBatchTransaction(ArrayList cmds", ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Urgent problem: Any help greatly appreciated
    ... Can you combine those store procedures to derive a new one in your SQL ... > I am trying to execute a number of SQL stored procedures in a single ... > transaction. ... > executing DataAccessProvider.executeBatchTransaction(ArrayList cmds", ...
    (microsoft.public.dotnet.general)