How to select from excel table with left join in access database - EXCEL VBA

Question

i'm having difficult to create on query with two different database in ADO, i need to make a lot of queries with different sources, for example select from excel file with left join in access file.

When i use two different excel files like the code below works fine.

    Dim SQL As String
    Dim CN As New ADODB.Connection
    Dim rs As New ADODB.Recordset

    Set CN = New ADODB.Connection
    Set rs = New ADODB.Recordset

    'Open connection
    CN.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=C:\ExcelTable.xlsx" & _
    ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"

    SQL = " SELECT * FROM [table1$] t1" _
        & " LEFT JOIN (SELECT * FROM" _
        & " [Excel 12.0 Xml;HDR=Yes;Database=C:\db1.xlsx].table2) t2" _
        & " ON t1.[reftable1] = t2.reftable2"

    rs.Open SQL, CN, adOpenDynamic

    If rs.EOF = False Then


        Do While Not rs.EOF
            debug.print rs("field1")
            rs.MoveNext
        Loop


    End If

    rs.Close
    CN.Close

But i need to make this query with left join in the access file and i got the error: "cannot update database or object is read only" when i try to open the record set.

My code:

    Dim SQL As String
    Dim CN As New ADODB.Connection
    Dim rs As New ADODB.Recordset

    Set CN = New ADODB.Connection
    Set rs = New ADODB.Recordset

    'Open connection
    CN.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=C:\ExcelTable.xlsx" & _
    ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"

    SQL = " SELECT * FROM [table1$] t1" _
        & " LEFT JOIN (SELECT * FROM" _
        & " [Data Source=C:\db1.accdb].table2) t2" _
        & " ON t1.[reftable1] = t2.reftable2"

    rs.Open SQL, CN, adOpenDynamic

    If rs.EOF = False Then


        Do While Not rs.EOF
            debug.print rs("field1")
            rs.MoveNext
        Loop


    End If

    rs.Close
    CN.Close

Is Table2 a sheet name in first code? Second version code is in one DB and calling another DB and workbook? Tried the first code and does not work. Tried reversing so CN is set for Access and Excel is in the SQL statement. Didn't work either. I can't make either connection string work embedded in SQL statement. I can't find any example that shows a connection string within SQL statement except for pass through query. — June7
– June7, Commented Apr 7, 2019 at 4:47
Folowing June7 comment. is Linking to Excel acceptable solution? — Siyon DP
– Siyon DP, Commented Apr 7, 2019 at 10:15

user2648008 · Accepted Answer · 2019-04-08 05:47:20Z

1

Remove the Excel 12.0 spec from main connection string since that gets applied to both sources. Instead open the access database first without Excel 12.0 spec

CN.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data source=c:\db1.accdb"

now specify the extended property of Excel 12.0 only for the workbook

SQL = " SELECT t1.name, t2.unit FROM [Excel 12.0;HDR=Yes;Database=C:\ExcelTable.xlsx;].[Table1$] t1" _
    & " LEFT JOIN (SELECT * FROM Table1) t2" _
    & " ON t1.reftable1 = t2.reftable2"

Hope this helps.

answered Apr 8, 2019 at 5:47

user2648008

1524 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to select from excel table with left join in access database - EXCEL VBA

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related