Problem using UpdateBatch in ADO with INNER JOIN...

[patjones]

Hi:

I am attempting to pull a backend table into a local recordset, make some changes to it, and commit the changes back to the table. I'm using an ADO recordset in Access 2007. The backend resides on a network drive while the front end is on my C: drive. My code is as follows:

 
 
Dim rstBalances As New ADODB.Recordset
Dim strSQL As String
 
strSQL = "SELECT fldDateEffective, fldCurrentAL, fldCurrentSick, fldCurrentCT, fldHoursPerDay , fldRDO1 , fldRDO2 FROM tblLeaveBalances INNER JOIN (tblEmployee INNER JOIN tblTitles ON tblEmployee.fldTitleCode = tblTitles.fldTitleCode) ON tblEmployee.fldERN = tblLeaveBalances.fldERN"
 
With rstBalances
    .CursorLocation = adUseClient
    .LockType = adLockBatchOptimistic
 
    .Open strSQL, conn
 
    Do While Not .EOF
 
        If .Fields.Item(1) > 300 Then
 
            .Fields.Item(1) = .Fields.Item(1) - (GetNumOfWorkdays(.Fields.Item(0), Date, .Fields.Item(5), .Fields.Item(6)) - 1) * .Fields.Item(4)
            .Fields.Item(0) = Format(Date, "mm/dd/yy")
 
        End If
 
        .Update
        .MoveNext
 
    Loop
 
    .UpdateBatch
 
    .Close
 
End With
 
Set rstBalances = Nothing
 

It pulls the data and loops through the records just fine. However, I get a "6 - Overflow" error on the .UpdateBatch.

Now, when I try this with a simpler recordset that does not involve any JOINS, it works fine. Unfortunately, in order to do the update, I need information from two other tables - although I'm only updating information in one table. From what I've been able to find out, it seems like there might be a problem trying to do an .UpdateBatch on an INNER JOIN recordset?

I'm aware that this is not the best performing method for doing this. I just can't think of another way to accomplish my goal at this point. Thanks for any and all help!

Pat

[patjones]

Hi:

Figured the problem out. The fields that I'm using to put together the ON clauses for my INNER JOINS...were not listed in the SELECT clause. The following appended SQL string works, and causes the code to function properly:

 
strSQL = "SELECT tblLeaveBalances.fldDateEffective, tblLeaveBalances.fldCurrentAL, tblLeaveBalances.fldCurrentSick, tblLeaveBalances.fldCurrentCT, tblTitles.fldHoursPerDay , tblEmployee.fldRDO1 , tblEmployee.fldRDO2, tblEmployee.fldERN, tblEmployee.fldTitleCode, tblTitles.fldTitleCode, tblLeaveBalances.fldERN FROM tblLeaveBalances INNER JOIN (tblEmployee INNER JOIN tblTitles ON tblEmployee.fldTitleCode = tblTitles.fldTitleCode) ON tblEmployee.fldERN = tblLeaveBalances.fldERN"
 
 

Pat