Problem with .UpdateBatch in ADO... 1

[zepphead80]

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

 

[SoCalAccessPro]

Since you're looping throught the records one by one, and updating prior to .movenext, .updatebatch isn't really applicable here.

Just remove the .updatebatch method and your code should work fine.


Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com

 

[zepphead80]

Thanks for your reply Tyrone.

I commented my .UpdateBatch out, and it does get rid of the error, but it also does not update the table.

Is there a restriction on using a JOIN in this situation?

 

[MajP]

I might be missing something, but it looks as if you could do this with an update query and no VBA (except for your function call to GetNumOfWorkdays)

 

[zepphead80]

Hi MajP.

Are you suggesting that I use an UPDATE - SET - WHERE? My call to GetNumOfWorkdays requires information from one of the other tables that is not being updated (hence the INNER JOIN in my SQL string).

How would I make that work in the context of an UPDATE? Perhaps as a sub-query embedded in the SET clause?

Thanks for any insight you can offer...

 

[SoCalAccessPro]

Where are you defeining the variable conn ?

Anyway, an update query may or may not work. I think if you have 3 or more tables (which you do) than a query is not updatable. The rules of how and when a query is updatable or not are rahter confusing.

For starters, do your table have unique indexes ?


Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com

 

[Golom]

I notice that none of your JOIN fields are included in the SELECT. If those (i.e. fldTitleCode, fldERN) are key fields then there isn't enough information in the recordset to identify which records to update.

You will need to look at the "Status" properties for the fields you are trying to update and at the "Status" property of the Recordset after the .Update statement.

Those will tell you if the fields can be updated and what happened when ADO attempted to update the record.

 

[zepphead80]

Hi Tyrone:

I define my connection ("conn") globally so that I need to open it only once (e.g. when the database's primary form opens at startup).

The tables in question do have primary keys. I have attached a PDF of my relationships report if you care to see it.

I've been mulling over how to use an UPDATE - SET { } to accomplish what I want, but am still not sure how to go about incorporating my call to GetNumOfWorkdays...

Thank you for your help.

 

[zepphead80]

Hi Golom:

Thanks for your suggestions. When I look at the .Status property, it gives me "0" (adFieldOK) - which is the default according to MS documentation.

When I do

       Debug.Print .Fields.Item(1).value & "     " & .Fields.Item(0).value

it shows the values in the fields to be properly updated. But when I then open up the back end and look in the table, the table itself has not changed.

I will look into your suggestion regarding the WHERE clause field names in my SELECT statement.

Thanks so much!

 

[MajP]

I am not very good with SQL but I am pretty sure you can do an update query with an inner join. Here is my rough stab.

something roughly like:
[/code]

UPDATE
tblLeaveBalances as A

INNER JOIN (tblEmployee INNER JOIN tblTitles ON tblEmployee.fldTitleCode = tblTitles.fldTitleCode) ON tblEmployee.fldERN = tblLeaveBalances.fldERN

SET
A.fldCurrentAl = A.fldCurrentAl - (GetNumOfWorkdays(A.fldDateEffective, Date, A.fldRO1, A.fldRO2) - 1) * A.fldHoursPerDay,

A.fldDateEffective = Format(Date, "mm/dd/yy"
WHERE A.fldCurrentAl > 300
[/code]

 

[MajP]

Golom,
I know you are a query MVP is that close?

 

[zepphead80]

Hi Everyone:

It looks like Golom got it exactly right. When I inserted into the SELECT clause, the fields being used in the ON clauses for the JOIN operations, AND did my .UpdateBatch - it worked perfectly.

When I kept the .UpdateBatch commented out, it did not work. However, I suspected that would be the case anyway.

Thanks for all your help...

Pat

 

[Golom]

MajP

Structurally it looks fine.

I suspect however that issuing that command as you have coded it would raise an error. ADO may not be able to resolve the "GetNumOfWorkdays" call which (I suspect) is a VB function in Access and not a system function that ADO knows about.

I know that would be the case in pure VB6. Access however is a bit of a different creature so everything may work there.

 

[zepphead80]

Hi:

Actually, GetNumOfWorkdays is a function that I made. Many of our employees have their "weekends" on days other than Saturday and Sunday. Also, the "w" (weekday-only option) flag for VB's DateDiff does not work. So I wrote a short custom function to calculate the number of working days between two dates that takes these factors into account.

Pat