如何在Access数据库中使用左联接从Excel表中进行选择-EXCEL VBA [英] How to select from excel table with left join in access database - EXCEL VBA

问题描述

我很难在ADO中使用两个不同的数据库创建查询，我需要使用不同的来源进行很多查询，例如从具有访问权限的左连接的excel文件中进行选择.

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.

当我使用两个不同的excel文件时，如下面的代码可以正常工作.

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.

我的代码:

    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

推荐答案

从主连接字符串中删除Excel 12.0规范，因为同时应用于两个来源.而是先打开访问数据库，而不使用Excel 12.0规范

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"

现在指定Excel 12.0的扩展属性仅适用于工作簿

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"

希望这会有所帮助.

这篇关于如何在Access数据库中使用左联接从Excel表中进行选择-EXCEL VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！