Yep set it to 'yes'
When the DBA said there was deadlocking what other processes was involved in the deadlock? there is always 2 or more.
I will have to get back to you with the specifics, but I know that there are other people using the same table via a different (read only) query.
Are there other applications using this database or just your access front end?
Only one application uses the database, but up to 25 people at a time are able to use it (Split database with each user having their own FE), and between 5-10 of them are involved in the testing.
This may be specific to SQL server, but does selecting "No Locks" for query helps any at all?
In Access's query builder, if you open properties windows (you have to click on gray area to actually bring up the query's properties as it just show field properties by default), there is a property "Locking Type" where you can choose Edited Records, Table (?), or No Locks.
In my case (MySQL), I specify No Locks for any read-only recordsets, and Edited Records for any recordsource. However, unlike SQL Server, InnoDB does not require a lock to read the row as it uses MVCC to sidestep that problem entirely.
DoCmd.OpenForm strFormname, , , , , acHidden
With Forms(strFormname)
'Set the forms record source and turn on the form
.RecordSource = "SELECT * FROM SomeTable WHERE PKID = " & SelectedID
.Visible = True
.SetFocus
End With
.As far as the deadlocks, the same thing generally applies but you really need to find the cause of the deadlock. Applying the fix will reduce the frequency of deadlocks but won't eliminate them.
[COLOR=black][FONT=Verdana]DoCmd.OpenForm strFormname, , , , , acHidden[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]With Forms(strFormname)[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]'Set the forms record source and turn on the form[/FONT][/COLOR]
[COLOR=black][FONT=Verdana].RecordSource = "SELECT * FROM SomeTable WHERE PKID = " & SelectedID[/FONT][/COLOR]
[COLOR=black][FONT=Verdana].Visible = True[/FONT][/COLOR]
[COLOR=black][FONT=Verdana].SetFocus[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]End With[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Private Sub ResearcherID_DblClick(Cancel As Integer)[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Dim rst As Recordset[/FONT][/COLOR]
[COLOR=black][FONT=Verdana][COLOR=red][B]' DoCmd.OpenForm "fmrPeople", acNormal[/B][/COLOR][/FONT][/COLOR]
[COLOR=black][FONT=Verdana][COLOR=red][B]DoCmd.OpenForm strFormname, , , , , acHidden[/B][/COLOR][/FONT][/COLOR]
[COLOR=black][FONT=Verdana]If IsNull(Me.ResearcherID) Then[/FONT][/COLOR]
[COLOR=black][FONT=Verdana][COLOR=black][FONT=Verdana][COLOR=black][FONT=Verdana][COLOR=red][B]' If the field on the main form is BLANK, open the Add Contact[/B][/COLOR][/FONT][/COLOR]
[B][COLOR=#ff0000]' Form and start at the next available record (ADD A RECORD)[/COLOR][/B]
[/FONT][/COLOR]DoCmd.GoToRecord , , acNewRec[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]' Set the default date to the current date[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]' and Position the cursor on the Title Field[/FONT][/COLOR]
[COLOR=black][FONT=Verdana][Forms]![fmrPeople].Form.DateAdded = Date[/FONT][/COLOR]
[COLOR=black][FONT=Verdana][Forms]![fmrPeople].Form.Title.SetFocus[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Else[/FONT][/COLOR]
[COLOR=black][FONT=Verdana][COLOR=black][FONT=Verdana][COLOR=red][B]' If the field on the main form is not BLANK, open the Add Contact[/B][/COLOR][/FONT][/COLOR]
[B][COLOR=#ff0000]' Form pointing at the selected record (UPDATE A RECORD)[/COLOR][/B]
[COLOR=red][B]{ NOT SURE WHAT TO DO HERE }[/B][/COLOR][/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Set rst = Forms!fmrPeople.Form.RecordsetClone[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]rst.FindFirst "[Person_ID] = " & CStr(Me.ResearcherID.Column(1))[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]If Not rst.NoMatch Then[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Forms!fmrPeople.Form.Bookmark = rst.Bookmark[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Else[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]MsgBox CStr(Me.ResearcherID.Column(0)) & " Not Found!"[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]End If[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]' Set the default date to the current date[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]' and Position the cursor on the Title Field[/FONT][/COLOR]
[COLOR=black][FONT=Verdana][Forms]![fmrPeople].Form.DateAdded = Date[/FONT][/COLOR]
[COLOR=black][FONT=Verdana][Forms]![fmrPeople].Form.Title.SetFocus[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]rst.Close[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Set rst = Nothing[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]End If[/FONT][/COLOR]
[COLOR=black][FONT=Verdana][COLOR=red][B].Visible = True[/B][/COLOR][/FONT][/COLOR]
[COLOR=black][FONT=Verdana][COLOR=red][B].SetFocus[/B][/COLOR][/FONT][/COLOR]
[COLOR=black][FONT=Verdana]End Sub[/FONT][/COLOR]
Did you try my suggestion of setting data entry to yes??
Rather than try and code a very similar end result in VBA, the idea is to stop you returning all the records when you open the form, which should stop the locking
Ok no probs,
I would have buttons on the form to put the form in different modes, for example add new record button, search for record button (here you can do the modify record bit, datAdrenaline's example would be good for this)
I had buttons there and I was asked to remove them. Apparently, some of the users thought that "Add Record" referred to writing the current contents of the Form to the database, and they kept losing updates.
Basically you want to avoid returning the whole record set as I think this is what is causing you problems.
I totally agree. I also found some articles describing how SQL server handles Access Query vs SQL Server View implementation. Apparently, when you implement an Access Query, you have several types of write locking choices available (the one we use is "No Locks", which does not lock the record until a write is requested). On the other hand, an SQL Server View request for data locks the table until all of the requested data is transferred.
If this is true, then this is most likely the source of the problem, and the answer lies in finding a way to determine of the table is locked before the write and to delay the write (retrying "n" times before timing out) until the locking status has passed.
Just out of interest and because i dont think anyone has asked this question, how many records in the underlying table?
The table is what I would describe as small to medium sized and currently contains about 9000 records that are about 320 characrters allocated for each (Plus one varchar(Max) (for comments) that is rarely used. The actual usage is an average of 100 characters or less.
To be honest mate, someone has told you to remove the buttons and now your trying to do everything with the one form and by the sounds of it your data is quite wide even if you dont have many records.
I would tell them that you are going back to the old way because this is causing performance issues, and a simple bit of user training can't be too much to ask can it?
Maybe you could even make your buttons look like the default record selectors then they hopefully wont know the difference.