I have a database system developed in VB6, and we have a scenario where more than one user may register at the same time triggering an insert in the database. I have used normal sqlconnection and recordset to make the insert and i initialize it with a pessimistic lock. Now how can i check in my application before inserting a record, if the table has been locked or not, thus if the table being inserted to has been locked currently i can alert the user that the table is in use or i can store his data temporarily and insert it once the lock is released. The underlying database is Access and the application is across multiple systems with database on a server.
+1
A:
You might want to read through Locking Shared Data by Using Recordset Objects in VBA. Most of it applies to VB6 as well as VBA.
It isn't really "normal" to lock a whole table, and you can't even do it via ADO and the Jet OLE DB Provider. Your question doesn't provide enough information to suggest any specific course of action.
You don't "check before inserting" either. Applications should be designed to stumble over locks relatively rarely. When they do, you deal with this as an exception. This is reflected in both the DAO and ADO APIs.
Bob Riemersma
2009-07-30 06:21:12