Pivot tables not refreshing after data source update

[Bill E. Hollywood, FL]

I'm trying to two refresh pivot tables on a worksheet that are bound
to the same data. I find that it takes two successive calls in order
to get this to work. However, a "Refresh" button placed on the page
that calls basically the same code works perfectly when clicked only
one time. Below is the code to change the datasource query and
refresh the pivot tables when the user changes one of the inputs on
the page:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Trapper

'Reset command text and requery
If (Target.Address = Me.Range("StartDate").Address Or
Target.Address = Me.Range("EndDate").Address) Then
With ActiveWorkbook.Connections("srv1658
JumboRolls").OLEDBConnection
.CommandText = "My SELECT statement"
.Refresh
End With

For Each t In Worksheets("Analysis").PivotTables
t.PivotCache.Refresh
Next
End If
Exit Sub
Trapper:
'MsgBox Err.Description
Exit Sub

End Sub

Here is the code called on a button click that works just fine

Sub RefreshPivotTables()
For Each t In Worksheets("Analysis").PivotTables
t.PivotCache.Refresh
Next
End Sub

Does anyone have any idea why the first block of code would not
properly refresh the Pivot tables? It seems like a caching issue and
perhaps I don't understand the cache well enough.

Bill

 

[Bill E. Hollywood, FL]

The resolution to this was to add

..BackgroundQuery = False

to the OLEDBConnection just before the call to Refresh. Thank you to
Bill Manville and Jan Karel Pieterse in the Compuserve Office forum
for the suggestion.

Bill