Set listobject as ADODB object and use sql on this.

[jaryszek]

Hi Guys,

i want to assing existed listobjects to adodb recodsers and use joins of them in Excel.

So "select Current * from table Current left Join Previous On current.order = previous.order"

How to use adodb object n Excel with listobjects?
Have i create always new adodb connection for this?

Best,
Jacek

[xlnitwit]

Listobjects are like dynamic named ranges and don't exist to ADO unless the workbook is open, which can cause memory leaks and should be avoided whenever possible.

[jaryszek]

xlnitwit, hmm

i wanted to take solution from here to find differences:

https://stackoverflow.com/questions/...th-differences

Workbook will be open all the time because macro will run via VBA. But still as you mentioned - it can be not efficient way?

Best,
Jacek

[xlnitwit]

If you want to use that solution, nothing is stopping you. You can simply use the listobject's Range.Address instead of the Usedrange.address.

[jaryszek]

i know xlnitwit that i can use it but i am always looking for the best solution.

I think there is no other good options to compare tables.
I am writing here about this problem and nobody has idea how to compare tables:

https://www.excelforum.com/excel-pro...ml#post4915738

best,
Jacek

[xlnitwit]

It's a pretty vague question, as written, in my opinion. What information are you interested in, and what output do you expect? How do you determine what is the best solution?

[jaryszek]

I am checkig, testing and comparing different solutions. Sometimes i am measuring time for different codes.
And try ti think and use my experience.
If i will get macro with selection code - i know what this is very slow solution and to avoid this.

I want to have additions, deletions and updates - so delta between these two tables.

So Added, for example:
ID = 4, name, system

So deleted, for example:
ID=3, name, system

updates:
ID = 1, Name, System
ID= 2, Name, System

Best,
Jacek

[jaryszek]

I will update the other topic with output what i want.

Best,
Jacek

[jaryszek]

Sub tableComparator()

    Dim rst1, rst2, cmd, conn, esteLibro, wbActive, sSql
    
    Set conn = CreateObject("ADODB.Connection")
    Set rst1 = CreateObject("ADODB.RecordSet")
    Set rst2 = CreateObject("ADODB.RecordSet")
    
    
    wbActive = ActiveWorkbook.FullName
    
    sSql = "select * from [Sheet1$t_test]"
    
    conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
            "Data Source='" & wbActive & "';" & _
            "Extended Properties=""Excel 12.0;HDR=Yes;"";"
            
    Set rst1 = CreateObject("ADODB.RecordSet")
    
    rst1.Open sSql, oConn, adOpenStatic, adLockReadOnly

End Sub

What reference i should use here?

"select * from [Sheet1$t_test]" --> this is not working.

[xlnitwit]

Use the address of the table range (as in the SO article to which you linked) not its name.

[jaryszek]

thank you.

I have found solution which is not working for me.

Sub tableComparator()

    Dim rst1, rst2, cmd, conn, esteLibro, wbActive, sSql
    
    Set conn = CreateObject("ADODB.Connection")
    Set rst1 = CreateObject("ADODB.RecordSet")
    Set rst2 = CreateObject("ADODB.RecordSet")
    
    
    wbActive = ActiveWorkbook.FullName
    
    sSql = "select * from [t_test$]"
    
    conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
            "Data Source='" & wbActive & "';" & _
            "Extended Properties=""Excel 12.0;HDR=Yes;"";"
            
    Set rst1 = CreateObject("ADODB.RecordSet")
    
    rst1.Open sSql, oConn, adOpenStatic, adLockReadOnly

End Sub

so here:

sSql = "select * from [t_test$]"

is not working.
Do you have similar issue?

macro is from here:
https://stackoverflow.com/questions/...an-excel-sheet

What is working for me is:

 With Range("t_test")
        tblAddress = "[" & .Parent.name & "$" & .Address(False, False) & "]"
    End With

    sSql = "select * from " & tblAddress

so can i refer to tablename (listobject name)?
Or only to named range?
Or only to address?

Best,
Jacek

[xlnitwit]

You have to use the address, unless it's a static range, in which case the name will work. Tables and dynamic named ranges have to use the address, not the name.

[jaryszek]

thank you xlitniwt,

why macrin in the link worked for others?

Best,
Jacek

[xlnitwit]

It didn't use tables at all as far as I can see.

[jaryszek]

aa ok, they are using named ranges.

thank you!

Best,
Jacek

[jaryszek]

Ok guys,

my macro and code in attachment.

https://paste.ofcode.org/ZqQEnurLTT4q2EcwrG44dy

Now i want to write join on these two recordsets.
Or maybe better is to write left join within only one recordset (rst1?) and not to split tables into 2 separate recordets?

How can i do this?

SQL should work like below:

SELECT rst1.*
FROM rst1 LEFT JOIN rst2 ON rst1.Order= rst2.Order
WHERE rst2.Order Is Null

Please help,
Best,
Jacek

[CK76]

Hmm, as xlnitwit mentioned earlier, I'd avoid using ADO on open workbook. It's known to cause memory leak and can cause issues.
You could work around it by making a temp copy of the open workbook and querying from that.

Though since you have Excel 2016. I'd recommend using PowerQuery (Get & Transform) instead of ADO. It's more robust and easier to maintain.

[CK76]

Oh and if you need to use ADO. You can't join recordset like SQL. Recordset is array object and you'll need to iterate.
You can instead do single SQL query and join 2 in one shot and return single recordset.

What's the expected output though? Do you want only Order #3 from t_testNew?

[jaryszek]

hi CK76,

thank you.

It's known to cause memory leak and can cause issues.

Where did you get to know this?

You could work around it by making a temp copy of the open workbook and querying from that.

Ok. I am comparing 2 workbooks, previous version and current. I can create new temp workbooks and base on them.

I'd recommend using PowerQuery (Get & Transform) instead of ADO

I do not like this solution because:
1. All users have to have PQ
2. This is for manual querying, not creating automations

You can instead do single SQL query and join 2 in one shot and return single recordset.

ok this is dofficult. How to write SQL with join?

and my output is within sheet Result.

So for Added SQL is:
SELECT t_current.*
FROM t_current LEFT JOIN t_previous ON t_current.Order= t_previous.Order
WHERE t_previous.Order Is Null

For deleted SQL is:
Select t_previous.*
from t_current right join t_previous on t_current.Order= t_previous.Order
where t_current.Order

I do not know how to write syntsax of SQL for each statements above.

Best,
Jacek

[CK76]

Where did you get to know this?

It's fairly well known issue for ADO. There used to be article in MS Support. They've deprecated the article.
But as far as I know it's never been fixed (I was still able to reproduce the issue back in 2015, and know others reported same in 2016).

Link to web archive of the article.
https://web.archive.org/web/20080321....com/kb/319998

2. This is for manual querying, not creating automations

I get your 1st point. But this is not true. You can set up layers to pass parameter for dynamic query, with set interval refresh (or upon workbook open). Also, you can use ODBC query instead of "From Excel" to query Excel workbook(s) using SQL statements.

I'm bit busy for next couple of hours. I'll upload sample this afternoon.

[jaryszek]

Thank you CK76.

I was searching and i see that you can use VBA with power query - very big advantage for it ;-)

you can use ODBC query instead of "From Excel" to query Excel workbook(s) using SQL statements.

i am very interested in this.

edit:
i tried to run firs SQL:

SELECT t_current.*
FROM t_current LEFT JOIN t_previous ON t_current.Order= t_previous.Order
WHERE t_previous.Order Is Null

but i failed. I can not refer to field names (like t_current.Order) here...using ODBC.

Best,
Jacek

[CK76]

Here, try attached. I made some modification to your table set up. Made both have same # of columns (added Ram to previous), and changed Order to OrderID in both tables to avoid ambiguity in query string.

Data (tables) now both reside in Data workbook. SQL workbook contains the code and query to bring in data.

Function fileName(strPath As String) As Variant
    Dim myFile As String
    With Application.FileDialog(msoFileDialogFilePicker)
        .Title = "Select data file"
        .AllowMultiSelect = False
        .InitialFileName = IIf(Right(strPath, 1) <> "\", strPath & "\", strPath)
        If .Show <> -1 Then GoTo NextCode
        myFile = .SelectedItems(1)
    End With
    
NextCode:
    fileName = myFile
End Function

Sub queryFiles()
    Dim cn As Object, rst As Object
    Dim strQuery As String, dataFile As String, prevFile As String
    Dim i As Long
    Dim x
    
    dataFile = fileName(ThisWorkbook.Path)

    If Len(dataFile) = 0 Then
        MsgBox "Data File not chosen"
        Exit Sub
    End If
    
    Set cn = CreateObject("ADODB.Connection")
On Error GoTo ErrHandle:
    With cn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "Data Source=" & dataFile & ";" & _
                          "Extended Properties=""Excel 12.0 Xml;HDR=Yes;"""
        .CursorLocation = 3
        .Open
    End With
    strQuery = "SELECT [t1].*, 'Added' as [Action] From [Current$] as [t1] Left Join [Previous$] as [t2] "
    strQuery = strQuery & "ON [t1].OrderID = [t2].OrderID Where [t2].OrderID Is Null UNION ALL "
    strQuery = strQuery & "SELECT [t3].*, 'Deleted' as [Action] From [Previous$] as [t3] Left Join [Current$] as [t4] "
    strQuery = strQuery & "ON [t3].OrderID = [t4].OrderID Where [t4].OrderID Is Null;"
    Debug.Print strQuery
    Set rst = CreateObject("ADODB.Recordset")
    rst.Open strQuery, cn, 1, 3
    For iCols = 0 To rst.Fields.Count - 1
        Sheet1.Cells(1, iCols + 1).Value = rst.Fields(iCols).name
    Next
    Sheet1.Range("A2").CopyFromRecordset rst
    rst.Close
ErrHandle:
    If Err.Number <> 0 Then
        MsgBox "Error! " & Err.Description
    End If
    cn.Close
End Sub

As for ODBC query using PowerQuery. See link for post I made in another forum for some tips.
https://chandoo.org/forum/threads/us...8/#post-232154

[jaryszek]

wow,

thank you CK76 ! Awesome code.

I tested it and it is working.

Hmm but problem will be when i will have 2 tables withnin each worksheet.
You can not refer to tables, only to sheets to perform SQL statements...
So how to compare a lot of tables each other usinig SQLs?

Maybe i could refer to named ranges but i think this is not possible to refer to column headers from it.

So i have to i think go deeper into PQ solution.

thank you once again,

Best,
Jacek

[xlnitwit]

You can refer to the address of each table directly, and yes you can use headers with named ranges.

[jaryszek]

I checked this xlnitwit. This is not working. Did you try this at all? Or you are only think that it is possible...
Do you have maybe example code how to create this?

I created example listobjects where i want to perform SQL (in attachment).

When you are converting tables to address - how to get header?

"Select * from " & [Current$B6:E9] will work.

But performing join like:

"SELECT * FROM " & [Current$A10:D13] & "LEFT JOIN " & [Previous$A10:D13] & " ON " & [Current$A10:D13].OrderID = [Previous$A10:D13].OrderID & _
"WHERE " & [Previous$A10:D13].OrderID & " Is Null" will not work.

Best,
Jacek

[xlnitwit]

Well, no, of course that won't work since Current$A10:D13 is not the range for the table. Use Current$B6:E9 and it will work.

[jaryszek]

Wow !

This was my typo but i used syntac from CK76 macro and now is working good !

Thank you very much !
Love you!

Best,
Jacek

[jaryszek]

One more thing,

what if have 2 diffetent workbooks with data (tables are in different workbooks).
I have to open 2 odbc connections?
I mean where to provide which workbook i want to use?

Best,
Jacek

[xlnitwit]

You can join different workbooks- or other data sources- in the SQL

"SEL ECT * FROM [Sheet1$] tA INNER JOIN (SEL ECT Field1 FROM `C:\some path\somefile.xlsx`.[Sheet1$]) As tB ON tA.[Field1] = tB.[Field1]"

Note: I needed to put spaces in the middle of the word 'select' to get it past the firewall!

[jaryszek]

thank you very much.

But still i have to open one workbook odbc connection yes?

Best,
Jacek

[xlnitwit]

Yes, you have to open a connection to one of them.

[jaryszek]

thank you my friend,

Best Wishes,
Jacek