Import de données d'un serveur IBM AS/400 dans Access

Méthode pour importer des données d'un serveur IBM AS/400 dans une table Access et pouvoir ainsi faire des analyses personnalisées.

Commentez Donner une note à l'article (5)

Article lu   fois.

L'auteur

Liens sociaux

Viadeo Twitter Facebook Share on Google+   

I. Introduction

Certaines entreprises possèdent l'ensemble de leurs données sur serveur AS/400. Système ô combien hermétique, mais ô 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/400.
Certes on peut toujours faire des queries mais ce n'est pas d'une convivialité folle.

Une solution parmi d'autres, est de se connecter au serveur et de rapatrier les données nécessaires dans une table Access ce qui permet de concilier les données AS/400 avec les commodités de la bureautique et les possibilités de traitement d'Access.

II. Petit rappel sur l'AS/400

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 adresse IP (192.170.xxx.xxx) par exemple.
Pour que la correspondance entre ces deux données soit effective, il faudra ajouter une ligne dans le fichier hosts du poste.

Image non disponible

Ce fichier pour un poste XP Pro est présent dans: \Windows\System32\Drivers\Etc

III. Définition de l'application

Pour étayer notre propos, nous allons simuler l'importation dans Access du chiffre d'affaires de la veille fait par un vendeur donné (dont le code est « V12 »).
Sur l'AS/400 les données sont stockées dans une bibliothèque et deux fichiers.
La bibliothèque s'appelle "GESTION".
Le premier fichier appelé "VENTES1" comprend entre autres les zones suivantes :

Nom zone Description Type Nb caractères
NOBON N° du 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 client Char 8
MOBON Montant du bon Packed 11


Le deuxième fichier appelé "VENDEUR" comprend entre autres les zones suivantes :

Nom zone Description Type Nb caractères
COVEN Code vendeur Char 4
NOVEN Nom vendeur Char 20


Voilà donc où sont stockées les informations dont nous avons besoin.

IV. Mise en place de l'application

Pour ce faire, nous pouvons utiliser deux techniques différentes.

IV-A. Solution sans ODBC et avec ADO

Cette solution a l'avantage d'être plus facile à déployer, mais nécessite plus de code VBA.

IV-A1. Prérequis

Le prérequis technique est simplement d'avoir sur le PC, le driver IBM de ClientAccess.

Pour avoir accès aux données nous allons nous servir du composant ADO (ActiveX Data Object).
Je vous recommande donc de lire les tutoriels consacrés au sujet ici.

IV-A2. Mise en place de la table et du formulaire de paramétrage de connexion

Ces paramètres sont ceux de l'adresse du serveur et les identifiants et mot de passe du profil autorisé sur l'AS/400.
En tout premier lieu et afin d'éviter des problèmes de maintenance en cas de changement d'AS/400 ou de changement d'utilisateur nous devons avoir un moyen simple de stocker ces informations.
Cela permettra de se connecter de façon automatique, lors d'opérations programmées par exemple la nuit.
Entre autres méthodes, un moyen simple consiste à créer une table "Tbl_Parametres".

Image non disponible


Nous mettrons la propriété "Masque de saisie" du champ "Password" sur "Mot de passe".

Image non disponible


Puis nous créons le formulaire correspondant :

Image non disponible


Là aussi nous mettrons la propriété "Masque de saisie" de la zone de texte "Password" sur "Mot de passe".
La propriété "Ajout autorisé" du formulaire doit être à Non.

Enregistrez puis ouvrez votre formulaire pour saisir vos paramètres :

Image non disponible


Nous avons donc nos paramètres de connexion à jour et en cas de modifications, il n'y aura pas besoin de retourner dans les pages de code.

IV-A3. Création de la structure de la table Access

Nous allons maintenant créer la table "Tbl_ImportCa" qui va nous servir à stocker les informations de l'AS/400.
Ouvrons donc une nouvelle table en mode "Création" et créons les champs suivants :

Nom Zone Description Type Propriété
Id_Import Numéro d'enregistrement d'importation Numéro auto Clé primaire
Id_Bon Numéro du bon Texte Taille champ : 10
Id_Vendeur Code du vendeur Char Taille champ : 4
Date_BonImport Date du bon au format texte Char Taille champ : 6
Id_Client Code du client Char Taille champ : 8
Mont_Bon Montant du bon Numérique Réel double
Nom_Vendeur Nom du vendeur Texte Taille champ : 20
Date_Bon Date du bon au format date Date/heure Format : jj/mm/aa


Ce qui nous donne dans le générateur :

Image non disponible


Deux constatations :
  - les champs de type Texte doivent avoir la même taille que sur le fichier AS/400 ;
  - la création de deux champs Date :
    l'un "Date_BonImport" est destiné à rapatrier les données Année, Mois, Jour sous format Texte par concaténation ;
    l'autre "Date_Bon" à convertir le premier en format Date.

Si nous voulions faire directement l'importation dans un champ Date, nous aurions une erreur « incompatibilité de type ».
Nous verrons plus tard comment faire la conversion.

IV-A4. Création du formulaire d'accueil

Nous allons maintenant créer un formulaire d'accueil "Frm_Accueil".
Dans ce formulaire, nous mettrons une zone de texte indépendante "Txt_DateBon" avec la propriété de format jj/mm/aa nous permettant de saisir la date à laquelle nous voulons avoir les résultats.

Image non disponible


En mode formulaire nous pouvons donc saisir une date ou en choisir une en nous aidant du calendrier intégré.

Image non disponible


Néanmoins comme nous voulons a priori avoir les résultats du dernier jour d'activité, et que l'entreprise travaille du lundi au samedi compris, il faut créer une fonction de remplissage automatique du champ sur l'événement "Ouverture" du formulaire.
Pour cela en mode création, nous allons sur les propriétés du formulaire :

Image non disponible


Le fait de cliquer sur le bouton en bout de ligne avec les trois petits points va nous ouvrir la fenêtre Microsoft Visual Basic sur l'événement que nous voulons renseigner.

Image non disponible


Nous pouvons donc compléter le code ainsi :

 
Sélectionnez

Option Compare Database

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) = vbMonday Then
        Me.Txt_DateBon.Value = DateAdd("d", -2, Date)
    Else
        ' Sinon la veille
        Me.Txt_DateBon.Value = DateAdd("d", -1, Date)
        ' On pourrait également inclure les jours fériés
    End If
End Sub

Enregistrons et ouvrons notre formulaire :

Image non disponible


Alors que nous sommes le 10/08/11, la zone de texte affiche bien le 09/08/11.
Nous aurions été le 08/08/11 (un lundi) la zone de texte aurait affiché le 06/08/11.

IV-A5. Module d'importation

Nous allons maintenant nous attacher au code nécessaire au remplissage de notre table.

Pour cela, ouvrons l'éditeur VBA (Alt + F11) et insérons un module.

Image non disponible


Dans l'explorateur de projet (Ctrl + R) nous avons bien un nouveau module "Module1".

Image non disponible


Positionnons la souris sur "Module1" et ouvrons la fenêtre de propriétés (F4).
Renommons "Module1" en "Mdl_Import".
Dans l'explorateur double-cliquons sur "Mdl_Import".
Nous sommes maintenant dans la fenêtre de saisie du code correspondant au module.
Si elle n'est pas présente nous ajoutons la ligne "Option Explicit".
Cette option nous obligera à déclarer toutes nos variables, et avoir ainsi un code plus performant.


Image non disponible


Il ne nous reste plus qu'à saisir le code de la procédure.

Procédure Import_Ca
Sélectionnez
		
Option Compare Database
Option Explicit
----------------------------------------------------------------------------------------------------------
Public Sub Import_Ca()
On Error GoTo Error_Import_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 Fld As ADODB.Field
    Dim StrNameAS400 As String        'Variable nom de l'AS/400
    Dim StrNameUser As String         'Variable nom utilisateur
    Dim StrPasswordUser As String     'Variable mot de passe utilisateur
    Dim Champ1 As String, Champ2 As String, Champ3 As String, Champ4 As String, Champ5 As String, Champ6 As String

'Autres variables
    Dim StrDate As String             'Variable Date de bon
    Dim strTabDelete As String
    Dim strSql As String
    Dim i As Integer

    ' Attribue des valeurs aux variables

    ' Transforme le contrôle date du formulaire d'accueil (par exemple 24/10/06) en texte de 6 caractères (061024)
    StrDat = Right(Form_Frm_Accueil.[Txt_DateBon], 2) & Mid(Form_Frm_Accueil.[Txt_DateBon], 4, 2) & Left(Form_Frm_Accueil.[Txt_DateBon], 2)

    ' Les trois variables suivantes vont chercher leur valeur dans la table « Paramètres »
    StrNameAS400 = Nz(DLookup("Name_AS400", "Tbl_Parametres"))
    StrNameUser = Nz(DLookup("User", "Tbl_Parametres"))
    StrPasswordUser = Nz(DLookup("Password", "Tbl_Parametres"))

    ' Nous supprimons les données de la table "Tbl_Import_Ca"
    DoCmd.SetWarnings False      'Stoppe les messages d'alerte
    strTabDelete = "DELETE * FROM [Tbl_ImportCa];"
    DoCmd.RunSQL strTabDelete    'Rétablit les messages d'alerte
    DoCmd.SetWarnings True

    ' Nous lançons la connexion.
    Set CnnAs400 = CreateObject("ADODB.connection")
    CnnAs400.Open "provider=IBMDA400;data source=" & StrNameAS400 & "", StrNameUser, StrPasswordUser

    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 GESTION.VENTES1 T01 " & _
             " JOIN GESTION.VENDEUR T02 " & _
             " ON T01.COVEN = T02.COVEN " & _
             " WHERE (T01.BONDA||T01.BONDM||T01.BONDJ = '" & StrDate & "' AND " & _
             " T01.COVEN = 'V12 ') "

    'Pour avoir notre champ Date1 nous faisons une concaténation sur l'année, le mois et le jour.
       
	RsAs400.Open strSql

    Do Until RsAs400.EOF
        i = 1
        For Each Fld In RsAs400.Fields
            Select Case i
            Case 1
                Champ1 = Nz(Fld.Value)
            Case 2
                Champ2 = Nz(Fld.Value)
            Case 3
                Champ3 = Nz(Fld.Value)
            Case 4
                Champ4 = Nz(Fld.Value)
            Case 5
                Champ5 = Nz(Fld.Value)
            Case 6
                Champ6 = Nz(Fld.Value)
            Case Else
            End Select
            i = i + 1
        Next Fld
        If Rsdb.State = 0 Then
            ' Ouverture de la table et remplissage
            Rsdb.Open "[Tbl_ImportCa]", Cnndb, adOpenKeyset, adLockOptimistic
        End If
        ' Attribution des valeurs aux champs correspondants
        With Rsdb
            .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

    '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 Tbl_Import_Ca Set Date_Bon " & _
                 "= Right([Date1],2) & '/' & Mid([Date1],3,2) & '/' & Left([Date1],2)"
    DoCmd.SetWarnings True

Exit_Import_Ca:
    'Ferme la connexion et libère les variables
    Rsdb.Close
    RsAs400.Close
	Call CloseConnection(Cnndb)
	Call CloseConnection(CnnAs400)
    Set CnnAs400 = Nothing
    Set Cnndb = Nothing
    Set RsAs400 = Nothing
    Set Rsdb = Nothing
Exit Sub

Error_Import_Ca:
MsgBox "Erreur importation " & Err.Number & "  " & Err.Description
Resume Exit_Import_Ca

End Sub
----------------------------------------------------------------------------------------------------------

Sub CloseConnection(Cnx As ADODB.Connection)
    With Cnx
        If .State = adStateOpen Then
           .Close
        End If
    End With
End Sub

Attention
Quand vous saisissez les critères d'une zone « Char » dans une requête, il faut bien tenir compte du nombre de caractères demandés.
Dans notre exemple le code vendeur 'V12' compte trois caractères, alors que la zone en prévoit quatre.
Il faut donc dans le critère mettre un espace supplémentaire entre les deux quotes, ce qui donne: « T01.COVEN = 'V12 ' » et non «T01.COVEN = 'V12' ».

D'autre part, il arrive que les zones AS/400 Année, Mois, Jour ne soient pas de type « Char » mais de type « Zoned ».
Dans ce cas la requête ne marche pas et il faut changer le mode de concaténation.

variation requête
Sélectionnez
				
    '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 GESTION.VENTES1 T01 " & _
             " JOIN GESTION.VENDEUR T02 " & _
             " ON T01.COVEN = T02.COVEN " & _
             " WHERE (Digits(T01.BONDA)Concat Digits(T01.BONDM)Concat Digits(T01.BONDJ) = '" & StrDate & "' AND " & _
             " T01.COVEN = 'V12 ') "

IV-A6. Appel de la procédure

Il ne nous reste plus qu'à appeler la procédure à partir du bouton de validation.

Procédure Import_Ca
Sélectionnez
	
Private Sub Cmd_Valid_Click()
  Call Import_Ca
  DoEvents
  MsgBox "L'importation est terminée."
End Sub

IV-B. Solution avec ODBC

À contrario de la technique vue plus haut, celle-ci nécessite peu ou pas de code, mais est plus longue à déployer sur de nombreux postes.
Nous n'aurons pas besoin de la table et du formulaire de paramètres, par contre nous garderons notre formulaire d'accueil avec le choix de date.

IV-B1. Prérequis

Le prérequis est comme dans la solution précédente simplement d'avoir sur le PC, le driver IBM de Client Access.

Nous allons dans ce cas nous servir des liaisons ODBC.
Pour la configuration des liaisons, vous pouvez vous aider du tutoriel de LedZeppII ici.

IV-B-2. Paramètre ODBC

Comme pour tout paramètre ODBC, allons dans le panneau de configuration, puis sur "Outils d'administration" et cliquons sur "Sources de données(ODBC)".

Si vous avez Vista 64 bits, Seven 64 bits ou Server 2008 64 bits avec Office 32 bits il faut passer par : C:\Windows\SysWOW 64\Odbcad32.exe.

Image non disponible


Cliquons sur "Ajouter" et choisissons le driver Client Access.

Image non disponible


Dans l'onglet "Général", donnons un nom à notre connexion et saisissons le nom de notre système.

Image non disponible


Puis dans l'onglet "Serveur", notons le nom de la ou des bibliothèques demandées.

Image non disponible

Si nous avions eu besoin de plusieurs bibliothèques, nous les aurions saisies dans la liste en les séparant par une virgule.

Validons. Notre source de données système apparaît bien dans la liste.

Image non disponible

IV-B-3. Requête directe

Nous allons pouvoir maintenant, envoyer dans l'AS/400 notre requête, afin d'en récupérer les données.
Pour cela, passons en mode création de requête. Ne mettons aucune table source.
Puis sélectionnons "SQL direct".

Image non disponible


Dans la fenêtre de propriété, sur le paramètre "Chaîne de connexion ODBC" servons nous des trois petits points pour aller rechercher notre source de données machines.

Image non disponible

Puis saisissons notre requête.

Image non disponible


Comme vous pouvez le voir, dans la condition Where une date définie est saisie.
Dans notre exemple, cette date va être conditionnée par le formulaire d'accueil.
Nous allons donc créer une procédure de modification de la requête, afin de lui donner le critère choisi dans le formulaire.

Procédure Maj_QryImport
Sélectionnez
	
Public Sub Maj_QryImport()  'Mise à jour de la requête Qry_Import + création des tables et remplissage
    On Error GoTo Error_Maj_QryImport
    Dim oDb As DAO.Database
    Dim StrDat As String
    Dim oQdf As DAO.QueryDef
    Dim StrSql As String

    'Attribue à la variable la valeur de la liste déroulante du formulaire d'accueil
    StrDat = Form_Frm_Accueil.[Txt_DateBon].Value
    StrDat = Right(StrDat, 2) & Mid(StrDat, 4, 2) & Left(StrDat, 2)

    Set oDb = CurrentDb
    'Redéfinit la requête en y ajoutant la variable date choisie
    Set oQdf = oDb.QueryDefs("Qry_Import")
    StrSql = " " & _
           " SELECT T01.NOBON, T01.COVEN, T01.BONDA||T01.BONDM||T01.BONDJ, T01.COCLI, T01.MOBON, T02.NOVEN " & _
           " FROM GESTION.VENTES1 T01 " & _
           " JOIN GESTION.VENDEUR T02 " & _
           " ON T01.COVEN = T02.COVEN " & _
           " WHERE (T01.BONDA||T01.BONDM||T01.BONDJ = '" & StrDat & "' AND " & _
           " T01.COVEN = 'V12 ') "

    'Applique la modification
    oQdf.SQL = StrSql

Exit_Maj_QryImport:
    oDb.Close
    Set oDb = Nothing
    Set oQdf = Nothing
    Exit Sub

Error_Maj_QryImport:
    If Err.Number = 3265 Then
        MsgBox "La requête n'existe pas"
    Else
        MsgBox Err.Number & "  " & Err.Description
    End If
    Resume Exit_Maj_QryImport
End Sub

Après il suffira avec l'assistant:
  - soit de faire une requête de création de table à partir de "Qry_Import" ;
  - soit de faire une requête "Ajout" pour alimenter une table déjà créée.

V. Conclusion

Voilà notre table Access est alimentée et nous pouvons maintenant créer les requêtes, formulaires et états souhaités avec toutes les possibilités offertes par Access.
Certaines extractions plus importantes peuvent être programmées la nuit, en faisant appel à une procédure lancée au démarrage de l'application.

Ceci est un exemple très simple qui peut bien sûr être complété en fonction des besoins. Par exemple :
- centraliser le fichier de paramètres sur un serveur ;
- introduire des variables pour les bibliothèques ;
- travailler sur une période avec une date de départ et d'arrivée ;
- alimenter une table vendeurs qui elle-même servira de source à une zone de liste en y joignant une variable ;
...

Comme vous le voyez les possibilités sont nombreuses, et vous pourrez choisir la technique qui vous convient le mieux.

VI. Remerciements

Un grand merci à toute l'équipe de Dvp et plus particulièrement :

Pour leurs remarques et conseils avisés :
. Dolphy35 ;
. LedZeppII ;
. Argyronet.


Pour ses corrections orthographiques et syntaxiques :
. _Max_.

Vous avez aimé ce tutoriel ? Alors partagez-le en cliquant sur les boutons suivants : Viadeo Twitter Facebook Share on Google+   

  

Copyright © 2012 Jean-Damien GAYOT. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.