automatisation de requêtes ODBC

Version imprimable

Bonjour,

Je cherche à automatiser une requête ODBC depuis Excel, vers une base de données.
L'idée est la suivante:
- 1ère requête : j'extraie les données correspondant à un certain n°
- je traite ces données dans Excel par des calculs très simples, que je recopie dans une autre feuille
- 2ème requête: données correspondant au n° suivant
- traitement de ces données
...

Mon problème concerne la requête. Plusieurs cas de figures se présentent, selon les programmes que j'ai essayés:
- soit la 1ère requête fonctionne, mais à partir de la 2eme le programme bug à l'expression : .Refresh BackgroundQuery:=False (erreur '1004')
- soit je passe cette expression à True et alors le programme tourne, mais en fait les données ne sont jamais inscrites dans la feuille Excel: il se contente de "Lancer la requête à partir de Base de données WCLIP" et enchaine les étapes suivantes sans qu'il y ait de données dans la feuille...

Voici le code (la Sub principale s'appelle Prog et est en dernier, et la requête est la sub requeteAffCreeFeuille... le reste est annexe)

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
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
 
Option Explicit
Option Base 1
 
 
'je commence par créer deux tableaux contenant
'les noms des CF et les heures correspondantes
Public tabCF(40) As String, tabH(40) As Double
 
'naff est le numéro de l'affaire pour laquelle on va faire la requete
'ligAff est le n° de la ligne correspondante dans la feuille "données"
'ligCoutsMO est le n° de la ligne correspondant au n° d'affaire dans la feuille "couts MO"
Public naff As Integer, ligAff As Integer, ligCoutsMO As Integer
 
Sub remplirCF()
 
    Dim a As Integer
    For a = 1 To UBound(tabCF)
        tabCF(a) = ""
    Next
 
    tabCF(1) = "n° aff"
 
    'je copie les CF de la feuille "couts" dans le tabCF
    Worksheets("couts").Select
    Dim i As Integer
    For i = 4 To 23
        tabCF(i - 2) = Cells(5, i)
    Next
    tabCF(22) = Cells(5, 3)
    tabCF(23) = Cells(5, 24)
    tabCF(24) = Cells(5, 25)
 
    'je recopie ces valeurs dans la feuille "coutsMO"
    Worksheets("couts MO").Select
    Dim j As Integer
    For j = 1 To 24
        Cells(1, j) = tabCF(j)
    Next j
 
End Sub
 
Sub completerCF()
    Worksheets("couts MO").Select
    Dim p As Integer
    For p = 25 To UBound(tabCF)
        Cells(1, p) = tabCF(p)
    Next p
End Sub
 
Sub requeteAffCreeFeuille()
 
    Sheets.Add
    Range("A1").Select
    Application.CutCopyMode = False
    With ActiveSheet.QueryTables.Add(Connection:= _
        "ODBC;DSN=Base de données WCLIP;;ANA=c:\wclipper\WCLIP.wd5\WCLIP.wdd;;REP=T:\FICHIERS\GALVA-AFA\;" _
        , Destination:=Range("A1"))
        .CommandText = Array( _
        "SELECT POINT.COFRAIS, POINT.TPSPASSE, POINT.NAF, POINT.DAT" & Chr(13) & "" & Chr(10) & "FROM c:\wclipper\WCLIP.wd5\WCLIP.wdd~POINT POINT" & Chr(13) & "" & Chr(10) & "WHERE (POINT.NAF=" & naff & ")" & Chr(13) & "" & Chr(10) & "ORDER BY POINT.NAF, POINT.COFRAIS" _
        )
        .name = "Lancer la requête à partir de Base de données WCLIP_1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=True
    End With
 
End Sub
 
'effectue la somme des heures par CF de la feuille "requete",
'et les enregistre dans le tableau tabH
Sub sommeH()
    Dim m As Integer, ligne As Integer, som As Double, cf As String
 
    'on reinitialise le tableau des heures
    For m = 1 To UBound(tabH)
        tabH(m) = 0
    Next m
 
    tabH(1) = naff
 
    cf = Cells(2, 1).Value
    som = 0
    ligne = 2
 
    If cf = "" Then
        MsgBox "Attention: pas de données dans la feuille requete pour l'affaire" & naff
    End If
 
    'on suppose que les CF sont en colonne 1 et les temps passes en colonne 2
    While cf <> ""
        'on somme les h tant qu'on ne change pas de CF
        While cf = Cells(ligne, 1)
            som = som + Cells(ligne, 2)
            ligne = ligne + 1
        Wend
 
        'on enregistre la valeur de som dans tabH, dans la bonne colonne
        tabH(chercheDansTabCF(cf)) = som
 
        'on réinitialise som et on passe au CF suivant
        som = 0
        cf = Cells(ligne, 1)
    Wend
    'ActiveWindow.SelectedSheets.Delete
 
End Sub
 
Function chercheDansTabCF(CentreFrais As String) As Integer
    Dim n As Integer
    n = 1
 
    While n < UBound(tabCF)
        If tabCF(n) = CentreFrais Then
            chercheDansTabCF = n
            Exit Function
        End If
        n = n + 1
    Wend
 
    'si cf n'est pas dans tabCF on l'ajoute
    n = 25
    While n < UBound(tabCF)
        If tabCF(n) = "" Then
            tabCF(n) = CentreFrais
            chercheDansTabCF = n
            Exit Function
        End If
        n = n + 1
    Wend
    MsgBox "Pb dans la fonction chercheDansTabCF"
 
End Function
 
Sub remplircoutsMO()
    Worksheets("couts MO").Select
    Dim o As Integer
    For o = 1 To UBound(tabH)
        Cells(ligCoutsMO, o) = tabH(o)
    Next o
End Sub
 
 
'la sub renvoie le n° d'affaire suivant
'et la dernière ligne qui lui correspond dans la feuille "données"
 
Sub prochaineAff()
    Worksheets("données").Select
 
    'si le tableau ne contient pas de données, un message s'affiche
    If naff = 0 Then
        MsgBox "Il n'y pas d'affaires dans la feuille données"
        Exit Sub
    End If
 
    'on cherche le n° de la dernière ligne contenant le n° d'affaire
    While Cells(ligAff + 1, 1) = naff
        ligAff = ligAff + 1
    Wend
 
    'je sélectionne la 1ère ligne du groupe qui contient le n° d'affaire suivant
    ligAff = ligAff + 1
    naff = Cells(ligAff, 1)
 
End Sub
 
Sub Prog()
 
    'je commence par remplir la feuille "couts MO" avec les bons noms de CF
    remplirCF
 
    'j'initialise mes variables naff et ligAff
    naff = Worksheets("données").Cells(2, 1)
    ligAff = 2
    ligCoutsMO = 2
 
    While naff <> 0
        'j'effectue la requete avec le n° d'affaire
        requeteAffCreeFeuille
        'requeteMultiple
        'reqVinz
        'j'effecute la somme des heures par CF et remplie tabH
        sommeH
        'je recopie tabH dans la feuille "couts MO"
        remplircoutsMO
        'je sélectionne le prochain n° d'affaire
        prochaineAff
        ligCoutsMO = ligCoutsMO + 1
    Wend
 
    'j'ajoute les CF "bizarres"
    completerCF
 
End Sub

Pour le moment j'ai essayé les solutions suivantes:
- créer une nouvelle feuille à chaque fois (cf ci dessus requeteAffCreeFeuille)
- lancer la requête à partir de la même feuille à chaque fois (cf ci-dessous en activant reqMultiple au lieu de requeteAffCreeFeuille)
- créer un nouveau classeur à chaque fois et l'enregistrer sous un nom différent à chaque n° (je vous fais grâce du 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
 
Sub requeteMultiple()
'
' requeteMultiple Macro
' Macro enregistrée le 12/03/2008 par SCAFFHOLDING
'
 
    Worksheets("requete").Select
    Cells.Select
    Selection.ClearContents
    'Selection.QueryTable.Delete
    Range("A1").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "ODBC;DSN=Base de données WCLIP;;ANA=c:\wclipper\WCLIP.wd5\WCLIP.wdd;;REP=T:\FICHIERS\GALVA-AFA\;" _
        , Destination:=Range("A1"))
        .CommandText = Array( _
        "SELECT POINT.COFRAIS, POINT.TPSPASSE" & Chr(13) & "" & Chr(10) & "FROM c:\wclipper\WCLIP.wd5\WCLIP.wdd~POINT POINT" & Chr(13) & "" & Chr(10) & "WHERE (POINT.NAF=" & naff & ")" & Chr(13) & "" & Chr(10) & "ORDER BY POINT.COFRAIS" _
        )
        .name = "Lancer la requête à partir de Base de données WCLIP_17"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

A chaque fois le pb revient!

Ce n'est pas un pb de temps, puisque même si je fais "patienter" Excel pendant que la requete est lancée, en lui faisant faire des opérations dans le vide, celle-ci ne s'achève jamais ... (dans le cas .Refresh BackgroundQuery:=True)

Je commence à un peu désespérer...
Si quelqu'un a une idée, je suis vraiment preneur!!
merci d'avance

Vincent

Bonsoir,

Je pense que tu dois utiliser une requete parametrée et modifier la valeur de ce parametre dans une boucle sans recréer ta requete à chaque fois:

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
Sub EssaiRequeteParametree()
    Dim QT As QueryTable, i As Long, NAFS
 
    Sheets.Add
    NAFS = Array(1, 2, 3)
 
    Set QT = ActiveSheet.QueryTables.Add(Connection:= _
        "ODBC;DSN=Base de données WCLIP;;ANA=c:\wclipper\WCLIP.wd5\WCLIP.wdd;;REP=T:\FICHIERS\GALVA-AFA\;" _
        , Destination:=Range("A1"))
 
    With QT
        .Parameters.Add "NAF"
        'Le parametre NAF est inclus dans la requete (le ? de la clause Where)
        .CommandText = _
            "SELECT POINT.COFRAIS, POINT.TPSPASSE, POINT.NAF, POINT.DAT" & Chr(13) & "" & Chr(10) & _
            "FROM c:\wclipper\WCLIP.wd5\WCLIP.wdd~POINT POINT" & Chr(13) & "" & Chr(10) & _
            "WHERE (POINT.NAF=?)" & Chr(13) & "" & Chr(10) & _
            "ORDER BY POINT.NAF, POINT.COFRAIS"
        For i = 0 To 2
            'Affecte une valeur au parametre avant le rafraichissement
            .Parameters("NAF").SetParam xlConstant, NAFS(i)
            .Refresh False
            'Traitement sur le resultat de la requete
            Debug.Print .ResultRange.Rows.Count
        Next
    End With
End Sub

A adapter bien sur.

Cordialement,

Tirex28/

14/03/2008, 10h28
vinzopus

Bonjour Tirex28,

Tout d'abord merci beaucoup pour ton aide!
Hélas la requête que tu proposes ne fonctionne pas lorsque je l'exécute:
erreur générale ODBC '1004'
et indique que l'endroit où intervient le bug est: .Refresh False

La Sub crée donc la nouvelle feuille mais n'arrive pas à faire ne serait-ce que la première requête...

Je vais tt de meme essayer de reprendre le principe de ne pas créer une requête à chaque fois et de l'adapter à mon cas. Si tu as une idée de pourquoi ta requête ne fonctionne pas chez moi, ce serait top!!

Merci encore

Vincent

Bonjour a tous,

Je fais pas mal d'outile extraction sur Clipper et je n'ai jamais eu de soucis pour effectuer des requetes multiples.

En tous cas je ne pense pas que cela ne vienne de ta requête regarde plutot ds le reste de ton code

pour obtimiser la requête:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 Sub requeteMultiple() ' ' requeteMultiple Macro ' Macro enregistrée le 12/03/2008 par SCAFFHOLDING ' Worksheets("requete").Select Cells.ClearContents With ActiveSheet.QueryTables.Add(Connection:= _ "ODBC;DSN=Base de données WCLIP;;ANA=c:\wclipper\WCLIP.wd5\WCLIP.wdd;;REP=T:\FICHIERS\GALVA-AFA\;" _ , Destination:=Range("A1")) .CommandText = Array( _ "SELECT POINT.COFRAIS, POINT.TPSPASSE FROM POINT WHERE POINT.NAF=" & naff & " ORDER BY POINT.COFRAIS") .Refresh BackgroundQuery:=False End With End Sub
Bon courage
Jean Pierre

Alors effectivement c'est assez bizarre...
Lorsque j'exécute la sub suivante, ca marche, les requetes s'enchainent.
En revanche lorsque je lance tout mon code alors ca se bloque à la deuxième requête... pourtant naff a bien la valeur voulue, donc je ne vois pas trop ce qui change!

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
Sub requeteMultiple()
'
' requeteMultiple Macro
' Macro enregistrée le 12/03/2008 par SCAFFHOLDING
'
    naff = 9000
    While naff < 9010
 
        Worksheets("requete").Select
        Cells.Select
        Selection.ClearContents
        'Selection.QueryTable.Delete
        Range("A1").Select
        With ActiveSheet.QueryTables.Add(Connection:= _
            "ODBC;DSN=Base de données WCLIP;;ANA=c:\wclipper\WCLIP.wd5\WCLIP.wdd;;REP=T:\FICHIERS\GALVA-AFA\;" _
            , Destination:=Range("A1"))
            .CommandText = Array( _
            "SELECT POINT.COFRAIS, POINT.TPSPASSE" & Chr(13) & "" & Chr(10) & "FROM c:\wclipper\WCLIP.wd5\WCLIP.wdd~POINT POINT" & Chr(13) & "" & Chr(10) & "WHERE (POINT.NAF=" & naff & ")" & Chr(13) & "" & Chr(10) & "ORDER BY POINT.COFRAIS" _
            )
            .Name = "Lancer la requête à partir de Base de données WCLIP_17"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .PreserveColumnInfo = True
            MsgBox "ok" & naff
            .Refresh BackgroundQuery:=False
        End With
    naff = naff + 1
    Wend
End Sub

Une idée?
Merci!

14/03/2008, 11h42
Jean-Pierre49
A tout hasard

Recupere POINT.NAF ds ta requête
Code:

1 2 3 "SELECT POINT.NAF, POINT.COFRAIS, POINT.TPSPASSE FROM End
Jean Pierre
14/03/2008, 11h47
Invité

Bonjour

Au lieu de faire tourner dans le vide, essayes d'intercaler l'instruction

Code:

DoEvents

entre chaque déclenchement de requête (c'est juste une idée).

Starec
14/03/2008, 12h11
vinzopus

Merci mais hélas ni l'ajout de POINT.NAF ni l'insertion d'un DoEvents n'améliore les choses...

De tout maniere je persiste à dire que ce n'est pas ta requête qui pose problême.

revois ton code

essaye un autre type de boucle

Code:

1
2
3
4
5
 
For naff = 9000 to 9009
 
.........................
Next naff

Essayes aussi

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
Option Explicit
Sub requeteMultiple()
 ' Avec reference Microsoft DAO 3.6 Object Library
                            Dim NomSourceODBC        As String
                            Dim MoteurODBC           As String
                            Dim FichierODBC          As String
 
                            Dim TexteRequete         As String
                            Dim Donnees              As DAO.Database
                            Dim RequeteA             As Recordset
 
    NomSourceODBC = "Base de données WCLIP"
    FichierODBC = ";REP=T:\FICHIERS\GALVA-AFA\;"
    MoteurODBC = "ANA=c:\wclipper\WCLIP.wd5\WCLIP.wdd;;"
    Set Donnees = DAO.OpenDatabase(NomSourceODBC, False, False, MoteurODBC & FichierODBC)
 
 
    TexteRequete = "Select POINT.COFRAIS, Sum(POINT.TPSPASSE)" & _
                        "From POINT " & _
                            "Where POINT.NAF=" & naff & ")" & _
                            " Group by POINT.COFRAIS " & _
                                "Order by POINT.COFRAIS"
    Set RequeteA = Donnees.OpenRecordset(TexteRequete, DAO.dbOpenSnapshot)
 
    Set Cellule = Range("A1")
    For CompA = 0 To RequeteA.Fields.Count - 1
        Cellule.Offset(0, CompA).Value = RequeteA.Fields(CompA).Name
    Next CompA
    Range("A2").CopyFromRecordset RequeteA
 
End Sub

pas teste avec WCLIP.wd5 mais qui fonctionne avec WCLIP.wd7 dont les ODBC sont avec hyper file 7
Mais tu n'utilisera plus QUERY

Jean Pierre

14/03/2008, 14h17
vinzopus

Alors effectivement si je fais une boucle For et non while dans ma Sub Prog() ca marche!!
Du coup je vais changer un peu la structure du programme et ca devrait marcher...
Merci beaucoup donc!!

Au passage, je ne comprends pas pquoi avec while ca ne marchait pas...
14/03/2008, 15h02
Jean-Pierre49

Il y a de grande chance qu'avec while tu intervient directement ds l'execution de ta requete
on utilise Do While Not pour lire les enregistrements d'une requête.

Jean-Pierre
14/03/2008, 15h06
vinzopus

Alors ca y est je crois que j'ai découvert le fin mot de l'histoire... et il est d'une bêtise affligeante!!! et très ennervante!!

En fait c'est simplement que la requête ne fonctionne pas pour certains numéros d'affaire!!

Reste à savoir pourquoi...
Mais pour l'heure je pars en vacances pour 1 semaine, donc je verrai à mon retour!

Merci beaoucp pour votre aide en tout cas.

Bien cordialement,

Vincent
25/03/2008, 12h23
vinzopus

Bonjour,

Je me replonge dans mes soucis après une semaine de vacances...

Voici désormais ma situation:
- si je mets partout False pour BackGroundQuery
>ca marche pour les "bons" numéros d'affaires (ie ceux qui renvoient un enregistrement)
>ca renvoie une erreur '1004' sur BackGroundQuery pour les "mauvais" numéros d'affaires (ie ceux pour lesquelles la requête ne renvoie rien)

- si je mets partout True pour BackGroundQuery
>tous les types de requête marchent bien, mais uniquement à la toute fin de ma procédure. Tout le programme s'exécute ainsi avant que le bon résultat de la requête ne s'affiche (or je veux exploiter ce résultat...)

Quelqu'un saurait-il comment résoudre l'un ou l'autre des problèmes?

Merci encore!

Vincent