title: ADODB.Connection with Excel to Access database performance improvement
body: <p>We have created a access database and one developer has created a function into excel to access the data from this database.
  The connection is done via ADDB.Connection. 
  The formula returns 1 figure which is the sum of volumes in the Access database taking into consideration some of the parameters. 
  When we run the formula in one cell it works perfectly, but as our reports contain more than one cell with this formula (about 160 cells). The reports are really slow to run.</p>
  
  <p>One of our expert in Access check the database and he does not see any mistake in the way it has been built.</p>
  
  <p>Would anybody have an idea how to sort this?
  What about librairies we should tick in Excel to have it working properly?
  We work today on MS Excel &amp; Access 2003 (I know we are out of age) but I would have the opportunity to upgrade to Excel &amp; Access 2007 (what an progress ;-) ), would it help?</p>
  
  <p>This is the detail of our vba code in Excel</p>
  
  <pre><code>'The function is called GetMarketData
  Function GetMarketData(Optional ProductLine As String, Optional country As String, Optional CountryGroup As String, Optional Market As String, Optional startMonth As Long, Optional endMonth As Long, Optional Brand As String, Optional Model As String, Optional Segment As String, Optional EngineSegment As String)
  
  'This is the declaration of variables
  Dim con As ADODB.Connection
  Dim rsQuery As ADODB.Recordset
  Dim i As Integer
  Dim iCount As Integer
  Dim resVar As Integer
  Dim query1 As String, query2 As String, query3 As String, query4 As String
  Dim parametres As String, param1 As String, param2 As String, param3 As String, param4     As String, param5 As String, param6 As String, param7 As String, param8 As String, param9 As String, param10 As String
  
  
  ' Query cut in 3 parts (will be regrouped later on)
  query1 = "SELECT Sum(T_MARKET.Volume) "
  query2 = "FROM T_COUNTRY_GROUP INNER JOIN (T_TIME_PERIOD INNER JOIN (T_SELECTED_MARKET INNER JOIN (T_SEGMENT INNER JOIN (T_PRODUCT_LINE INNER JOIN ((T_BRAND INNER JOIN T_MODEL ON T_BRAND.ID_Brand = T_MODEL.FK_Brand) INNER JOIN ((T_COUNTRY INNER JOIN T_MARKET ON T_COUNTRY.ID_Country = T_MARKET.FK_Country) INNER JOIN ((T_ENGINE_SEGMENT INNER JOIN T_PRODUCT ON T_ENGINE_SEGMENT.ID_Engine_Segment = T_PRODUCT.FK_Engine_Segment) " _
           &amp; "INNER JOIN T_SEGMENT_FINAL ON T_ENGINE_SEGMENT.ID_Engine_Segment = T_SEGMENT_FINAL.FK_Engine_Segment) ON (T_PRODUCT.ID_Product = T_MARKET.FK_Product) AND (T_COUNTRY.ID_Country = T_SEGMENT_FINAL.FK_Country)) ON T_MODEL.ID_Model = T_PRODUCT.FK_Model) ON T_PRODUCT_LINE.ID_Product_Line = T_SEGMENT_FINAL.FK_Product_Line) ON (T_SEGMENT.ID_Segment = T_SEGMENT_FINAL.FK_Segment) AND (T_SEGMENT.ID_Segment = T_PRODUCT.FK_Segment)) ON T_SELECTED_MARKET.ID_Selected_Market = T_SEGMENT_FINAL.FK_Selected_Market) ON T_TIME_PERIOD.ID_Period = T_MARKET.FK_Time_period) ON T_COUNTRY_GROUP.ID_Country_Groupe = T_COUNTRY.FK_Country_Group "
  query3 = "WHERE ("
  
      param1 = "((T_PRODUCT_LINE.Product_Line)='" &amp; ProductLine &amp; "')"
      param2 = " AND ((T_COUNTRY.Country)='" &amp; country &amp; "')"
      param3 = " AND ((T_COUNTRY_GROUP.Country_Group)='" &amp; CountryGroup &amp; "')"
      param4 = " AND ((T_SELECTED_MARKET.Selected_Market)='" &amp; Market &amp; "')"
      param5 = " AND ((T_TIME_PERIOD.Cal_FullDate)&gt;=" &amp; startMonth &amp; ")"
      param6 = " AND ((T_TIME_PERIOD.Cal_FullDate)&lt;=" &amp; endMonth &amp; ")"
      param7 = " AND ((T_BRAND.Brand)='" &amp; Brand &amp; "')"
      param8 = " AND ((T_MODEL.Model)='" &amp; Model &amp; "')"
      param9 = " AND ((T_SEGMENT.Segment)='" &amp; Segment &amp; "')"
      param10 = " AND ((T_ENGINE_SEGMENT.Engine_Segment)='" &amp; EngineSegment &amp; "')"
  
      parametres = param1
  
      If country &lt;&gt; "" Then
          parametres = parametres &amp; param2
      End If
      If CountryGroup &lt;&gt; "" Then
          parametres = parametres &amp; param3
      End If
      If Market &lt;&gt; "" Then
          parametres = parametres &amp; param4
      End If
      If startMonth &lt;&gt; 0 Then
          parametres = parametres &amp; param5
      End If
      If endMonth &lt;&gt; 0 Then
          parametres = parametres &amp; param6
      End If
      If Brand &lt;&gt; "" Then
          parametres = parametres &amp; param7
      End If
      If Model &lt;&gt; "" Then
          parametres = parametres &amp; param8
      End If
      If Segment &lt;&gt; "" Then
          parametres = parametres &amp; param9
      End If
      If EngineSegment &lt;&gt; "" Then
          parametres = parametres &amp; param10
      End If
  
  
  query4 = ");"
  
  'Connexion with the Access database
  Set con = New ADODB.Connection
  con.Open "Provider=Microsoft.Jet.OLEDB.4.0;" &amp; "Data Source=" &amp; URL_Base &amp; ";"
  
  'Open a recordset based on the query
  Set rsQuery = New ADODB.Recordset
  Set rsQuery.ActiveConnection = con
  rsQuery.Open query1 &amp; query2 &amp; query3 &amp; parametres &amp; query4
  
  ' Give back the value from the query
  GetMarketData = rsQuery.Fields(0).Value
  
  'Clear variables
  rsQuery.Close
  Set rsQuery = Nothing
  con.Close
  Set con = Nothing
  </code></pre>
  
  <p>End Function</p>