Access2000 Transaction Processing Demo

Keywords: 	 Access2000 Transaction Processing Demo 

---

This very simple script and attached MDB illustrate using the Advanced SQL features of the Jet 4.0 Provider. Script is commented.

; ///////////////////////////////////////////////////////
; 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


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 )

BoxOpen("Performing Access Transaction Processing","Opening %src%")
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
;//cond    = "Dilcode>'59999'"

cond    = "Dialcode>'59999'"
cSQL    = "DELETE * FROM Codes WHERE %cond%;"
DB.Execute(cSQL,,128)


; o.k. so the delete command has been issued, wiyh no errors
; do we want to change our mind?
result  = AskYesNo( "Delete Rows", "Where %cond%?")
If result == @YES
   ; data is gone per query.
   cSQL = "COMMIT TRANSACTION;"
   DB.Execute(cSQL, ,128)
   goto end
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

and here's the MDB:

CTY.MDB file