Erreur VBA sur requete SQL

Version imprimable

Bonjour à tous,

Je realise un fichier de reporting, qui intérroge une base SQL (sql server 2008) et j'ai une erreur avec la requete suivante:

Code:

1
2
3
4
5
6
7
8
WITH requete1 AS (SELECT G.razon AS Societe, T.descrip AS Atelier, R.Descrip AS tarif, TPF.Tecnicidad AS M, TPF.FechaDesde AS date_debut, TPF.PrecioHora AS prix
				FROM   ttTarifaPrecioFecha AS TPF
				INNER JOIN tgempresa AS G ON TPF.emp = G.emp 
				INNER JOIN tgtaller AS T  ON TPF.emp = T.emp AND TPF.taller = T.taller  
				INNER JOIN ttTarifa AS R  ON R.Codigo=TPF.Codigo  
				WHERE  TPF.Tecnicidad = 'M2' AND  TPF.Codigo IN ('TCE','TCL','TGA')),
requete2 AS (SELECT Societe ,Atelier, tarif, date_debut, Prix, RANK() OVER(PARTITION BY Societe ,Atelier,tarif ORDER BY date_debut DESC) AS RANG 	FROM   requete1)
SELECT Societe ,Atelier, tarif, date_debut ,prix, RANG FROM   requete2 WHERE  RANG = 1;

Mais se qui est bizarre si je lance cette requête sur mon serveur SQL, la requête affiche bien le résultat le résultat sans aucun probleme.

voici le segement du code concernée:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
'--------------------------------------------------------------
    Set f7 = fso.OpenTextFile(chemin & "\sql\t2.sql", ForReading)
    une_variable7 = f7.ReadAll
    f7.Close
               Set f07 = fso.OpenTextFile(chemin & "\test.txt", 2, True)
 f07.write (une_variable7)
 f07.Close
 
    Dim rsBatiiiiii As ADODB.Recordset
    Set rsBatiiiiii = New ADODB.Recordset
    With rsBatiiiiii
    .ActiveConnection = cnBat
    .Open une_variable7
             DerniereLigne7 = Worksheets("Tarif M2").Range("A100000").End(xlUp).Row + 1
    Worksheets("Tarif M2").Range("A" & DerniereLigne7).CopyFromRecordset rsBatiiiiii
   .Close
 
    End With

L'erreur est sur .Open une_variable7 avec comme message "syntaxe incorrecte vers le mot clé 'with'"

J'ai testé ce bout de code en changeant seulement la requête en question. Au lieu de pointe sur ma requete ci-dessus, j'ai fait pointé sur autre requete(stockvo.sql). Le script VBA c'est bien executé sans erreur.

Quel est le probléme avec le WITH ?

Merci d'avance pour votre aide

guigui69

bonjour,
tu exécute une requête sql dans vb comme dans oracle sql développeur,
hors dans vb il faut que se soit du texte (String) et oracle nous somme dans un éditeur de texte

Code:

1
2
3
4
5
6
7
8
 Sql = "SELECT G.razon AS Societe, T.descrip AS Atelier, R.Descrip AS tarif, TPF.Tecnicidad AS M, TPF.FechaDesde AS date_debut, TPF.PrecioHora AS prix"
 Sql = Sql & "               FROM   ttTarifaPrecioFecha AS TPF"
 Sql = Sql & "               INNER JOIN tgempresa AS G ON TPF.emp = G.emp"
 Sql = Sql & "               INNER JOIN tgtaller AS T  ON TPF.emp = T.emp AND TPF.taller = T.taller"
 Sql = Sql & "               INNER JOIN ttTarifa AS R  ON R.Codigo=TPF.Codigo"
 Sql = Sql & "               WHERE  TPF.Tecnicidad = 'M2' AND  TPF.Codigo IN ('TCE','TCL','TGA')),"
 Sql = Sql & "requete2 AS (SELECT Societe ,Atelier, tarif, date_debut, Prix, RANK() OVER(PARTITION BY Societe ,Atelier,tarif ORDER BY date_debut DESC) AS RANG    FROM   requete1)"
Sql = Sql & "SELECT Societe ,Atelier, tarif, date_debut ,prix, RANG FROM   requete2 WHERE  RANG = 1;"

Merci , mais j'ai toujours la même erreur:

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
 
'    Set f7 = fso.OpenTextFile(chemin & "\sql\t2.sql", ForReading)
 '   une_variable7 = f7.ReadAll
 '   f7.Close
   '            Set f07 = fso.OpenTextFile(chemin & "\test.txt", 2, True)
' f07.write (une_variable7)
'f07.Close
 
  Sql = "WITH requete1 AS (SELECT G.razon AS Societe, T.descrip AS Atelier, R.Descrip AS tarif, TPF.Tecnicidad AS M, TPF.FechaDesde AS date_debut, TPF.PrecioHora AS prix"
 Sql = Sql & "               FROM   ttTarifaPrecioFecha AS TPF"
 Sql = Sql & "               INNER JOIN tgempresa AS G ON TPF.emp = G.emp"
 Sql = Sql & "               INNER JOIN tgtaller AS T  ON TPF.emp = T.emp AND TPF.taller = T.taller"
 Sql = Sql & "               INNER JOIN ttTarifa AS R  ON R.Codigo=TPF.Codigo"
 Sql = Sql & "               WHERE  TPF.Tecnicidad = 'M2' AND  TPF.Codigo IN ('TCE','TCL','TGA')),"
 Sql = Sql & "requete2 AS (SELECT Societe ,Atelier, tarif, date_debut, Prix, RANK() OVER(PARTITION BY Societe ,Atelier,tarif ORDER BY date_debut DESC) AS RANG    FROM   requete1)"
Sql = Sql & "SELECT Societe ,Atelier, tarif, date_debut ,prix, RANG FROM   requete2 WHERE  RANG = 1;"
 
    Dim rsBatiiiiii As ADODB.Recordset
    Set rsBatiiiiii = New ADODB.Recordset
    With rsBatiiiiii
    .ActiveConnection = cnBat
    .Open Sql
             DerniereLigne7 = Worksheets("Tarif M2").Range("A100000").End(xlUp).Row + 1
    Worksheets("Tarif M2").Range("A" & DerniereLigne7).CopyFromRecordset rsBatiiiiii
   .Close
 
    End With

Merci d'avance pour ton aide

guigui69

04/08/2013, 19h22
AlainTech

Bonjour,

Je ne connais pas bien la syntaxe du WITH mais ne faudrait-il pas répéter le WITH avant requete2?

Et donc, supprimer la virgule à la fin du WITH requete1.

EDIT:
Ben non. C'est pas ça. Ta syntaxe semble correcte.
09/08/2013, 13h32
guigui69

Bonjour,

je reviens car je suis toujours bloqué sur cette requete avec le mot WITH

Que faut-il faire pour qu'il accepte de WITH?

guigui69

Je pense qu'il y a un problème de variable :

1 - D'abord définir une connexion ADOBE
2 - Ensuite définir un Recordset et l'éxecuter via Execute
3- Copier le résultat sur la feuille Excel

exemple :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
    Dim Cn As Connection
    Dim Rst As Recordset 
    Dim Fichier As String
    Fichier = ThisWorkbook.Path & "\" & ThisWorkbook.Name
    Set Cn = New ADODB.Connection
    With Cn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = "Data Source=" & Fichier & _
            ";Extended Properties=Excel 8.0;"
        .Open
    End With
    Set Rst = New Recordset
    Set Rst = Cn.Execute("select * from [data$]")
    Workbooks(NomW).Worksheets("Tmp").Cells(1, 1).CopyFromRecordset Rst
 
    Set Rst = Nothing
    Cn.Close
    Set Cn = Nothing

Si on suit cette même logique, votre code devient

Code:

1
2
3
4
5
6
7
 
     Dim rs As ADODB.Recordset
     Set rs = new ADOB.Recordset 
     Set rs = cn.execute(sql)
 
        DerniereLigne7 = Worksheets("Tarif M2").Range("A100000").End(xlUp).Row + 1
    Worksheets("Tarif M2").Range("A" & DerniereLigne7).CopyFromRecordset rs

En supposant que vous avez bien défini la connexion bien sur.

Bonjour,

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
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
 
Sub Macro1()
Application.Calculation = xlManual
Dim valcel As String
Dim utilisateur As String
 
valcel = Excel.Range("Informations!B3").Value
valce2 = Excel.Range("Informations!B4").Value
valce3 = Excel.Range("Informations!B5").Value
a = Split(valce3, "|")
id_societe = a(0)
id_atelier = a(1)
'MsgBox (">>>>>" & valce3 & "<<<<>>>>" & id_atelier & "<<<<>>>>" & id_societe & "<<<<")
chemin = ActiveWorkbook.Path
 
Dim cnBat As ADODB.Connection
Set cnBat = New ADODB.Connection
Dim strConn As String
strConn = "PROVIDER=SQLOLEDB;"
strConn = strConn & "DATA SOURCE=XX.XX.XX.XX;UID=login;PWD=password;DATABASE=BDD"
cnBat.Open strConn
cnBat.CommandTimeout = 0
 
Set fso = CreateObject("Scripting.FileSystemObject")
Const ForReading = 1, ForWriting = 2
Worksheets("TempsPassés-TempsFacturés").Range("A2:S100000").ClearContents
Worksheets("OR-ENCOURS").Range("A2:N100000").ClearContents
Worksheets("Présences réelles").Range("A2:D100000").ClearContents
Worksheets("Vente VN-VO").Range("A2:O100000").ClearContents
Worksheets("Tarif M2").Range("A2:U100000").ClearContents
Worksheets("Stock VN").Range("A2:j100000").ClearContents
Worksheets("Stock VO").Range("A2:k100000").ClearContents
Worksheets("SQL1").Range("A2:k100000").ClearContents
'------------------
Dim rsBat0 As ADODB.Recordset
Set rsBat0 = New ADODB.Recordset
 
With rsBat0
     .ActiveConnection = cnBat
     If (id_atelier = 0) Then
     .Open "SELECT emp,taller FROM tgtaller WHERE tgtaller.taller NOT IN(31) "
     Else
     .Open "SELECT emp,taller FROM tgtaller WHERE tgtaller.taller=" & id_atelier
     End If
                 'MsgBox ("Societe:" & rsBat("emp") & "Point de vente" & rsBat("puntoventa"))
        Do While Not rsBat0.EOF
 
           Set f0 = fso.OpenTextFile(chemin & "\sql\requete-facture-atelier.sql", ForReading)
                une_variable0 = Replace(f0.ReadAll, "datedebut", valcel)
                une_variable0 = Replace(une_variable0, "datefin", valce2)
                une_variable0 = Replace(une_variable0, "societe", rsBat0("emp"))
                une_variable0 = Replace(une_variable0, "atelier", rsBat0("taller"))
                f0.Close
             '  Set f1 = fso.OpenTextFile(chemin & "\" & rsBat0("taller") & ".txt", 2, True)
 ' f1.write (une_variable)
 ' f1.Close
           ' MsgBox (une_variable)
           Dim rsBati0 As ADODB.Recordset
            Set rsBati0 = New ADODB.Recordset
            With rsBati0
    .ActiveConnection = cnBat
      .Open une_variable0
        DerniereLigne = Worksheets("SQL1").Range("A100000").End(xlUp).Row + 1
 
            'MsgBox (DerniereLigne)
            Worksheets("SQL1").Range("A" & DerniereLigne).CopyFromRecordset rsBati0
          .Close
    End With
 
 
 
    rsBat0.MoveNext
Loop
 
     .Close
 
 
 
End With
 
 
'--------------------------------------------------------------
    Set f7 = fso.OpenTextFile(chemin & "\sql\t2.sql", ForReading)
    une_variable7 = f7.ReadAll
    f7.Close
               Set f07 = fso.OpenTextFile(chemin & "\test.txt", 2, True)
 f07.write (une_variable7)
 f07.Close
 
  Sql = " WITH requete1 AS (SELECT G.razon AS Societe, T.descrip AS Atelier, R.Descrip AS tarif, TPF.Tecnicidad AS M, TPF.FechaDesde AS date_debut, TPF.PrecioHora AS prix"
 Sql = Sql & "               FROM   ttTarifaPrecioFecha AS TPF"
 Sql = Sql & "               INNER JOIN tgempresa AS G ON TPF.emp = G.emp"
 Sql = Sql & "               INNER JOIN tgtaller AS T  ON TPF.emp = T.emp AND TPF.taller = T.taller"
 Sql = Sql & "               INNER JOIN ttTarifa AS R  ON R.Codigo=TPF.Codigo"
 Sql = Sql & "               WHERE  TPF.Tecnicidad = 'M2' AND  TPF.Codigo IN ('TCE','TCL','TGA')),"
 Sql = Sql & "requete2 AS (SELECT Societe ,Atelier, tarif, date_debut, Prix, RANK() OVER(PARTITION BY Societe ,Atelier,tarif ORDER BY date_debut DESC) AS RANG    FROM   requete1)"
 Sql = Sql & "SELECT Societe ,Atelier, tarif, date_debut ,prix, RANG FROM   requete2 WHERE  RANG = 1;"
 
    Dim rsBatiiiiii As ADODB.Recordset
    Set rsBatiiiiii = New ADODB.Recordset
    With rsBatiiiiii
    .ActiveConnection = cnBat
    .Open Sql
             DerniereLigne7 = Worksheets("Tarif M2").Range("A100000").End(xlUp).Row + 1
    Worksheets("Tarif M2").Range("A" & DerniereLigne7).CopyFromRecordset rsBatiiiiii
   .Close
 
    End With
 
cnBat.Close
Set rsBat = Nothing
Set cnBat = Nothing
 
Application.Calculate
Application.Calculation = xlAutomatic
 
ActiveWorkbook.RefreshAll
End Sub

Je précise que si je change la requête avec un simple "select * from etc"
à la place de celle actuel:

Code:

1
2
3
4
5
6
7
8
  Sql = " WITH requete1 AS (SELECT G.razon AS Societe, T.descrip AS Atelier, R.Descrip AS tarif, TPF.Tecnicidad AS M, TPF.FechaDesde AS date_debut, TPF.PrecioHora AS prix"
 Sql = Sql & "               FROM   ttTarifaPrecioFecha AS TPF"
 Sql = Sql & "               INNER JOIN tgempresa AS G ON TPF.emp = G.emp"
 Sql = Sql & "               INNER JOIN tgtaller AS T  ON TPF.emp = T.emp AND TPF.taller = T.taller"
 Sql = Sql & "               INNER JOIN ttTarifa AS R  ON R.Codigo=TPF.Codigo"
 Sql = Sql & "               WHERE  TPF.Tecnicidad = 'M2' AND  TPF.Codigo IN ('TCE','TCL','TGA')),"
 Sql = Sql & "requete2 AS (SELECT Societe ,Atelier, tarif, date_debut, Prix, RANK() OVER(PARTITION BY Societe ,Atelier,tarif ORDER BY date_debut DESC) AS RANG    FROM   requete1)"
 Sql = Sql & "SELECT Societe ,Atelier, tarif, date_debut ,prix, RANG FROM   requete2 WHERE  RANG = 1;"

pour tester cela passe sans problème et cela ajoute bien mes lignes dans mon document excel.

Je ne vois pas d’où peut venir l'erreur.

guigui69