title: VBA New Database Connection
body: <p>How to change the code below to prevent what you see in the screenshot.</p>
  
  <p>I am running a macro with the following code</p>
  
  <pre><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
  </code></pre>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p><strong><em>How do I establish a DB connection without constantly creating new connections?</em></strong> </p>
  
  <p>Has anyone had similar problems working with Excel DB macros?</p>
  
  <p><img src="https://i.stack.imgur.com/NOyp1.png" alt="Multiple connections"></p>
  
  <hr>
  
  <p><strong>UPDATE</strong></p>
  
  <p>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:</p>
  
  <pre><code>    With Sheets("FVols").QueryTables.Add(Connection:=rec1, Destination:=Sheets("FVols").Range("A1"))
      .name = "data"
      .FieldNames = True
      .Refresh BackgroundQuery:=True &lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;-----
  
  End With
  </code></pre>