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

Query doesn't finish executing

P: 3
Hi all, I am trying to execute code to insert records from an Access 2002 Xtab query to a remote Sql_Server table. I keep getting a random problem where the Access query doesn't complete before the Sql_Server insert begins. I have tried locks, state checking and indexing. It is almost like the code is running too fast for the process to be completed. In the example below, I get the same null result repeatedly for one field in one record.

Any ideas?

Expand|Select|Wrap|Line Numbers
  1. Public Sub UpdateDataForSalesReport()
  2. Dim conn As New ADODB.Connection
  3. Dim cmd As New ADODB.Command
  4. Dim strSQL As String
  5.  
  6. conn.Open gc_strSCEHostConnection
  7. Set cmd.ActiveConnection = conn
  8.  
  9. conn.Execute ("delete from weekly_dept_sales_recap_sunday_we where store_num = '" & strStoreNum & "'")
  10. conn.Close
  11.  
  12. strSQL = "TRANSFORM Sum(sales_amt) AS SumOfsales_amt SELECT fiscal_year, period, week, weeks_in_period, " _
  13.        & "week_ending, store_num, store_name, IIf(Mid([department_num],3,1)='9','0099',[department_num]) AS dept_num, " _
  14.        & "financial_department_num, IIf(Mid([department_num],3,1)='9','Other',[department_desc]) AS dept_desc " _
  15.        & "From tbl_dates_departments_sunday_ending GROUP BY fiscal_year, period, week, weeks_in_period, week_ending, " _
  16.        & "store_num, store_name, IIf(Mid([department_num],3,1)='9','0099',[department_num]), " _
  17.        & "financial_department_num, IIf(Mid([department_num],3,1)='9','Other',[department_desc]) " _
  18.        & "ORDER BY IIf(Mid([department_num],3,1)='9','0099',[department_num]) PIVOT Weekday([date],2) In (1,2,3,4,5,6,7) "
  19.  
  20. With Me.datDailyDetail
  21.     .ConnectionString = gc_strSCELocalConnection
  22.     .RecordSource = strSQL
  23.     .LockType = adLockOptimistic
  24.     .CursorType = adOpenForwardOnly
  25.     .Refresh
  26. End With
  27.  
  28. Do While Me.datDailyDetail.Recordset.State = adStateExecuting
  29. Loop
  30.  
  31. Do While Me.datDailyDetail.Recordset.RecordCount < 21
  32.     Me.datDailyDetail.Refresh
  33. Loop
  34.  
  35. With Me.datSpecialItems
  36.     .ConnectionString = gc_strSCELocalConnection
  37.     .RecordSource = "select * from tbl_weekly_dept_sales_recap_sunday_we"
  38.     .LockType = adLockOptimistic
  39.     .CursorType = adOpenForwardOnly
  40.     .Refresh
  41. End With
  42.  
  43. Do While Me.datDailyDetail.Recordset.State = adStateExecuting
  44. Loop
  45.  
  46. Call SalesCashMod.LogMessage(strUser, strComputerName, strComputerAddress, Me.Name, "ReportDailySalesRecap", "Local Sales Data Appended")
  47.  
  48. With Me.datDailyDetail.Recordset
  49.     Do While Not .EOF
  50.         Me.datSpecialItems.Recordset.AddNew
  51.         Me.datSpecialItems.Recordset!store_num = !store_num
  52.         Me.datSpecialItems.Recordset!store_name = !store_name
  53.         Me.datSpecialItems.Recordset!fiscal_year = !fiscal_year
  54.         Me.datSpecialItems.Recordset!period = !period
  55.         Me.datSpecialItems.Recordset!Week = !Week
  56.         Me.datSpecialItems.Recordset!weeks_in_period = !weeks_in_period
  57.         Me.datSpecialItems.Recordset!week_ending = !week_ending
  58.         Me.datSpecialItems.Recordset!department_num = !dept_num
  59.         Me.datSpecialItems.Recordset!financial_department_num = !financial_department_num
  60.         Me.datSpecialItems.Recordset!department_desc = !dept_desc
  61.         Me.datSpecialItems.Recordset!mon = ![1]
  62.         Me.datSpecialItems.Recordset!tue = ![2]
  63.         Me.datSpecialItems.Recordset!wed = ![3]
  64.         Me.datSpecialItems.Recordset!thu = ![4]
  65.         Me.datSpecialItems.Recordset!fri = ![5]
  66.         Me.datSpecialItems.Recordset!sat = ![6]
  67.         Me.datSpecialItems.Recordset!sun = ![7]
  68.         Me.datSpecialItems.Recordset.Update
  69.         Do While Me.datSpecialItems.Recordset.State = adStateExecuting
  70.         Loop
  71.         .MoveNext
  72.     Loop
  73.  
  74. End With
  75.  
  76. Do While Me.datSpecialItems.Recordset.State = adStateExecuting
  77. Loop
  78.  
  79. Me.datSpecialItems.Recordset.Close
  80. Me.datDailyDetail.Recordset.Close
May 10 '07 #1
Share this question for a faster answer!
Share on Google+

Post your reply

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