Access 2000 Advanced DML Transactions

; ///////////////////////////////////////////////////////
; Winbatch - Access 2000 Advanced DML Transactions
; The sample cty.mdb includes two tables, Codes and Codesbackup
; (which are identical). The script below illustrates the
; Advanced SQL in the Jet 4.0 Provider, specifically to BEGIN,
; Execute, then either COMMIT or ROLLBACK Transactions
;
; NOTE: these commands are not available in Access or DAO, and
;       should not be mixed with ADO Transaction functions.
;
; Stan Littlefield December 20, 2002
; ///////////////////////////////////////////////////////

#DefineSubRoutine errchk()
ERR      = DB.Errors
n        = ERR.Count
If n>0
   e2    = ""
   For i = 0 To n-1
      x  = DB.Errors(i)
      e2 = StrCat(e2,x.Description,@CRLF)
   Next
   lErr = @TRUE
   BoxText( e2 )
Else
   BoxText( "Delete Statement Had %n% Errors" )
EndIf
ERR.Clear()     ; reset Error File
TimeDelay( 3 )  ; display message

Return( 0 )
#EndFunction


; note about the access database
; the main table used is code, and you will delete 660 rows
; these rows have been previously copied into another table called DeletedRows
; and can be manually appended back into the codes table, so you can practice
; the script several times to test Rollback and Commit
src     = StrCat( DirGet(),"CTY.MDB" )
If ! FileExist( src ) Then Exit

;// set error handler and cancel event as goto
IntControl(73,2,0,0,0)
IntControl( 72,1,0,0,0 )

txt =   "Opening %src%"
BoxOpen("Performing Access Transaction Processing",txt)
lErr    = @FALSE
cConn   = "Provider=MicroSoft.Jet.OLEDB.4.0; Data Source=%src%"
DB      = ObjectOpen("ADODB.Connection")
ERR     = ObjectOpen("ADODB.Error")

DB.Open(cConn)

cSQL    = "BEGIN TRANSACTION;"
DB.Execute(cSQL, ,128)
;//use this line if you want to cause an error
;//the field DialCode is spelled wrong
;//cond    = "Dilcode>'59999'"
cond    = "Dialcode>'59999'"
txt = StrCat(txt,@CRLF,"DELETING Rows Where...",@CRLF,cond)
BoxText(txt)
cSQL    = "DELETE * FROM Codes WHERE %cond%;"
DB.Execute(cSQL,,128)


; o.k. so the delete command has been issued, with no errors
; do we want to change our mind?
If !AskYesNo( "Cancel DELETE Command", "Where %cond%?")
   DB.Execute("COMMIT TRANSACTION;", ,128)
   Goto End
Else
   BoxText("Rolliong Back Transaction")
EndIf

:oops
; data will not be permanently deleted
cSQL    = "ROLLBACK TRANSACTION;"
DB.Execute(cSQL)

:End
ObjectClose( ERR )
DB.Close()
ObjectClose( DB )
BoxShut()
Exit

:CANCEL
Goto oops

:WBERRORHANDLER
; in this instance, any error is reason to Rollback Transaction
; check the Connection Error Object
; If there are errors then immediately roll back
; the transaction. Useful if you were performing a series
; of DELETE Statements
errchk()
If lErr Then Goto oops

;// as an alternative, you might want to use the
;// Winbatch LastErr(), and parse the
;// [OLE Exception] section
;// at the begining of he script put
;// ErrIni         = StrCat( DirWindows(0),"wwwbatch.ini")
;// If FileExist( ErrIni ) Then FileDelete( ErrIni )
;// Then in the error handler put
;// cErr           = IniReadPvt("OLE Exception","TLI","Unknown Error",ErrIni )
;// BoxText( Strcat("Script Error:",LastError(),@CRLF,cErr ) )
;// TimeDelay( 3 )
;// goto oops

Article ID:   W17137

File Created: 2007:07:03:14:28:26

Last Updated: 2007:07:03:14:28:26

Database Search

TechHome

Access 2000 Advanced DML Transactions