Form locking with multiple users

[MSAccessRookie]

I have a problem with Locking on one of my user input forms. The program uses an MS Access 2003 Front End and an SQL Server 2005 Back End to store the tables. The structure of the table being used, the query that is the recordsource for the form, and the code for the two methods of opening the form are all shown below. When multiple users try to add a user at the same time, we get intermittent deadlocks. This is not a normal procedure but it did happen several times on the initial testing day. Almost everything else was perfect, but this was a major problem issue (they called it a show-stopper level) for the users.

Users enter the form through one of two methods.

1. There is a button marked "Add Person" that calls the Macro below
2. A user can Double click on a name and also enter the form with the event code listed below.

One additional Point that may be of interest. I changed the form from "Form View" to "DataSheet View", and discovered that the query displays itself as having TWO AutoNumber Fields (Person_ID and Researcher). This has to be incorrect on at least two levels that I am aware of:

1. Access only allows one AutoNumber field per table, and all the values come from the same table.
2. While the Person_ID field is distinct for each record, has no NULL values, and is the Primary Key for the table, the Researcher Field contains duplicates and also contails NULL Values.


Does anyone have any Ideas as to how this is possible, and if it is in any way related to my problem?

-----------------------------------------------------------------------

TABLE

[B][SIZE=3][B][FONT=Courier New]TABLE dbo.tblPeople([/FONT][/B][/SIZE][/B]
[B][SIZE=3][B][FONT=Courier New]Person_ID int IDENTITY(1,1) [COLOR=red]NOT NULL[/COLOR],[/FONT][/B][/SIZE][/B]
[B][SIZE=3][B][FONT=Courier New]Researcher nvarchar(255) [COLOR=red]NULL[/COLOR],[/FONT][/B][/SIZE][/B]
[B][SIZE=3][B][FONT=Courier New]L_Name nvarchar(50) [COLOR=red]NULL[/COLOR],[/FONT][/B][/SIZE][/B]
[B][SIZE=3][B][FONT=Courier New]F_Name nvarchar(50) [COLOR=red]NULL[/COLOR],[/FONT][/B][/SIZE][/B]
[B][SIZE=3][B][FONT=Courier New]M_Initial nvarchar(50) [COLOR=red]NULL[/COLOR],[/FONT][/B][/SIZE][/B]
[B][SIZE=3][B][FONT=Courier New]Title nvarchar(50) [COLOR=red]NULL[/COLOR],[/FONT][/B][/SIZE][/B]
[B][SIZE=3][B][FONT=Courier New]CommonName nvarchar(50) [COLOR=red]NULL[/COLOR],[/FONT][/B][/SIZE][/B]
[B][SIZE=3][B][FONT=Courier New]Notes nvarchar(max) [COLOR=red]NULL[/COLOR],[/FONT][/B][/SIZE][/B]
[B][SIZE=3][B][FONT=Courier New]email nvarchar(50) [COLOR=red]NULL[/COLOR],[/FONT][/B][/SIZE][/B]
[B][SIZE=3][B][FONT=Courier New]DateAdded datetime [COLOR=red]NULL[/COLOR],[/FONT][/B][/SIZE][/B]
[B][SIZE=3][B][FONT=Courier New]SSMA_TimeStamp timestamp NOT [COLOR=red]NULL[/COLOR])[/FONT][/B][/SIZE][/B]

QUERY

[B][SIZE=3][B][FONT=Courier New]SELECT [/FONT][/B][/SIZE][/B]
[B][SIZE=3][B][FONT=Courier New]IIf(IsNull(L_Name),"",L_Name) & [/FONT][/B][/SIZE][/B]
[B][SIZE=3][B][FONT=Courier New] IIf(IsNull(L_Name),"",IIf(IsNull(F_Name),"",", ")) & [/FONT][/B][/SIZE][/B]
[B][SIZE=3][B][FONT=Courier New] IIf(IsNull(F_Name),"",F_Name) AS Expr1, [/FONT][/B][/SIZE][/B]
[B][SIZE=3][B][FONT=Courier New]Person_ID, [/FONT][/B][/SIZE][/B]
[B][SIZE=3][B][FONT=Courier New]Researcher, [/FONT][/B][/SIZE][/B]
[B][SIZE=3][B][FONT=Courier New]DateAdded, [/FONT][/B][/SIZE][/B]
[B][SIZE=3][B][FONT=Courier New]Title, [/FONT][/B][/SIZE][/B]
[B][SIZE=3][B][FONT=Courier New]F_Name, [/FONT][/B][/SIZE][/B]
[B][SIZE=3][B][FONT=Courier New]M_Initial, [/FONT][/B][/SIZE][/B]
[B][SIZE=3][B][FONT=Courier New]L_Name, [/FONT][/B][/SIZE][/B]
[B][SIZE=3][B][FONT=Courier New]email, [/FONT][/B][/SIZE][/B]
[B][SIZE=3][B][FONT=Courier New]Notes, [/FONT][/B][/SIZE][/B]
[B][SIZE=3][B][FONT=Courier New]IIf(IsNull(Title),"",Title & " ") & [/FONT][/B][/SIZE][/B]
[B][SIZE=3][B][FONT=Courier New] IIf(IsNull(F_Name),"",F_Name & " ") [/FONT][/B][/SIZE][/B]
[B][SIZE=3][B][FONT=Courier New] L_Name AS FormalName, [/FONT][/B][/SIZE][/B]
[B][SIZE=3][B][FONT=Courier New]IIf(Not (IsNull(F_Name)),F_Name & " ",Title & " ") & [/FONT][/B][/SIZE][/B]
[B][SIZE=3][B][FONT=Courier New] L_Name AS SemiFormalName, [/FONT][/B][/SIZE][/B]
[B][SIZE=3][B][FONT=Courier New]CommonName, [/FONT][/B][/SIZE][/B]
[B][SIZE=3][B][FONT=Courier New]SSMA_TimeStamp[/FONT][/B][/SIZE][/B]
[B][SIZE=3][B][FONT=Courier New]FROM tblPeople[/FONT][/B][/SIZE][/B]
[B][SIZE=3][B][FONT=Courier New]ORDER BY 1;[/FONT][/B][/SIZE][/B]

MACRO

[B][SIZE=3][B][FONT=Courier New]Macro Name:  AddPerson[/FONT][/B][/SIZE][/B]
[B][SIZE=3][B][FONT=Courier New][B][SIZE=3][B][FONT=Courier New] Statement1: OpenForm[/FONT][/B][/SIZE][/B]
[/FONT][/B][/SIZE][/B][B][SIZE=3][B][FONT=Courier New][B][SIZE=3][B][FONT=Courier New][B][SIZE=3][B][FONT=Courier New] Statement2: GotoRecord[/FONT][/B][/SIZE][/B]
[B][SIZE=3][B][FONT=Courier New] Statement3: GotoControl[/FONT][/B][/SIZE][/B]
 
Entended Definitions
 OpenForm[/FONT][/B][/SIZE][/B]
  Form Name fmrPeople[/FONT][/B][/SIZE][/B]
[B][SIZE=3][B][FONT=Courier New]  View Form[/FONT][/B][/SIZE][/B]
[B][SIZE=3][B][FONT=Courier New]  Window Mode Normal[/FONT][/B][/SIZE][/B]
[B][SIZE=3][B][FONT=Courier New] GotoRecord[/FONT][/B][/SIZE][/B]
[B][SIZE=3][B][FONT=Courier New]  Record New[/FONT][/B][/SIZE][/B]
[B][SIZE=3][B][FONT=Courier New] GotoControl[/FONT][/B][/SIZE][/B]
[B][SIZE=3][B][FONT=Courier New]  Control Name Title[/FONT][/B][/SIZE][/B]

EVENT

[B][SIZE=3][B][FONT=Courier New]Private Sub ResearcherID_DblClick(Cancel As Integer)[/FONT][/B][/SIZE][/B]
 
[B][SIZE=3][B][FONT=Courier New]Dim rst As Recordset[/FONT][/B][/SIZE][/B]
 
[B][SIZE=3][B][FONT=Courier New]DoCmd.OpenForm "fmrPeople", acNormal[/FONT][/B][/SIZE][/B]
 
[B][SIZE=3][B][FONT=Courier New]If IsNull(Me.ResearcherID) Then[/FONT][/B][/SIZE][/B]
[B][SIZE=3][B][FONT=Courier New] ' Start at the next available new record[/FONT][/B][/SIZE][/B]
 
[B][SIZE=3][B][FONT=Courier New] DoCmd.GoToRecord , , acNewRec[/FONT][/B][/SIZE][/B]
 
[B][SIZE=3][B][FONT=Courier New] ' Set the default date to the current date[/FONT][/B][/SIZE][/B]
[B][SIZE=3][B][FONT=Courier New] ' and Position the cursor on the Title Field[/FONT][/B][/SIZE][/B]
 
[B][SIZE=3][B][FONT=Courier New] Forms!fmrPeople.Form.DateAdded = Date[/FONT][/B][/SIZE][/B]
[B][SIZE=3][B][FONT=Courier New] Forms!fmrPeople.Form.Title.SetFocus[/FONT][/B][/SIZE][/B]
 
[B][SIZE=3][B][FONT=Courier New]Else[/FONT][/B][/SIZE][/B]
[B][SIZE=3][B][FONT=Courier New] Set rst = Forms!fmrPeople.Form.RecordsetClone[/FONT][/B][/SIZE][/B]
 
[B][SIZE=3][B][FONT=Courier New] rst.FindFirst "Person_ID = " & CStr(Me.ResearcherID.Column(1))[/FONT][/B][/SIZE][/B]
 
[B][SIZE=3][B][FONT=Courier New] If Not rst.NoMatch Then[/FONT][/B][/SIZE][/B]
[B][SIZE=3][B][FONT=Courier New]     Forms!fmrPeople.Form.Bookmark = rst.Bookmark[/FONT][/B][/SIZE][/B]
[B][SIZE=3][B][FONT=Courier New] Else[/FONT][/B][/SIZE][/B]
[B][SIZE=3][B][FONT=Courier New]     MsgBox CStr(Me.ResearcherID.Column(0)) & " Not Found!"[/FONT][/B][/SIZE][/B]
[B][SIZE=3][B][FONT=Courier New] End If[/FONT][/B][/SIZE][/B]
 
[B][SIZE=3][B][FONT=Courier New] ' Set the default date to the current date[/FONT][/B][/SIZE][/B]
[B][SIZE=3][B][FONT=Courier New] ' and Position the cursor on the Title Field[/FONT][/B][/SIZE][/B]
 
[B][SIZE=3][B][FONT=Courier New] Forms!fmrPeople.Form.DateAdded = Date[/FONT][/B][/SIZE][/B]
[B][SIZE=3][B][FONT=Courier New] Forms!fmrPeople.Form.Title.SetFocus[/FONT][/B][/SIZE][/B]
 
[B][SIZE=3][B][FONT=Courier New] rst.Close[/FONT][/B][/SIZE][/B]
[B][SIZE=3][B][FONT=Courier New] Set rst = Nothing[/FONT][/B][/SIZE][/B]
 
[B][SIZE=3][B][FONT=Courier New]End If[/FONT][/B][/SIZE][/B]
 
[B][SIZE=3][B][FONT=Courier New]End Sub[/FONT][/B][/SIZE][/B]

 

[Banana]

Why have a Macro and VBA code that does essentially same thing. Better to use one method and drop the other. You still can call the same method for both "Add User" button and clicking on user's name.

What does it make you think Researcher is a autonumbering field? What indexes are on the table?

Another question, do you know exactly how/where deadlock occurs? Normally to have a fatal deadlock require that you do something like this:

UserA creates a RecordA, and locks it.
UserB creates a RecordB, and locks it.
UserA wants to read RecordA, but has to wait for UserB to be done with it.
UserB wants to read RecordB but has to wait for UserA to be done with it.

At that point, UserB is waiting on UserA whom is waiting on UserB; that's one way to get a deadlock. So the question to ask is are they able to insert and select at same time without committing? For example, if they could click Add Users open form then starting filling out but stop halfway and go back to main form... that is a recipe for potential deadlock.

I hope that helped some.

 

[MSAccessRookie]

Why have a Macro and VBA code that does essentially same thing. Better to use one method and drop the other. You still can call the same method for both "Add User" button and clicking on user's name.

They both have te ability to open the form and insert in the next record, but that is where the similarity stops.

The Macro depends on nothing, and does ONLY that. It is the original code to call the form.

The VBA Code depends on the value contained in a particular Combo Box Field, and not only does that (when the Combo Box Field is NULL), but also has the ability to allow the user to open the form with an existing record already selected (when the Combo Box Field is NOT NULL). It was added at the request of the users (and is currently suspected to be a possible source of the problem that we are having).

What does it make you think Researcher is a autonumbering field? What indexes are on the table?

Please refer to the attached images of the Form and the DataSheet



Another question, do you know exactly how/where deadlock occurs? Normally to have a fatal deadlock require that you do something like this:

UserA creates a RecordA, and locks it.
UserB creates a RecordB, and locks it.
UserA wants to read RecordA, but has to wait for UserB to be done with it.
UserB wants to read RecordB but has to wait for UserA to be done with it.

At that point, UserB is waiting on UserA whom is waiting on UserB; that's one way to get a deadlock. So the question to ask is are they able to insert and select at same time without committing? For example, if they could click Add Users open form then starting filling out but stop halfway and go back to main form... that is a recipe for potential deadlock.

We do not know how the deadlock occurs yet, but I suspect it might be the testing scenario. Often the easiest way to test something is to take the first available qualifying record and use it for the test. In our case, I am sure that at least some of the users chose the first or second available records for part of the test, because they each have a researcher identified, or the fourth and sixth records, because they each do not have a researcher identified. In the real world of business, this exact event should not happen, since the users will each most likely be handling different researchers when adding or modifying records.

I hope that helped some.

Yes it did, and I thank you for your time. Any further observations will be appreciated as well.

 

[Banana]

FYI just in case you didn't know, you can use [quote]Quote Me![/quote] tag to make a quotation:

Quote Me!

They both have te ability to open the form and insert in the next record, but that is where the similarity stops.

The Macro depends on nothing, and does ONLY that. It is the original code to call the form.

The VBA Code depends on the value contained in a particular Combo Box Field, and not only does that (when the Combo Box Field is NULL), but also has the ability to allow the user to open the form with an existing record already selected (when the Combo Box Field is NOT NULL). It was added at the request of the users (and is currently suspected to be a possible source of the problem that we are having).

Well, for code management, it's always preferable to have one place to do one thing, and if you needed multiple entry points, it's best to just go to the same place, and if necessary, supply optional parameters to change the behavior.

Please refer to the attached images of the Form and the DataSheet

I wonder if it's possible that Researcher is actually referring to PersonID in the control source?

I've seen two autonumber column in a query before, but that is usually when I join a one side table with many side table and insert ID columns from both table. This is not what you did in your case, so I'm not quite how you are getting this. Or it may be something to do with SQL server; which I'm not quite familiar with.

We do not know how the deadlock occurs yet, but I suspect it might be the testing scenario. Often the easiest way to test something is to take the first available qualifying record and use it for the test. In our case, I am sure that at least some of the users chose the first or second available records for part of the test, because they each have a researcher identified, or the fourth and sixth records, because they each do not have a researcher identified. In the real world of business, this exact event should not happen, since the users will each most likely be handling different researchers when adding or modifying records.

That's another thing- Access by default doesn't prepare for such situation and that has to be done by the developers, so if you went and tested with so small set, it may bork the results...

 

[MSAccessRookie]

Well, for code management, it's always preferable to have one place to do one thing, and if you needed multiple entry points, it's best to just go to the same place, and if necessary, supply optional parameters to change the behavior.

I totally agree with this philisophy and will attempt to get permission to do what you suggest, although it may not be necessary to do this right away. Let me run a scenario by you:

o I copy the PRIVATE Sub to a PUBLIC Sub its own Module (or at least in another Module with other PUBLIC Subs).
o I Modify the Sub to handle a parameter indicating the PersonID to Point to when the Form Opens (NULL will still go to a New Record).
o I Modify each Macro call to a Sub Call with a forced NULL parameter

Did I leave anything out?

I wonder if it's possible that Researcher is actually referring to PersonID in the control source?

I've seen two autonumber column in a query before, but that is usually when I join a one side table with many side table and insert ID columns from both table. This is not what you did in your case, so I'm not quite how you are getting this. Or it may be something to do with SQL server; which I'm not quite familiar with.

I would never have thought that it was possible, but that was the exact problem. The Researcher Field was defined to contain the contents of the PersonID Field. This caused two problems. First, it created the two AutoNumber fields, and second, it inserted a value that was defined as an INTEGER into a Field that was defined as a TEXT. Perhaps this condition was related to the problem with the locking? The field has been redefined to point to the Database Table Field Researcher, and initial testing has not been able to reproduce the problem.

That's another thing- Access by default doesn't prepare for such situation and that has to be done by the developers, so if you went and tested with so small set, it may bork the results...

Right on the money with that observation, although I have not heard bork used as a verb for quite a while.

 

[Banana]

I totally agree with this philisophy and will attempt to get permission to do what you suggest, although it may not be necessary to do this right away. Let me run a scenario by you:

o I copy the PRIVATE Sub to a PUBLIC Sub its own Module (or at least in another Module with other PUBLIC Subs).
o I Modify the Sub to handle a parameter indicating the PersonID to Point to when the Form Opens (NULL will still go to a New Record).
o I Modify each Macro call to a Sub Call with a forced NULL parameter

Did I leave anything out?[/code]

That's certainly doable. I would personally just call the public function directly, not using macros at all, but that's just my personal prejudice.

I would never have thought that it was possible, but that was the exact problem. The Researcher Field was defined to contain the contents of the PersonID Field. This caused two problems. First, it created the two AutoNumber fields, and second, it inserted a value that was defined as an INTEGER into a Field that was defined as a TEXT. Perhaps this condition was related to the problem with the locking? The field has been redefined to point to the Database Table Field Researcher, and initial testing has not been able to reproduce the problem.

I know I've made similar screw ups like that before- in midst of hubris and gutting the form, I would rename a control but not set the control source to the right column and thus get weird results, especially for autonumbers, which was what you saw.

Right on the money with that observation, although I have not heard bork used as a verb for quite a while.

Actually, it is a verb. I think you mean you (and I, now when I think about it) haven't heard it in future/present tense; it was always in past.