transactions (Clear up a basic concept?)

[cyberbiker]

I am attempting to improve my skills and find that I am often missing a basic concept.I know some things work and
use those techniques as I was taught but as I can more
skill, I begin to question the logic.

This question is very basic. I know that if I am updating 2 recordsets and I want either both to succeed or roll back then I would do something like the first code

on error goto fail
DB.BeginTrans
RS1.Update
RS2.update
DB.CommitTrans
exit sub
fail:
DB.RollbackTrans
MsgBox Err.Description, vbExclamation

However
Is there any valid reason for doing the following?
(Where I am updating only a single recordset).

on error goto fail
DB.BeginTrans
RS1.Update
DB.CommitTrans
exit sub
fail:
DB.RollbackTrans
MsgBox Err.Description, vbExclamation


VB 6 and ADO connection
DB is connection object

Thanks for the clearification

Terry (cyberbiker)

 

[vb5prgrmr]

No, not really. If a single recordset is going to be successful (or not) there is no reason to include the overhead of transactions. Transactions are best suited for multiple updates that must have an all or nothing approach.

However on a single recordset update it is good to continue with the error catching so the user can be notified if it was successful or not.

Good Luck

 

[cyberbiker]

Thanks a lot. That was what I was getting from my reasoning.

It is just when I started working with VB, I was told to do certain things (or else) and as I worked, those became habits.

I apologize for such basic concepts, but I just need them cleared up a bit.

Terry (cyberbiker)

 

[LostInCode]

Using a transaction around a single update does have advantages.
Single updates will always get wrapped in a transaction anyways, as will any updates, whether specifies explicitly or not.

The advantage of explicitly using a transaction is to be able to do a RollBack, but also to be able to flush the write cache, such as with Jet's lazy writes, by using the optional argument dbFlushCache on the Commit method.
This is an advantage in multi-user situations where other users need to see changes by other uses to data as soon as possible.

 

[cyberbiker]

Thanks LostInCode

I am not finding "dbFlushCache" in either VB6 help or Books on Line as it applies to ADO.

Would this possibly be something that only pertains to an Access database?

Or am I missing something?

Terry (cyberbiker)

 

[LostInCode]

Use dbForceOSFlush for DAO

 

[cyberbiker]

Thank you.

I should have picked up on the db as being DAO, but I have not used DAO except with the "Jet Engine" of an Access database and that not recently.

Is there something similar for ADO?

If I am understanding what I am reading, I would suspect there is not.



Terry (cyberbiker)

 

[LostInCode]

For JET?

Yes there is.

Add a reference to MS Jet and Replication Objects 2.5 Library
Then:
Public Sub Conn_RefreshCache(conn As ADODB.Connection)
Dim jro As New jro.JetEngine
jro.RefreshCache conn
Set jro = Nothing
End Sub

 

[cyberbiker]

Thanks again lostincode.

As is so often the case for me, bits and pieces from the brighter guys lead me to additional ideas which takes me some time to follow up so I am a bit slow thanking you and VB5Programmer

You answers lead me to some information that cleared up quite a bit of confusion.

Since I am not using Jet in this app, I would not need to flush the cache (since none exists I think with SQL Server)

But any app where I would use Jet then I would need to flush the cache.



Terry (cyberbiker)