您只需要打开一次连接。这意味着您可以在该一个活动连接上执行多个查询。您必须关闭连接并释放参考（特别是使用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