Hi All
I'm an old hand with VBA in Excel, but Access is new to me. I've been writing a program that has an Excel Form (and code), but reads/writes all it's data to Access. Since there are about 120 users of this form, I've had problems with the database getting 'locked' and other users getting errors. It seems there's a hundred way to open a recordset...
DB_SQL = "SELECT * FROM MyTable"
1) .Open DB_SQL, DB_Conn, adOpenDynamic, adLockBatchOptimistic
2) .Open DB_SQL, DB_Conn, adOpenKeyset, adLockPessimistic
3) .Open DB_SQL, DB_Conn, adOpenKeyset, adLockBatchOptimistic
4) .Open DB_SQL, DB_Conn, adOpenDynamic, adLockOptimistic
5) .Open DB_SQL, DB_Conn, adOpenStatic, adlockReadOnly
6) .Open DB_SQL, DB_Conn, adOpenDynamic, adLockPessimistic
7) ...and many more...
Can someone please tell me which is the best way to open a cursor for these situations:
Adding a new record - With .AddNew, adding 1 record at a time (I use method 1 above)
Updating a record with a small change quickly (I use method 4)
Updating hundreds of records at once, the connection has to remain open for about 10 minutes? (this is when other users start getting errors, it seems the whole table is locked, I use method 6)
Can anyone help me with past experiences? How can I make my updates and not lock anyone else out? Thanks.
I'm an old hand with VBA in Excel, but Access is new to me. I've been writing a program that has an Excel Form (and code), but reads/writes all it's data to Access. Since there are about 120 users of this form, I've had problems with the database getting 'locked' and other users getting errors. It seems there's a hundred way to open a recordset...
DB_SQL = "SELECT * FROM MyTable"
1) .Open DB_SQL, DB_Conn, adOpenDynamic, adLockBatchOptimistic
2) .Open DB_SQL, DB_Conn, adOpenKeyset, adLockPessimistic
3) .Open DB_SQL, DB_Conn, adOpenKeyset, adLockBatchOptimistic
4) .Open DB_SQL, DB_Conn, adOpenDynamic, adLockOptimistic
5) .Open DB_SQL, DB_Conn, adOpenStatic, adlockReadOnly
6) .Open DB_SQL, DB_Conn, adOpenDynamic, adLockPessimistic
7) ...and many more...
Can someone please tell me which is the best way to open a cursor for these situations:
Adding a new record - With .AddNew, adding 1 record at a time (I use method 1 above)
Updating a record with a small change quickly (I use method 4)
Updating hundreds of records at once, the connection has to remain open for about 10 minutes? (this is when other users start getting errors, it seems the whole table is locked, I use method 6)
Can anyone help me with past experiences? How can I make my updates and not lock anyone else out? Thanks.