I have 8 computers on my access2007 front end , connected to my 2008 sql backend. Most times the users are working on the same patient chart (medical office) and changing data on the same forms. I have an initialize module that creates the same connection and a function that returns the recordset for just about everything. I am getting more and more write conflicts and I find the ado cursor type, location, and lock very confusing. Any advice as to the best way to set up the recordset function?
Public Function GrabRst(strSQL As String) As Recordset
Set GrabRst = New Recordset
.ActiveConnection = CurrentProject.Connection
.Source = strSQL
.LockType = adLockPessimistic
.CursorType = adOpenDynamic
On Error GoTo cont
.Open ' Grabrst.state=0 if closed =1 if open
MsgBox "Unable to open a recordset using the statement: " & strSQL
then throughout the VBA I do this all the time:
private sub whatever()
dim rst as recordset
set rst=grabrst("select * from dbo.patients where [visit#]=" & lngVisit) 'this is one example of a select
<work with rst>
any advice what would be a good cursor loc, type, lock, etc?
I have noticed that rst.updatebatch works all the time.
but like i said i am getting many more write conflicts ( i know that I update the recordsets programmatically and that I have to update the forms before updating the rst...still wondering if i should alter the recordset properties in the GrabRst function.)