adOpenKeySet or adOpenDynaic or adOpenStatic?
We almost always open RecordSet on client side, using CursorLocation as adUseClient (3)
Brief note on CursorLocation:
It is not necessary that we specify "oRs:CursorLocation = adUseClient // 3 " everytime we open a recordset. If we set connection object's CursorLocation property to adUseClient, all recordsets opened with that connection are all by default clientside only. We save one line of code while opening every recordset. Even otherwise it is always desirable to set connection object's CursorLocation to adUseClient.
We can not specify cursor location of recordset opened with methods like oCn:OpenSchema(...), oCn:Execute( cSql ), etc. It defaults to the cusrorlocation of the connection object. If we do not set oCn:CursorLocation to adUseClient, this recordset is opened on serverside and we can not navigate the recordset backwards and not all methods and data work like clientside recordset.
Back to CursorType:
When we look around we see many postings setting custor type either to adOpenKeySet or adOpenDynamic but never adOpenForwardOnly or adOpenStatic.
adOpenKeySet ( 1 )
keyset cursor. Like a dynamic cursor, except that you can't see records that other users add, although records that other users delete are inaccessible from your Recordset. Data changes by other users are still visible.
adOpenDynamic ( 2 )
Uses a dynamic cursor. Additions, changes, and deletions by other users are visible, and all types of movement through the Recordset are allowed, except for bookmarks, if the provider doesn't support them.
adOpenStatic ( 3 )
Uses a static cursor. A static copy of a set of records that you can use to find data or generate reports. Additions, changes, or deletions by other users are not visible.
When we read the above descriptions we consider using one of them and never adOpenStatic.
But what we ignore is that when we open a recordset on the client-side, only permitted cursortype is adOpenStatic. Let us read this:
Note: If the CursorLocation property is set to adUseClient, the only valid setting for the CursorType property is adOpenStatic.
Note: No error will occur if an unsupported value is set, the provider will just change to a supported CursorType instead.
So whatever CursorType we specify adUseKeySet or adUseDynamic, expecting all those benefits described above, ADO will totally ignore our settings and open the recordset as adOpenStatic ONLY.
After opening the recordset with any CursorType, if we ever care to query
? oRs:CursorLocation
thee result will always be 3 (adOpenStatic).
So why this useless and purposeless code oRs:CursorLocation := adOpenDynamic in our program, wasting oneline of code and about 40 keystrokes every time?
Isn't this better?
Once set oCn:CursorLication := adUseclient // 3
and while opening recodsets, save time, energy, space and codesize (and that too with false expectations) by not specifying oRs:CursorLocation and oRs:CursorType. The recordset opened would be exactly the same as the one we open even after specifying them.
When we open with connection string (not connection object) we need to specify cursorlocation, otherwise it will default to server-side cursor.
