transactions in access

[Louis]

Hi

I have an access frontend with mysql backend.

MySQL has a transaction feature - where the whole sql run can be voided if
one part fails. My VBA code is as follows : -

Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
cnn.CursorLocation = adUseClient
cnn.Open sqller
cnn.Execute(sql) - This is an insert statement
cnn.Execute(sqla) - This is an update statement
cnn.Close
Set cnn = Nothing

Should i concat sql and sqla to one sql run with a ; after the first
statement. Will this allow me to then use the transaction feature of mysql ?

Or is there another way i can do it in access ?

 

[Michel Walsh]

Hi,

I don't know MySQL, but with Jet, it can be either to start a
transaction explicitly:


Cnn.Execute "BEGIN TRANSACTION"

and then, to either commit or roll it back:

Cnn.Execute "ROLLBACK TRANSACTION"



Furthermore, you can also try the BeginTrans() method of the connection ( if
the OLEDB provider of MySQL supports it)

cnn.BeginTrans

...

cnn. CommitTrans ' or cnn.RollbackTrans



Hoping it may help,
Vanderghast, Access MVP