Excel VBA SQL ADO Recordset Returning Old Cached Results

RowanB86

New Member
Joined
Mar 11, 2021
Messages
2
Office Version
  1. 2013
Platform
  1. Windows
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:


VBA Code:
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
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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.
 
Upvote 0

Forum statistics

Threads
1,223,157
Messages
6,170,418
Members
452,325
Latest member
BlahQz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top