Cursor location, type and record-locking considerations

[KevCon]

a few multi-user environment ADP questions if you don't mind...

1) Ok, first question is, having read that CursorLocation is inherited, maybe I'm confused, but if I open a connection (cn) without specifying a CursorLocation, the default is adUseServer, which means specifying a CursorLocation for the subsequent recordset (rs) such as adUseClient is ignored and adUseServer is used?

which means the following code is incorrect:

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = CurrentProject.Connection
Set rs = New ADODB.Recordset
Set rs.ActiveConnection = cn

With rs
.Source = "SELECT * FROM tbl_Employees"
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.CursorLocation = adUseClient
.Open
End With

-----------
and should be:

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = CurrentProject.Connection
cn.CursorLocation = adUseClient 'ADDED TO SPECIFY
Set rs = New ADODB.Recordset
Set rs.ActiveConnection = cn

With rs
.Source = "SELECT * FROM tbl_Employees"
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.CursorLocation = adUseClient 'DON'T NEED TO SPECIFY SINCE IT'S INHERITED??
.Open
End With


2) other question is, did I read that keyset cannot be used with adUseClient, forcing me to use adOpenStatic?

3) and can pessimistic locking be used with adUseServer/adOpenDynamic ?

4) if I set the OnOpen event to some ADO code to bind a form, then do subforms that are bound to sub-tables inherit the same rs properties automatically or should I set an OnOpen for each of those as well?

thanks for your assistance

 

[cmmrfrds]

The Recordset is a separate object from the connection.
What you have here will override the default.
With rs
.Source = "SELECT * FROM tbl_Employees"
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.CursorLocation = adUseClient
.Open
End With
ADO will force the cursortype to adOpenStatic when using a client side cursor.

What is your requirement for keyset? I have never found a need in an Access Application for keyset. Why not static? I suggest reading up on keyset and dynamic cursors since they will produce other problems to deal with.

Are you using unbound Forms?

Do you really have a need for pessimistic locking or dynamic cursors?

 

[KevCon]

thanks for replying, yes I have my forms unbound, no problem there,
and no, after reading quite a bit over the past few weeks, I don't think adUseServer or Dynamic or Pessimistic are the best strategies.

I appreciate you confirming that adUseCLient sets adOpenStatic, I thought I read that but could not find it again.

it sounds to me like:

With rs
.Source = "SELECT * FROM tbl_Employees"
.LockType = adLockOptimistic
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.Open
End With

is the best method, however, not specifying the CursorLocation for the Connection, but rather specifiying it for the Recordset (I've seen with and without), what does that mean for the Connection??

thanks..

 

[cmmrfrds]

My understanding is that the cursorlocation on the connection object can be used to override or set the default location for the subsequent recordset objects. For example, if you set it on the connection then any recordsets using the connection would use that setting. Thereby overriding the default server side location on the recordset. Since it is only 1 line of code, I would set it on the recordset which will provide more documentation for others that look at the code.

 

[KevCon]

good point, is always good to document, however if I don't want to I'm thinking since the Connection's cursor location sets the recordset, and adUseServer sets adOpenStatic, then the following (which appears to work) is true:

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = CurrentProject.Connection
cn.CursorLocation = adUseClient
Set rs = New ADODB.Recordset
With rs
Set .ActiveConnection = cn
.Source = "SELECT * FROM tbl_Employees"
.LockType = adLockOptimistic
.Open
End With
Set Me.Recordset = rs

..
I suppose I will have to run some concurrency tests, but it works.

 

[KevCon]

.. I meant adUseClient sets type to adOpenStatic...

 

[cmmrfrds]

That should work.

If you want some indepth insight into ADO and is very clear reading, then pick up the book by Rob McDonald called "Serious ADO". I have this book and it is very valuable.