Discussion :

[laugab]

Bonjour,

J'ai une procédure stockée déclarée ainsi dans SQL SERVER 2008 :

Code sql :

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

1
2
3
4
5
6
7
8
CREATE PROCEDURE [dbo].[sp_QT_DonneesBrutes] (@ClauseAnnee varchar(1000))
AS
DECLARE @SQL varchar(8000)
SET @SQL = '
SELECT ID, TXT_NOM FROM dbo.T_TEST
  WHERE BOL_VALIDE = 1 ' + @ClauseAnnee + '
  ORDER BY TXT_NOM;'
EXEC (@SQL);

(je l'ai simplifié pour l'exemple). Mon paramètre est de type varchar et une des valeurs possible est ' AND NUM_ANNEE = 2010'. J'ai choisi de passer des variables ainsi plutot que de mettre un paramètre dans ma requête du genre :

Code sql :

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

WHERE BOL_VALIDE = 1 AND NUM_ANNEE = @annee

parce que je dois parfois récupérer les données de toutes les années (et j'ai plusieurs clauses comme ça que je n'ai pas mis dans l'exemple).

Je cherche à récuperer le résultat de cette procédure stockée dans un recordset ADO pour l'affecter au recordset d'un formulaire 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
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
Private Sub InitTableauBrut()
    Dim AdoCn_sql As ADODB.Connection
    Dim AdoRs As ADODB.Recordset    
    Dim StrErreur As String
    Dim Bol_Result As Boolean
 
On Error GoTo Err_Erreur
    ' Connexion à la base de données SQVDATA (SQL SERVER)
    Bol_Result = InitConnectionBase(AdoCn_sql, StrErreur)
    If VBA.Len(StrErreur) > 0 Then GoTo Fin
    If Bol_Result = False Then
        StrErreur = "Procedure InitTableauBrut; La connexion SQL Server n'a pas abouti."
        GoTo Fin
    End If
    Set AdoRs = New ADODB.Recordset   
    'Charge les données brutes pour la sélection faite
    AdoRs.Open "EXECUTE [SQVDATA].[dbo].[sp_QT_DonneesBrutes] '" & Replace(m_StrSqlClauseAnnee, "'", "''") & "'", AdoCn_sql, adOpenKeyset, adLockOptimistic
    Me.SSF_DONNEES_BRUTES.Form.RecordSource = ""
    Set Me.SSF_DONNEES_BRUTES.Form.Recordset = AdoRs
Fin:
    'Libère le recordset
    If Not AdoRs Is Nothing Then
        If AdoRs.State <> 0 Then AdoRs.Close
        Set AdoRs = Nothing
    End If
    'Ferme la connexion à la bdd
    If Not AdoCn_sql Is Nothing Then
        AdoCn_sql.Close
        Set AdoCn_sql = Nothing
    End If
    'Affiche le message d'erreur si erreur
    If VBA.Len(StrErreur) > 0 Then MsgBox StrErreur, vbCritical, m_nom_base
    On Error GoTo 0
    Exit Sub
Err_Erreur:
    'Stocke le message d'erreur
  StrErreur = "Procedure F_PRINCIPAL(InitTableauBrut); Erreur N°" & _
    Err.Number & " - Description : " & Err.Description
  GoTo Fin
End Sub

Mais j'ai l'erreur "Erreur N°430 - Description : La classe ne gère pas Automation ou l'interface attendue" à l'exécution du code

Code :

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

Set Me.SSF_DONNEES_BRUTES.Form.Recordset = AdoRs

J'ai vu sur d'autres discussions de ce forum que cela pouvait être du à la référence ADO qui serait à mettre à jour (j'ai la référence msado28) mais ce qui m'étonne c'est que le recordset est bien chargé avec toutes les lignes et les colonnes.
D'ailleurs si je l'oriente vers un export Excel avec une commande "CopyFromRecordset AdoRs" j'ai bien mes données dans Excel).
De plus si je modifie la procédure stockée de cette façon (pour ignorer le paramètre) :

Code sql :

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

1
2
3
CREATE PROCEDURE [dbo].[sp_QT_DonneesBrutes] (@ClauseAnnee varchar(1000))
AS SELECT ID, TXT_NOM FROM dbo.T_TEST
  WHERE BOL_VALIDE = 1 ORDER BY TXT_NOM;

Je n'ai plus l'erreur et mon formulaire est bien chargé.

Pour info, j'ai essayé la methode décrite dans la discussion http://www.developpez.net/forums/d49...edure-stockee/ mais j'ai toujours l'erreur :

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
Private Sub InitTableauBrut()
    Dim AdoCn_sql As ADODB.Connection
    Dim AdoRs As ADODB.Recordset    
    Dim AdoCmd As ADODB.Command       'Objet commande permet d'éxécuter la procedure stocké
    Dim AdoParam As ADODB.Parameter   'Permet de définir un parametre de la PS    
    Dim StrErreur As String
    Dim Bol_Result As Boolean
 
On Error GoTo Err_Erreur
    ' Connexion à la base de données SQVDATA (SQL SERVER)
    Bol_Result = InitConnectionBase(AdoCn_sql, StrErreur)
    If VBA.Len(StrErreur) > 0 Then GoTo Fin
    If Bol_Result = False Then
        StrErreur = "Procedure InitTableauBrut; La connexion SQL Server n'a pas abouti."
        GoTo Fin
    End If
    Set AdoRs = New ADODB.Recordset       
 
    'on défini l'objet CMD
    Set AdoCmd = New ADODB.Command
    AdoCmd.ActiveConnection = AdoCn_sql
    AdoCmd.CommandType = adCmdStoredProc
    AdoCmd.CommandText = "sp_QT_DonneesBrutes"
 
    'on donne une valeur au parametre
    Set AdoParam = AdoCmd.Parameters("@ClauseEspece")
    AdoParam.Value = m_StrSqlClauseEspece
 
    'on donne au RS le resultat renvoyé par la procedures stockée
    'Set AdoRs = AdoCmd.Execute
    AdoRs.Open AdoCmd, , adOpenKeyset, adLockOptimistic
    'Charge les données brutes pour la sélection faite
    Me.SSF_DONNEES_BRUTES.Form.RecordSource = ""
    Set Me.SSF_DONNEES_BRUTES.Form.Recordset = AdoRs
Fin:
    'Libère le recordset
    If Not AdoRs Is Nothing Then
        If AdoRs.State <> 0 Then AdoRs.Close
        Set AdoRs = Nothing
    End If
    'Ferme la connexion à la bdd
    If Not AdoCn_sql Is Nothing Then
        AdoCn_sql.Close
        Set AdoCn_sql = Nothing
    End If
    'Affiche le message d'erreur si erreur
    If VBA.Len(StrErreur) > 0 Then MsgBox StrErreur, vbCritical, m_nom_base
    On Error GoTo 0
    Exit Sub
Err_Erreur:
    'Stocke le message d'erreur
  StrErreur = "Procedure F_PRINCIPAL(InitTableauBrut); Erreur N°" & _
    Err.Number & " - Description : " & Err.Description
  GoTo Fin
End Sub

Est ce que je déclare mal ma procédure stockée dans SQL SERVER ou est ce que j'ai un pb dans le VBA?

Merci d'avance de votre aide.

[Tofalu]

Il ne vous manquerez pas un espace devant ORDER BY ?

Parce que là, à priori cela donne :

Code :

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

WHERE BOL_VALIDE = 1  AND NUM_ANNEE = 2010ORDER BY TXT_NOM;'

[laugab]

Bonjour et merci,

Je vous prie de m'excuser, en voulant simplifier l'exemple je n'ai pas reporté l'espace manquant.
Je précise que la procédure stockée (sous ses deux versions de code) fonctionne très bien, avec ou sans paramètre, je le vérifie en exécutant dans Management Studio la valeur de la chaine :

Code :

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

"EXECUTE [SQVDATA].[dbo].[sp_QT_DonneesBrutes] '" & Replace(m_StrSqlClauseAnnee, "'", "''") & "'"

Ou en debug en affichant le contenu de mon recordset.

C'est bien sur la ligne :

Code :

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

Set Me.SSF_DONNEES_BRUTES.Form.Recordset = AdoRs

que se produit l'erreur.

Avec mes excuses.

[Kloun]

Bonjour,

Quel est l'intérêt d'utiliser une PS ?

[laugab]

Bonjour Kloun,
Selon moi, l'intérêt d'utiliser une procédure stockéee par rapport à du sql basé sur des tables liées est de déporter le code coté serveur et de mieux tracer les utilisations des tables. Si changement sur les tables, on sait directement quelles requêtes sont impactées grâce aux dépendances.
Dans mon cas, un autre intérêt est de pouvoir changer de frontal sans avoir à redévelopper toute l'application (migration access --> web par exemple).
Cordialement,

[Tofalu]

Disons que cela perd de l'intérêt si vous tapez du SQL dans les paramètres. Il serait préférable que le client n'est qu'à spécifier les paramètres en toute transparence du langage du moteur de base de données.

[laugab]

Oui, je le conçois.
Mon pb vient peut être tout bêtement de la procédure stockée SQL server. Si j'utilise mal l'objet procédure stockée, quel objet utiliser alors?
Mes paramètres sont parfois du type " AND TXT_LIEU IN ('PARIS', 'MARSEILLE') ", je ne crois pas que les requêtes paramétrées gèrent ça.
Cordialement,

[Tofalu]

Il y a des possibilités :

http://www.developpez.net/forums/d47...at-liste-d-id/

[laugab]

Merci,
Dans mon cas, cela ne changera pas le pb.
J'ai essayé avec la procédure stockée suivante (la même qu'avant en ignorant les paramètres) :

Code sql :

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

1
2
3
4
5
6
7
8
CREATE PROCEDURE [dbo].[sp_QT_DonneesBrutes] (@ClauseAnnee varchar(1000))
AS
DECLARE @SQL varchar(8000)
SET @SQL = '
SELECT ID, TXT_NOM FROM dbo.T_TEST
  WHERE BOL_VALIDE = 1
  ORDER BY TXT_NOM;'
EXEC (@SQL);

Et j'ai la même erreur 430. Le pb ne vient donc pas des paramètres mais du type de retour de la procédure stockée. En passant par un SELECT... ça fonctionne , en passant par un EXEC('SELECT...') cela ne fonctionne pas.

Pourtant, il me semble être obligé de passer par cet EXEC pour concatener mes paramètres (car même en passant par une vraie requête paramétrée (en utilisant votre lien pour gérer les IN(...)), comment gère t'on le cas d'un paramère inexistant?
Si je n'ai pas de lieu à filtrer (parce que je les veux tous), ma requête ressemblera quand même à SELECT ... WHERE TXT_LIEU IN () ... et elle va planter.

[Tofalu]

Personnellement je ne rencontre pas de soucis avec Exec(), voici comme j'affecte le recordset

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
Dim oRst As ADODB.Recordset
Dim oCx As ADODB.Connection
Dim oCmd As ADODB.Command
 
Set oCx = CurrentProject.Connection
 
Set oCmd = New ADODB.Command
 
With oCmd
    .ActiveConnection = oCx
    .CommandType = adCmdStoredProc
    .CommandText = "procClientParAnnee "
    .Parameters("@ClauseAnnee").Value = " AND annee>2009"
    Set oRst = oCmd.Execute
End With
 
Set Me.Recordset = oRst

[laugab]

Merci,
J'ai essayé votre méthode (seule la ligne Set oRst = oCmd.Execute diffère du mien) et j'ai une erreur différente :
Erreur N°7965 - Description : L'objet entré n'est pas une propriété Recordset valide.
Pourtant quand je navigue dans le recordset en debug, il contient bien les données...

[Tofalu]

A la différence de vous j'étais en ADP alors que vous devez être en mdb ou accdb. Je retest et reviens vers vous.

[Tofalu]

Pour éviter cette erreur, il faut que les recordsets soient situés côté client. Comme la propriété n'est pas disponible pour les recordsets déjà ouvert, il est nécessaire d'agir en amont sur l'objet connection.

Code :

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

1
2
3
 With oCmd
   .ActiveConnection = oCx
    .ActiveConnection.CursorLocation = adUseClient

[laugab]

Merci beaucoupp Tofalu, cela a résolu mon problème.
J'exécute donc une procédure stockée contenant un EXEC() grâce à l'jout de cette ligne.
Je l'appelle directement avec la méthode :

Code :

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

AdoRs.Open "EXECUTE [SQVDATA].[dbo].[sp_QT_DonneesBrutes] '" & Replace(m_StrSqlClauseAnnee, "'", "''") & "'", AdoCn_sql, adOpenKeyset, adLockOptimistic

Enfin, pour ma culture perso, pouvez vous lm'en dire plus sur les projets ADP que je ne connais pas, est-ce intéressant dans mon cas où je n'ai effectivement que des tables SQL server?
Merci à tous.

[Tofalu]

Le projet ADP c'est la facilité d'Access avec le lien des objets SQL Server sans passer par ODBC et le moteur Jet.

Sans aucun doute, si vous travaillez avec SQL Server et Access, remplacez votre fichier accdb par un adp

[billybob2]

Le projet ADP c'est la facilité d'Access avec le lien des objets SQL Server sans passer par ODBC et le moteur Jet.

Sans aucun doute, si vous travaillez avec SQL Server et Access, remplacez votre fichier accdb par un adp

Attention , dans Access 2013 les projets ADP ne sont plus gérés :

http://office.microsoft.com/fr-fr/ac...102749226.aspx