将数据从SQL Server加载到Excel的最快方法 [英] Fastest way to load data from SQL Server to Excel

问题描述

我在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键并打破它.

我想知道是否有更快的方法?还是另一种无需将整个表拉至Excel即可验证用户数据的方法?

任何输入将不胜感激.

解决方案

在我们讨论之后.

此刻，每个用户都试图在短时间内从服务器中提取大量数据，然后这些数据将变得过时，因为除非用户关闭并重新打开工作簿，否则该数据不会被更新.

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

这将使您每次用户进行更改时都发送自定义查询-例如按Enter键.

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

您应该真正查看VBA记录集如何希望您进行参数连接，以避免小鲍比表. /p>

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

I have a table on SQL Server that is relatively huge (approx 300,000 rows). This data is used as a pool of data to validate what users do in an Excel spreadsheet.

One way to ensure that users are always using the most up-to-date data is to set up a VBA to auto pull data from SQL server whenever the spreadsheet is open.

Code:

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

The issue with this is it takes a very long time. Sometimes, it takes too long and I end up having to ctrl+break it.

I was wondering whether there is a quicker way of doing this? Or another way to validate user data without having to pull the entire table to Excel?

Any input would be very much appreciated.

解决方案

Following our discussion.

At the moment, every user is trying to pull large amounts of data from the server in a short space of time, and then the data will get stale as it is not updated unless the user closes and reopens the workbook.

Instead, switch to a Worksheet_Change() macro. This will refresh the data every time a change is made.

This will allow you to send a customised query every time the user makes a change - e.g. presses enter.

Change your strSQL from a static query drawing out all the data to strSQL = "SELECT name FROM [Database] WHERE name = " & Target.Value

N.B. You should really look up how VBA recordset wants you to do parameter concatenation in order to avoid Little Bobbie Tables.

If the query is successful - i.e. a valid entry - then it should return the same name. If there is no corresponding entry - i.e. not a valid selection - then you should get an empty recordset. This means you can test the result of the query with If rsObj.RecordCount < 1 Then in order to gracefully exit (or prompt the user etc.) when there is no valid match.

这篇关于将数据从SQL Server加载到Excel的最快方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！