UpdateBatch method

[Dr_Evil]

I am calling a stored procedure with ADO, to populate a FarPoint Spread 3 grid. My question is... Is it possible to use the .UpdateBatch Method when using a Stored Proc for the command object. If so does anyone have an example, or a proposed solution. This is necessary to reduce network traffic due to the volume of records that will be updated with this application. Thanks in advance...

[JonnyCab]

Private Sub butSubmit_Click()
' This sub updates tables in the Oracle table with record data
' ...using the CommitTrans method to confirm the successful update
' ... of table fields. After successful update, the WriteToFile sub
' ... is called to create a text file.
' All SQL queries are done through one SQL function!!.
On Local Error GoTo butSubmit_Click_Err

Dim strHeader As String

Screen.MousePointer = vbHourglass 'Processing mouse icon.
With DataEnvironment1
.Connection1.Open
.rscmdTemp.Open
.rscmdTemp.MoveFirst
.rscmdEmpHour.Open
.rscmdMachHour.Open
' Adding Employee record details to the Oracle table tblEmpHour
Do While Not .rscmdTemp.EOF
If .rscmdTemp!Indicator = "H" Then
strHeader = .rscmdTemp!TSheetNum & .rscmdTemp!TSheetDate
Else
If .rscmdTemp!Indicator = "E" Then
.rscmdEmpHour.AddNew
.rscmdEmpHour!ContractCode = .rscmdTemp!CCode
.rscmdEmpHour!ContractSeq = .rscmdTemp!CSequence
.rscmdEmpHour!Hours = .rscmdTemp!Hours
.rscmdEmpHour!Amount = .rscmdTemp!Hours * .rscmdTemp!PayRate
.rscmdEmpHour!EmpNo = .rscmdTemp!ECode
.rscmdEmpHour!Activity = .rscmdTemp!ACode
.rscmdEmpHour!Resource = .rscmdTemp!RCode
.rscmdEmpHour!Location = .rscmdTemp!LCode
.rscmdEmpHour!LocationRef = SQL2("LocationRef", "tblLocation", "tblLocation.Code", .rscmdTemp!LCode)
.rscmdEmpHour!CostCentre = SQL2("CostCentre", "tblContract", "tblContract.ContractCode", .rscmdTemp!CCode)
.rscmdEmpHour!GLCode = SQL2("GLCode", "tblGLedger", "tblGLedger.CostCentre", .rscmdEmpHour!CostCentre)
.rscmdEmpHour!Reference = SQL2("WorkUnit", "tblContract", "tblContract.ContractCode", .rscmdEmpHour!ContractCode) _
& .rscmdEmpHour!CostCentre & SQL2("ProjectNo", "tblContract", "tblContract.ContractCode", .rscmdEmpHour!ContractCode) _
& .rscmdTemp!JCode & strHeader
.rscmdEmpHour!PayRate = .rscmdTemp!PayRate
.rscmdEmpHour!RateType = SQL2("RateType", "tblPayType", "tblPayType.PayCode", .rscmdTemp!PCode)
Else
If .rscmdTemp!Indicator = "M" Then
.rscmdMachHour.AddNew
.rscmdMachHour!ContractCode = .rscmdEmpHour!ContractCode
.rscmdMachHour!ContractSeq = .rscmdEmpHour!ContractSeq
.rscmdMachHour!MachCode = .rscmdTemp!MCode
.rscmdMachHour!Activity = .rscmdEmpHour!Activity
.rscmdMachHour!ConUnit = SQL2("ConUnit", "tblContract", "tblContract.ContractCode", .rscmdMachHour!ContractCode)
.rscmdMachHour!Units = .rscmdTemp!Hour
.rscmdMachHour!Amount = .rscmdTemp!Rate * .rscmdTemp!Hour
.rscmdMachHour!Reference = .rscmdEmpHour!Reference
.rscmdMachHour!EmpNo = .rscmdEmpHour!EmpNo
End If
End If
End If

.rscmdTemp.MoveNext

Loop

.Connection1.BeginTrans ' Change recordsets update tables

On Error GoTo ConflictHandler
' After errors are checked for all fields are updated
.rscmdEmpHour.UpdateBatch
.rscmdMachHour.UpdateBatch

On Error GoTo 0

.Connection1.CommitTrans
' Message to confirm update complete.
MsgBox "The Oracle Database has been Updated", vbInformation, " *** Processing Complete ***"
.rscmdEmpHour.Close
.rscmdMachHour.Close
.rscmdTemp.Close
.Connection1.Close
Screen.MousePointer = vbArrow
WriteToFile
Exit Sub

ConflictHandler:

.rscmdEmpHour.Filter = adFilterConflictingRecords
.rscmdMachHour.Filter = adFilterConflictingRecords
.rscmdEmpHour.MoveFirst
.rscmdMachHour.MoveFirst
Do While Not .rscmdEmpHour.EOF
Debug.Print "Conflict: Name: " & .rscmdEmpHour!EmpNo
.rscmdEmpHour.MoveNext
Loop
Do While Not .rscmdMachHour.EOF
Debug.Print "Conflict: Name: " & .rscmdMachHour!MachCode
.rscmdMachHour.MoveNext
Loop

.Connection1.RollbackTrans
Resume Next
'.rscmdEmpHour.Close
'.rscmdMachHour.Close
.rscmdTemp.Close
.Connection1.Close
End With

butSubmit_Click_Exit:
Exit Sub
butSubmit_Click_Err:

If Not ErrorHandler("butSubmit_Click", Str(Err.Number), Err.Description) Then
MsgBox "Error in ErrorHandler Function! Please contact Jonny Wilson"
End If
Resume butSubmit_Click_Exit

End Sub

[Dr_Evil]

Thanks JonnyCab but that wasn't exactly what I was looking for. I spent some time on this last night and got everything working as needed with only 17 lines of code, including the error handler.