vba - 最快的 ADO 将粘贴表从 sql-server 复制到 Excel

Question

我正在将数据表从 sql-server 移动到 Excel 中。
我不需要通过记录集来获取数据并将其粘贴到工作表中。

我是否为记录集的 Open 使用了正确的参数？方法？

Dim recSet As ADODB.Recordset
Set recSet = New ADODB.Recordset
aConnection.Open
recSet.Open stringSQL, aConnection, adOpenForwardOnly, adLockReadOnly, adCmdText 
wb.Sheets(sName).Cells(1, 1).CopyFromRecordset recSet
recSet.Close
If Not (recSet Is Nothing) Then
    If (recSet.State And 1) = 1 Then recSet.Close
    Set recSet.ActiveConnection = Nothing
    Set recSet = Nothing
End If

Answer 1

这是我用来从 MSSQLServer 中提取数据的方法，也许它对你有用:

Sub test()
    Dim Connection As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim QT As Excel.QueryTable
    Dim ConnectionString As String
    Dim SQL As String
    Set Connection = New ADODB.Connection
    Set rs = New ADODB.Recordset

    ConnectionString = ""
    SQL = "SELECT * FROM SomeTable"
    Connection.Open ConnectionString
    rs.Open SQL, Connection, adOpenStatic, adLockReadOnly

    Set QT = ActiveSheet.QueryTables.Add(rs, ActiveSheet.Cells(1, 1))
    QT.Refresh:    rs.Close:    QT.Delete:    Connection.Close
End Sub