Database Search

TechHome

• Access 2000 Advanced DML Transactions
• Access Recordset to CSV File UDF
• Access RunCommand Constants
• ACCESS Table or Query Exists
• Access to Excel via SQL
• Access Visible Problem
• Adding Fields to an Existing Access Table
• Advanced DML Transactions
• Advanced SQL in the Jet 4.0 Provider Example
• Another OLE with Access Example
• Automating Access Tutorial
• Check if Access Table Exists
• Commit Rollback Begin Trans Updatebatch
• Control Access Forms
• Count Access Records
• Csv to Access
• Dealing with ACCESS 97 Field Types
• execute a macro in Access
• Execute a Public function in Access
• Export Access Report in Choice of Formats
• Export MDB to Flat File
• Find Read and Insert Data in Access
• Gather ACCESS Table and Column Information Into Excel
• Get Access Version That Created MDB File
• Get Column Data From an Access Database
• Get Record Count
• How to Use the OpenTable method
• Launch Access Macro from Commandline
• Loop thru a recordset in Access
• Office 2003 Macro Security Issue
• OLE and Access
• OLE Import a table into Access
• OLE Import Table into Access
• Open Access DB with DAO
• Pack an Access Database
• Query and Export Data from Microsoft Access
• Read Write Delete Tables from Access DB
• Repair MDB
• Retrieve Data in Access
• SQL UPDATE Fails with Access
• UDF to Return Record Count
• Updating Table From Filtered Recordset
• Ways to Get Data from and Access file without Access installed

Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.

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


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

Article ID:   W16097

File Created: 2004:03:30:15:42:50

Last Updated: 2004:03:30:15:42:50