Results 1 to 12 of 12

Thread: How to query an open ListObject?

  1. #1

    Thread Starter
    Member
    Join Date
    Jan 2011
    Posts
    41

    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)
    Last edited by johnywhy; Oct 3rd, 2021 at 12:09 PM.

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: How to query an open ListObject?

    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?
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    Member
    Join Date
    Jan 2011
    Posts
    41

    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?
    Attached Files Attached Files
    Last edited by johnywhy; Oct 3rd, 2021 at 05:28 PM.

  4. #4
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,419

    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
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  5. #5

    Thread Starter
    Member
    Join Date
    Jan 2011
    Posts
    41

    Re: How to query an open ListObject?

    Quote Originally Posted by Zvoni View Post
    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.
    Last edited by johnywhy; Oct 4th, 2021 at 11:23 AM.

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: How to query an open ListObject?

    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
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  7. #7

    Thread Starter
    Member
    Join Date
    Jan 2011
    Posts
    41

    Re: How to query an open ListObject?

    Quote Originally Posted by westconn1 View Post
    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.

  8. #8
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: How to query an open ListObject?

    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
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  9. #9

    Thread Starter
    Member
    Join Date
    Jan 2011
    Posts
    41

    Re: How to query an open ListObject?

    Quote Originally Posted by westconn1 View Post
    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.

  10. #10

    Thread Starter
    Member
    Join Date
    Jan 2011
    Posts
    41

    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:
    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/us...-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.
    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.

    Name:  Screenshot (95).jpg
Views: 433
Size:  8.8 KB

    Name:  Screenshot (96).jpg
Views: 324
Size:  7.1 KB

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

  11. #11
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  12. #12

    Thread Starter
    Member
    Join Date
    Jan 2011
    Posts
    41

    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

Tags for this Thread

Posting Permissions

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



Click Here to Expand Forum to Full Width