Updating Table From Filtered Recordset

; Winbatch - Updating Table From Filtered Recordset
;
; This script looks at Deleting rows based on a filter, then
; setting a filter criteria for Pending Rows and setting 
; 'marshalling' for batch update - Jet 4.0/ Access 2002
;
; Note: Errors are being treated by the WB Errorhandler only, an
;       ADO Error Object is Never Opened
;
; Stan Littlefield June 8, 2004 [please retain this header]
;/////////////////////////////////////////////////////////////////////
;
;
; Note: this script assumes exclusive use, and so the recordset changes
;       are not resync'd with the original table
;
;/////////////////////////////////////////////////////////////////////



IntControl(73,1,0,0,0)
level=1
; constants
adLockBatchOptimistic = 4
adLockOptimistic = 3
adOpenStatic = 3
adOpenKeyset = 1
adUseClient = 3
adCmdText = 1
adUpdateBatch = 65536
adExecuteNoRecords = 128
adAffectCurrent = 1
adFilterPendingRecords = 1
adMarshalModifiedOnly = 1
; check for database and open connection
cMDB = StrCat( FilePath( IntControl(1004,0,0,0,0)),"ForTesting.mdb")
If ! FileExist(cMDB) Then Exit

BoxOpen("Opening %cMDB%","Inserting Test Rows")
DB= 0
Conn="Provider=MicroSoft.Jet.OLEDB.4.0; Data Source=%cMDB%;"
DB = CreateObject("ADODB.Connection")
DB.Open(Conn)

;just a precaution in case the script fails for some reason
;or you want to run it multiple times
DB.Execute("DELETE * FROM ForUpdate;")

;Create 50 test rows in the table
For i = 1 To 50
   cSQL = StrCat("INSERT INTO ForUpdate (TypeVar, NameVar) VALUES ('Type",i,"','Name",i,"')" )
   DB.Execute(cSQL, , adCmdText + adExecuteNoRecords)
Next

;Open a client-side recordset and display rowcount
RS = CreateObject("ADODB.Recordset")
RS.CursorLocation = adUseClient   ; used because RS will be disconnected
RS.CursorType = adOpenStatic
; If changing multiple rows you want to issue RS.UpdateBatch()
RS.LockType = adLockBatchOptimistic
RS.Supports( adUpdateBatch )    
RS.ActiveConnection = DB
RS.Open( "SELECT * FROM ForUpdate;",,,,adCmdText)
level=2
BoxShut()
Message("Recordset ForUpdate",StrCat("Has ",RS.RecordCount," records.") )


;set a filter and display the rows meeting the filter criteria
RS.Filter = "TypeVar Like 'Type1%%'"
Message("Filter Set To 'Type1%%'",StrCat("There are ",RS.RecordCount," matching rows...") )

;delete all rows in the filter
While ! RS.EOF
  RS.Delete(adAffectCurrent)
  RS.MoveNext()
EndWhile
    
;re-set the filter to a special constant and display rows
RS.Filter = adFilterPendingRecords
Message("The recordset has ",StrCat(RS.RecordCount," pending rows [For Update]") )

;update the main table from the Recordset
;in effect, the 11 rows deleted by the filter
;will be deleted from the Main Table
RS.MarshalOptions = adMarshalModifiedOnly
RS.UpdateBatch()

RS.Close()
RS=0
DB.Close()
DB=0
Exit


:Cancel
DB.Close()
DB=0
Exit

:WBERRORHANDLER          ; From DetLev
IntControl(73,1,0,0,0)

WbError = LastError()
WbTextcode = WbError
If WbError==1668||WbError==2669||WbError==3670
   ; 1668 ; "Minor user-defined error"
   ; 2669 ; "Moderate user-defined error"
   ; 3670 ; "Severe user-defined error"
   WbError = ItemExtract(1,IntControl(34,-1,0,0,0),":")
   WbTextcode = -1
EndIf
WbErrorString = IntControl(34,WbTextcode,0,0,0)
WbErrorDateTime = StrCat(TimeYmdHms(),"|",StrFixLeft(GetTickCount()," ",10))

WbErrorFile = StrCat(DirWindows(0),"WWWBATCH.INI")
IniWritePvt(WbErrorDateTime,"CurrentScript",WbErrorHandlerFile      ,WbErrorFile)
IniWritePvt(WbErrorDateTime,"ErrorValue"   ,WbError                 ,WbErrorFile)
IniWritePvt(WbErrorDateTime,"ErrorString"  ,WbErrorString           ,WbErrorFile)
IniWritePvt(WbErrorDateTime,"ScriptLine"   ,WbErrorHandlerLine      ,WbErrorFile)
IniWritePvt(WbErrorDateTime,"ScriptOffset" ,WbErrorHandlerOffset    ,WbErrorFile)
IniWritePvt(WbErrorDateTime,"VarAssignment",WbErrorHandlerAssignment,WbErrorFile)
IniWritePvt("","","",WbErrorFile)

WbErrorMsgText = StrCat(WbErrorDateTime,@CRLF)
WbErrorMsgText = StrCat(WbErrorMsgText,"Current Script: ",WbErrorHandlerFile,@CRLF)
WbErrorMsgText = StrCat(WbErrorMsgText,"LastError #:",WbError,@CRLF)
WbErrorMsgText = StrCat(WbErrorMsgText,"Error Text: ",wberrortextstring,@CRLF)
WbErrorMsgText = StrCat(WbErrorMsgText,"[Additional] ",wberroradditionalinfo,@CRLF,@CRLF)
; Line in script that caused Error.
WbErrorMsgText = StrCat(WbErrorMsgText,"WbErrorHandlerLine:",@CRLF,WbErrorHandlerLine,@CRLF)
; Offset into script of error line, in bytes.
WbErrorMsgText = StrCat(WbErrorMsgText,"WbErrorHandlerOffset: ",WbErrorHandlerOffset,@CRLF)
; Variable being assigned on error line, or "" if none.
WbErrorMsgText = StrCat(WbErrorMsgText,"WbErrorHandlerAssignment: ",WbErrorHandlerAssignment,@CRLF)
If (WbErrorHandlerAssignment>"") Then %WbErrorHandlerAssignment% = "UNKNOWN"
ClipPut(WbErrorMsgText)
WbErrorMsgText = StrCat(WbErrorMsgText,"[Error Copied To Clipboard]")
Message("wbErrorHandler",WbErrorMsgText)

If level>1 Then RS=0

If DB<>0 Then DB.Close()

DB=0

Exit
;///////////////////////////////////////////////////////////////////////

Article ID:   W16600

File Created: 2005:02:18:12:21:36

Last Updated: 2005:02:18:12:21:36

Database Search

TechHome

Updating Table From Filtered Recordset