Transactions... 
Author Message
 Transactions...

Hi.

I'm afraid i need u to understand the transactions. I can't find any site
very helpful on the www.
I work with VB6 and ADO.
I have a database located on a server, the my application will run on 2
clients.
I have to update two tables at the same time, and i want to avoid that if
the two clients are updating the same record at the same time, the
aplication hangs, and worst, that only one of the two tables is updated.
So, I thought about transactions.
But, i don't understand what type of lock (pessimistic, optimistic...) i
have to choose (because i am in a client/server mode), and how to write the
code. I thought about this, just tell me what's wrong please:

dim mydb as new ADODB.Recordset
dim myrs1 as new ADODB.Recordset
dim myrs2 as new ADODB.Recordset

set mydb.connectionString="...........myaccessbdd.mdb"
mydb.open

myrs1.open "SELECT * FROM Table1 WHERE
ID=10;",mydb,adOpenStatic,adLockPessimistic,adCmdText

on error goto myError

mydb.beginTrans

myrs1.fields("Total")=100
myrs1.update

myrs2.open "SELECT * FROM Table2 WHERE
ID=10;",mydb,adOpenStatic,adLockPessimistic,adCmdText

myrs2.fields("Total")=100
myrs2.update

mydb.commitTrans

on error goto 0

closing:
    myrs1.close
    myrs2.close
    mydb.close
    set myrs1=nothing
    set myrs2=nothing
    set mydb=nothing
    exit sub

myError:
    msgbox "Error number: " & Err.number & ".",vbCritical,"Error !"
    mydb.rollback
    resume closing

Any help would be very appreciated,

Thanks,

Melina.



Sat, 07 May 2005 18:33:37 GMT  
 Transactions...
First you have a mistake :
dim mydb as new ADODB.Connection (not Recordset)

second you have to open the query as adLockOptimistic

HTH
romeo



Sat, 07 May 2005 21:16:32 GMT  
 Transactions...
First: bad copy/past.
Second: Why optimistic is better in THAT case (client/server) ?
Thirs, please, what about the cursor type ? adopenkeyset ? adopenstatic ?
Which one is the best, still in the cleint/server case with access ?

thanks,

melina



Sun, 08 May 2005 02:26:01 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. To Transaction or Not to Transaction that is the question

2. ADODB TRANSACTION ADO ERROR No Transaction is Active

3. Why to use COM+ transaction and not just SQL Server transaction

4. Reqiured Transaction and Required New Transaction

5. Cannot connect to the transaction manager or the transaction (-2147168229)

6. ADODB TRANSACTION ADO ERROR No Transaction is Active

7. ADO Connection Transaction vs. ObjectContext Transaction

8. ADOConnection.Transaction vs. ObjectContext.Transaction

9. Transaction In Transaction

10. When to use MTS transactions and ADO Transactions

11. Why to use COM+ transaction and not just SQL Server transaction

12. ADO and Transaction in SQL Server 7.0: The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

 

 
Powered by phpBB® Forum Software