title: How to optimize a connection in vba
body: <p>I am working with vba, and I am using an excel sheet to find prices using SQL code within vba. Below is an example of how it is done:</p>
  <pre><code>Sub connection()
  
  Set cn = New ADODB.connection
  
   Set cn = CreateObject(&quot;ADODB.Connection&quot;)
      With cn
          .Provider = &quot;Microsoft.ACE.OLEDB.12.0&quot;
          .ConnectionString = &quot;Data Source=&quot; &amp; ThisWorkbook.Path &amp; &quot;\&quot; &amp; ThisWorkbook.Name &amp; &quot;;&quot; &amp; _
          &quot;Extended Properties=&quot;&quot;Excel 12.0 Xml;HDR=YES&quot;&quot;;&quot;
          .Open
      End With
      
  
  
  
  End Sub
  Function getAddres(ByVal sTableName As String) As String
  
      With Range(sTableName &amp; &quot;[#All]&quot;)
          getAddres = &quot;[&quot; &amp; .Parent.Name &amp; &quot;$&quot; &amp; .Address(False, False) &amp; &quot;]&quot;
      End With
  
  End Function
  
  Function Get_Coeff_1_crit(couv As String, tabelle As String, edit As String, crit1 As String) As Variant
  
  Dim NomFeuille As String, texte_SQL As String
  Dim rst As ADODB.Recordset
  Dim strRangeAddress As String
  
  Call connection
  
      strRangeAddress = getAddres(edit)
    
      texte_SQL = &quot;SELECT  [Coeff] FROM &quot; &amp; strRangeAddress &amp; &quot; WHERE ([Tabelle]=&quot; &amp; Chr(34) &amp; tabelle &amp; Chr(34) &amp; _
      &quot; )  AND (( [Booléen_1] = &quot; &amp; crit1 &amp; &quot; ) OR   ( [Fixe_1] = &quot; &amp; crit1 &amp; &quot; ) OR  (&quot; &amp; crit1 &amp; &quot;  BETWEEN [Min_1] AND [Max_1] ))&quot;
          
      Set rst = New ADODB.Recordset
      Set rst = cn.Execute(texte_SQL)
      On Error Resume Next
          Get_Coeff_1_crit = 0
      
      Get_Coeff_1_crit = rst.Fields(0).Value    'ws.Range(&quot;N7&quot;).CopyFromRecordset Rst
  
  End Function
  </code></pre>
  <p>So I have an input of variables and this function looks for the table and correct variable combination.</p>
  <p>My issue is that with many functions each establishing a separate connection with the excel sheet, it takes an insane amount of time to process a lot of prices.</p>
  <p>I was wondering if anyone knows of a better way, I am rather new at vba, so I was wondering if we can store dataframes within the process like in python or is there a different way to not establish connections each time.</p>
  <p>Thanks.</p>