如何在ADODB SQL查询中连接Excel命名区域

Question

我需要将Excel表格中的数据移动到数据库。为此，我创建了ADODB连接，并能够执行以下SQL查询： INSERT INTO myTable SELECT * FROM [Excel 12.0 Macro;HDR=Yes;Database=C:\MyPath\MyFile.xlsb].[Shee1$A1:C100] 我的问题是范围不能指向超过255列，即列IU。我想尝试使用命名范围，但我找不到合适的符号表示法。我找到的所有示例都直接连接到工作簿，使用SELECT * FROM [Sheet1$]引用或SELECT * FROM myRange作为命名范围的示例。我尝试了一些类似于...

[Excel 12.0 Macro;HDR=Yes;Database=C:\MyPath\MyFile.xlsb].[myRange]
[Excel 12.0 Macro;HDR=Yes;Database=C:\MyPath\MyFile.xlsb].[myRange$]
[Excel 12.0 Macro;HDR=Yes;Database=C:\MyPath\MyFile.xlsb].myRange
[Excel 12.0 Macro;HDR=Yes;Database=C:\MyPath\MyFile.xlsb;Name=myRange]

我尝试使用命名区域，但是没有成功。在这里使用命名区域的正确方法是什么？使用命名区域是否有助于解决列数限制问题？

我原本期望 [Excel 12.0 Macro;HDR=Yes;Database=C:\MyPath\MyFile.xlsb].[myRange] 可以奏效，但是它却抛出了以下错误：“Microsoft Access 数据库引擎找不到对象 'myRange'。确保对象存在 (...)”

我可以通过将数据从源工作表复制到临时工作表中，并将其在 255 列限制内处理，但是用正确的方法解决问题会更好。

- brainac

---

该命名范围是动态的还是静态的？动态的无法与 ADO 一起使用。 - Rory

名称的范围是工作表还是工作簿？ - buran

实际上不太清楚 - 你是在Access还是Excel中运行代码？ - buran

很多年前，Excel 的列数被限制在 255 或 256 列。我猜测尝试连接到 Excel 可能会遇到这个限制：提供程序可能没有设计请求/查看超过该列数的列数，或者 Excel 没有设计超过该列数的列数。这也可能与 Access 表中 255 个字段的限制有关。 - Cindy Meister

使用命名范围应该解决列编号问题（假设命名范围本身不比255列更宽，且源工作簿已关闭）。我认为我们缺少相关代码来说明何时/如何创建这些名称。 - Rory

Answer 1

不确定您是否能找到连接到命名范围的解决方案。我试图让它有效，但我也没有成功，我怀疑在255列之后的架构中没有包含它，但可能是错误的。

我认为您可能需要一个高效的解决方案，不依赖循环来添加数据到Access中。这比仅进行插入更需要代码，但我希望它适合您的具体问题。

我能够在约3秒钟内插入大约2500个记录（所有整数），因此速度相当快。

Option Explicit

Private Function GetDisconnectedRecordset(TableName As String) As ADODB.Recordset
    Dim conn As ADODB.connection: Set conn = getConn()
    Dim rs   As ADODB.Recordset: Set rs = New ADODB.Recordset

    With rs
        .CursorLocation = adUseClient ' <-- needed for offline processing
        'Get the schema of the table, don't return anything
        .Open "Select * from " & TableName & " where false", conn, adOpenDynamic, adLockBatchOptimistic
    End With

    rs.ActiveConnection = Nothing
    conn.Close
    Set conn = Nothing
    Set GetDisconnectedRecordset = rs
End Function

'Do an update batch of the data
'Portion used from: https://dev59.com/a1wY5IYBdhLWcg3wQ16k
Sub PopulateDataFromNamedRange()
    Dim conn        As ADODB.connection
    Dim ws          As Excel.Worksheet: Set ws = ThisWorkbook.Worksheets("Sheet2") 'Update to your sheet/wb
    Dim NamedRange  As Excel.Range: Set NamedRange = ws.Range("Test") ' Update to your named range
    Dim NamedItem   As Excel.Range
    Dim rs          As ADODB.Recordset: Set rs = GetDisconnectedRecordset("[TestTable]") 'Specify your table name in access
    Dim FieldName   As String
    Dim Row         As Long
    Dim AddRow      As Long

    'Add Data to the disconnected recordset
    For Each NamedItem In NamedRange
        If Not NamedItem.Row = 1 Then
            Row = NamedItem.Row
            If Not Row = AddRow Then rs.AddNew
            AddRow = NamedItem.Row
            FieldName = ws.Cells(NamedItem.Row - (NamedItem.Row - 1), NamedItem.Column).Value
            rs.Fields(FieldName).Value = NamedItem.Value
        End If
    Next

    'Connect again
    Set conn = getConn()
    Set rs.ActiveConnection = conn

    rs.UpdateBatch '<-- 'Update all records at once to Access
    conn.Close
End Sub

Private Function getConn() As ADODB.connection
    Dim conn As ADODB.connection: Set conn = New ADODB.connection
    conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Ryan\Desktop\Example.accdb"
    Set getConn = conn
End Function

- Ryan Wildry

---

有趣的解决方案。正如我之前所写的，我已经为这个问题找到了一个解决方法，但我发现它足够有趣，所以在这里寻求帮助。 - brainac

Answer 2

我遇到了同样的问题，解决方案非常简单，但只适用于工作簿级别的命名区域。 连接必须针对工作簿进行（即[Excel 12.0 Macro;HDR=Yes;Database=C:\MyPath\MyFile.xlsb]）。 然后在查询中只需键入：SELECT * FROM [myRange]（请注意方括号和$符号的缺失）。

- SteBar

---

我很久以前就完成了这个项目，但我喜欢你的建议。我没有测试过它，但乍一看应该可以工作。我想通过方括号引用数据库。 - brainac

Answer 3

你需要使用一个结构：

[Excel 12.0 Macro;HDR=Yes;Database=C:\MyPath\MyFile.xlsb].[$myRange]

- Dmitry Bryantsev

Answer 4

我找到了这个链接 sql on range 我已经测试了大部分的例子，没有遇到任何问题。我将添加一个新的例子，也可以在名为range的表格上工作。

strQuery = “SELECT * FROM [Sheet1$mynamedrange]“

- Louis Lietaer