By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,471 Members | 722 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,471 IT Pros & Developers. It's quick & easy.

Table Update taking time?

P: 77
Hi All,

Pls help me with another issue....

Here's a code that I'm using:

Expand|Select|Wrap|Line Numbers
  1. Set rsMain = New ADODB.Recordset
  2.     vSQL = "SELECT * from tmpHCRECON_PROD2"
  3.     Set rsMain = conMain.Execute(vSQL)
  4.     rsMain.MoveFirst
  5.     Do While Not rsMain.EOF
  6.         vRemark = ""
  7.         If rsMain(16) = "AOD" Then
  8.             vRemark = "Employee is on " & rsMain(16) & " since " & rsMain(23)
  9.         ElseIf rsMain(16) = "TransferOut" Then
  10.             vRemark = "Please remove from PSHR as Employee Transferred Out with TransferOut Date " & rsMain(24)
  11.         ElseIf rsMain(16) = "Refer2HR" Then
  12.             vRemark = "Please remove from PSHR as Employee is Referred to HR with Ref2HR Date " & rsMain(25)
  13.         ElseIf rsMain(16) = "Attrition" Then
  14.             vRemark = "Please remove from PSHR as Employee has attrited with LWD " & rsMain(22) & " and DOA " & rsMain(21)
  15.         End If
  16.         vRemark = vRemark & "."
  17.         Set rsFirst = New ADODB.Recordset
  18.         vSQL = "select wfm_remarks from tmpHCRECON_PROD2 where emp_id=" & rsMain(0)
  19.         rsFirst.Open vSQL, StrCon, adOpenDynamic, adLockBatchOptimistic
  20.         rsFirst(0) = vRemark
  21.         rsFirst.UpdateBatch
  22.         rsMain.MoveNext
  23.     Loop
  24.     Set rsFirst = Nothing
  25.     oWS.Name = SheetName    'renaming the worksheet
  26.     Set rsSecond = New ADODB.Recordset
  27.     vSQL = "SELECT emp_id,emp_name,wfm_remarks from tmpHCRECON_PROD2 order by emp_id asc"
  28.     Set rsSecond = conMain.Execute(vSQL)
  29.     MsgBox rsSecond(2)
  30.    ------
  31.    ------
  32.  
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?
Sep 5 '08 #1
Share this Question
Share on Google+
9 Replies


P: 77
Just to add on, I've tried to Requery the recordset after updating it, but the result is the same...
Sep 5 '08 #2

P: 77
Guys, any clues to this please?
Sep 6 '08 #3

FishVal
Expert 2.5K+
P: 2,653
Try to use Recordset.Open method instead of Connection.Execute.
Sep 6 '08 #4

P: 77
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?
Sep 6 '08 #5

FishVal
Expert 2.5K+
P: 2,653
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
Sep 6 '08 #6

FishVal
Expert 2.5K+
P: 2,653
Expand|Select|Wrap|Line Numbers
  1.     Do While Not rsMain.EOF
  2. .....
  3.         Set rsFirst = New ADODB.Recordset
  4.         vSQL = "select wfm_remarks from tmpHCRECON_PROD2 where emp_id=" & rsMain(0)
  5.         rsFirst.Open vSQL, StrCon, adOpenDynamic, adLockBatchOptimistic
  6.         rsFirst(0) = vRemark
  7.         rsFirst.UpdateBatch
  8.         .....
  9.     Loop
  10. .....
  11.  
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?
Sep 6 '08 #7

P: 77
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...
Sep 6 '08 #8

FishVal
Expert 2.5K+
P: 2,653
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.

Expand|Select|Wrap|Line Numbers
  1. Set rsMain = New ADODB.Recordset
  2.     vSQL = "SELECT * from tmpHCRECON_PROD2"
  3.     Set rsMain = conMain.Execute(vSQL)
  4.     rsMain.MoveFirst
  5.  
So far so good. You open recordset and iterate it record by record.

Expand|Select|Wrap|Line Numbers
  1.     Do While Not rsMain.EOF
  2.         vRemark = ""
  3.         If rsMain(16) = "AOD" Then
  4.             vRemark = "Employee is on " & rsMain(16) & " since " & rsMain(23)
  5.         ElseIf rsMain(16) = "TransferOut" Then
  6.             vRemark = "Please remove from PSHR as Employee Transferred Out with TransferOut Date " & rsMain(24)
  7.         ElseIf rsMain(16) = "Refer2HR" Then
  8.             vRemark = "Please remove from PSHR as Employee is Referred to HR with Ref2HR Date " & rsMain(25)
  9.  
  10.         ElseIf rsMain(16) = "Attrition" Then
  11.             vRemark = "Please remove from PSHR as Employee has attrited with LWD " & rsMain(22) & " and DOA " & rsMain(21)
  12.         End If
  13.         vRemark = vRemark & "."
  14.  
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.

Expand|Select|Wrap|Line Numbers
  1.         Set rsFirst = New ADODB.Recordset
  2.         vSQL = "select wfm_remarks from tmpHCRECON_PROD2 where emp_id=" & rsMain(0)
  3.         rsFirst.Open vSQL, StrCon, adOpenDynamic, adLockBatchOptimistic
  4.         rsFirst(0) = vRemark
  5.         rsFirst.UpdateBatch
  6.         rsMain.MoveNext
  7.     Loop
  8.  
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.

Expand|Select|Wrap|Line Numbers
  1.     Set rsFirst = Nothing
  2.     oWS.Name = SheetName    'renaming the worksheet
  3.     Set rsSecond = New ADODB.Recordset
  4.     vSQL = "SELECT emp_id,emp_name,wfm_remarks from tmpHCRECON_PROD2 order by emp_id asc"
  5.     Set rsSecond = conMain.Execute(vSQL)
  6.     MsgBox rsSecond(2)
  7.    ------
  8.    ------
  9.  
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
Sep 6 '08 #9

P: 77
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:

Expand|Select|Wrap|Line Numbers
  1. chk:
  2.     Set rsMain = New ADODB.Recordset
  3.     vSQL = "SELECT emp_id,emp_name,tl_id,tl_name,wfm_remarks from tmpHCRECON_PROD2 order by emp_name asc"
  4.     Set rsMain = conMain.Execute(vSQL)
  5.     rsMain.MoveNext
  6.     Do While Not rsMain.EOF
  7.         If IsNull(rsMain(4)) = True Or rsMain(4) = "" Then
  8.             'Record has still not updated
  9.             GoTo chk
  10.         End If
  11.         rsMain.MoveNext
  12.     Loop
  13.     oWS.Range(vStartCol & vStartRow + 1).CopyFromRecordset rsMain
  14.  
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 :-)
Sep 7 '08 #10

Post your reply

Sign in to post your reply or Sign up for a free account.