Discussion :

[Access Newbie]

Bonjour,

Je rencontre quelques problèmes pour convertir une requête Access en Procédure stockée (projet Access).

Voici le code SQL de la requête Access:

Code :

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

1
2
3
4
 
SELECT Outil.N°Service, Service.[Nom Service], Affectation.N°Monteur, Monteur.[Nom Monteur], Affectation.N°affaire, Affectation.N°outil, Outil.[nom outil], Outil.N°Marque, Marque.[Nom Marque], Outil.[date achat], Outil.[date controle], Outil.commentaire, Affectation.[date affectation], Outil.N°Groupe, Groupe.[Nom Groupe], Affectation.N°Affectation, Outil.type, Outil.N°Etat, Etat.[Nom Etat], Affectation.N°outil, Outil.controle
FROM Service INNER JOIN ((Marque INNER JOIN (Groupe INNER JOIN (Etat INNER JOIN Outil ON Etat.N°Etat = Outil.N°Etat) ON Groupe.N°Groupe = Outil.N°Groupe) ON Marque.N°Marque = Outil.N°Marque) INNER JOIN (Monteur INNER JOIN (Affaire RIGHT JOIN Affectation ON Affaire.[nom affaire] = Affectation.N°affaire) ON Monteur.N°Monteur = Affectation.N°Monteur) ON Outil.N°Outil = Affectation.[num outil]) ON Service.N°Service = Outil.N°Service
WHERE (((Outil.N°Service) Like [Formulaires]![Formulaire Affectation Outil IPR]![NumService]) AND ((Outil.N°Etat) Like [Formulaires]![Formulaire Affectation Outil IPR]![choix outilhs])) OR (((Affectation.N°Monteur) Like [Formulaires]![Formulaire Affectation Outil IPR]![NumMonteur]) AND ((Outil.N°Etat) Like [Formulaires]![Formulaire Affectation Outil IPR]![choix outilhs])) OR (((Affectation.N°affaire) Like [Formulaires]![Formulaire Affectation Outil IPR]![NumAffaire]) AND ((Outil.N°Etat) Like [Formulaires]![Formulaire Affectation Outil IPR]![choix outilhs])) OR (((Outil.N°Marque) Like [Formulaires]![Formulaire Affectation Outil IPR]![NumMarque]) AND ((Outil.N°Etat) Like [Formulaires]![Formulaire Affectation Outil IPR]![choix outilhs])) OR (((Outil.N°Groupe) Like [Formulaires]![Formulaire Affectation Outil IPR]![NumFamille]) AND ((Outil.N°Etat) Like [Formulaires]![Formulaire Affectation Outil IPR]![choix outilhs])) OR (((Affectation.N°outil) Like [Formulaires]![Formulaire Affectation Outil IPR]![num outil] And (Affectation.N°outil)=[Outil].[num outil]) AND ((Outil.N°Etat) Like [Formulaires]![Formulaire Affectation Outil IPR]![choix outilhs]));

J'ai donc créé cette procédure stockée:

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
 
CREATE PROCEDURE [dbo].[R affectation IPR modif] 
@Numservice int,
@NumMonteur int,
@NumAffaire int,
@NumMarque int,
@NumFamille int,
@NumOutil int,
@Outilhs int
AS
 
SELECT Outil.[N°Service], Service.[Nom Service], Affectation.[N°Monteur], Monteur.[Nom Monteur], Affectation.[N°affaire], Affectation.[N°outil], Outil.[nom outil], Outil.[N°Marque], Marque.[Nom Marque], Outil.[date achat], Outil.[date controle], Outil.commentaire, Affectation.[date affectation], Outil.[N°Groupe], Groupe.[Nom Groupe], Affectation.[N°Affectation], Outil.type, Outil.[N°Etat], Etat.[Nom Etat], Affectation.[N°outil], Outil.controle
FROM Service INNER JOIN ((Marque INNER JOIN (Groupe INNER JOIN (Etat INNER JOIN Outil ON Etat.[N°Etat] = Outil.[N°Etat]) ON Groupe.[N°Groupe] = Outil.[N°Groupe]) ON Marque.[N°Marque] = Outil.[N°Marque]) INNER JOIN (Monteur INNER JOIN (Affaire RIGHT JOIN Affectation ON Affaire.[nom affaire] = Affectation.[N°affaire]) ON Monteur.[N°Monteur] = Affectation.[N°Monteur]) ON Outil.[N°Outil] = Affectation.[num outil]) ON Service.[N°Service] = Outil.[N°Service]
WHERE ((Outil.[N°Service] = @Numservice) 
AND (Outil.[N°Etat] = @Outilhs)) 
OR (((Affectation.[N°Monteur]) = @NumMonteur) 
AND ((Outil.[N°Etat]) = @Outilhs)) 
OR (((Affectation.[N°affaire]) = @NumAffaire) 
AND ((Outil.[N°Etat]) = @Outilhs)) 
OR (((Outil.[N°Marque]) = @NumMarque) 
AND ((Outil.[N°Etat]) = @Outilhs)) 
OR (((Outil.[N°Groupe]) = @NumFamille) 
AND ((Outil.[N°Etat]) = @Outilhs)) 
OR (((Affectation.[N°outil]) = @NumOutil 
AND (Affectation.[N°outil])=[Outil].[num outil]) 
AND ((Outil.[N°Etat]) = @Outilhs));

et enfin je lance ce code dans le query analyser:

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
 
DECLARE @Numservice int
DECLARE @NumMonteur int
DECLARE @NumAffaire int
DECLARE @NumMarque int
DECLARE @NumFamille int
DECLARE @NumOutil int
DECLARE @Outilhs int
 
SET @Numservice=1
SET @NumMonteur =""
SET @NumAffaire =""
SET @NumMarque =""
SET @NumFamille =""
SET @NumOutil =""
SET @Outilhs =2
 
 
EXECUTE [R affectation IPR modif] @Numservice, @NumMonteur, @NumAffaire, @NumMarque, @NumFamille, @NumOutil, @Outilhs

Normalement je devrais récuperer la liste des outils dont le service est 1 ET dont le N°Etat est égal à 2.
Or c'est pas le cas il me renvoie juste les outils dont l'état est égal à deux.

J'espère avoir été assez clair, si vous voulez + de renseignements n'hésitez pas

Merci d'avance.

PS le début de la requête fonctionne, c'est à partir de :

Code :

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

1
2
3
 
OR (((Affectation.[N°affaire]) = @NumAffaire) 
AND ((Outil.[N°Etat]) = @Outilhs))

que ça bug (le rest fonctionne aussi)

[Didier L]

Bonjour,

Je pense que tu as un problème avec les paranthèses

Netoyes celles qui ne servent à rien. Gardes juste les nécéssaires

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
WHERE 
(Outil.[N°Service] = @Numservice AND Outil.[N°Etat] = @Outilhs) 
OR 
(Affectation.[N°Monteur] = @NumMonteur AND Outil.[N°Etat]) = @Outilhs) 
OR 
(Affectation.[N°affaire] = @NumAffaire AND Outil.[N°Etat]) = @Outilhs) 
OR 
(Outil.[N°Marque] = @NumMarque AND Outil.[N°Etat]) = @Outilhs) 
OR 
(Outil.[N°Groupe] = @NumFamille AND Outil.[N°Etat]) = @Outilhs) 
OR 
(Affectation.[N°outil] = @NumOutil AND Affectation.[N°outil]=[Outil].[num outil] 
AND Outil.[N°Etat] = @Outilhs);

Et merci de penser aux balises de code

[Access Newbie]

tout d'abord merci

Mais hélas ce n'est pas bon j'ai la même erreur mais en fait je me rends compte que l'erreur vient de la condition:

Code :

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

1
2
 
OR ((Affectation.[N°affaire] = @NumAffaire) AND (Outil.[N°Etat] = @Outilhs))

mais je vois pas ce qui le gene surtout que ca fonctionne dans access...

[Didier L]

Tes paramètres sont déclaré en Int et tu leurs passes "" donc ils sont traduits normalement par 0 !

Est-ce une explication?

[Access Newbie]

ben je pensais que le mieux aurait été de mettre * mais ca fonctionne pas non plus

Je sais plus trop quoi faire...

[Access Newbie]

J'ai une nouvelle question

si dans le code vba de mon appli access j'avais ce code:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
Set db = CurrentDb()
Set hst = db.OpenRecordset("SELECT * FROM [R Monteur par N° Outil Modif] WHERE [N°Monteur]=" & supp)
 
While Not hst.EOF
    numoutil = hst("N°Outil")
    Set rst = db.OpenRecordset("SELECT * FROM [Outil] WHERE [N°Outil]=" & numoutil)
    rst.Edit
    rst("Affecte") = "n"
    rst.Update
    hst.MoveNext
    rst.Close
Wend

est ce que je doit faire une procédure stockée avec update ou bien je modifie juste la requête [R Monteur par N° Outil Modif] et je fais ensuite mon update via un OpenRecordset en ADO (puisque je suis en projet access)? (si ça se trouve les 2 fonctionnent ou peut-être aucun...)(si les 2 marche quel est le mieux?)

Concretement est-ce que je dois enlever les .addnew et les remplacer par une Proc Stockée INSERT, enlever les .update et les remplacer par une PS UPDATE et les .delete par des DELETE ?

Merci de votre participation

[Didier L]

Le mieux dans tous les cas aurait été de faire une requête update avec une jointure sur ta première requête "R Monteur par N° Outil Modif" plutôt que de parcourir un recordset.

Dans le cas d'un projet ADP il faut favorisé les traitement du coté serveur.
Au maximum le Insert, Delete , Update doivent être des PS.

Dons l'idéal est de faire une PS qui sera une requête Update de masse avec une jointure sur ta requête (ou équivalent) "R Monteur par N° Outil Modif" avec en paramètre le "N°Monteur"

Du style :

Code :

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

1
2
3
4
5
6
7
8
9
 
CREATE PROCEDURE MAJ_AFFECTE @Monteur int
AS
Update Outil Set Affecte='n'
From 
Outil Inner Join [R Monteur par N° Outil Modif] 
On Outil.[N°Outil]= [R Monteur par N° Outil Modif].[N°Outil]
Where
[N°Monteur]=@Monteur

Dans l'exemple la requête "[R Monteur par N° Outil Modif]" existe bien sur SQL.
Tu peux aussi directement mettre une clause select à la place du style:

Code :

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

1
2
3
4
 
From 
Outil Inner Join (Select ....From ... Where ...) Marq 
On Outil.[N°Outil]= Marq.[N°Outil]

[Access Newbie]

Merci didier c'es bien cool de prendre tout ce temps pour me répondre

Cependant, un problème résolu = dix nouveaux problèmes

Comment fait-on pour affecter une valeur aux paramètres d'une procédure stockée via vba? (dans mon appli access j'avais des formulaires liés a des requetes; dans mon projet acces j'ai remplacé la requête par une proc stockée mais le problème c'est qu'une PS a des arguments... En effet, quand j'ouvre le form il execute la PS (qui n'a pas de paramètres puisqu"elle est liée au form) et me demande de saisir la valeur des arguments de ma PS dans une textbox ... et c'est pas ce que je voudrais.... of course....

J'ai essayé d'affecter des valeurs aux arguments de ma PS avec ce code mais ca marche pas...

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
 
Dim bds As ADODB.Connection
Set bds = New ADODB.Connection
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
Dim param As ADODB.Parameter
Set param = New ADODB.Parameter
 
bds.ConnectionString = "UID=" + "sa" & ";PWD=" & "domas" & ";" & "Driver={SQL Server};Server=" & "pc100" & ";Database=" & "demo" & ";"
bds.Open
cmd.CommandText = "R affectation IPR modif2"
cmd.CommandType = adCmdStoredProc
param.Name = "@NumService"
param.type = adInteger
param.Value = 1
cmd.Parameters.Append param

Merci

[Didier L]

Bonjour,

Pour ma part, mes formulaires ne sont pas basé sur des PS mais sur des vues afin de pouvoir éventuellement modifier les données.

Ma vue est très générale et j'ouvre mon formulaire avec une clause Where.

Pas besoin de paramètre

Sinon si la source de mon formulaire est une ps du style "MaPs" et qu'elle attend un paramètre du style un nombre : "MaPs 2" à l'ouverture du formulaire je change en vba la source de mon formulaire et je lui affecte:

Code :

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

1
2
 
Me.recordsource = "MaPs " & MonParamètre

[Access Newbie]

Ma vue est très générale et j'ouvre mon formulaire avec une clause Where.

J'ai essayé de faire avec le recordsource et en lui mettant une claus where mais je suis pas sur que ce soit ce que tu voulais dire

Code :

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

1
2
3
4
5
 
 
Me.RecordSource = "test" & " WHERE (((Outil.N°Service) = " 
& Forms![Formulaire Affectation Outil IPR]![NumService].Value & ")
AND ((Outil.N°Etat) = " & Forms![Formulaire Affectation Outil IPR]![choix outilhs].Value & "))"

[Access Newbie]

Je viens d'essayer avec cette synthaxe, pas moyen non plus...

Code :

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

Me.RecordSource = "dbo" & " 1" & "," & "1"

la source d'enregistrement "dbo 1,1" spécifiée dans ce formulaire n'existe pas...

[Access Newbie]

Sinon j'ai la solution qui marche mais bon pas terrible pour les perf vu qu'on se sert pas de l'interet du client / serveur

Code :

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

1
2
3
4
 
Me.RecordSource = "SELECT ...  FROM ...  
WHERE (Outil.[N°Service] = " & Param1 &  
"AND Outil.[N°Etat] = " & Param2  & ")"

[Didier L]

Ta première syntaxe est presque bonne :

Code :

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

1
2
3
4
 
Me.RecordSource = "test" & " WHERE (((Outil.N°Service) = " 
& Forms![Formulaire Affectation Outil IPR]![NumService].Value & ")
AND ((Outil.N°Etat) = " & Forms![Formulaire Affectation Outil IPR]![choix outilhs].Value & "))"

sauf que si "Test" est une Vue il faut faire :

Code :

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

1
2
3
4
 
Me.RecordSource = "select * from test WHERE (((Outil.N°Service) = " 
& Forms![Formulaire Affectation Outil IPR]![NumService].Value & ")
AND ((Outil.N°Etat) = " & Forms![Formulaire Affectation Outil IPR]![choix outilhs].Value & "))"

Une PS s'appelle symplement par son nom suivi de ses paramètres séparé par des virgules, pour les vues, elles sont considérées comme des Tables donc il faut faire "Select * from ..."

[Access Newbie]

Je sais pas quoi dire
Si ce n'es tque t'es un dieu!!!

Merci encore

[Didier L]

Y a pas de quoi

Tout à ton service

A la prochaine

Bon Dev

[Access Newbie]

Salut,

Bon certes pour l'instant ca marche mais j'aimerais bien exploiter à fond les perfs apportés par l'architecture client / serveur, donc exploiter au mieux les procédures stockées.

Actuellement en faisant des vues et en mettant la condition where c'est très bien mais le server renverra quand même un grand nombre d'informations, et c'est ensuite le poste client qui se charge de filtrer les résultats.

Pourrais tu me montrer la stucture d'une de test PS et comment tu l'appel dans vba parce que chez moi le :

Code :

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

1
2
 
Execute "NomPS" & param

Ca fonctionne pas (il faudrait peut être créer un objet de type ADODB.command?)
Merci

[Didier L]

Plusieurs questions:
Ta PS renvoie des résultats ou non?
Tu veux t'en servir ou ? Dans un formulaire ou dans un état ? Dans le code?
Dans ton exemple :

Code :

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

1
2
 
Execute "NomPS" & param

Il manque un espace après le nim de ta PS:

Code :

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

1
2
 
Execute "NomPS " & param

[Access Newbie]

Ma ps renvoie bien des résultats (et elle les envoie dans un sous formulaire). Je pensais donc passer par le recordsource du formulaire mais quand j'essaye acces me dit que la source d'enregistrement n'existe pas.

qd j'essaye avec ce code, la PS se lance mais on me demande de saisir les parametres manuellement (c'est normal)

Code :

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

1
2
 
Me.RecordSource = "RaffectationIPRmodif2"

mais quand j'essaye de mettre les parametres directement ca ne fonctionne pas. J'essaye avec cett synthaxe:

Code :

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

1
2
 
Me.RecordSource = "RaffectationIPRmodif2 " & 1

Erreur: "La source d'enregistrement "RaffectationIPRmodif2 1" spécifiée dans ce formulaire n'existe pas."

[Didier L]

Bonjour et Méaculpa,

cela fait quelque temps que je n'avais pas travaillé avec les projets ADP

En fait dans ton formulaire tu as une propiété dans l'onglet "Données" qui est "Inpu Parameters" et il faut te servir de cette dernière dans le cas d'une PS.
Tu doit la remplir ainsi :

Code :

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

1
2
 
@Param1=1;@Param2=1

Si tu veux la changer en code tu doit faire :

Code :

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

1
2
 
InputParameters="@Param1=1,@Param2=1"

Attention un ; dans le formulaire et une , dans le code

[Access Newbie]

Encore une fois merci didier!!!

Mais j'ai encore un ptit problème:
lorsque j'execute ma requete via access (onglet PS et je lance ma PS), j'obtiens 510 résultats (c'est ce qu'il me faut)

Lorsque j'execute la même requête (via VB) avec les mêmes paramètres la requete me délivre tous les résultats (environ 600)
J'execute ce code VBA:

Code :

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

1
2
3
4
 
Me.InputParameters = "@Num_service=1, @Outil_hs=1"
Me.RecordSource = "AffectationIPRparService"
Me.Requery

(sachant que la PS "AffectationIPRparService" attend ces deux paramètres)

J'ai l'impression qu'il ne prend pas les deux parametres.(il prend juste Num_Service)

Ma PS "AffectationIPRparService":

Code :

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

1
2
3
4
5
6
7
8
9
10
 
Create PROCEDURE [dbo].AffectationIPRparService
@Num_service int,
@Outil_hs int
AS
SELECT ...
FROM ...
WHERE (Outil.[N°Service] = @Num_service AND Outil.[N°Etat] = @Outil_hs)
 
GO