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

Article ID:   W15578

File Created: 2017:08:29:11:58:40

Last Updated: 2017:08:29:11:58:40

	ADO DAO
	Collections
	Constants
	Crystal Reports
	DSOFILE
	Errors
	ImageMagick
	IMAPI
	LogParser
	Lotus Notes
	MDAC
	Netscape
	OLE and Outlook
	OLE TUTORIAL
	OLE with Access
	OLE with Acrobat Viewer
	OLE with Excel
	OLE with Google Earth
	OLE with MSIE
	OLE with PowerPoint
	OLE with Word
	OLE with XML
	OpenOffice
	PDF Conversion
	RegExp
	Samples from Users
	SAPI
	Shell
	SQLite
	Task Scheduler
	Textract
	UDFs
	WIA
	WIL Type Viewer
	WindowsInstaller
	WinHttpRequest
	Winsock
	WSH
	Zip

Database Search

TechHome

Access2000 Transaction Processing Demo