Experts advice ADO Connection

[ayh8disjob]

Hi Everyone I need an experts advice on this matter
Keys
adOpenKeyset,adOpenStatic,adOpenDynamic
Lock
adLockReadOnly,adLockOptimistic,adLockPessimistic

Which is best combination to to open a database
that i will use in a multiuser client-server environment?
lets say the screnario is
30 or more Users with SQL server database backend
Screnario are as follows:
1. some will just view a record
2. while others will modify a record
3. while others will try to delete a record

What is the best combination to secure my data
when I open recordset in each screnario?

hope you could help me thanks in advance!

 

[jonscott8]

The typical rule of thumb is to open recordsets with ONLY the functionality necessary, nothing more - nothing less. Apply that to your scenarios to produce the best combination of locktype/cursortype/cursorlocation for each. There are several articles in the MSDN on ADO optimization if you'd like more reference.

http://www.msdn.microsoft.com

PS. there are more jobs out there ;-) Jon Hawkins

 

[MikeCox]

Here's the description for each that my book gives to help you decide -

CursorTypes:

adOpenKeyset. You can't see records that have been added to the recordset by other users, but updates and deletes performed by other users do affect your recordset. Most efficient if recordset is large.
adOpenDynamic. You can see all the changes to the data performed by other users while your recordset is open. Usually the least effecient, but most powerful.
adOpenStatic. A copy of all the data for a recordset. Particularly useful when you're looking up data or running reports. Very efficient with small recordsets.

LockTypes:

adLockReadOnly. No updates to the recordset are permitted.
adLockPessimistic. Records in the recordset are locked when editing begins, and remain locked until you execute the Update method or move to another record.
adLockOptimistic. Records are locked only at the instant you execute the Update method or move to another record.

------------------------------

If the data will be changing a lot adKeySet and adLockOptimistic would be my choice, and let the front end enforce the user access levels.

Hope that helps!

~Mike
Now and then it's good to pause in our
pursuit of happiness and just be happy.

- Guillaume Apollinaire