How to query an open ListObject?

[johnywhy]

I want to pull data from a ListObject into VBA using SQL statements. I don't need to pivot. I don't need any front-end display or controls.

My first thought was ADO, but a memory leak has been reported when querying an open workbook with ADO:



https://www.access-programmers.co.uk/forums/threads/run-time-error-2147024882-8007000e-out-of-memory-while-building-ado-recordset.313008/
https://stackoverflow.com/questions/36714282/excel-access-db-ado-memory-leak-system-resource-exceeded
https://www.msofficeforums.com/excel-programming/21433-slow-comparison-replace-script.html
https://www.decisionmodels.com/memlimitsd.htm


What options are there to pull data from a ListObject in an open workbook into VBA?

I'm looking at "Connections" and "Data Model". It seems that creating a "new Connection" is the simplest way to connect to a ListObject, correct? Can i execute a SQL statement against a Connection? Is it necessary to add the table to the Data Model?

I think i don't need to use a Query object, because i think that's a front-end display component which sits on top of Connections, and therefor just adds more overhead, correct?

I think i might make use of one of the Power addins, but prefer to use the core features without addins, if possible. Again, seems like unnecessary overhead.

Is there a different/better/easier way?

(Also asked here
https://www.vbforums.com/showthread.php?893602-How-to-query-an-open-ListObject&p=5538435
https://www.excelforum.com/excel-programming-vba-macros/1360604-how-to-query-an-open-listobject.html)

[johnywhy]

I'm able to create a WorkbookConnection object pointing to a ListObject. Not sure how to pulll data from it, tho.



Dim oCon As WorkbookConnection
Set oCon = ThisWorkbook.Connections.Add2("Test 2", "Another test", "WORKSHEET;MyBook.xlsm", _
"MyBook.xlsm!MyTable", 7, False, False)

The following fails. It's identical to the above statement, but i tried to use a SQL statement



Set oCon = ThisWorkbook.Connections.Add2("Test 5", "AnotheR test", "WORKSHEET;MyBook.xlsm", _
"Select * From MyBook.xlsm$A2:B4", xlCmdExcel, True, False)

Also fails with "!" instead of "$".

[snb]

Why don't you post your workbook ?


Sub M_snb()
sn = Sheet1.ListObjects(1).HeaderRowRange

With CreateObject("ADODB.Recordset")
.Open "SELECT " & sn(1, 1) & ", " & sn(1, 4) & " FROM `Sheet1$`", "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0 Xml"""
MsgBox .getstring
End With
End Sub

[johnywhy]

Why don't you post your workbook ?
snb, iinm your code doesn't query a ListObject, it queries the sheet.

[snb]

Sub M_snb()
sn = Sheet1.ListObjects(1).HeaderRowRange

With CreateObject("ADODB.Recordset")
.Open "SELECT " & sn(1, 1) & ", " & sn(1, 4) & " FROM `Sheet1$`Table1 ", "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0 Xml"""

MsgBox .getstring
End With
End Sub

[johnywhy]

Sub M_snb()
sn = Sheet1.ListObjects(1).HeaderRowRange

With CreateObject("ADODB.Recordset")
.Open "SELECT " & sn(1, 1) & ", " & sn(1, 4) & " FROM `Sheet1$`Table1 ", "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0 Xml"""

MsgBox .getstring
End With
End Sub

Thx for that!

Please tell me more about your usage of With, instead of a dimensioned variable.

Regarding the leak, i think it may have been resolved. Here's why i suspect so:

- The original MS article describing the leak is no longer available on the MS website.
- Here's (https://www.betaarchive.com/wiki/index.php/Microsoft_KB_Archive/319998) an archived copy of the article. It says "APPLIES TO Excel 2000, OLE DB Provider for Jet 4.0, Excel 97, Excel 2002". Not sure if i'm using Jet at all, but def not using those old versions of Excel.
- This coder (https://social.msdn.microsoft.com/Forums/sqlserver/en-US/c1dadb6e-ea00-4436-9a8c-9aa72623505a/bug-memory-leak-occurs-when-you-query-an-open-excel-worksheet-by-using-activex-data-objects-ado) tried to duplicate the original leak, and failed.

Altho this guy (https://databaseadvisors.com/pipermail/accessd/2014-March/110744.html) has experienced the problem as recently as 2014. Not sure what version he's on.
And this person (https://stackoverflow.com/questions/36714282/excel-access-db-ado-memory-leak-system-resource-exceeded) in 2016.

[snb]

It's all functioning well in Excel 2010.

Read on With ... End With in the Excel Helpfiles or in your VBA handbook.
With ... end with automatically opens a temporary object variable; it doesn't occupy any lasting space in working memory. It is reserved to objects exclusively

[johnywhy]

It's all functioning well in Excel 2010.

Read on With ... End With in the Excel Helpfiles or in your VBA handbook.
With ... end with automatically opens a temporary object variable; it doesn't occupy any lasting space in working memory. It is reserved to objects exclusively

I understand well how to use With. Been using it for years. However, I'm skeptical that the memory is cleared at End With. Here's why.

End Sub is also supposed to clear objects -- even those declared with object variables.

But we know, from those phantom workbooks which remain open in the VBA project explorer, that you still need to set the variable to Nothing. End Sub isn't enough.

I believe phantoms can happen with certain other objects, but we don't notice because the objects don't appear in the project explorer.

So I'm not convinced the same issue doesn't affect End With. Still, there's no declared variable, so maybe...

[snb]

End Sub is also supposed to clear objects

Only in your imagination.

[p45cal]

But we know, from those phantom workbooks which remain open in the VBA project explorer, that you still need to set the variable to Nothing. End Sub isn't enough.
Where have you read that?
If I create a new workbook then close it again straightaway (while I have the VBE open), it appears in the project explorer and remains there. How does that indicate there are uncleared objects in that workbook (especially as I haven't written any code in that workbook)?

Separately, regarding your main questions in your first post in this thread; Is the emphasis on being able to use SQL to query an Excel table in another (open) workbook, or to bring the data into VBA? I ask because Power Query (aka Get & Transform data) has been built-in to Excel since version 2016 (so it's no longer an add-in (it's now what you're calling a core feature), so no extra-overhead). Power Query brings data in from Excel tables in its stride, isn't limited to the number of rows on a sheet, can be 'connection-only' and is possibly more powerful and easy to use than pure sql with vba, especially since the PQ Editor previews the steps as you go along. I think there's very little transformation/filtering that you can do in SQL that you can't do in Power Query.
Why don't you tell us the kinds of things you want to do with the data, to see whether it can be done easily with PQ?
Often, PQ transformations will be folded into SQL (though I recognise that an expert sql coder will likely still be able to produce more efficient code then machine-produced sql code).

[johnywhy]

Where have you read that?

SO:

Object variables are guaranteed to be cleaned (set to Nothing) when they go out of scope, this decrements the reference counters in their respective objects. No manual action required. It is even harmful to do a manual cleanup, as it gives you a false sense of more correct code (https://stackoverflow.com/questions/18594258/reading-first-few-characters-in-large-text-files-in-vba/18594888#comment27378612_18594888).
https://stackoverflow.com/a/19038890/209942



If I create a new workbook then close it again straightaway (while I have the VBE open), it appears in the project explorer and remains there. How does that indicate there are uncleared objects in that workbook

johnywhy:

Sub testPhantom()
Dim oBk As Workbook
Set oBk = Workbooks.Add
oBk.Close
' phantom remains in project explorer
Set oBk = Nothing
' phantom is gone
End Sub

if i end sub without setting to nothing, the phantom usually disappears. But, under some circumstances, the phantom unexpectedly remains after End Sub

Andy Pope:

There have been many posts about this over the years.
http://www.excelguru.ca/blog/2009/03/17/microsoft-add-in-causes-vbe-ghost-project-issues/
http://www.bettersolutions.com/excel/ECC653/MR026524332.htm
http://www.office-archive.com/2-excel/15f29edb770122c1.htm
https://answers.microsoft.com/en-us/office/forum/office_2010-excel/phantom-projects-displayed-in-vbe-in-office-2010/017178ed-1c4d-4ce8-a1fe-cd52bf3748b0
https://www.mrexcel.com/board/threads/bizarre-vba-issue-phantom-vba-project-files-after-workbook-is-closed.783940/

i've experienced phantom workbooks enough times over the years that i always set to nothing explicitly. In fact, i do that for ALL object variables. I find that seems to make my projects less sluggish, and less likely to crash. Imo, if this happens with workbooks, how do we know it's not happening with other objects?


Why don't you tell us the kinds of things you want to do with the data, to see whether it can be done easily with PQ?
My understanding is that PQ is for front-end display. My purpose isn't front-end. I only want to bring the info into VBA. Also, PQ is far more power and features than i need. My use-case doesn't involve pulling large datasets from the sheet -- i'm just pulling a single scalar value, from a single field, from a single row, based on a multi-field criteria.

[p45cal]

My understanding is that PQ is for front-end display. My purpose isn't front-end. I only want to bring the info into VBA. Also, PQ is far more power and features than i need. My use-case doesn't involve pulling large datasets from the sheet -- i'm just pulling a single scalar value, from a single field, from a single row, based on a multi-field criteria.
PQ, although powerful can do simple stuff like extract single values from a table (even if that table has one field and one row!). It's all quite mysterious (as if you're avoiding saying what you're wanting to do - which is fine if this is largely an academic question or perhaps you've invested significant time writing vba code and this thread is about adding a small enhancement to it (in which case it's really easy to read-in (specific fields of) a table in an open workbook for further filtering in vba).


re:
Set oBk = Nothing
' phantom is goneNot here it isn't. It stays. Even after closing and re-opening the VBE.
VBE 7.1 version 1113, Excel 365.

[johnywhy]

There's nothing mysterious. I've described exactly what my need is. If you're asking me to describe my client's entire application, that's off-topic, private, and unnecessary to answer the questions here.

PQ has rich resources -- too rich for my use-case. My expectation is that PQ will consume a good chunk of resources. Too many features, intended for front-end display, meant for large datasets: Wrong tool for the job.



It stays. Even after closing and re-opening the VBE.
That's weird. i just tested on my 365. The phantom goes away on setting to nothing. Same behavior as Excel 2016.

Microsoft® Excel® for Microsoft 365 MSO (16.0.14326.20384) 64-bit , on Windows 10
VBE 7.1/1110

[johnywhy]

I just ran the test code from the original leak article (https://www.betaarchive.com/wiki/index.php/Microsoft_KB_Archive/319998). The original code failed on the following line:



cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & WorkbookFullName & "Extended Properties=Excel 8.0"
Error: Provider cannot be found. It may not be properly installed.


The reason is:

The Microsoft OLE DB Provider for Jet and the Jet ODBC driver are available in 32-bit versions only. You can't run them in 64 bit mode.
https://www.connectionstrings.com/using-jet-in-64-bit-environments/


This affects me because i'm on Excel 2016 64 bit. The 64-bit solution is:



With Office 2010, there are new drivers, the 2010 Office System Driver, which will be provided in both 32-bit and 64-bit versions. You can use these drivers to let your application connect to Access, Excel and text files in a 64 bit environment utilizing the new 64-bit drivers. The provider name is "Microsoft.ACE.OLEDB.12.0".


The driver is already installed, so i just need to use the ACE connection string.

cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & WorkbookFullName & ";Extended Properties=""Excel 12.0;HDR=YES"";"
https://www.connectionstrings.com/ace-oledb-12-0/


My data contained 1 million+ records. While running, i watched Excel in Task manager (i didn't use the memory counters from the original MS article). On each loop, Excel memory consumption varied from about 200 MB to about 400 MB. Never grows beyond 400 MB, never crashes, always jumps back to 250 MB on each loop. There was no steady increase, as you'd see with a leak.

29038

29039


So i think i can conclude that the memory leak doesn't affect 64-bit Excel with the 64-bit driver.

[snb]

The connection string 'Microsoft.ACE.OLEDB.12.0' has nothing to do with 64-bit Excel.
Your 'conclusions' lack sensible reasoning and appropriate knowledge.

[johnywhy]

The connection string 'Microsoft.ACE.OLEDB.12.0' has nothing to do with 64-bit Excel.
Your 'conclusions' lack sensible reasoning and appropriate knowledge.
I'm glad you identified an error in my comment, but that's less helpful if you don't provide the correct info.
My reasoning is based on the article which i quoted and linked. Is that article incorrect? or have i misunderstood the article?