Record locking problem using ADO (1 Viewer)

[Roni Sutton]

I am using Rumba Object X in a VB executable to hit an Access 97 db. We have a mainframe based loan system from wence we basically copy information into the various fields in the database. The problem is that when multiple users are performing the function of 'scraping' the data from the mainframe into the table we get record locking errors on the update line of the recordset after the addnew. I am using ado so my code looks like this:

dim rshistory as New ADODB.Recordset
dim historysql as string


historysql = "Select * from tblHistNew"
rshistory.Open historysql, gcn, adOpenDynamic, adLockPessimistic
rshistory.addnew
rshistory![loannum] = gscr(22, 12, 10)
rshistory![amt] = gscr(20,3,8)
rshistory.update

gscr is a user defined function that gets the data from the screen. The first number is the row, the second the column and the third the length of the data to put into the recordset field. When it dies, the rshistory.update line is highlighted and I get a run time error that generally says something like "Record is locked by user 'admin' on machine 2du945720."

Is there a better way to open the record set so that multiple people can add records to the table at the same time without causing a conflict? Is my generic SQL Select statement the best way to reference the table or is there a better way? Would using the Insert INTO statement work better rather than opening the recordset and using addnew? I hate to go through and re-do all this code unless I have to because it's monstrously large. I'm really hoping this is something obvious that I'm just overlooking.

(I've also tried opening my recordset as follows:
rshistory.Open historysql, gcn, adOpenKeySet, adLockOptimistic)

PLEASE HELP - the users are about to lynch me.

Roni

 

[RichMorrison]

Roni,

If we assume that one user is adding ONE record at a time, then there is no need to open any recordsets.

You can just make a SQL string that does an INSERT with the data supplied by the user. Then DoCmd.RunSQL will insert one record.

This will reduce record contention significantly. If you still get an locking error then you can make an error handler that waits and retries the insert.

HTH,
RichM

 

[Roni Sutton]

Thanks! I know that would be the most expedient way and next time I do one of these, that's what I'll do. However, it would require changing several hundred lines of code. Someone emailed me and told me that using the Select * without a where clause will cause an entire table to be locked. I have changed all my select statements and added a where clause. The open will return an empty recordset, but a record can be added to it. If that doesn't work, I will probably just bite the bullet and work a couple of all-nighters.