使用Excel VBA运行SQL查询 [英] Using Excel VBA to run SQL query

问题描述

我对SQL和VBA相当新鲜。我写了一个SQL查询，我希望能够从Excel工作簿中的VBA子程序调用和运行，然后将查询结果带入工作簿。我已经找到了一些在线索引（stackoverflow和其他地方）声称这样做，但我无法理解它们，因为它们没有解释。例如，这里是一个在线查找的子网：

  Sub ConnectSqlServer（）
 
 Dim conn作为ADODB.Connection 
 Dim rs As ADODB.Recordset 
 Dim sConnString As String 
 
'创建连接字符串。 
 sConnString =Provider = SQLOLEDB; Data Source = INSTANCE\SQLEXPRESS; &安培; _ 
初始目录= MyDatabaseName; &安培; _ 
集成安全= SSPI 
 
'创建Connection和Recordset对象。 
 Set conn = New ADODB.Connection 
设置rs =新建ADODB.Recordset 
 
'打开连接并执行。 
 conn.Open sConnString 
设置rs = conn.Execute（SELECT * FROM Table1;）
 
'检查我们有数据。 
如果不是rs.EOF然后
'传输结果。 
表格（1）.Range（A1）。CopyFromRecordset rs 
'关闭记录集
 rs.Close 
 Else 
 MsgBox错误：没有记录返回。 ，vbCritical 
 End If 
 
'清理
如果CBool​​（conn.State和adStateOpen）然后conn.Close 
设置conn = Nothing 
设置rs = Nothing 
 
 End Sub 
  

首先，这是否有效？第二，我需要在sub中替换（看起来像提供者，数据源，初始目录等），我在哪里可以找到替换它们的信息？

我希望这个问题不是太混乱，我感谢你的帮助！

解决方案

以下是我目前用来拉数据从MS SQL Server 2008进入VBA。您需要确保您具有正确的ADODB参考[ VBA编辑器 - >工具 - >参考]，并确保您已经选中了Microsoft ActiveX数据对象2.8库，这是从底部检查的第二行（我在Windows 7上使用Excel 2010;您的ActiveX版本可能略有不同，但仍将以Microsoft ActiveX开头）：

<强>使用远程主机连接到MS SQL的子模块用户名/密码

  Sub Download_Standard_BOM（）
'初始化变量
 Dim cnn As New ADODB.Connection 
 Dim rst As New ADODB.Recordset 
 Dim ConnectionString As String 
 Dim StrQuery As String 
 
'设置连接字符串用于访问MS SQL数据库
'确保更改：
'1：PASSWORD 
'2：USERNAME 
'3：REMOTE_IP_ADDRESS 
'4：DATABASE 
 ConnectionString =Provider = SQLOLEDB.1; Password = PASSWORD; Persist Security Info = True;用户ID = USERNAME;数据源= REMOTE_IP_ADDRESS;准备的使用过程= 1;自动翻译= True;数据包大小= 4096;使用加密Data = False;可能时使用列排序的标签= False;初始目录= DATABASE
 
'打开与数据库的连接
 cnn.Open ConnectionString 
'超时错误（以秒为单位）执行整个查询;这将在VBA超时之前运行15分钟，但是您的数据库可能在此值之前超时
 cnn.CommandTimeout = 900 
 
'这是您需要运行的实际MS SQL查询;您应该使用更强大的SQL编辑器（如HeidiSQL）来检查此查询，以确保您的查询有效
 StrQuery =SELECT TOP 10 * FROM tbl_table
 
'执行实际查询
 rst.Open StrQuery，cnn 
'将所有从StrQuery的结果转移到活动工作簿中的第一个工作表的单元格$ 
表（1）.Range（A2）。CopyFromRecordset rst 
 End Sub 
  

I am fairly new to SQL and VBA. I have written a SQL query that I would like to be able to call and run from a VBA sub in an excel workbook and then bring the query results into the workbook. I have found some subs online (stackoverflow and other places) that claim to do this but I am having trouble understanding them as they contain no explanation. For example, here is a sub that I found online:

Sub ConnectSqlServer()

    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sConnString As String

    ' Create the connection string.
    sConnString = "Provider=SQLOLEDB;Data Source=INSTANCE\SQLEXPRESS;" & _
                  "Initial Catalog=MyDatabaseName;" & _
                  "Integrated Security=SSPI;"

    ' Create the Connection and Recordset objects.
    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset

    ' Open the connection and execute.
    conn.Open sConnString
    Set rs = conn.Execute("SELECT * FROM Table1;")

    ' Check we have data.
    If Not rs.EOF Then
        ' Transfer result.
        Sheets(1).Range("A1").CopyFromRecordset rs
    ' Close the recordset
        rs.Close
    Else
        MsgBox "Error: No records returned.", vbCritical
    End If

    ' Clean up
    If CBool(conn.State And adStateOpen) Then conn.Close
    Set conn = Nothing
    Set rs = Nothing

End Sub

First of all, would this work? Second, what do I need to replace in the sub (it looks like provider, data source, initial catalog, etc) and where do I find the info to replace them with?

I hope this question is not too confusing and I appreciate your help!

解决方案

Below is code that I currently use to pull data from a MS SQL Server 2008 into VBA. You need to make sure you have the proper ADODB reference [VBA Editor->Tools->References] and make sure you have Microsoft ActiveX Data Objects 2.8 Library checked, which is the second from the bottom row that is checked (I'm using Excel 2010 on Windows 7; you might have a slightly different ActiveX version, but it will still begin with Microsoft ActiveX):

Sub Module for Connecting to MS SQL with Remote Host & Username/Password

Sub Download_Standard_BOM()
'Initializes variables
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim ConnectionString As String
Dim StrQuery As String

'Setup the connection string for accessing MS SQL database
   'Make sure to change:
       '1: PASSWORD
       '2: USERNAME
       '3: REMOTE_IP_ADDRESS
       '4: DATABASE
    ConnectionString = "Provider=SQLOLEDB.1;Password=PASSWORD;Persist Security Info=True;User ID=USERNAME;Data Source=REMOTE_IP_ADDRESS;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=DATABASE"

    'Opens connection to the database
    cnn.Open ConnectionString
    'Timeout error in seconds for executing the entire query; this will run for 15 minutes before VBA timesout, but your database might timeout before this value
    cnn.CommandTimeout = 900

    'This is your actual MS SQL query that you need to run; you should check this query first using a more robust SQL editor (such as HeidiSQL) to ensure your query is valid
    StrQuery = "SELECT TOP 10 * FROM tbl_table"

    'Performs the actual query
    rst.Open StrQuery, cnn
    'Dumps all the results from the StrQuery into cell A2 of the first sheet in the active workbook
    Sheets(1).Range("A2").CopyFromRecordset rst
End Sub

这篇关于使用Excel VBA运行SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！