vba - 将数据从 SQL Server 加载到 Excel 的最快方法

Question

我在 SQL Server 上有一个相对较大的表(大约 300,000 行)。该数据用作数据池来验证用户在 Excel 电子表格中执行的操作。

确保用户始终使用最新数据的一种方法是设置 VBA，以便在电子表格打开时自动从 SQL Server 提取数据。

代码:

Private Sub Workbook_Open()
        Set objMyConn = New ADODB.Connection
        Set objMyRecordset = New ADODB.Recordset
        Dim strSQL As String

    'Open Connection'
        objMyConn.ConnectionString = "Provider=SQLOLEDB; Data Source=Server Name;Initial Catalog=Database;User ID=User;Password=Password; Trusted_Connection=no"
        objMyConn.Open

    'Set and Excecute SQL Command'
        strSQL = "SELECT * FROM [Database]"

    'Open Recordset'
        Set objMyRecordset.ActiveConnection = objMyConn
        objMyRecordset.Open strSQL

    'Copy Data to Excel'
        Sheets("TEPSD").Range("A1").CopyFromRecordset (objMyRecordset)

        objMyConn.Close

End Sub

问题是这需要很长时间。有时，它需要太长时间，我最终不得不按 ctrl+break 它。

我想知道是否有更快的方法？或者还有另一种方法来验证用户数据，而无需将整个表格拉到 Excel 中？

任何意见都将非常感激。

Answer 1

经过我们的讨论。

目前，每个用户都试图在短时间内从服务器提取大量数据，然后数据就会变得陈旧，因为除非用户关闭并重新打开工作簿，否则数据不会更新。

相反，切换到 Worksheet_Change() 宏。每次进行更改时都会刷新数据。

这将允许您在每次用户进行更改时发送自定义查询 - 例如按 Enter 键。

更改您的 strSQL从静态查询中提取所有数据到 strSQL = "SELECT name FROM [Database] WHERE name = " & Target.Value

注意您应该真正查找 VBA 记录集希望您如何进行参数串联以避免 Little Bobbie Tables .

如果查询成功 - 即有效条目 - 那么它应该返回相同的名称。如果没有相应的条目 - 即不是有效的选择 - 那么您应该得到一个空的 recordset 。这意味着您可以使用 If rsObj.RecordCount < 1 Then 测试查询的结果。以便在没有有效匹配时优雅退出(或提示用户等)。