[RESOLVED] Record locking

[TheBionicOrange]

Morning all.

Quick question ....

When updating or adding records into a table via VB, if I were to use

Sql = "update TABLEA set B = B + 1 where C = '1234'"
connUpdate.Execute Sql

rather than

Sql = "update TABLEA set B = B + 1 where C = '1234'"    
Set rsUpdate = New ADODB.Recordset
cmdUpdate.CommandText = Sql: cmdUpdate.ActiveConnection = connUpdate
rsUpdate.Open cmdUpdate, , adOpenDynamic, adLockPessimistic
Set rsUpdate = Nothing

what are the benefits and/or pitfalls ?

I realise that not defining a recordset for updates is neater, as its one less object to have to deal with, but how is locking handled in the first example ?
Is it just determined by the server ?

Apologies if all this sounds obvious, but I just don't know.

[Pasvorto]

How about:

[vbcode]
connUpdate.Execute("update TABLEA set B = B + 1 where C = '1234'")\[/vbcode]

[TheBionicOrange]

Well thats just a variation of the same thing.
I'm using the first version, quite happily. I just wondered how any potential locking errors would be resolved thats all.

[Al42]

The database does the same thing whether you send it a recordset or not. Sending it a recordset, in command-type statements (update, insert, delete) gives the database extra work and increases traffic between the app and the database.

[TheBionicOrange]

Thanks ... thats kinda what I figured ... I just wasn't too sure so needed confirmation.