To open a recordset

[easycode]

Hello all

If i am going to develop a database with VBA what should be the best way to open a recordset considering that it will share info among 10 users (network environment)


CursorType, CursorLocation, LockType, etc

adlockOptimistic, adlockPessimistic, adOpendynamic, adOpenStatic, ......

and what should be any other considerations to take.

Thanks for your help, it's appreciated

 

[easycode]

forgot to mention i'm using ADO

Frav

 

[Golom]

Recordsets are not "shared" in the sense that only one is opened and everyone uses it. Each user will have their own instance of the recordset. The options that you use will depend on what use you want to make of the recordset. Here's an overview of what various Cursor Type settings do

adOpenForwardOnly Forward-only cursor. Default. Identical to a static cursor except that you can only scroll forward through records. This improves performance in situations when you need to make only a single pass through a recordset.

adOpenKeyset 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 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 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.

Similarly, Lock Type constants have these meanings

adLockReadOnly Default. Read-only—you cannot alter the data.

adLockPessimistic Pessimistic locking, record by record—the provider does what is necessary to ensure successful editing of the records, usually by locking records at the data source immediately upon editing.

adLockOptimistic Optimistic locking, record by record—the provider uses optimistic locking, locking records only when you call the Update method.

adLockBatchOptimistic Optimistic batch updates—required for batch update mode as opposed to immediate update mode.

and finally, Cursor Locations

adUseClient Uses client-side cursors supplied by a local cursor library. Local cursor services often will allow many features that driver-supplied cursors may not, so using this setting may provide an advantage with respect to features that will be enabled.

adUseServer Default. Uses data-provider or driver-supplied cursors. These cursors are sometimes very flexible and allow for additional sensitivity to changes others make to the data source. However, some features of the Microsoft Client Cursor Provider (such as disassociated recordsets) cannot be simulated with server-side cursors and these features will be unavailable with this setting.

 

[easycode]

Thanks Golom for replying,
I am troubleshooting a database and i am frequently getting catastrophic errors; when i debug i can see sometimes is because 2 users are at the same time trying to save a different contact record in the same table because the application stops when opening the cursor rstfff.open "..." ,... Im using access 2002 now, but when i was using access 2000 there was no problem, so i started to debug the recordsets

 

[Golom]

Open the database in Access and look at "Tools / Options / Advanced". Select "Record Level Locking". If the JET Engine is doing page locks rather than record locks, you will probably see conflicts even though users are not attempting to edit the same record.

 

[easycode]

Appreciate you replies

That could be the solution for my headaches, i'll investigate more about that, but in the mean time

I have my setting like this:

-Default Record Locking is set to No locks
-open database using record-level locking is check marked


i just read this:

In a networked environment, the best setting is usually Edited Record. Note: When a record is being edited, all records on it's 'page' are locked.

Should i change to Edited Records