adodb record locking

[99mel]

I have a program which selects, inserts and updates records in an Access database which resides on a server. There are 2 computers which are running this program.

However errors keep occuring to do with the database being locked.

I'm using adodb to connect to the access db. Do i need to use one of the lock properties within the connection string?

any help much appreciated!

 

[ektapratap]

I think you should try adOpenDynamic as the cursor type and adLockOptimistic as the lock property.

 

[CCLINT]

I wouldn't use an adOpenDynamic cursor...as a matter of fact, I would always try to avoid using one.

First of all, you need to identify what is causing the lock (when user edits a record, or when the recordset is first opened, etc.), and what is being locked (record, page, or all records in a recordset).

Is the lock being caused because the user begins to edit a record, and code has forced the recordset cursor to go into edit mode, or does the recordset it self have a lock on it.

Using bound controls can also be a bad thing for multi-user environments, unless you have some more advanced features in place.

I have found that the safest way to edit a record, and in doing so avoiding locks, is to allow the user to edit the form data, but do not allow the recordset to go into edit mode (do not set any field values) until the user is ready to save the data. Then do the needed validation, and if all is well, then set all the fields to the new values at once call the Update method - this should only take a few milli-seconds and therefore that is the only moment when the record is actually locked.

You can also set an ADO parameter to only lock a single record, and not a Page, and reduce conflicts even more.

And use an Optimistic cursor.

Now, another method, if it is feasible, is to create your recordset, use a client side cursor, get the data from the db, and then disconnect the recordset from it's active connection.
Now you can update all you want, and then reconnect to the database and use the UpdateBatch method to update changes, and resync to capture the changes made by other users, and disconnect again. However, I would do the update for only one record at a time.

You can also run an action query to update a single record with the new changes, (which is what the recordset update does anyways) avoiding the use of making changes to the db via Recordset object, if this would help as a temporary solution until you can modify the rest of your code to prevent throwing the record into edit mode too soon.

I perfer the first method, for editing records.

There are many ways. But, I would definately stay away from bound controls unless you have an advanced technique in place to prevent field changes from throwing a lock on the record until you are absolutly ready to do so.

[/b][/i][/u][sub]*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!

 

[99mel]

Thanks for the info, i forgot to mention that im not using bound controls and i'm actually using sql queries to update and insert the data.

I simply have a connection to a access db on the two clients pcs, this db has linked tabled to the main db on the server!

Each of the programs execute quite a few select statements (through sql queries), then as you suggested i work on the data on the form. Each client then updates/inserts records through sql statements. Resulting in the actual execution only taking a few milliseconds (i hope).

However the only problem i can see is the select/insert/update queries are taking more than a few milliseconds and while these are executing the other client pc is trying to fire off a query.

Hope u understand that

Thanks for ur help again!