Syntaxe VBA / SQL

**ridokou** · 12/01/2011, 11h08

Bonjour,

J'ai créé une requête SQL qui fonctionne parfaitement et je souhaite maintenant la mettre sous exel afin de pouvoir la lancer via un bouton.

J'ai sur le même classeur d'autres requêtes SQL attachées de cette manière et je ne rencontre pas de problèmes particulier.

Et la, j'ai un message d'erreur me disant que la syntaxe vers ':' est incorrect.

Pouvez vous me dire s'il y a des particularités avec les ':' ou si mon code comporte des ereurs ?
Merci d'avance,

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
sSQL = sSQL + " SELECT   DD.PARC_CONTRACTUEL,CC.CODE_TYPECNT,"
sSQL = sSQL + "          SUM(CASE"
sSQL = sSQL + "                 WHEN (MONTH(DD.DTE_FACT)-1) = 1 THEN (CAST(DD.KMS_PARCOURUS as NUMERIC))"
sSQL = sSQL + "                 ELSE 0"
sSQL = sSQL + "              END) AS KMS_PARCOURUS_01,"
sSQL = sSQL + "          SUM(CASE"
sSQL = sSQL + "                 WHEN (MONTH(DD.DTE_FACT)-1) = 2 THEN (CAST(DD.KMS_PARCOURUS as NUMERIC))"
sSQL = sSQL + "                 ELSE 0"
sSQL = sSQL + "              END) AS KMS_PARCOURUS_02,"
sSQL = sSQL + "          SUM(CASE"
sSQL = sSQL + "                 WHEN (MONTH(DD.DTE_FACT)-1) = 3 THEN (CAST(DD.KMS_PARCOURUS as NUMERIC))"
sSQL = sSQL + "                 ELSE 0"
sSQL = sSQL + "              END) AS KMS_PARCOURUS_03,"
sSQL = sSQL + "          SUM(CASE"
sSQL = sSQL + "                 WHEN (MONTH(DD.DTE_FACT)-1) = 4 THEN (CAST(DD.KMS_PARCOURUS as NUMERIC))"
sSQL = sSQL + "                 ELSE 0"
sSQL = sSQL + "              END) AS KMS_PARCOURUS_04,"
sSQL = sSQL + "         SUM(CASE"
sSQL = sSQL + "                 WHEN (MONTH(DD.DTE_FACT)-1) = 5 THEN (CAST(DD.KMS_PARCOURUS as NUMERIC))"
sSQL = sSQL + "                 ELSE 0"
sSQL = sSQL + "              END) AS KMS_PARCOURUS_05,"
sSQL = sSQL + "          SUM(CASE"
sSQL = sSQL + "                 WHEN (MONTH(DD.DTE_FACT)-1) = 6 THEN (CAST(DD.KMS_PARCOURUS as NUMERIC))"
sSQL = sSQL + "                 ELSE 0"
sSQL = sSQL + "              END) AS KMS_PARCOURUS_06,"
sSQL = sSQL + "          SUM(CASE"
sSQL = sSQL + "                 WHEN (MONTH(DD.DTE_FACT)-1) = 7 THEN (CAST(DD.KMS_PARCOURUS as NUMERIC))"
sSQL = sSQL + "                 ELSE 0"
sSQL = sSQL + "              END) AS KMS_PARCOURUS_07,"
sSQL = sSQL + "          SUM(CASE"
sSQL = sSQL + "                 WHEN (MONTH(DD.DTE_FACT)-1) = 8 THEN (CAST(DD.KMS_PARCOURUS as NUMERIC))"
sSQL = sSQL + "                 ELSE 0"
sSQL = sSQL + "              END) AS KMS_PARCOURUS_08,"
sSQL = sSQL + "          SUM(CASE"
sSQL = sSQL + "                 WHEN (MONTH(DD.DTE_FACT)-1) = 9 THEN (CAST(DD.KMS_PARCOURUS as NUMERIC))"
sSQL = sSQL + "                 ELSE 0"
sSQL = sSQL + "              END) AS KMS_PARCOURUS_09,"
sSQL = sSQL + "          SUM(CASE"
sSQL = sSQL + "                 WHEN (MONTH(DD.DTE_FACT)-1) = 10 THEN (CAST(DD.KMS_PARCOURUS as NUMERIC))"
sSQL = sSQL + "                 ELSE 0"
sSQL = sSQL + "              END) AS KMS_PARCOURUS_10,"
sSQL = sSQL + "          SUM(CASE"
sSQL = sSQL + "                 WHEN (MONTH(DD.DTE_FACT)-1) = 11 THEN (CAST(DD.KMS_PARCOURUS as NUMERIC))"
sSQL = sSQL + "                 ELSE 0"
sSQL = sSQL + "              END) AS KMS_PARCOURUS_11,"
sSQL = sSQL + "          SUM(CASE"
sSQL = sSQL + "                 WHEN (MONTH(DD.DTE_FACT)-1) = 12 THEN (CAST(DD.KMS_PARCOURUS as NUMERIC))"
sSQL = sSQL + "                 ELSE 0"
sSQL = sSQL + "              END) AS KMS_PARCOURUS_12,"
sSQL = sSQL + "          SUM(CAST(DD.KMS_FACT as NUMERIC)) AS KMS_FACT,"
sSQL = sSQL + "          MAX(CAST(DD.PX_UNITAIRE as money)) AS PX_UNITAIRE,"
sSQL = sSQL + "          SUM(CAST(DD.FACT_MONTANTNET as money)) AS FACT_MONTANTNET,"
sSQL = sSQL + "          SUM(CAST(PP.KMINCLUS as NUMERIC)) AS KMINCLUS"
sSQL = sSQL + " FROM     (SELECT AA.ID_FACT,"
sSQL = sSQL + "                  AA.DTE_FACT,"
sSQL = sSQL + "                  AA.DTEDEB,"
sSQL = sSQL + "                  AA.DTEFIN,"
sSQL = sSQL + "                  AA.ID_CLIENTFICHE,"
sSQL = sSQL + "                  AA.FACT_NUMPARC,"
sSQL = sSQL + "                  (CASE"
sSQL = sSQL + "                     WHEN (BB.PARC_ID IS NULL"
sSQL = sSQL + "                           AND AA.FACT_NUMPARC IS NULL) THEN 'PAS DE CONTRAT'"
sSQL = sSQL + "                     WHEN (BB.PARC_ID IS NULL"
sSQL = sSQL + "                           AND AA.FACT_NUMPARC IS NOT NULL) THEN AA.FACT_NUMPARC"
sSQL = sSQL + "                     WHEN (BB.PARC_ID IS NOT NULL) THEN BB.PARC_ID"
sSQL = sSQL + "                     Else: BB.PARC_ID"
sSQL = sSQL + "                   END) AS 'PARC_CONTRACTUEL',"
sSQL = sSQL + "                  BB.VERSION_CONTRAT,"
sSQL = sSQL + "                  AA.AAAAMM,"
sSQL = sSQL + "                  AA.TYPE_LIBELLEPROP,"
sSQL = sSQL + "                  AA.KMS_PARCOURUS,"
sSQL = sSQL + "                  AA.KMS_FACT,"
sSQL = sSQL + "                  AA.PX_UNITAIRE,"
sSQL = sSQL + "                  AA.FACT_MONTANTNET,"
sSQL = sSQL + "                  AA.TYPE_PRST,"
sSQL = sSQL + "                  AA.CODE_AGENCE,"
sSQL = sSQL + "                  AA.NO_CONTRAT,"
sSQL = sSQL + "                  AA.NUM_FACT"
sSQL = sSQL + "           FROM   (SELECT B.ID_FACT,"
sSQL = sSQL + "                          B.NUM_LIGNE,"
sSQL = sSQL + "                          H.DTE_FACT,"
sSQL = sSQL + "                          B.DTEDEB,"
sSQL = sSQL + "                          B.DTEFIN,"
sSQL = sSQL + "                          H.ID_CLIENTFICHE,"
sSQL = sSQL + "                          (CASE"
sSQL = sSQL + "                             WHEN B.FACT_NUMPARC IS NULL THEN (SELECT   TOP 1 LL.FACT_PARCID"
sSQL = sSQL + "                                                               FROM     S1SRVBDD02.IGLOO.DBO.FACT_LIGNE LL"
sSQL = sSQL + "                                                               WHERE LL.ID_FACT = B.ID_FACT"
sSQL = sSQL + "                                                                        AND (LL.NUM_LIGNE < B.NUM_LIGNE)"
sSQL = sSQL + "                                                                        AND TYPE_LIGNE = 'HE'"
sSQL = sSQL + "                                                               ORDER BY LL.NUM_LIGNE DESC)"
sSQL = sSQL + "                             Else: B.FACT_NUMPARC"
sSQL = sSQL + "                           END) AS 'FACT_NUMPARC',"
sSQL = sSQL + "                          H.AAAAMM,"
sSQL = sSQL + "                          B.TYPE_LIBELLEPROP,"
sSQL = sSQL + "                          (CASE"
sSQL = sSQL + "                             WHEN TYPE_LIBELLEPROP = 'lbl.ficheFactSiege.lblLigneTableau_RetourDepart' THEN FACT_QTE"
sSQL = sSQL + "                             Else '0'"
sSQL = sSQL + "                           END) AS 'KMS_Parcourus',"
sSQL = sSQL + "                          (CASE"
sSQL = sSQL + "                             WHEN TYPE_LIBELLEPROP = 'ref.typeprst.KM.aff' THEN FACT_QTE"
sSQL = sSQL + "                             Else '0'"
sSQL = sSQL + "                           END) AS 'KMS_FACT',"
sSQL = sSQL + "                          (COALESCE(B.PX_UNITAIRE,0))     AS 'PX_UNITAIRE',"
sSQL = sSQL + "                          (COALESCE(B.FACT_MONTANTNET,0)) AS 'FACT_MONTANTNET',"
sSQL = sSQL + "                          B.TYPE_PRST,"
sSQL = sSQL + "                          B.CODE_AGENCE,"
sSQL = sSQL + "                          (CASE"
sSQL = sSQL + "                             WHEN B.NO_CONTRAT IS NULL THEN (SELECT TOP 1 LL.NO_CONTRAT"
sSQL = sSQL + "                                                             FROM   S1SRVBDD02.IGLOO.DBO.FACT_LIGNE LL"
sSQL = sSQL + "                                                             WHERE LL.ID_FACT = B.ID_FACT"
sSQL = sSQL + "                                                                    AND (LL.NUM_LIGNE < B.NUM_LIGNE)"
sSQL = sSQL + "                                                                    AND LL.CODE_TYPEPRST = 'LO')"
sSQL = sSQL + "                             Else: B.NO_CONTRAT"
sSQL = sSQL + "                           END) AS 'NO_CONTRAT',"
sSQL = sSQL + "                          H.NUM_FACT"
sSQL = sSQL + "                   FROM   S1SRVBDD02.IGLOO.DBO.FACT_LIGNE B"
sSQL = sSQL + "                          LEFT JOIN S1SRVBDD02.IGLOO.DBO.FACT_HEADER H"
sSQL = sSQL + "                            ON H.ID_FACT = B.ID_FACT"
sSQL = sSQL + "                   WHERE  B.ID_FACT IN (SELECT ID_FACT"
sSQL = sSQL + "                                        FROM S1SRVBDD02.IGLOO.DBO.FACT_HEADER"
sSQL = sSQL + "                                        WHERE  CODE_TYPECNT = 'LD'"
sSQL = sSQL + "                                               AND DTE_FACT BETWEEN '20100101' AND '20101231'"
sSQL = sSQL + "                                               AND CODE_SOCPF = 'PFLO'"
sSQL = sSQL + "                                               AND ID_CLIENTFICHE <> '5007798'"
sSQL = sSQL + "                                               AND ID_CLIENTFICHE IN (SELECT SUBSTRING(NUM_CLIENT,3,9)"
sSQL = sSQL + "                                                                      FROM S1SRVBDD02.Z_STATS.DBO.LISTEGRDSCPTES"
sSQL = sSQL + "                                                                      WHERE  GROUPE_PRINCIPAL LIKE '%BRAKE%'))"
sSQL = sSQL + "                          AND (TYPE_LIBELLEPROP = 'lbl.ficheFactSiege.lblLigneTableau_RetourDepart'"
sSQL = sSQL + "                                OR CODE_TYPEPRST = 'KM')"
sSQL = sSQL + "                          AND H.NUM_FACT IS NOT NULL"
sSQL = sSQL + "                          AND H.IS_COMPTA_CEGID = '1') AA"
sSQL = sSQL + "                  LEFT JOIN S1SRVBDD02.IGLOO.DBO.CONTRAT_VAR BB"
sSQL = sSQL + "                    ON AA.NO_CONTRAT = BB.NO_CONTRAT"
sSQL = sSQL + "                       AND (CASE"
sSQL = sSQL + "                              WHEN (SELECT TOP 1 VERSION_CONTRAT"
sSQL = sSQL + "                                    FROM   S1SRVBDD02.IGLOO.DBO.CONTRAT_VAR CCC"
sSQL = sSQL + "                                    WHERE AA.NO_CONTRAT = CCC.NO_CONTRAT"
sSQL = sSQL + "                                           AND AA.DTE_FACT BETWEEN CCC.DTE_DEBUT AND (COALESCE(CCC.DTE_FIN,'31/12/2099'))) IS NOT NULL THEN (SELECT TOP 1 VERSION_CONTRAT"
sSQL = sSQL + "                                                                                                                                             FROM   S1SRVBDD02.IGLOO.DBO.CONTRAT_VAR CCC"
sSQL = sSQL + "                                                                                                                                             WHERE AA.NO_CONTRAT = CCC.NO_CONTRAT"
sSQL = sSQL + "                                                                                                                                                    AND AA.DTE_FACT BETWEEN CCC.DTE_DEBUT AND (COALESCE(CCC.DTE_FIN,'31/12/2099')))"
sSQL = sSQL + "                              ELSE (SELECT MAX(VERSION_CONTRAT)"
sSQL = sSQL + "                                    FROM   S1SRVBDD02.IGLOO.DBO.CONTRAT_VAR CCC"
sSQL = sSQL + "                                    WHERE  AA.NO_CONTRAT = CCC.NO_CONTRAT)"
sSQL = sSQL + "                            END) = BB.VERSION_CONTRAT) DD"
sSQL = sSQL + "                 "
sSQL = sSQL + "          LEFT JOIN S1SRVBDD02.IGLOO.DBO.CNT_PRST PP"
sSQL = sSQL + "            ON PP.NO_CONTRAT = DD.NO_CONTRAT"
sSQL = sSQL + "               AND PP.VERSION_CONTRAT = DD.VERSION_CONTRAT"
sSQL = sSQL + "               AND PP.CODE_TYPEPRST = 'KM'"
sSQL = sSQL + "          LEFT JOIN S1SRVBDD02.IGLOO.DBO.CONTRAT CC"
sSQL = sSQL + "            ON PP.NO_CONTRAT = CC.NO_CONTRAT"
sSQL = sSQL + "            "
sSQL = sSQL + " GROUP BY DD.PARC_CONTRACTUEL, CC.CODE_TYPECNT"
sSQL = sSQL + " ORDER BY DD.PARC_CONTRACTUEL"

**jfontaine** · 12/01/2011, 11h12

Bonjour,

As tu essayé de supprimer les ":" ?

Si SQL Server ils ne sont pas utile

**ridokou** · 12/01/2011, 11h37

Bonjour et merci pour ton aide,

je viens de supprimer les ":" car ils avaient été mis automatiquement lors de mon copier/coller de SQL server vers VBA.

Toutefois, j'ai maintenant un autre message me disant que "l'opération n'est pas autorisée si l'objet est ouvert" ...
J'ai pourtant fermer SQL server ou même tous les autres fichiers/applications que j'avais d'ouvert.

Est ce que ce message te parle ?

encore merci

**jfontaine** · 12/01/2011, 12h01

peux tu donner la ligne de code qui génère cette erreur

**ridokou** · 12/01/2011, 13h06

lorsque je vais sur le debogage, c'est la partie en jaune qui apparait mais je pense que l'erreur vient du code.
L'erreur est une erreur 3705 et en cherchant un peu sur le net j'ai vu que ça avait une relation avec mon protocole de conection.

Je continue de chercher dans mon coin également.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
    cmdCommand.CommandText = sSQL
    cmdCommand.CommandType = adCmdText
    rstRecordset.Open sSQL, cnnConn
    'cmdCommand.Execute
  
  'Open the recordset.
    rstRecordset.Open cmdCommand
    
  'Populer la feuille
    DestCell.Select
    DestCell.CopyFromRecordset rstRecordset
 
  'Fermer the recordset.
    rstRecordset.Close