SQL Queries in VBA Macros on Open Excel Workbook

Question

Apologies for being vague, as I'm trying my best to explain the problem I'm facing. I'm having problems with Excel macros that I've written that perform SQL queries on open Excel workbooks using a DAO recordset. These procedures tend to query a worksheet based table (on Sheet1), copy the output of the query to another sheet (Sheet2) and then another query might by performed on Sheet2 to output the final result to another sheet. This used to work perfectly when we were able to do this on a different server, but is falling down now that we've been forced to run it on a new server.

The kind of error that randomly occurs is that, for example, the first query will work and the correct output will appear on sheet 2, but when the second query is made on Sheet2, the query will not recognise that the contents of sheet 2 has been updated. It might treat it as if it still had the same data it had before the first query was performed. Having searched the web for solutions / advise on this, I've often been advised that this is a memory leakage issue, but never seen a good solution. If anyone can provide me with any tips for this issue, it will be hugely appreciated. Here is a simple example of a query I might attempt:

StrQuery = "SELECT DISTINCT Col1,Col2 FROM [Temp$] WHERE Col1 IS NOT NULL"
Set Conn = CreateObject("DAO.DBEngine.120")
Set db = Conn.OpenDatabase(ThisWorkbook.FullName, False, True, "Excel 12.0 Xml;HDR=Yes;")
Set rst2 = db.OpenRecordset(StrQuery)
Temp2.Cells(2, 1).CopyFromRecordset rst2
rst2.Close
db.Close

question from:https://stackoverflow.com/questions/65905151/sql-queries-in-vba-macros-on-open-excel-workbook

Answer 1

EDIT - this is completely wrong: tested without saves and the correct data is retrieved.

Apologies for the derail...

When you run a SQL query on an Excel file it queries the file as it exists on disk (ie. not in memory), so if you've not saved it after updating the content then the updated content will not be accessible to (eg) a query which queries output from a previous query.