mySQL Rollback

PedroMB
04-16-2004, 03:52 AM
Hello!

I need a opinion about the following problem

I want to Update and Insert data in a mySQL database directly from ADO in VBA Excel.

The rotines works perfectly,but in case of connection fail, can I control mySQL have possibility to make a rollback of incompleted data ???
mySQL have rollback possibility ???

Thanks

MKoslof
04-16-2004, 06:45 AM
Use the BeginTrans method of the command object.



Dim strString as string
Dim cn as ADODB.connection

strString = "INSERT INTO Table1(Field1, Field2) VALUES('blah', 'blah')"
Set cn = New ADODB.connection

cn.connectionstring = "YOUR ADO CONNECTION"
cn.Open

'now nest an error handler WITHIN your transaction
cn.BeginTrans

On Error goto handle_Error:

cn.Execute strString

'if it goes through, commit the changes

cn.commitTrans
cn.close

handle_Error: If err.number > 0 Then
cn.RollbackTrans
msgbox "The following error has occured: " & Err.description
cn.close
Exit sub
Else
Err.clear
End If

PedroMB
04-16-2004, 08:21 AM
Thanks very must...

I already find too, but I make the VBA rotine with SQL statements.
My rotine only works in a InnoDB and not MyISAM.
I think it only works in mySQL 4 and upper...

Nevetheless, your code is better.

Thanks again...

MKoslof
04-16-2004, 06:37 PM
No problem, glad to help.

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum