VBA新数据库连接 [英] VBA New Database Connection

问题描述

如何更改下面的代码，以防止在屏幕截图中看到什么。

我使用以下代码运行一个宏

  Dim conn As ADODB.Connection 
 Dim rec1 As ADODB.Recordset 
 Dim thisSql As String 
 
设置conn = New ADODB.Connection 
 
 Dim sConn As String 
 sConn =Provider = SQLOLEDB; Trusted_Connection = Yes; Server = xyz; Database = xyz; UID = xyz; PWD = xyz
 conn.Open sConn 
 
'这是创建多个连接。 
设置rec1 =新建ADODB.Recordset 
 rec1.打开thisSql，conn 
  

它运行SQL Server查询（大约20行长，包含4个连接）。一切都很好，除了事实上，经过几次运行，我的DB管理员说我的查询正在加载数据库太多。

现在，我的查询可以导致问题，或者可能是Excel一次开始运行多个连接。一些证据是下面的截图，数据库上的负载似乎随时间增加。

如何建立数据库没有不断创建新连接的连接？

有没有人在Excel DB宏中使用类似的问题？

---

更新

虽然下面的答案非常有用（特别是对于在VBA中开始的人）看来我的查询正在加载的主要原因是多个连接的组合，并忽略了我的代码中的一行：

  With Sheets（FVols）。QueryTables.Add（Connection：= rec1，Destination：= Sheets（FVols）。Range（A1））
 .name =data
。 FieldNames = True 
 .Refresh BackgroundQuery：= True <<<<<<<<<< lt;<<<<<< { -  
 
结束
  

解决方案

您只需要打开一次连接。这意味着您可以在该一个活动连接上执行多个查询。您必须关闭连接并释放参考（特别是使用ADODB ），以免发生冲突和其他连接相关问题。

如果您知道您要执行的查询可以创建一个数组（或集合）并将查询添加到队列中。

虽然您已经有一个打开的连接可以继续执行查询。

通过代码扫描，你和我之间没有什么区别，所以你应该能够看到发生了什么，在哪里。如果有什么不清楚，请在评论中提出问题

  Sub DbConnection（）
 
 Dim cn As ADODB.Connection 
 Set cn = New ADODB.Connection 
 Dim rs As ADODB.Recordset 
 
 Dim strConn As String 
 strConn =Driver = {SQL Server}; Server =; Database =; UID =; PWD =
 
 cn.Open strConn 
 
 Dim queryArr，i 
 queryArr = Array（SELECT * FROM [MyTable ]，SELECT * FROM [MyOtherTable]）
 
对于i = LBound（queryArr）到UBound（queryArr）
 ExecuteQuery queryArr（i），cn，rs 
下一步i 
 
 cn.Close 
 Set cn = Nothing 
 End Sub 
 
 Private Sub ExecuteQuery（查询As Variant，ByRef cn As ADODB.Connection，ByRef rs as ADODB.Recordset）
设置rs =新建ADODB.Recordset 
使用rs 
 .ActiveConnection = cn 
 .Open CStr（query）
表（1）。范围（A1）。CopyFromRecordset rs 
 .Close 
结束
设置rs = Nothing 
 End Sub 
  

现在，您只需执行 DBConnection（）一次，您在数组中列出的所有查询将被执行。

或者，如果您的查询创建于运行时，您可以将其传递给 DbConnection（）作为参数。

  Sub DbConnection（queryQueue As Collection）
 
 Dim cn As ADODB.Connection 
 Set cn = New ADODB.Connection 
 Dim rs As ADODB.Recordset 
 
 
 Dim strConn As String 
 strConn =Driver = {SQL Server}; Server = HELIUM\\PRI;数据库= sourcedata; UID =塔布拉; PWD = Tabula123！
 
 cn.Open strConn 
 
对于i = 1要查询Queue.Count 
 ExecuteQuery queryQueue.Item（i），cn，rs 
 Next i 
 
 cn.Close 
 Set cn = Nothing 
 End Sub 
 
 Private Sub ExecuteQuery（查询As Variant，ByRef cn as ADODB。连接，ByRef rs As ADODB.Recordset）
设置rs =新的ADODB.Recordset 
与rs 
 .ActiveConnection = cn 
 .Open CStr（查询）
表1）.Range（A1）。CopyFromRecordset rs 
 .Close 
结束
设置rs = Nothing 
 End Sub 
  / pre> 
 
 
更新：
 
 
 
您可以将连接声明为全局变量现在，您可以根据需要多次运行 DBConnection（），每次都不会创建新连接，而是使用全局连接对象。 
  Option Explicit 
 
 Public cn As ADODB.Connection 
 
 S ub DbConnection（）
 
设置cn =新ADODB.Connection 
 Dim rs As ADODB.Recordset 
 
 Dim strConn As String 
 strConn =Driver = {SQL Server}; Server =;数据库=; UID =; PWD =
 
 cn.Open strConn 
 
设置rs =新建ADODB.Recordset 
使用rs 
 .ActiveConnection = cn 
。打开SELECT * FROM [MyTable]
 Sheets（1）.Range（A1）。CopyFromRecordset rs 
 .Close 
 End with 
 Set rs = Nothing 
 
 cn.Close 
 Set cn = Nothing 
 End Sub 
  
 
How to change the code below to prevent what you see in the screenshot.

I am running a macro with the following code
Dim conn As ADODB.Connection
Dim rec1 As ADODB.Recordset
Dim thisSql As String

Set conn = New ADODB.Connection

Dim sConn As String
  sConn = "Provider=SQLOLEDB;Trusted_Connection=Yes;Server=xyz;Database=xyz;UID=xyz;PWD=xyz"
conn.Open sConn

' this is creating multiple connections.
Set rec1 = New ADODB.Recordset
rec1.Open thisSql, conn
which runs a SQL Server query (which is around 20 lines long and contains 4 joins). Everything is fine except for the fact that after a couple times of running it my DB admin says that my query is loading up the DB too much.

Now, my query could be causing the problem, or it could be that Excel is starting to run multiple connections at once. Some evidence for this is the screenshot below and the fact that the load on the database appears to increase with time.

How do I establish a DB connection without constantly creating new connections? 

Has anyone had similar problems working with Excel DB macros?





UPDATE

While the answers below were very useful (especially for someone starting out in VBA), it seems that the main reason my query was taking up load was a combination of multiple connections and having overlooked a line in my code:
    With Sheets("FVols").QueryTables.Add(Connection:=rec1, Destination:=Sheets("FVols").Range("A1"))
    .name = "data"
    .FieldNames = True
    .Refresh BackgroundQuery:=True <<<<<<<<<<<<<<<<<<<<<<<-----

End With

解决方案
You only need to open the connection once. That literally means you can execute multiple queries on that one active connection. You must close the connection and free the reference (specially with ADODB) to avoid running into collisions and other connection related problems.

If you know the queries you are going to be executing you can create an array (or collection) and add queries to the queue. 

While you already have an open connection to work with you can keep executing queries. 

Scan through code there is not much difference between yours and mine so you should be able to see what is going on and where. Please, ask questions in the comments if anything is unclear
   Sub DbConnection()

    Dim cn As ADODB.Connection
    Set cn = New ADODB.Connection
    Dim rs As ADODB.Recordset

    Dim strConn As String
    strConn = "Driver={SQL Server};Server=; Database=; UID=; PWD="

    cn.Open strConn

    Dim queryArr, i
    queryArr = Array("SELECT * FROM [MyTable]", "SELECT * FROM [MyOtherTable]")

    For i = LBound(queryArr) To UBound(queryArr)
        ExecuteQuery queryArr(i), cn, rs
    Next i

    cn.Close
    Set cn = Nothing
End Sub

Private Sub ExecuteQuery(query As Variant, ByRef cn As ADODB.Connection, ByRef rs As ADODB.Recordset)
    Set rs = New ADODB.Recordset
    With rs
        .ActiveConnection = cn
        .Open CStr(query)
        Sheets(1).Range("A1").CopyFromRecordset rs
        .Close
    End With
    Set rs = Nothing
End Sub
Now, you only need to execute the DBConnection() once and all the queries you listed in the array will be executed.

Alternatively, if your queries are created at run-time you can pass it to the DbConnection() as a parameter.
Sub DbConnection(queryQueue As Collection)

    Dim cn As ADODB.Connection
    Set cn = New ADODB.Connection
    Dim rs As ADODB.Recordset


    Dim strConn As String
    strConn = "Driver={SQL Server};Server=HELIUM\PRI; Database=sourcedata; UID=tabula; PWD=Tabula123!"

    cn.Open strConn

    For i = 1 To queryQueue.Count
        ExecuteQuery queryQueue.Item(i), cn, rs
    Next i

    cn.Close
    Set cn = Nothing
End Sub

Private Sub ExecuteQuery(query As Variant, ByRef cn As ADODB.Connection, ByRef rs As ADODB.Recordset)
    Set rs = New ADODB.Recordset
    With rs
        .ActiveConnection = cn
        .Open CStr(query)
        Sheets(1).Range("A1").CopyFromRecordset rs
        .Close
    End With
    Set rs = Nothing
End Sub


Update:

You can declare your connection as a Global Variable. Now you can run the DBConnection() as many times as you like and you will not be creating a new connection each time. Instead you will be using the global connection object.
Option Explicit

Public cn As ADODB.Connection

Sub DbConnection()

    Set cn = New ADODB.Connection
    Dim rs As ADODB.Recordset

    Dim strConn As String
    strConn = "Driver={SQL Server};Server=; Database=; UID=; PWD="

    cn.Open strConn

    Set rs = New ADODB.Recordset
    With rs
        .ActiveConnection = cn
        .Open "SELECT * FROM [MyTable]"
        Sheets(1).Range("A1").CopyFromRecordset rs
        .Close
    End With
    Set rs = Nothing

    cn.Close
    Set cn = Nothing
End Sub


                        
这篇关于VBA新数据库连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！