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