Excel VBA SQL ADO Recordset Returning Old Cached Results

[RowanB86]

Hi,

If anyone can provide a solution to this, it will be massively appreciated. I have written a number of VBA macros at work that perform SQL queries to join Excel worksheet-based tables and place the output in another worksheet within the same workbook. This worked well until we moved to a new terminal server that used Windows Server 2016 (our old terminal server used Windows Server 2008). The sporadically occurring problem we have now is that sometimes queries will not pick up the latest version of tables and will perform on cached (old) versions of tables. This problem occurs seemingly randomly. As soon as the workbook is saved, the latest versions of tables are recognised, but saving makes a lot of the processes I've built impractical and we didn't need to save before we moved to a new terminal server. If anyone can provide any insight into why this issue might be occurring, it will be very helpful.

This is a very simple example of the type of query I might write:

Sub JoinTables()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim ws3 As Worksheet
Dim Rept As Worksheet
Dim StrQuery As String
Dim WBConnStr As String
Dim i As Long
Application.ScreenUpdating = False

Set ws3 = ThisWorkbook.Worksheets("JOINED_TABLE")
Set Rept = ThisWorkbook.Worksheets("Rept")

ws3.UsedRange.ClearContents
Rept.UsedRange.ClearContents
WBConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=" & ThisWorkbook.FullName & ";Mode=Share Deny None;Extended Properties=""Excel 12.0;HDR=Yes"";"
StrQuery = "SELECT * FROM [WIN_ID_DHS_CODE$] WHERE WIN_ID > " & ThisWorkbook.Worksheets("Sheet5").Range("A1").Value
cnn.Open WBConnStr
rst.Open StrQuery, cnn, adOpenDynamic, adLockReadOnly
ws3.Cells(2, 1).CopyFromRecordset rst
For i = 1 To rst.Fields.Count
ws3.Cells(1, i).Value = rst.Fields(i - 1).Name
Next i
cnn.Close
Set rst = Nothing
Set cnn = Nothing

StrQuery = "SELECT a.*,b.CAP_CODE FROM [JOINED_TABLE$] a LEFT JOIN [WIN_ID_CAP_CODE$] b ON a.WIN_ID = b.WIN_ID"

cnn.Open WBConnStr
rst.Open StrQuery, cnn, adOpenDynamic, adLockReadOnly
Rept.Cells(2, 1).CopyFromRecordset rst
For i = 1 To rst.Fields.Count
Rept.Cells(1, i).Value = rst.Fields(i - 1).Name
Next i
cnn.Close
Set rst = Nothing
Set cnn = Nothing

Application.ScreenUpdating = True
End Sub

 

[RoryA]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Excel VBA SQL ADO Recordset Returning Old Cached Results [SOLVED]
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[RowanB86]

Thanks Rory. Sorry about that.