excel - 如何在 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] 能正常工作，但它会抛出以下错误:'The Microsoft Access数据库引擎找不到对象“myRange”。确保对象存在 (...)'

我可以通过将数据从源工作表复制到临时工作表来解决这个问题，并将其限制在 255 列限制以内，但如果方法正确，那就太好了。

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://stackoverflow.com/questions/32821618/insert-full-ado-recordset-into-existing-access-table-without-loop
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