[VBA] Optimisation d'un liaison avec Access

Bonjour,

A mon humble avis, c'est plutôt avec la requête ou les index dans
Access que tu pourras gagner du temps.

A mon humble avis, c'est plutôt avec la
requête ou les index dans Access que
tu pourras gagner du temps.

Le problème est que la requête est toute simple et ne peut à mon avis pas
être encore optimisée et la base de donnée Access provient d'un logiciel et
ne peut être modifié mais seulement lu.

Je me demandais juste si certaines options de la fonction "QueryTables"
pouvaient permettre d'accélèrer l'exécution de la requête... comme par
exemple le MaxBufferSize ou le eTimeout ? Peut on les modifier sans risquer
d'affecter la stabilité et la fiabilité des données récupérées ? Et à quoi
correspondent vraiment ces options ?

Je pense que MaxBufferSize est un buffer entre la BdD et Excel et qu'en
l'augmentant l'on devrait accroître la rapidité de la requête mais dans
quelle mesure peut on l'accroître ? Et eTimeout je pense que cela correspond
au délai maxi pour laquelle si il n'y a aucune réponse la requête est
annulée ? Mais réduire ce temps apporte-t-il une accélèration de la requête
?

--
@+
HD

Bonjour HD,

As-tu essayé avec ADO... Voici un exemple ....


Seulement ces 3 lignes de code de la procédure ont besoin
d'être défini selon les paramètres de ton application.

CheminDb = "C:Mes documentsComptoir.mdb"
NomTable = "Employés"
Requete = "Select * From " & NomTable & ""

ESSENTIEL: AJOUT DE LA BIBLIOTHÈQUE :
'Microsoft activex data objects 2.0 librairy

'------------------------------------------
Sub ImporterDesDonnéesDeAccess()

'Si ta table dans Access contient un champ "OleObject"
'Il faut l'exclure de la requête...sinon ça va planter !!!

'Attention, si access possède un champ mémo
'Limitation à 1823 caractères le nombre de
'caractère importé dans Excel

'Les champs "date" de la table access vont apparaître
'dans excel sous leur forme numérique avec la méthode
'CopyFromRecordSet version 2000 et 2002. Au besoin
'prévoir quelques lignes de code pour formatage

Dim X As Integer, C As Integer
Dim Cnt As New ADODB.Connection
Dim Rst As New ADODB.Recordset
Dim Rg As Range, Sh As Worksheet
Dim NbEnr As Long, CheminDb As String
Dim NomTable As String, NomFeuille As String
Dim Tblo As Variant, Requete As String

CheminDb = "C:Mes documentsComptoir.mdb"
NomTable = "Employés"
Requete = "Select * From " & NomTable & ""

'La chaîne de connexion à une base de donnée Access
Cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & CheminDb & ";"

'Ouverture du recordset
Rst.Open Requete, Cnt, adOpenStatic

'Si aucun enregistrement est trouvé lors de la requête
If Rst.RecordCount = 0 Then
MsgBox "Aucun enregistrement trouvé." & vbCrLf & _
"Fin de l'opération.", vbInformation + vbOKOnly, "Annulation"
' Ferme la connection et le recordset
Rst.Close: Cnt.Close
'Libère la mémoire vive occupée par les objets
Set Rst = Nothing: Set Cnt = Nothing
Set Rg = Nothing: Set sh = Nothing
Exit sub
End If

'Éviter le rafraîchissement de l'écran
Application.ScreenUpdating = False

'Conserve dans une variable le nom de la feuille active.
NomFeuille = ThisWorkbook.ActiveSheet.Name

'Ajoute une nouvelle feuille où seront acheminées les données
Set Sh = Worksheets.Add

'détermine la cellule supérieur gauche où
'le recordset va être copié
With Sh
Set Rg = .Range("A1")
End With

'Si tu es intéressé de récupérer directement les noms
'des champs de ton recordset, tu peux utiliser ce
'qui suit :

Do
Rg.Offset(, C) = Rst.Fields(C).Name
C = C + 1
X = X + 1
Loop Until X = Rst.Fields.Count

'Section copiant le recordset dans excel

'La procédur va pouvoir choisir selon la version
'd'excel d'installer laquelle des méthodes choisir.

'Un test sur la version excel en cours ...
If Val(Mid(Application.Version, 1, InStr(1, _
Application.Version, ".") - 1)) > 8 Then
'EXCEL 2000 or 2002: La méthode CopyFromRecordset
Rg.Offset(1).CopyFromRecordset Rst
Else
'EXCEL 97 ou antérieur: Méthode GetRows

' GetRows transforme le recordset en tableau de base 0
Tblo = Rst.GetRows
'Première dimension tableau = Champ du recordset
'Deuxièm dimension Tableau = Données du recordset

'Détermine le nombre d'enregistrements
'Le +1 c'est pour tenir compte du tableau qui est de
'base 0 ... première cellule dans excel est cells(1,1)
NbEnr = UBound(Tblo, 2) + 1

'Transpose le tableau(tblo) pour que la première dimension
'devienne les données et que sa présentation soit
'conforme lorsqu'il sera copié dans Excel
Rg.Offset(1).Resize(NbEnr, Rst.Fields.Count).Value = TransposeSpecial(Tblo)
End If

'Redimensionnement des cellules.
Rg.CurrentRegion.Columns.AutoFit
Rg.CurrentRegion.Rows.AutoFit

'Sélection de la feuille au départ de la procédure
Worksheets(NomFeuille).Select

' Ferme la connection et le recordset
Rst.Close: Cnt.Close

'Libère la mémoire vive occupée par les objets
Set Rst = Nothing: Set Cnt = Nothing
Set Rg = Nothing: Set sh = Nothing
End Sub
'------------------------------------------

'Fonction requise par la procédure pour les
'versions antérieures à excel 200

'------------------------------------------
Function TransposeSpecial(ByRef Arr As Variant) As Variant
Dim A As Integer, B As Integer, Arr1() As Variant
Dim C As Integer, D As Integer
A = UBound(Arr, 1): B = UBound(Arr, 2)
ReDim Arr1(B, A)
For C = 0 To A
For D = 0 To B
Arr1(D, C) = Arr(C, D)
Next
Next
TransposeSpecial = (Arr1)
End Function
'------------------------------------------


Salutations!




"HD" <hd@anti.spam.fr> a écrit dans le message de news: durvoh$2ioi$1@biggoron.nerim.net...
Bonjour,

Y'a t il moyen d'optimiser la fonction d'accès à une base de donnée Access
afin que celle ci soit plus rapide à s'exécuter ?

Voici ma fonction :

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MS Access 97 Database;DBQ=" & dBFic & ";DefaultDir=" & dBRep
& ";DriverId%;FIL=MS Access;MaxBufferSize 48;Pag" _
), Array("eTimeout=5;")), Destination:=Range("AA5"))
.Sql = ReqTps
.Refresh False
End With


Merci d'avance pour votre aide
--
@+
HD