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

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,146
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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.
 

Forum statistics

Threads
1,136,909
Messages
5,678,514
Members
419,768
Latest member
eguechi09x

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
Top