How to query an open ListObject?
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...ordset.313008/
https://stackoverflow.com/questions/...ource-exceeded
https://www.msofficeforums.com/excel...ce-script.html
https://www.decisionmodels.com/memlimitsd.htm
I don't know if this affects Excel 2016 onward.
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?
Is there a different/better/easier way?
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 front-end overhead.
(also asked here
http://www.vbaexpress.com/forum/show...bject&p=411636
https://www.excelforum.com/excel-pro...istobject.html)
Re: How to query an open ListObject?
Quote:
What options are there to pull data from a ListObject in an open workbook into VBA?
options may be affected by the amount of data in the list object
can you post a workbook with sample data?
could you read the data into a disconnected recordset, then work from that?
1 Attachment(s)
Re: How to query an open ListObject?
Sure, a disconnected recordset is fine. How? And why? Do you think disconnected would be immune to the leak?
I'm on 2016.
Assume the data is a very small set, say 10 items, 5 fields.
Why does size affect options? What if it was a million items?
I'm able to create a WorkbookConnection object pointing to a ListObject. Not sure how to pulll data from it, tho.
Code:
Dim oCon As WorkbookConnection
Set oCon = ThisWorkbook.Connections.Add2("Test 2", "Another test", "WORKSHEET;MyBook.xlsm", _
"MyBook.xlsm!MyTable", 7, False, False)
It would be cool if i could use the same statement with a SQL statement. Then we're not using ADO. (tho' maybe it's ADO under the hood?)
The following fails. It's identical to the above statement, but i tried to use a SQL statement.
Code:
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 "$".
I understand the table gets added to the workbook data model, and i understand i can query the data model (hope i don't have to use DAX).
Works:
Code:
Sub QryConn()
Dim oRs As New ADODB.Recordset, oCon As ADODB.Connection
' works
Set oCon = ThisWorkbook.Model.DataModelConnection.ModelConnection.ADOConnection
' fails
' Set oCon = ThisWorkbook.Connections("Test1").ModelConnection.ADOConnection
' Table2 is ListObject name. It's ALSO name of table in the Data Model
' so we're not using the WorkbookConnection object at all
' works. DAX, not SQL
oRs.Open "EVALUATE Table2", oCon, adOpenForwardOnly, adLockReadOnly
' works
Sheet3.Range("A1").CopyFromRecordset oRs
' works, but why have to transpose?
Dim vRecs
oRs.MoveFirst
vRecs = oRs.GetRows
vRecs = WorksheetFunction.Transpose(vRecs)
[Sheet3!A1:B3] = vRecs
' works
oRs.MoveFirst
Do Until (oRs.EOF)
Debug.Print oRs.Fields(0).Value, oRs.Fields(1).Value
oRs.MoveNext
Loop
oRs.Close
Set oRs = Nothing
oCon.Close
Set oCon = Nothing
End Sub
But .... still ADO. Does the data model eliminate the memory leak issue?
Re: How to query an open ListObject?
Having never worked with the Connections, there is one thing i noticed in your second code-block (which you said fails):
Your SQL cannot work, since you tell it to SELECT from a Workbook, and from a Range within that Workbook, but which Sheet?
A Workbook can have multiple sheets (like a database: Sheet = Table)
Next: You're Select * From .... but you define a Range.
SELECT * returns all Columns!
It should be something like "SELECT TOP(3) ColumnA, ColumnB FROM MyWorkBook.Sheets(1)" assuming your Column-Headers are in Row1
Re: How to query an open ListObject?
Quote:
Originally Posted by
Zvoni
Your SQL cannot work, since you tell it to SELECT from a Workbook, and from a Range within that Workbook, but which Sheet?
Good catch! These fail too.
Code:
sTbl = Sheet1.ListObjects(1).DataBodyRange.Address(External := True)
Set oCon = ThisWorkbook.Connections.Add2("Test 5", "AnotheR test", "WORKSHEET;" & sBk, _
"Select * From " & sTbl, xlCmdSql, True, False)
Code:
sTbl = Sheet1.ListObjects(1).Name
sBk = ThisWorkbook.Name
Set oCon = ThisWorkbook.Connections.Add2("Test 5", "AnotheR test", "WORKSHEET;" & sBk, _
"Select * From " & sBk & "!" & sTbl, xlCmdSql, True, False)
`Select *` should be sufficient.
Re: How to query an open ListObject?
Quote:
Do you think disconnected would be immune to the leak?
i can not answer this, but as it would not have to read the open workbook, it may not be causing the same issue
i have used ADO against open workbooks many times for limited operations, without any noticeable memory leak, but i was not particularly looking for one at the time
Re: How to query an open ListObject?
Quote:
Originally Posted by
westconn1
it would not have to read the open workbook
Maybe not dynamically, but it does have to read the open workbook to obtain the recordset in the first place.
Re: How to query an open ListObject?
Quote:
but it does have to read the open workbook to obtain the recordset in the first place.
but it could read from the open workbook without using sql, that is why i asked about the size of the data
Re: How to query an open ListObject?
Quote:
Originally Posted by
westconn1
but it could read from the open workbook without using sql, that is why i asked about the size of the data
for my use-case, i need to pull a scalar from the table based on a multi-field criteria. i don't know of any built-in functions for that.
2 Attachment(s)
Re: How to query an open ListObject?
I just ran the test code from the original leak article. The original code failed on the following line:
Code:
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:
This affects me because i'm on Excel 2016 64 bit. The 64-bit solution is:
Quote:
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.
Code:
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.
Attachment 182510
Attachment 182511
So i think i can conclude that the memory leak doesn't affect 64-bit Excel with the 64-bit driver.
Re: How to query an open ListObject?
there is some difference in the ace connection strings for the different type of workbooks, to tell if it is macro enabled or binary type, if you need more on that you should be able to find at connectionstrings.com
i think you need the ace drivers for all workbooks from excel 2007 (maybe 2010), even in a 32 bit environment, because i remember having to use ace drivers for some coding when i was still running XP 32bit
i am not sure if the ace drivers are backwards compatible to earlier versions of excel
as i said previously i had never had a known issue with memory leaks from using ado or ace drivers
maybe if you had posted your connection in the OP someone may have picked up the problem, but i had assumed your connection was valid and your problem was the memory leak
Re: How to query an open ListObject?
Yep, my connection was valid. The issue of concern was the memory leak.
The connection in the original leak article fails, prolly because it's for an old version of Excel and 32-bit Jet. The 64-bit ACE connection string works fine.
thx
