Requète SQL et QueryTable

Version imprimable

Bonjour, voilà j'ai repris le code d'une application Excel qui fonctionne assez bien mis à part que plus je lance la méthode utilisé pour exécuter les requête plus Excel devient instable. Au point où il n'arrête pas de me balancer des messages comme quoi il n'a pas assez de ressources disponibles ou qu'il n'a pas assez de mémoire pour afficher un entier...

Je ferme Excel et il continu de me polluer l'existence : il reste dans la liste des tâches (gestionnaire des tâches), me prend de 150 à 200 Mo de Ram, et toute la puissance de mon processeur (99%)...

De là je me demandais s'il n'y avait pas quelque chose dans la méthode qui lance les requêtes qui reste en mémoire à chaque fois que je la lance !

Voilà le code :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 Sub ConnectListe() Dim Chemin As String Dim Fichier As String Dim NomPilote As String Dim ChaineConnection As String Dim tabRequete() As Variant Dim i As Integer tabRequete = Array("req1", _ "req2", _ "req3") Chemin = ThisWorkbook.Path Fichier = ThisWorkbook.Name NomPilote = fRequete.Range("znRequetePilote") ChaineConnection = "ODBC;DSN=" & NomPilote & ";DBQ=" & Chemin & "\" & Fichier & ";DefaultDir=" & Chemin & ";DriverId=790;MaxBufferSize=2048;PageTimeout=5;" For i = LBound(tabRequete) To UBound(tabRequete) fRequete.Range(tabRequete(i)).QueryTable.Connection = ChaineConnection fRequete.Range(tabRequete(i)).QueryTable.CommandText = fRequete.Range(tabRequete(i)).Cells(1, 1).Offset(-2, 0) fRequete.Range(tabRequete(i)).QueryTable.Refresh Next i End Sub
Merci pour l'aide que vous pourrez m'apporter.

Edit : si vous connaissez une solution plus souple, plus légère je suis preneur !

10/07/2010, 21h10
AlainTech

Pour ma part, je n'utilise jamais les QueryTables. Je les trouve trop gourmandes.
Je préfère créer un RecordSet et alimenter la feuille avec le résultat.
12/07/2010, 09h39
Golork

Salut

Citation:

Envoyé par AlainTech

Pour ma part, je n'utilise jamais les QueryTables. Je les trouve trop gourmandes.

C'est bien ce qui me laisse penser !

Citation:

Je préfère créer un RecordSet et alimenter la feuille avec le résultat.

Aurais tu un bon tuto sur l'utilisation des recordsets ? C'est surtout que je ne voudrais pas recoder tous mes classeurs qui utilisent des QueryTables !

Edit : j'ai trouvé ça (http://access.developpez.com/faq/?page=SQL) qui je pense correspond à ce que je recherche mais voilà comment je fait pour me connecter à un fichier Excel qui est ouvert en cours d'utilisation ?
Procédure :
- J'ouvre mon fichier Excel
- Je clique sur un bouton pour rapatrier les exports
- Les requêtes de sélection se font et remplissent une feuille spéciale pour ça
- Je consulte les rapports.
Je ne peut pas me permettre de créer un autre classeur !

Merci pour tes réponses !
12/07/2010, 09h59
mayekeul

bonjour,

http://vb.developpez.com/bidou/recordset-ado/

Cela devrait t'aider à comprendre les recordsets, sujet vaste s'il en est!!!!!
12/07/2010, 10h02
Golork

Salut, vaut il mieux que j'utilise la méthode ADO ou DAO ?
Sachant que je souhaite que mes classeurs soient fonctionnels sans être obligé d'ajouter les références manuellement par mes clients ?

Bon tout compte fait les recordset c'est pas si leger que ça ! Ou alors y a un truc que j'ai mal fait car j'ai les même problèmes de lenteur !

voici mon code :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
Sub DAOOpenRecordset()
Dim rs As DAO.Recordset
Dim fld As DAO.Field
Dim wrk As DAO.Workspace
Dim cnx As DAO.Connection
 
Dim req, chemin, fichier, provider, NomPilote As String
 
    chemin = ThisWorkbook.Path
    fichier = ThisWorkbook.Name
    NomPilote = fRequete.Range("znRequetePilote")
 
    provider = "ODBC;DSN=" & NomPilote & ";DBQ=" & chemin & "\" & fichier
 
    Set wrk = CreateWorkspace("", "", "", dbUseODBC)
    Set cnx = wrk.OpenConnection("", , , provider)
    req = fRequete.Range("A127").Value
 
    Set rs = cnx.OpenRecordset(req, dbOpenDynamic)
 
    Dim col, i As Integer
 
    If Not rs.EOF Then
        rs.MoveFirst
        col = 1
        For Each fld In rs.Fields
            Feuil2.Cells(1, col) = fld.Name
            col = col + 1
        Next
 
        For i = 2 To 10
            col = 1
            For Each fld In rs.Fields
                Feuil2.Cells(i, col) = fld.Value
                col = col + 1
            Next
            rs.MoveNext
        Next
    End If
    rs.Close
End Sub

Après 5 lancements Excel est monté à 125 Mo de Ram utilisé... Et là je n'ai affiché que 10 lignes ! Qu'est-ce que ça serait si j'affichais tout !

a chaud :

Citation:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
Sub DAOOpenRecordset()
Dim rs As DAO.Recordset
Dim fld As DAO.Field
Dim wrk As DAO.Workspace
Dim cnx As DAO.Connection
 
Dim req, chemin, fichier, provider, NomPilote As String
 
    chemin = ThisWorkbook.Path
    fichier = ThisWorkbook.Name
    NomPilote = fRequete.Range("znRequetePilote")
 
    provider = "ODBC;DSN=" & NomPilote & ";DBQ=" & chemin & "\" & fichier
 
    Set wrk = CreateWorkspace("", "", "", dbUseODBC)
    Set cnx = wrk.OpenConnection("", , , provider)
    req = fRequete.Range("A127").Value
 
    Set rs = cnx.OpenRecordset(req, dbOpenDynamic)
 
    Dim col, i As Integer
 
    If Not rs.EOF Then
        rs.MoveFirst
        col = 1
        For Each fld In rs.Fields
            Feuil2.Cells(1, col) = fld.Name
            col = col + 1
        Next
 
        For i = 2 To 10
            col = 1
            For Each fld In rs.Fields
                Feuil2.Cells(i, col) = fld.Value
                col = col + 1
            Next
            rs.MoveNext
        Next
    End If
    rs.Close
End Sub

j'ai changé juste une partie

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
Sub DAOOpenRecordset()
Dim rs As DAO.Recordset
Dim fld As DAO.Field
Dim wrk As DAO.Workspace
Dim cnx As DAO.Connection
 
Dim req, chemin, fichier, provider, NomPilote As String
 
    chemin = ThisWorkbook.Path
    fichier = ThisWorkbook.Name
    NomPilote = fRequete.Range("znRequetePilote")
 
    provider = "ODBC;DSN=" & NomPilote & ";DBQ=" & chemin & "\" & fichier
 
    Set wrk = CreateWorkspace("", "", "", dbUseODBC)
    Set cnx = wrk.OpenConnection("", , , provider)
    req = fRequete.Range("A127").Value
 
    Set rs = cnx.OpenRecordset(req, dbOpenDynamic)
 
    Dim col, i As Integer
 
Feuil2.range("A2").copyfromrecordset rs   
 
For Each fld In rs.Fields
    Feuil2.Cells(1, col) = fld.Name
    col = col + 1
Next
rs.Close
 
End Sub

ça ira déjà un ti peu plus vite