Discussion :

[MARCELBENH]

Bonjour à tous,
je lance une requète dans une macro avec le code suivant

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
        
        .CommandText = Array( _
 "SELECT MFGHEAD.MFGNUM_0 , MFGHEAD.ZZOFBARCO_0 , MFGITM.ITMREF_0 ,ITMMASTER.ITMDES1_0 ,MFGITM.EXTQTY_0 ,MFGITM.UOMEXTQTY_0 ,MFGITM.BPCNUM_0 ,ITMMASTER.SAUSTUCOE_0 , ", _
 " (select BOMSHO_0 from sagex3v5p.OXYGENE.BOMD BOMD,sagex3v5p.OXYGENE.ITMMASTER ITMMASTER1  WHERE BOMD.ITMREF_0=MFGITM.ITMREF_0 ", _
 " AND BOMD.CPNITMREF_0=ITMMASTER1.ITMREF_0 AND ITMMASTER1.TCLCOD_0='COLOR') ,(select CPNITMREF_0 from sagex3v5p.OXYGENE.BOMD BOMD,sagex3v5p.OXYGENE.ITMMASTER ITMMASTER1  WHERE BOMD.ITMREF_0=MFGITM.ITMREF_0 ", _
 " AND BOMD.CPNITMREF_0=ITMMASTER1.ITMREF_0 AND ITMMASTER1.TCLCOD_0='COLOR'),BPCUSTOMER.XBPCINST_0 ,(case when BPCUSTOMER.ZZ2ETIQ_0=1 then 'NON' when BPCUSTOMER.ZZ2ETIQ_0=2 then 'OUI' end) ,", _
 " (case when BPCUSTOMER.ZZADRETIQ_0=1 then 'NON' when BPCUSTOMER.ZZADRETIQ_0=2 then 'OUI' end) ,( SELECT ATEXTRA.TEXTE_0 FROM sagex3v5p.OXYGENE.ATEXTRA ATEXTRA WHERE (ATEXTRA.CODFIC_0='ATABDIV') AND (ATEXTRA.ZONE_0='LNGDES') AND (ATEXTRA.IDENT1_0='23') AND ", _
 " (ATEXTRA.IDENT2_0=ITMMASTER.TSICOD_3)) ,( SELECT ATEXTRA.TEXTE_0 FROM sagex3v5p.OXYGENE.ATEXTRA ATEXTRA WHERE (ATEXTRA.CODFIC_0='ATABDIV') AND (ATEXTRA.ZONE_0='LNGDES') AND (ATEXTRA.IDENT1_0='24') AND ", _
 " (ATEXTRA.IDENT2_0=ITMMASTER.TSICOD_4)) ", _
 " FROM sagex3v5p.OXYGENE.MFGHEAD MFGHEAD, sagex3v5p.OXYGENE.MFGITM MFGITM,sagex3v5p.OXYGENE.ITMMASTER ITMMASTER,sagex3v5p.OXYGENE.BPCUSTOMER BPCUSTOMER", _
 " WHERE MFGITM.MFGNUM_0 = MFGHEAD.MFGNUM_0 AND ITMMASTER.ITMREF_0=MFGITM.ITMREF_0 AND BPCUSTOMER.BPCNUM_0=MFGITM.BPCNUM_0" _
)

et excel me répond soit :
"erreur 13 - incompatibilité de type" (au niveau de l'array)
soit "erreur générale odbc"

or, cette requete marche parfaitement dans le query (mais comme elle est complexe, on ne peut pas utiliser les paramètre - d'où ma décision de passer par le vb)

je suis allé voir un peu partout ... mais là je sèche
la base est du sql server
Merci à vous ô grands gourous du vb et du sql

[MARCELBENH]

Bon ... c'est résolu

Voila le code

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
 
Sql = Array( _
        "SELECT MFGHEAD.MFGNUM_0 , MFGHEAD.ZZOFBARCO_0,MFGITM.ITMREF_0,ITMMASTER.ITMDES1_0,MFGITM.EXTQTY_0 ,MFGITM.UOMEXTQTY_0 ,MFGITM.BPCNUM_0 ,ITMMASTER.SAUSTUCOE_0 , ", _
        " (select BOMSHO_0 from sagex3v5p.OXYGENE.BOMD BOMD,sagex3v5p.OXYGENE.ITMMASTER ITMMASTER1  WHERE BOMD.ITMREF_0=MFGITM.ITMREF_0 ", _
         Chr(10) & "" & Chr(13) & "AND BOMD.CPNITMREF_0=ITMMASTER1.ITMREF_0 AND ITMMASTER1.TCLCOD_0='COLOR'),", _
         " (select CPNITMREF_0 from sagex3v5p.OXYGENE.BOMD BOMD,sagex3v5p.OXYGENE.ITMMASTER ITMMASTER1  WHERE BOMD.ITMREF_0=MFGITM.ITMREF_0 ", _
         Chr(10) & "" & Chr(13) & "AND BOMD.CPNITMREF_0=ITMMASTER1.ITMREF_0 AND ITMMASTER1.TCLCOD_0='COLOR'),BPCUSTOMER.XBPCINST_0,", _
         Chr(10) & "" & Chr(13) & "(case when BPCUSTOMER.ZZ2ETIQ_0=1 then 'NON' when BPCUSTOMER.ZZ2ETIQ_0=2 then 'OUI' end),(case when BPCUSTOMER.ZZADRETIQ_0=1 then 'NON' when BPCUSTOMER.ZZADRETIQ_0=2 then 'OUI' end),", _
         Chr(10) & "" & Chr(13) & "( SELECT ATEXTRA.TEXTE_0 FROM sagex3v5p.OXYGENE.ATEXTRA ATEXTRA WHERE (ATEXTRA.CODFIC_0='ATABDIV') AND (ATEXTRA.ZONE_0='LNGDES') AND (ATEXTRA.IDENT1_0='23') AND(ATEXTRA.IDENT2_0=ITMMASTER.TSICOD_3)),", _
         Chr(10) & "" & Chr(13) & "( SELECT ATEXTRA.TEXTE_0 FROM sagex3v5p.OXYGENE.ATEXTRA ATEXTRA WHERE (ATEXTRA.CODFIC_0='ATABDIV') AND (ATEXTRA.ZONE_0='LNGDES') AND (ATEXTRA.IDENT1_0='24') AND(ATEXTRA.IDENT2_0=ITMMASTER.TSICOD_4))", _
        Chr(13) & "" & Chr(10) & "FROM sagex3v5p.OXYGENE.MFGHEAD MFGHEAD,sagex3v5p.OXYGENE.MFGITM MFGITM,sagex3v5p.OXYGENE.ITMMASTER ITMMASTER,sagex3v5p.OXYGENE.BPCUSTOMER BPCUSTOMER ", _
        "WHERE MFGHEAD.MFGNUM_0=MFGITM.MFGNUM_0 AND MFGITM.ITMREF_0=ITMMASTER.ITMREF_0 AND BPCUSTOMER.BPCNUM_0=MFGITM.BPCNUM_0" _
        )
 
    With ActiveSheet.QueryTables.Add(Connection:= _
        "ODBC;DSN=OXYLEC;UID=administrateur;APP=Microsoft Office XP;WSID=TSE;DATABASE=sagex3v5p;Trusted_Connection=Yes" _
        , Destination:=Range("A1"))
        .CommandText = Sql

voili merci à ceux qui m'ont aidé (personne ? bon bah j'ai réussi)

c'est