ADO database locking and timeout

[petermeachem]

I have a bit of a problem.

I am doing an update query which should complete in about 1 second with a single user.

If I deliberately lock the database by running the update on the same records from another pc, I want a fairly prompt message to the first user to that effect, so they can resave.

If I do this:-
Dim cmd As New ADODB.Command
cmd.CommandType = adCmdText
cmd.CommandText = csql
cmd.CommandTimeout = 5
Set cmd.ActiveConnection = cnn
Set rs = cmd.Execute(L)

The first programme hangs completely for a very long time. Minutes in fact.

If I do this instead:-

cnn.Execute csql, L
I cannot set a timeout.
If I do this:-
cmd.Execute L

The number of records updated is always 0, although the records are updated properly.

Am I missing something obvious here?

Using VB6, Access database, updating about 5 records out of 200,000.

I just need to:-
a) get the timeout working.
b) be sure that the records have actually saved when the software says they have so I can clear the save button.

In the current VB3 programme, the equivalent update will hang for 30 secs and then just return with no error, so I cannot tell whether the data has been saved or not. Hence the move to VB6 and ADO. It just seems to make it worse though.

Any help appreciated. Changing the database to SQL Server etc is not an option by the way. Peter Meachem
peter@accuflight.com

 

[petermeachem]

I think I have found the answer in MS article Q175264.

Basically I needed to set the connection CommandTimeout to 5. The cnn.Execute csql, L form then works very nicely without all that messy command object stuff.

The first pc that I clicked save on wins the save but is delayed (still) by the second one locking it. The second gives a lock message after 5 secs, and the first saved in 17 secs.

There is clearly still something odd going on, but so long as pc #1 doesn't hang forever, I shall be ok. Peter Meachem
peter@accuflight.com

 

[hirick]

The indefinite hang-up in your first message was probably caused by attempting to return a recordset from an update query. Update queries don't create recordsets.

Is your table indexed on the fields in the where clause? You didn't say how long it takes to update the table if only one PC is used, but if you're on a slow network connection and/or using a table scan to find the records to update, that can slow you down considerably.

 

[petermeachem]

Good point about the recordset. I must be tired.

The indexing is just fine. The delay is caused by deliberately locking a record by updating it on 2 pc's at the same time. Setting connection CommandTimeout fixed it. Peter Meachem
peter@accuflight.com