title: Excel OLE DB connection refresh issues
body: <p>I have an Excel workbook with two dynamic OLE DB queries. I'm having issues with refreshes.</p>
  
  <p>To set things up I have a SQL table-valued function as the source. The data is of the nature of...</p>
  
  <pre><code>SGrp   SG_Desc   SKU   SKU_Desc   Server   Billed
  1      Item 1    111   whatever    15        12
  1      Item 2    222   some more   10         9
  2      Item 3    333   zzz         10         8
  3      Item 4    555   abc         20        18
  </code></pre>
  
  <p>On the first sheet ("Overall") I have a data connection that summarizes the groups with the command text of dynamically modified with one button.</p>
  
  <pre><code>SELECT SGrp, SG_Desc, SUM(Served) AS Served, SUM(Billed) AS Billed FROM mySQLdb ('8/19/2018','8/25/2018') WHERE SGrp &lt;&gt; '' GROUP BY SGrp, SG_Desc ORDER BY SG_Desc
  </code></pre>
  
  <p>I then have a cell with a data validation list that selects the group and a button to execute the VBA to dynamically modify the other connection. There's also two cells with the report start &amp; end date for filtering. When I push the "Detail" button it runs the code below.</p>
  
  <pre><code>Private Sub RunDetail_Click()
  
  Dim StartDate As Date
  Dim EndDate As Date
  Dim SGrp As String
  
  Range("A1").Value = Range("G8").Value2
  
  StartDate = Sheets("Overall").Range("H1").Value
  EndDate = Sheets("Overall").Range("H2").Value
  SGrp = Sheets("Overall").Range("A1").Value
  
  SGrp = LTrim(RTrim(SGrp))
  
  With ActiveWorkbook.Connections("CJP_DeliveryRecap_Detail").OLEDBConnection
      .CommandText = "SELECT SKU, SKU_Desc, Served, Billed FROM mySQLdb ('" &amp; StartDate &amp; "','" &amp; EndDate &amp; "') WHERE SG_Desc='" &amp; SGrp &amp; "'"
  '    .Refresh
  '    ActiveWorkbook.Connections("CJP_DeliveryRecap_Detail").Refresh
  End With
  
  'RefreshOLEDB
  'ThisWorkbook.RefreshAll
  ActiveWorkbook.Connections("CJP_DeliveryRecap_Detail").Refresh
  'Application.CalculateUntilAsyncQueriesDone
  
  Application.Wait (Now + TimeValue("0:00:03"))
  
  Dim rc As Integer
  Dim i As Integer
  
  Worksheets("Detail").Activate
  
  With Worksheets("Detail").Range("CJP_DeliveryRecap_Detail")
      rc = .Rows.Count
  End With
  
  With Worksheets("Detail").Range("E1048576")
      .Select
      .End(xlUp).Select
  End With
  i = Selection.Row
  
  Worksheets("Detail").Range("E5").Select
  
  Worksheets("Detail").Range("E5:G" &amp; i).ClearContents
  
  Worksheets("Detail").Range("E5").Value = 1
  Worksheets("Detail").Range("F5").Value = "=+CJP_DeliveryRecap_Detail[@Served]*E5"
  Worksheets("Detail").Range("G5").Value = "=+CJP_DeliveryRecap_Detail[@Billed]*E5"
  Sheets("Detail").Range("E5:G5").Copy Sheets("Detail").Range("E6:E" &amp; rc + 4)
  Sheets("Detail").Range("F1").Value = ("=SUM(F5:F" &amp; rc + 4 &amp; ")")
  Sheets("Detail").Range("G1").Value = ("=SUM(G5:G" &amp; rc + 4 &amp; ")")
  
  End Sub
  </code></pre>
  
  <p>So, what is it doing? The first "strange" thing is I occasionally get errors in the code that I have to continue on. A lot of times, but not all, it hits the Application.Wait line, then the Worksheets("Detail").Activate line, and sometimes the line where I set values or copy data.</p>
  
  <p>There are some comments where I've been testing various refreshes etc. The issue is because while when the code completes it displays the data conn detail correctly but the calculations as to the size of the results is from the prior set. If I click the button a second time then it calculates them correctly. I would of course prefer not to have a arbitrary 3sec delay but simply run the rest of the code after the resultant records have been retrieved.</p>
  
  <p>Where am I going wrong as I've been banging my head against the wall with this. Most of what I do is in Access but Excel was the proper tool in this case.</p>
  
  <p>I just thought of, but not thrilled to, would it work to have a separate sub to handle the record size calcs and cell values clear\set and call that from the button click or would that still not be generated until after the original sub ends.</p>
  
  <p>Thanks in advance,</p>