+ Reply to Thread
Results 1 to 14 of 14

Excel VBA SQL ADO Recordset Returning Old Cached Results

  1. #1
    Registered User
    Join Date
    11-13-2009
    Location
    Cumbria
    MS-Off Ver
    Excel 2016
    Posts
    87

    Excel VBA SQL ADO Recordset Returning Old Cached Results

    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:

    HTML 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

  2. #2
    Banned User!
    Join Date
    01-17-2021
    Location
    Omaha, NE
    MS-Off Ver
    office 2016
    Posts
    211

    Re: Excel VBA SQL ADO Recordset Returning Old Cached Results

    do you know where old cached versions of tables are? that seems odd. savings have nothing to do with cache i dont think. you are not using power query so tables that are old should not be saved. have you look in Wef folders?

  3. #3
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,180

    Re: Excel VBA SQL ADO Recordset Returning Old Cached Results

    You shouldnít really be running sql on the open workbook as it causes memory leaks.

    However, the obvious thing to check is that the workbooks have been saved, Iím pretty sure the sql will only query saved data, if you make changes without saving and then run the query, I think this would result in what youíre seeing

  4. #4
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,180

    Re: Excel VBA SQL ADO Recordset Returning Old Cached Results

    Sorry, missed the bit about saving in your post. Iím 99.9% sure that you would always have needed to save, are you sure auto save or something wasnít on previously negating the need to manually save

  5. #5
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,180

    Re: Excel VBA SQL ADO Recordset Returning Old Cached Results

    Why donít you just use get and transform, no memory leaks and no need to save 👍🏻

  6. #6
    Registered User
    Join Date
    11-13-2009
    Location
    Cumbria
    MS-Off Ver
    Excel 2016
    Posts
    87

    Re: Excel VBA SQL ADO Recordset Returning Old Cached Results

    I want to delete this message, but not sure how.
    Last edited by RowanB; 03-04-2021 at 06:57 AM.

  7. #7
    Registered User
    Join Date
    11-13-2009
    Location
    Cumbria
    MS-Off Ver
    Excel 2016
    Posts
    87

    Re: Excel VBA SQL ADO Recordset Returning Old Cached Results

    Quote Originally Posted by Kyle123 View Post
    Sorry, missed the bit about saving in your post. Iím 99.9% sure that you would always have needed to save, are you sure auto save or something wasnít on previously negating the need to manually save
    No. Saving wasn't necessary at all. You could make a manual alteration to a table and the query would pick it up straightaway without saving.

  8. #8
    Registered User
    Join Date
    11-13-2009
    Location
    Cumbria
    MS-Off Ver
    Excel 2016
    Posts
    87

    Re: Excel VBA SQL ADO Recordset Returning Old Cached Results

    Is it possible to automate get and transform in a VBA macro?
    Last edited by RowanB; 03-04-2021 at 07:02 AM.

  9. #9
    Registered User
    Join Date
    11-13-2009
    Location
    Cumbria
    MS-Off Ver
    Excel 2016
    Posts
    87

    Re: Excel VBA SQL ADO Recordset Returning Old Cached Results

    Quote Originally Posted by Kyle123 View Post
    You shouldnít really be running sql on the open workbook as it causes memory leaks.

    However, the obvious thing to check is that the workbooks have been saved, Iím pretty sure the sql will only query saved data, if you make changes without saving and then run the query, I think this would result in what youíre seeing
    I've heard things about memory leaks using this method, but it used to work perfectly on the old terminal server we used to use.

  10. #10
    Registered User
    Join Date
    11-13-2009
    Location
    Cumbria
    MS-Off Ver
    Excel 2016
    Posts
    87

    Re: Excel VBA SQL ADO Recordset Returning Old Cached Results

    Quote Originally Posted by omahaNative_1023 View Post
    do you know where old cached versions of tables are? that seems odd. savings have nothing to do with cache i dont think. you are not using power query so tables that are old should not be saved. have you look in Wef folders?
    I'm not sure where they'd be cached / saved. How do I explore Wef folders?

  11. #11
    Registered User
    Join Date
    11-13-2009
    Location
    Cumbria
    MS-Off Ver
    Excel 2016
    Posts
    87

    Re: Excel VBA SQL ADO Recordset Returning Old Cached Results

    Quote Originally Posted by Kyle123 View Post
    Why donít you just use get and transform, no memory leaks and no need to save 👍🏻
    Thanks for the suggestion. Is it possible to automate get and transform through a VBA macro? Is it possible to define the get and transform operation / query in a VBA macro?

  12. #12
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    6,318

    Re: Excel VBA SQL ADO Recordset Returning Old Cached Results

    Yes, it is, but if you are actually using Office 2007 as your profile says, you will not have it available.
    Rory
    I drink, and I know things

  13. #13
    Registered User
    Join Date
    11-13-2009
    Location
    Cumbria
    MS-Off Ver
    Excel 2016
    Posts
    87

    Re: Excel VBA SQL ADO Recordset Returning Old Cached Results

    Quote Originally Posted by rorya View Post
    Yes, it is, but if you are actually using Office 2007 as your profile says, you will not have it available.
    Just changed my profile to Excel 2013. Thank you for the help. Going to have to do a lot of recoding work to utilise get and transform in place of my SQL queries, but maybe that's what I've got to do. Such a shame to lose the power of SQL when it seemed to perform so smoothly before.

  14. #14
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    6,318

    Re: Excel VBA SQL ADO Recordset Returning Old Cached Results

    I think you'll find that Get and Transform (aka Power Query) will make your life easier in the long run.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Open recordset not returning records
    By anrichards22 in forum Access Tables & Databases
    Replies: 3
    Last Post: 07-12-2019, 12:56 PM
  2. [SOLVED] Sort recordset results
    By dwbennett in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-10-2015, 08:55 AM
  3. [SOLVED] ADO Recordset Only Returning 1 Value
    By NeedForExcel in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-14-2015, 02:59 AM
  4. Recordset keeps returning EOF and shouldn't be.
    By BerkshireGuy in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-23-2006, 10:33 AM
  5. [SOLVED] Querytable results into vba Recordset
    By Aaron in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-19-2005, 06:18 AM
  6. Returning a recordset object from a function
    By Alratan in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-06-2005, 04:09 AM
  7. [SOLVED] problem returning a recordset from a function
    By Laurent M in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-25-2005, 06:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1