Need to rollback the SQL delete statement...

[sabavno]

Hi,

At some point in my program I do delete everything from one table and then copy records from another to that table again...

DoCmd.RunSQL ("DELETE * FROM tblFIFROLL&quot

DoCmd.RunSQL ("SELECT tblFIFROLL1.*, * INTO tblFIFROLL FROM tblFIFROLL1&quot

If hit ESC while the second SQL statements executes, I loose my tblFIFROLL, I mean the table itself is deleted, not only the records from it.

PLEASE, advise how to rollback the DELETE if error happends during the execution of SELECT statement. Or advise any other effecient method on performing the DELETE and INSERT tasks with the tables.

I'll appreciate your help.

Thanks.

 

[FancyPrairie]

I don't have a lot of time to test what I'm telling you, but this might give you a start.

Dim cnn as ADODB.Connection
Dim bolBeginTrans as boolean

on error goto ErrHandler

bolBeginTrans = False
Set cnn = CurrentProject.Connection

cnn.BeginTrans
bolBeginTrans = True

cnn.Execute "DELETE * FROM tblFIFROLL"
cnn.Execute "SELECT tblFIFROLL1.*, * INTO tblFIFROLL FROM tblFIFROLL1"

cnn.CommitTrans
bolBeginTrans = False

ExitProcedure:

Exit Function

ErrHandler:

msgbox err.number & " " & err.description

if (bolBeginTrans) then
cnn.RollbackTrans
bolBeginTrans = false
end if

Resume ExitProcedure

 

[sabavno]

What references should I set up for ADO?
Cause now I am getting the error "Object required"

Thanks

 

[FancyPrairie]

By default Access sets a reference to the following (unless you converted the database from a previous version). Anyway, the reference should be: Microsoft ActiveX Data Objects 2.1