Table Update taking time?

[yaaara]

Hi All,

Pls help me with another issue....

Here's a code that I'm using:

Set rsMain = New ADODB.Recordset
    vSQL = "SELECT * from tmpHCRECON_PROD2"
    Set rsMain = conMain.Execute(vSQL)
    rsMain.MoveFirst
    Do While Not rsMain.EOF
        vRemark = ""
        If rsMain(16) = "AOD" Then
            vRemark = "Employee is on " & rsMain(16) & " since " & rsMain(23)
        ElseIf rsMain(16) = "TransferOut" Then
            vRemark = "Please remove from PSHR as Employee Transferred Out with TransferOut Date " & rsMain(24)
        ElseIf rsMain(16) = "Refer2HR" Then
            vRemark = "Please remove from PSHR as Employee is Referred to HR with Ref2HR Date " & rsMain(25)
        ElseIf rsMain(16) = "Attrition" Then
            vRemark = "Please remove from PSHR as Employee has attrited with LWD " & rsMain(22) & " and DOA " & rsMain(21)
        End If
        vRemark = vRemark & "."
        Set rsFirst = New ADODB.Recordset
        vSQL = "select wfm_remarks from tmpHCRECON_PROD2 where emp_id=" & rsMain(0)
        rsFirst.Open vSQL, StrCon, adOpenDynamic, adLockBatchOptimistic
        rsFirst(0) = vRemark
        rsFirst.UpdateBatch
        rsMain.MoveNext
    Loop
    Set rsFirst = Nothing
    oWS.Name = SheetName    'renaming the worksheet
    Set rsSecond = New ADODB.Recordset
    vSQL = "SELECT emp_id,emp_name,wfm_remarks from tmpHCRECON_PROD2 order by emp_id asc"
    Set rsSecond = conMain.Execute(vSQL)
    MsgBox rsSecond(2)
   ------
   ------
 

Issue: When this code is executed normally, the messagebox is blank. However, when i put in a breakpoint anywhere to execute the code, the messagebox displays the correct values.. I'm using this messagebox recordset variable to export some data in an Excel file so currently, it displays blank.. The field does get updated as I can see the data in there...

Apparantly, during normal execution, the update is still going on when the next query picks up the values?

Any clues on how to prevent this please?

[yaaara]

Just to add on, I've tried to Requery the recordset after updating it, but the result is the same...

[yaaara]

Guys, any clues to this please?

[FishVal]

Try to use Recordset.Open method instead of Connection.Execute.

[yaaara]

I tried the .Open method as well but still no luck... Apparantly its only the timing which is causing this issue as when a breakpoint is fed in and the code is then executed after stepping in manually, then the output is as desired (maybe coz the tables get enough time to get updated?)

Any way by which we can know whether a table has been updated or not and then execute the next statements?

[FishVal]

I tried the .Open method as well but still no luck... Apparantly its only the timing which is causing this issue as when a breakpoint is fed in and the code is then executed after stepping in manually, then the output is as desired (maybe coz the tables get enough time to get updated?)

Any way by which we can know whether a table has been updated or not and then execute the next statements?

• Possible asynchronous execution is actually the reason why I've recommended to use Recordset.Open method.
• If you have concerns about whether update is finished when you fetch records, then prove it at least. E.g. by updating the table prior to code execution and eliminating table update portion of code.
• Try to open table for update with adLockOptimistc LockType and use Recordset.Update method instead of Recordset.UpdateBatch.

Regards,
Fish

[FishVal]

    Do While Not rsMain.EOF
.....
        Set rsFirst = New ADODB.Recordset
        vSQL = "select wfm_remarks from tmpHCRECON_PROD2 where emp_id=" & rsMain(0)
        rsFirst.Open vSQL, StrCon, adOpenDynamic, adLockBatchOptimistic
        rsFirst(0) = vRemark
        rsFirst.UpdateBatch
        .....
    Loop
.....
 

Well, yaaara.

Could you please clarify what this portion of code actually does ?

Regards,
Fish

P.S. I have looked all the code and, frankly speaking, it doesn't make much sense - it is too kludgy for well no obvious reason. Could you explain what is it actually supposed to do?

[yaaara]

The code is updating a Remark Field based on an Employee Status (As checked in the If... Else... Endif loop in the code..

After the updation is done, the final table is output in an Excel file...

I have tried using the .Open method with the LockOptimistic Lock type and I also need to use the UpdateBatch command as there would be multiple users working on the DB at the same time...

I have re-checked the code and it works well if some time lag is given in between the updation of the table and then re-referencing the same table for output in Excel... Not sure how to induce the time lag or if any other method of achieving this task is available...

[FishVal]

The code is updating a Remark Field based on an Employee Status (As checked in the If... Else... Endif loop in the code..

After the updation is done, the final table is output in an Excel file...

Dear yaaara,

imagine yourself on my place. ;)
I know nothing about purpose of the code as well as I have no idea what a special reasons do you have to implement your solution in a way that IMHO has no obvious reason to be so complicated.

So I would comment your code and you will tell me where I'm wrong.

Set rsMain = New ADODB.Recordset
    vSQL = "SELECT * from tmpHCRECON_PROD2"
    Set rsMain = conMain.Execute(vSQL)
    rsMain.MoveFirst
 

So far so good. You open recordset and iterate it record by record.

    Do While Not rsMain.EOF
        vRemark = ""
        If rsMain(16) = "AOD" Then
            vRemark = "Employee is on " & rsMain(16) & " since " & rsMain(23)
        ElseIf rsMain(16) = "TransferOut" Then
            vRemark = "Please remove from PSHR as Employee Transferred Out with TransferOut Date " & rsMain(24)
        ElseIf rsMain(16) = "Refer2HR" Then
            vRemark = "Please remove from PSHR as Employee is Referred to HR with Ref2HR Date " & rsMain(25)
 
        ElseIf rsMain(16) = "Attrition" Then
            vRemark = "Please remove from PSHR as Employee has attrited with LWD " & rsMain(22) & " and DOA " & rsMain(21)
        End If
        vRemark = vRemark & "."
 

That part of code is clear too. Though I don't see anything what couldn't be done with simple query using calculated field or join with lookup table.

        Set rsFirst = New ADODB.Recordset
        vSQL = "select wfm_remarks from tmpHCRECON_PROD2 where emp_id=" & rsMain(0)
        rsFirst.Open vSQL, StrCon, adOpenDynamic, adLockBatchOptimistic
        rsFirst(0) = vRemark
        rsFirst.UpdateBatch
        rsMain.MoveNext
    Loop
 

That part is more interesting.

• You initialize rsFirst with new ADODB recordset object. It is not a very clever stroke, taking into account that ADODB recordset object created and assigned to rsFirst in previous loop iteration was not explicitely closed and destroyed.
• You open the same table being iterated in the main loop filtering it on [emp_id] field by the value of Field(0) of the same table. I expect with a high probability that the record you modify via rsFirst is the same rsMain is positioned on. And [emp_id] has unique values only - otherwise yhe code does noy make sense at all.

    Set rsFirst = Nothing
    oWS.Name = SheetName    'renaming the worksheet
    Set rsSecond = New ADODB.Recordset
    vSQL = "SELECT emp_id,emp_name,wfm_remarks from tmpHCRECON_PROD2 order by emp_id asc"
    Set rsSecond = conMain.Execute(vSQL)
    MsgBox rsSecond(2)
   ------
   ------
 

Here is nothing to comment.
Nu ... maybe ... without any relation to previous comments ...
I think you are right that after rsFirst variable was used that way multiple times it is better to get rid of it and start with rsSecond variable. ;)

I have tried using the .Open method with the LockOptimistic Lock type and I also need to use the UpdateBatch command as there would be multiple users working on the DB at the same time...

Could you explain how batch update will help you to eliminate possible conflicts between different users?

I have re-checked the code and it works well if some time lag is given in between the updation of the table and then re-referencing the same table for output in Excel... Not sure how to induce the time lag or if any other method of achieving this task is available...

Having read all you code I don't see, if I don't miss something, any reason for table update before export as well as any reason for writing code for what could be achieved with a simple query without kludgy tricks and unexpected side effects.

Kind regards,
Fish

[yaaara]

That part is more interesting.

• You initialize rsFirst with new ADODB recordset object. It is not a very clever stroke, taking into account that ADODB recordset object created and assigned to rsFirst in previous loop iteration was not explicitely closed and destroyed.
• You open the same table being iterated in the main loop filtering it on [emp_id] field by the value of Field(0) of the same table. I expect with a high probability that the record you modify via rsFirst is the same rsMain is positioned on. And [emp_id] has unique values only - otherwise yhe code does noy make sense at all.

Thanks for your great inputs Fish... However, just to clarify, rsFirst has been initialized only within the loop. In the previous iterations, rsMain is the recordset which is used.

However, I have fixed the issue by changing my code to the following:

chk:
    Set rsMain = New ADODB.Recordset
    vSQL = "SELECT emp_id,emp_name,tl_id,tl_name,wfm_remarks from tmpHCRECON_PROD2 order by emp_name asc"
    Set rsMain = conMain.Execute(vSQL)
    rsMain.MoveNext
    Do While Not rsMain.EOF
        If IsNull(rsMain(4)) = True Or rsMain(4) = "" Then
            'Record has still not updated
            GoTo chk
        End If
        rsMain.MoveNext
    Loop
    oWS.Range(vStartCol & vStartRow + 1).CopyFromRecordset rsMain
 

Although I couldn't myself make out why the records are not being updated in a timely manner, but even then, checking and looping it back to a fresh recordset resolves the issue and I get the correct output...

Many thanks for your inputs guys :-)