Rollback problem in VBA

Question

Rollback problem in VBA

EllieUk

Old Hand

Points: 372
More actions
December 4, 2011 at 3:14 am

#251394

Hello,
I am struggling with a rollback problem....
For just a test purpose I have written the 3rd insert statement wrong.
when I debug the program at run, the debug says that the connection cannot rollback because is closed.
It seems that it close the connection automatically.
here below you can see what I am trying to do.
Thank you in advance for helping me.
on error go to rollback_lbl:
open connection
begintran
insert no.1
insert no.2
select no.1
select no.2
insert no.3
insert no.4
insert no.5
update no. 1
commit
rollaback_lbl:
msgbox " db error" err.description
rollback

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Post the actual code please.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Kalpesh Kanjibhai Patel SSC Enthusiast Points: 182 More actions · Answer 2

You should put Exit Sub before error label statement...OR put your original code so we can find your problem and give proper solution...

EllieUk Old Hand Points: 372 More actions · Answer 3

Dear friends,

I modified the code and now I have no errors in debugging. It executes the rollback.

The result is that it does not actually rollback. I can see records inserted in tab1 and 2.

I would like to rollback all the operations I did before... (from the insert in tab no.1....)

Here the code:

Sub INSERT_OPERATIONS
Dim pos,rtab1,rtab2,rtab3,rtab4,rtab5,Q1,Q2 as Integer
Dim vtext, vdescr,vquality as String
Dim vf1,vf2,vf3,vf4 as Integer
On Error GoTo error_exit
Set dbconn01 = New ADODB.Connection
Cnnxstr="Provider=SQLOLEDB.1............................."
dbconn01.connectiontimeout=30
dbconn01.open cnnxstr
Set cmdobj = New ADODB.Command
cmdobj.activeconnection=cnnxstr
cmdobj.commandtype= adcmdtext
dbconn01.BeginTrans
vtext="Insert into dbo.tab1....."
cmdobj.commandtext=vtext
cmdobj.execute rtab1
vtext="Insert into dbo.tab2....."
cmdobj.commandtext=vtext
cmdobj.execute rtab2
FIND_Q1ANDQ2 -------------------------------------> here I have another connection to retrieve data(open/close dbconn connection)
IF ERR.NUMBER<>0 THEN
GoTo error_exit
END IF
IF Q1 > 0 AND Q2 > 0 THEN
vtext="Insert into dbo.tab3....."
cmdobj.commandtext=vtext
cmdobj.execute rtab3
vtext="Insert into dbo.tab3....."
cmdobj.commandtext=vtext
cmdobj.execute rtab3
vf1=1111
vtext="Insert into dbo.tab3....."
cmdobj.commandtext=vtext
cmdobj.execute rtab3
vf1=2222
vtext="Insert into dbo.tab3....."
cmdobj.commandtext=vtext
cmdobj.execute rtab3
vf1=3333
vtext="Insert into dbo.tab3....."
cmdobj.commandtext=vtext
cmdobj.execute rtab3
vf2=1
vtext="Insert into dbo.tab4....."
cmdobj.commandtext=vtext
cmdobj.execute rtab4
vf2=2
vtext="Insert into dbo.tab4....."
cmdobj.commandtext=vtext
cmdobj.execute rtab4
vtext="Update dbo.tab2 set qual=1 where ....."
cmdobj.commandtext=vtext
cmdobj.execute rtab2
FIND_QUALITY ---------------------------> here I have another connection to retrieve data(open/close dbconn connection)
Select case Mid(vquality,6,2)
case "AA"
vdescr="descr.1"
case "AB"
vdescr="descr.2"
case "AC"
vdescr="descr.3"
case else
MsgBox ("Quality not valid!")
goto error_exit
end select
vtext="Insert into dbo.tab5....."
cmdobj.commandtext=vtext
cmdobj.execute rtab5
dbconn01.committrans
MsgBox ("Successfully executed!")
end if
exit sub
error_exit:
msgbox "error" & vbcrlf & err.number & "-" & err.description & vbcrlf & vtext, vbokonly
dbconn01.rollbacktrans
set cmdobj=nothing
end sub

Thank you very much for helping me.

Rollback problem in VBA

Cookies on SQLServerCentral