Can Transaction be used with recordsets?

Printable View

Jul 27th, 2006, 07:08 AM
Binish

Can Transaction be used with recordsets?

Hi All,

Can BeginTrans and CommitTrans be used with ADO recordsets as well? I am using 2 recordsets with rs.AddNew and rs.Update. Can I use BeginTrans and CommitTrans in this case? Or is it applicable only for Conn.Execute?

All help will be apreciated.

Thanks,

Binish
Jul 27th, 2006, 07:11 AM
d3gerald

Re: Can Transaction be used with recordsets?

recordsets dont need those lines of code, if you add a new record, you can directly say, rs.Addnew then supply the values to the fields. this can be done provided your recordset is directly connected to a single table in your database only and not a result of a stored procedure
Jul 27th, 2006, 07:13 AM
ganeshmoorthy

Re: Can Transaction be used with recordsets?
I hope BeginTrans and CommitTrans can be used only with the Connections and not with Recordsets...just before your addnew put BeginTrans and after the update put CommitTrans...
VB Code:
```
Conn.BeginTrans
rs.AddNew
'.....
rs.Update
Conn.CommitTrans
```
Jul 27th, 2006, 07:13 AM
Binish

Re: Can Transaction be used with recordsets?

In my case, I need to make sure either both my recordsets are updated after AddNew or None. Typically a case where I need to use a transaction
Jul 27th, 2006, 07:16 AM
ganeshmoorthy

Re: Can Transaction be used with recordsets?
VB Code:
```
Conn.BeginTrans
rs.AddNew 'Your 1st Recordset
'.....
rs.Update
 
rs1.AddNew 'Your 2nd Recordset
'....
rs1.Update
Conn.CommitTrans
```
This will make sure both the recordsets are updated, else none...
Jul 27th, 2006, 07:17 AM
Binish

Re: Can Transaction be used with recordsets?
Quote:
Originally Posted by ganeshmoorthy

I hope BeginTrans and CommitTrans can be used only with the Connections and not with Recordsets...just before your addnew put BeginTrans and after the update put CommitTrans...

VB Code:

Conn.BeginTrans
rs.AddNew
'.....
rs.Update
Conn.CommitTrans
I will definitely try this. Hope that will solve the problem
Jul 27th, 2006, 07:18 AM
ganeshmoorthy

Re: Can Transaction be used with recordsets?

have you checked my other post too...
Jul 27th, 2006, 07:19 AM
Binish

Re: Can Transaction be used with recordsets?
Quote:
Originally Posted by ganeshmoorthy

VB Code:

Conn.BeginTrans
rs.AddNew 'Your 1st Recordset
'.....
rs.Update

rs1.AddNew 'Your 2nd Recordset
'....
rs1.Update
Conn.CommitTrans

This will make sure both the recordsets are updated, else none...
I will try this and come back. Thanks a lot!
Jul 27th, 2006, 07:54 AM
mikee_phil

Re: Can Transaction be used with recordsets?

you should put an error handler incase an error occur on any of the recordset
error_handler:
debug.print rs.status
debug.print rs1.status
conn.rollbacktrans
err.clear

Quote:

adRecOK 0 The record was successfully updated.
adRecNew 1 The record is new.
adRecModified 2 The record was modified.
adRecDeleted 4 The record was deleted.
adRecUnmodified 8 The record wasn't modified.
adRecInvalid &H10 The record wasn't saved because its bookmark is invalid.
adRecMultipleChanges &H40 The record wasn't saved because it would affect multiple records.
adRecPendingChanges &H80 The record wasn't changed because it refers to a pending insert.
adRecCanceled &H100 The record wasn't saved because the operation was canceled.
adRecCantRelease &H400 The record wasn't saved because of existing record locks.
adRecConcurrencyViolation &H800 The record wasn't saved because optimistic concurrency was in use.
adRecIntegrityViolation &H1000 The record wasn't saved because it would violate integrity constraints.
adRecMaxChangesExceeded &H2000 The record wasn't saved because there were too many pending changes.
adRecObjectOpen &H4000 The record wasn't saved because of a conflict with an open storage object.
adRecOutOfMemory &H8000 The record wasn't saved because of an out-of-memory error.
adRecPermissionDenied &H10000 The record wasn't saved because the user had insufficient permissions.
adRecSchemaViolation &H20000 The record wasn't saved because it doesn't match the structure of the database.
adRecDBDeleted &H40000 The record had already been deleted from the database.
Jul 28th, 2006, 02:18 AM
Binish

Re: Can Transaction be used with recordsets?

That was really cool. Thanks a lot!