Access et AS400
L'entreprise dans laquelle je travaille a 90% de ses données sur As400. Système au combien rigide mais au combien fiable. Mais il arrive régulièrement qu'il y ait ponctuellement des analyses complémentaires à faire ne nécessitant pas forcément l'intervention de développeurs sur l'As.
Certes on peut toujours faire des Query mais ce n'est pas d'une convivialité folle.
Après avoir beaucoup ramé, beaucoup surfé et passé beaucoup de nuits blanches, j'ai réussi petit à petit à me faire un processus qui s'avère fiable et qui me permets d'avoir sur mon bureau, en arrivant à l'entreprise, tous mes états sortis en automatique pendant la nuit .
La difficulté pour un profane c'est qu'en fait le langage à appliquer est un mélange du langage Access et de langage Ibm.
Je vais donc essayer de vous faire bénéficier de ma petite expérience en sachant pertinemment que cela peut encore être amélioré et que n'étant pas du tout expert, il doit y avoir des moyens d'améliorer le code. Je compte sur tout le monde pour m'aider en cela.
Merci à tous les participants de CodeSourceS et Developpez.com qui ont permis à un non-informaticien d'apprendre quelques bases et de prélever ainsi les informations nécessaires.
Un petit rappel sur L'As400
L'architecture est basée sur des Bibliothèques (que l'on peut assimiler à des répertoires), dans lesquelles sont stockés des Fichiers (tables) avec différentes Zones (champs).
Il est défini par un nom du type 'S443A08G' ou par son adress IP (192.170.83.25) par exemple.
Un des autres problèmes auquel j'ai eu à faire face est que sur beaucoup de programmes les dates sont stockées dans plusieurs zones (une de 2 caractères pour le siècle, une de deux caractères pour l'année, une de 2 caractères pour le mois et une de 2 caractères pour le jour.
Prérecquis
Le prérecquis est simplement d'avoir sur le Pc , le driver Ibm de ClientAccess et le Mdac de Microsoft.
A ce propos si quelqu'un connait un driver libre, je suis preneur.
Définition de notre application
Pour étayer notre propos, nous allons simuler l'importation dans Access du chiffre d'affaire de la veille fait par un vendeur donné (dont le code est « V12 »).
Sur l'AS400 les données sont stockées dans une bibliothèque et deux fichiers.
La bibliothèque s'appelle « GESTCOM »
Le premier fichier appelé « AVENTP1 » et comprend entre autres les Zones suivantes
- NOBON (N° de bon) Char 8
- COVEN (Code vendeur) Char 4
- BONDA (Année du bon) Char 2
- BONDM (Mois du bon) Char 2
- BONDJ (Jour du bon) Char 2
- COCLI (Code du client) Char 10
- MOBON (Montant du Bon) Packed 11
Le deuxième fichier appelé « BVENDP1 » comprend entre autres les zones suivantes:
- COVEN (Code vendeur) Char 4
- NOVEN (Nom vendeur) Char 20
Voilà donc où sont stockées les informations dont nous avons besoin.
Nous allons maintenant créer une base access vide que nous appellerons Ca_vendeurs
1ère Etape
En tout premier lieu et afin d'éviter des problèmes de maintenance en cas de changement d'AS400 ou de changement d'utilisateur nous allons créer une table « Paramètres »
Nous allons créer un formulaire « Paramètres » basé sue cette table:
Mettre le masque de saisie du mot de passe sur password
La propriété Ajout autorisé du formulaire doit être à Non.
Pour finalement le remplir.
Nous avons donc nos paramètres de connexion à jour et en cas de modif, il n'y aura pas besoin de retourner dans les pages de code.
2ème Etape
Nous allons maintenant créer la table « Import_Ventes » qui va nous servir à stocker les informations de l'As400, avec des noms plus parlants. On peut le faire directement sous Vba pour garantir la sécurité de la base ou sous la forme classique Création de table:
Deux constatations:
-
Les champs Texte doivent avoir la même taille que sur le fichier As400
-
La création de deux champs date. L'un (Date1) est destiné à rapatrier les données Année, Mois,Jour sous forme texte
par concaténation, l'autre à convertir ce dernier en vrai format de date (Date_Bon). L'importation directe dans un champ Date entraînant
une « incompatibilité de type.. » Nous verrons plus tard comment faire la conversion.
3ème Etape
La constitution du formulaire d'accueil avec les paramètres de date.
Nous créons donc un contrôle indépendant appelé « DatBon » avec la propriété de format jj/mm/aa.
Comme nous voulons a priori avoir les résultats de la veille il faut créér une fonction de remplissage par défaut du champ sur l'évènement "Ouverture "du formulaire:
Private Sub Form_Open(Cancel As Integer)
' Si la date du jour = Lundi la date dans le contrôle doit être un samedi et non un Dimanche
If Weekday(Now) = 2 Then
Me.DatBon.Value = DateAdd("d", -2, Now)
Else
' Sinon la veille
Me.DatBon.Value = DateAdd("d", -1, Now)
' On pourrait également inclure les jours fériés
End If
End Sub
Ce qui fait que par exemple ce fichier ouvert le Jeudi 25 Octobre 2006 affichera automatiquement la date du Mercredi 24/10/06 et ce même fichier ouvert le Lundi 23 Octobre 2006 affichera la date du Samedi 21 Octobre.
4ème Etape
Nous pouvons maintenant procéder à l'importation des données.
Tout d'abord il faut s'assurer que la référence ADO est cochée
Pour cela :Outils - Références ADO Ext.
S'il n'est pas dans la liste chargez le Mdac téléchargeable chez Microsoft.
Nous allons créer un module qui sera lancé sur l'évènement Onclick du bouton OK ou sur l'ouverture du formulaire d'accueil suivant que l'on souhaite pouvoir modifier la date ou non.
Option Compare Database
Option Explicit
Public Function Imp_CA()
' variables de connexion ADO
Dim CnnAs400 As ADODB.Connection
Dim RsAs400 As ADODB.Recordset
Dim Cnndb As New ADODB.Connection
Dim Rsdb As New ADODB.Recordset
Dim strTabSupprimer As String
' variables paramètres
Dim Dat As String 'variable Date de bon
Dim Nas AsString 'variable nom de l'As400
Dim Nus As String 'Variable nom utilisateur
Dim Cus As String 'Variable Code Utilisateur
' Valeur des variables
' Transforme le controle date du formulaire d'accueil par exemple 24/10/06 en texte de 6 caractères 061024
Dat = Right(Form_Form_accueil.[DatBon], 2) & Mid(Form_Form_accueil.[DatBon], 4, 2) & Left(Form_Form_accueil.[DatBon], 2)
' Les trois variables suivantes vont chercher leurs valeurs dans la table « Paramètres »
Nas = Dlookup("Nom_AS400", "Paramètres")
Nus = Dlookup("Identifiant", "Paramètres")
Cus = Dlookup("Mot_Passe", "Paramètres")
' Nous supprimons les données de la table "Import_Ca"
DoCmd.SetWarnings False
strTabSupprimer = "DELETE * FROM [Import_Ca];"
DoCmd.RunSQL strTabSupprimer
DoCmd.SetWarnings True
' Nous lançons la connexion.
Set CnnAs400 = CreateObject("ADODB.connection")
CnnAs400.Open "provider=IBMDA400;data source=" & Nas & "", Nus, Cus ' Attention à l'orthographe, sinon galère
Set Cnndb = CurrentProject.Connection
Set RsAs400 = CreateObject("ADODB.recordset")
RsAs400.ActiveConnection = CnnAs400
' Nous créons la Requête.
strsql = " " & _
" select T01.NOBON ,T01.COVEN ,T01.BONDA||T01.BONDM||T01.BONDJ ,T01.COCLI ,T01.MOBON ,T02.NOVEN" & _
" from GESTCOM.AVENTP1 T01 " & _
" join GESTCOM.BVENDP1 T02 " & _
" On T01.COVEN = T02.COVEN " & _
" where (T01.BONDA||T01.BONDM||T01.BONDJ = '061025' AND T01.COVEN = ' V12' "
' Pour avoir notre champ Date1 nous faisons une concaténation et nous mettons dans la condition where une
date quelconque car l'As400 n'accepte pas directement la variable. Nous remplaçons la valeur bidon de la date par le contenu de la variable
strsql = Replace(strsql, "'061025'", Chr(39) & Dat & Chr(39))
RsAs400.Open strsql
Do Until RsAs400.EOF
i = 1
For Each Fld In RsAs400.Fields
Select Case i
Case 1
Champ1 = Fld.Value
Case 2
Champ2 = Fld.Value
Case 3
Champ3 = Fld.Value
Case 4
Champ4 = Fld.Value
Case 5
Champ5 = Fld.Value
Case 6
Champ6 = Fld.Value
Case Else
End Select
i = i + 1
Next Fld
If Rsdb.State = 0 Then
' ouverture de la table et remplissage
Rsdb.Open "[Import_Ca]", Cnndb, adOpenKeyset, adLockOptimistic
End If
With Rsdb
' attribution des valeurs aux champs correspondants
.AddNew Array("N°_Bon", "Code_Ven", "Date1", "Code_client", "CA_Bon", "Nom_Ven"), _
Array(Champ1, Champ2, Champ3, Champ4, Champ5, Champ6)
.Update
End With
RsAs400.MoveNext
Loop
' ferme la connexion
RsAs400.Close
Set RsAs400 = Nothing
Set Rsdb = Nothing
Set CnnAs400 = Nothing
Set Cnndb = Nothing
' La Table est remplie et il faut maintenant s'occuper du dernier champ qui nous intéresse
c'est à dire le champ "Date_Bon". Nous allons nous servir de la fonction Update.
DoCmd.SetWarnings False 'Stoppe les messages d'alerte
DoCmd.RunSQL "Update Import_Ca Set Date_Bon = Right([Date1],2) & '/' & Mid([Date1],3,2) & '/' & Left([Date1],2)"
DoCmd.SetWarnings True
End Function
Attention
Il arrive que les Zones As400 Années, Mois , Jour ne soient pas de Type « Char » mais de type « ZoneD ».
Dans ce cas la requête ne marche pas: il faut en changer le mode de concaténation.
' Nous créons la Requête
strsql = " " & _
" select T01.NOBON ,T01.COVEN ,Digits(T01.BONDA)Concat Digits(T01.BONDM)Concat Digits(T01.BONDJ) ,T01.COCLI ,T01.MOBON ,T02.NOVEN" & _
" from GESTCOM.AVENTP1 T01 " & _
" join GESTCOM.BVENDP1 T02 " & _
" On T01.COVEN = T02.COVEN " & _
" where (Digits(T01.BONDA)Concat Digits(T01.BONDM)Concat Digits(T01.BONDJ) = '061025' AND T01.COVEN = ' V12' "
Voilà notre table Access est alimentée et nous pouvons maintenant créer les Requêtes, Formulaires et Etats souhaités avec toutes les possibilités offertes par Access. Je conseille de vider les tables à la fermeture de la base pour allèger l'ensemble.
Dans le cas de réseau, les bases sont stockées sur le serveur pour toutes les taches automatiques de la nuit. Par contre si ce sont des taches susceptibles d'être utilisées par plusieurs personnes, il vaut mieux mettre une application runtime par poste.
Variations
Ceci est un exemple très simple qui peut bien sûr être complété. On peut travailler sur une période avec une date de départ et d'arrivée. On peut alimenter une table vendeurs et ensuite en sélectionner un dans une zone de liste en y joignant une variable. Tout est imaginable et j'ai de nombreuses applications qui tournent sur cette base.
Conclusion
Je suis très conscient que ce problème de liaison avec un As400 ne touche pas une majorité de gens, mais pour cette minorité, il y a beaucoup de mal à trouver quelque chose sur Internet. Alors si je peux mettre une petite pierre à l'édifice...