ADODB.Connection avec Excel pour l'amélioration de la performance de la database Access

Nous avons créé une database d'access et un développeur a créé une fonction sur excel pour accéder aux données de cette database. La connection se fait via ADDB.Connection. La formule renvoie 1 chiffre qui est la sum des volumes dans la database Access en tenant count de certains parameters. Lorsque nous exécutons la formule dans une cellule, cela fonctionne parfaitement, mais comme nos rapports contiennent plus d'une cellule avec cette formule (environ 160 cellules). Les rapports sont très lents à exécuter.

L'un de nos experts en access vérifie la database et il ne voit aucune erreur dans la façon dont il a été construit.

Quelqu'un aurait-il une idée de la façon de sortinger cela? Qu'en est-il des librairies, nous devrions cocher dans Excel pour que cela fonctionne correctement? Nous travaillons aujourd'hui sur MS Excel & Access 2003 (je sais que nous sums sans âge) mais j'aurais l'opportunité de passer à Excel & Access 2007 (quelle progression ;-)), cela aiderait-il?

Ceci est le détail de notre code vba dans Excel

'The function is called GetMarketData Function GetMarketData(Optional ProductLine As Ssortingng, Optional country As Ssortingng, Optional CountryGroup As Ssortingng, Optional Market As Ssortingng, Optional startMonth As Long, Optional endMonth As Long, Optional Brand As Ssortingng, Optional Model As Ssortingng, Optional Segment As Ssortingng, Optional EngineSegment As Ssortingng) '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 Ssortingng, query2 As Ssortingng, query3 As Ssortingng, query4 As Ssortingng Dim parametres As Ssortingng, param1 As Ssortingng, param2 As Ssortingng, param3 As Ssortingng, param4 As Ssortingng, param5 As Ssortingng, param6 As Ssortingng, param7 As Ssortingng, param8 As Ssortingng, param9 As Ssortingng, param10 As Ssortingng ' 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) " _ & "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)='" & ProductLine & "')" param2 = " AND ((T_COUNTRY.Country)='" & country & "')" param3 = " AND ((T_COUNTRY_GROUP.Country_Group)='" & CountryGroup & "')" param4 = " AND ((T_SELECTED_MARKET.Selected_Market)='" & Market & "')" param5 = " AND ((T_TIME_PERIOD.Cal_FullDate)>=" & startMonth & ")" param6 = " AND ((T_TIME_PERIOD.Cal_FullDate)<=" & endMonth & ")" param7 = " AND ((T_BRAND.Brand)='" & Brand & "')" param8 = " AND ((T_MODEL.Model)='" & Model & "')" param9 = " AND ((T_SEGMENT.Segment)='" & Segment & "')" param10 = " AND ((T_ENGINE_SEGMENT.Engine_Segment)='" & EngineSegment & "')" parametres = param1 If country <> "" Then parametres = parametres & param2 End If If CountryGroup <> "" Then parametres = parametres & param3 End If If Market <> "" Then parametres = parametres & param4 End If If startMonth <> 0 Then parametres = parametres & param5 End If If endMonth <> 0 Then parametres = parametres & param6 End If If Brand <> "" Then parametres = parametres & param7 End If If Model <> "" Then parametres = parametres & param8 End If If Segment <> "" Then parametres = parametres & param9 End If If EngineSegment <> "" Then parametres = parametres & param10 End If query4 = ");" 'login with the Access database Set con = New ADODB.Connection con.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & URL_Base & ";" 'Open a recordset based on the query Set rsQuery = New ADODB.Recordset Set rsQuery.ActiveConnection = con rsQuery.Open query1 & query2 & query3 & parametres & 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

Fonction de fin

Pour la plupart, DAO est beaucoup plus rapide que ADODB utilisé avec MS Access. Ceci est testé dans Excel. Notez que Like * n'inclut pas les Nulls, donc ce n'est qu'un exemple. Vous pouvez également créer des requêtes temporaires dans VBA et leur atsortingbuer des parameters, mais ils risquent d'être plus lents qu'une requête stockée.

 Function DAOMarket(Optional ProductLine As Ssortingng, Optional country As Ssortingng, _ Optional CountryGroup As Ssortingng, Optional Market As Ssortingng, Optional startMonth As Long, _ Optional endMonth As Long, Optional Brand As Ssortingng, _ Optional Model As Ssortingng, Optional Segment As Ssortingng, Optional EngineSegment As Ssortingng) 'Reference: Microsoft Office xx Access Database Engine Object Library Dim ws As DAO.Workspace Dim db As DAO.Database Dim sDb As Ssortingng Dim sSQL As Ssortingng Dim qdf As QueryDef Dim rs As DAO.Recordset sDb = "Z:\Docs\Test.accdb" Set ws = DBEngine.Workspaces(0) Set db = ws.OpenDatabase(sDb) ''A stored query, see below Set qdf = db.QueryDefs("GetMarket") qdf.Parameters![@ProductLine] = IIf(ProductLine & "" = "", "*", ProductLine) qdf.Parameters![@Country] = IIf(country & "" = "", "*", country) qdf.Parameters![@Country_Group] = IIf(CountryGroup & "" = "", "*", CountryGroup) qdf.Parameters![@Selected_market] = IIf(Market & "" = "", "*", Market) qdf.Parameters![@CalStart] = IIf(startMonth & "" = "", 1, startMonth) qdf.Parameters![@CalEnd] = IIf(endMonth & "" = "", 12, endMonth) qdf.Parameters![@Brand] = IIf(Brand & "" = "", "*", Brand) qdf.Parameters![@Model] = IIf(Model & "" = "", "*", Model) qdf.Parameters![@Segment] = IIf(Segment & "" = "", "*", Segment) qdf.Parameters![@Engine_Segment] = IIf(EngineSegment & "" = "", "*", EngineSegment) Set rs = qdf.OpenRecordset(dbOpenSnapshot) DAOMarket = rs.Fields(0) End Function

SQL

 SELECT SUM([t_market].[volume]) AS MarketVolume FROM ((t_selected_market 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_engine_segment INNER JOIN t_product ON t_engine_segment.id_engine_segment = t_product.fk_engine_segment) INNER JOIN t_segment_final ON t_engine_segment.id_engine_segment = t_segment_final.fk_engine_segment) ON t_model.id_model = t_product.fk_model) ON t_product_line.id_product_line = t_segment_final.fk_product_line) ON t_selected_market.id_selected_market = t_segment_final.fk_selected_market) INNER JOIN t_segment ON t_segment_final.fk_segment = t_segment.id_segment) INNER JOIN (t_country_group INNER JOIN (t_time_period INNER JOIN (t_country INNER JOIN t_market ON t_country.id_country = t_market.fk_country) ON t_time_period.id_period = t_market.fk_time_period) ON t_country_group.id_country_groupe = t_country.fk_country_group) ON t_product.id_product = t_market.fk_product WHERE ( ( ( t_product_line.product_line ) LIKE [@productline] ) AND ( ( t_country.country ) LIKE [@country] ) AND ( ( t_country_group.country_group ) LIKE [@country_group] ) AND ( ( t_selected_market.selected_market ) LIKE [@selected_market] ) AND ( ( t_time_period.cal_fulldate ) BETWEEN [@calstart] AND [@calend] ) AND ( ( t_brand.brand ) LIKE [@brand] ) AND ( ( t_model.model ) LIKE [@model] ) AND ( ( t_segment.segment ) LIKE [@segment] ) AND ( ( t_engine_segment.engine_segment ) LIKE [@engine_segment] ) ); = SELECT SUM([t_market].[volume]) AS MarketVolume FROM ((t_selected_market 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_engine_segment INNER JOIN t_product ON t_engine_segment.id_engine_segment = t_product.fk_engine_segment) INNER JOIN t_segment_final ON t_engine_segment.id_engine_segment = t_segment_final.fk_engine_segment) ON t_model.id_model = t_product.fk_model) ON t_product_line.id_product_line = t_segment_final.fk_product_line) ON t_selected_market.id_selected_market = t_segment_final.fk_selected_market) INNER JOIN t_segment ON t_segment_final.fk_segment = t_segment.id_segment) INNER JOIN (t_country_group INNER JOIN (t_time_period INNER JOIN (t_country INNER JOIN t_market ON t_country.id_country = t_market.fk_country) ON t_time_period.id_period = t_market.fk_time_period) ON t_country_group.id_country_groupe = t_country.fk_country_group) ON t_product.id_product = t_market.fk_product WHERE ( ( ( t_product_line.product_line ) LIKE [@productline] ) AND ( ( t_country.country ) LIKE [@country] ) AND ( ( t_country_group.country_group ) LIKE [@country_group] ) AND ( ( t_selected_market.selected_market ) LIKE [@selected_market] ) AND ( ( t_time_period.cal_fulldate ) BETWEEN [@calstart] AND [@calend] ) AND ( ( t_brand.brand ) LIKE [@brand] ) AND ( ( t_model.model ) LIKE [@model] ) AND ( ( t_segment.segment ) LIKE [@segment] ) AND ( ( t_engine_segment.engine_segment ) LIKE [@engine_segment] ) ); = SELECT SUM([t_market].[volume]) AS MarketVolume FROM ((t_selected_market 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_engine_segment INNER JOIN t_product ON t_engine_segment.id_engine_segment = t_product.fk_engine_segment) INNER JOIN t_segment_final ON t_engine_segment.id_engine_segment = t_segment_final.fk_engine_segment) ON t_model.id_model = t_product.fk_model) ON t_product_line.id_product_line = t_segment_final.fk_product_line) ON t_selected_market.id_selected_market = t_segment_final.fk_selected_market) INNER JOIN t_segment ON t_segment_final.fk_segment = t_segment.id_segment) INNER JOIN (t_country_group INNER JOIN (t_time_period INNER JOIN (t_country INNER JOIN t_market ON t_country.id_country = t_market.fk_country) ON t_time_period.id_period = t_market.fk_time_period) ON t_country_group.id_country_groupe = t_country.fk_country_group) ON t_product.id_product = t_market.fk_product WHERE ( ( ( t_product_line.product_line ) LIKE [@productline] ) AND ( ( t_country.country ) LIKE [@country] ) AND ( ( t_country_group.country_group ) LIKE [@country_group] ) AND ( ( t_selected_market.selected_market ) LIKE [@selected_market] ) AND ( ( t_time_period.cal_fulldate ) BETWEEN [@calstart] AND [@calend] ) AND ( ( t_brand.brand ) LIKE [@brand] ) AND ( ( t_model.model ) LIKE [@model] ) AND ( ( t_segment.segment ) LIKE [@segment] ) AND ( ( t_engine_segment.engine_segment ) LIKE [@engine_segment] ) );