Modification BDD access depuis un userform Excel

Salut tout le monde!!

Je suis actuellement étudiant en DUT Génie Electrique et Informatique Industrielle et j'effectue mon stage de deuxième année dans une entreprise de menuiserie, entreprise dans laquelle je dois réaliser une gestion de stock.

L'entreprise utilise le logiciel Batigest et le problème se situe au niveau des retraits de ressources servant à fabriquer meubles, portes et autres produits finis.

L'idée est de créer une appli Excel (2013) échangeant avec une BDD Access inventoriant les différents produits utilisés par l'entreprise. A chaque retrait de matériel, l'employé scannera un code barre correspondant qui implémentera le nombre de ressources utilisées par N° de chantier et référence.

J'ai donc réaliser l'apparition du fenêtre pop-up à l'ouverture avec 3 boutons (Valider/annuler/fermer) un champ pour le numéros de chantier, un autre pour rentrer la référence (grâce à la lecture du code barre par la douhette) et un champ pour la quantité et la date.

Le hic c'est que mon cursus ne m'a pas appris à coder en VBA... J'ai donc réussis à faire des boutons zones de textes et autres composants élémentaires mais n'ai aucune idée de comment récupérer les données entrées dans les champs de textes et les mettre au bonne endroit et encore moins dialoguer entre Excel et Access... J'ai donc besoin de votre aide s'il vous plait

Une précédente intervention m'avait permis de réaliser l'application jointe.

ci-dessous le cahier des charges plus en détails

L’application :

Celle-ci sera réalisée sur Excel, recevra des données de Batigest (libellé, code article, nombre en stock,…) et agira sur les stocks présents dans Batigest.

Lors de son ouverture, 3 choix de modes s’offriront aux utilisateurs, mode Normal, mode Inventaire et le mode Modifcation.

I- Mode Normal

Le mode Normal (Sortie) disposera de 3 boutons tactiles :

• Valider : valide et renvoie au menu
• Annuler : annule la commande mais réaffiche le formulaire
• Fermer : valide et renvoie au menu principal

• N°Chantier : avec vérification pour voir si e numéro existe ou pas
• Code article : sera rempli en scannant le code barre
• Quantité : on rentre la quantité que l’on prend
• Date : remplie par défaut avec la date actuelle mais modifiable si besoin

C’est ce mode qui permettra de signaler une sortie de stock par un artisan. Celui-ci devra rentrer un numéro de chantier valide, puis scanner l’article dont il a besoin et enfin rentrer la quantité sortie du stock. La quantité sera en unité cohérente avec l’article et sera reconvertie par l’application afin de correspondre avec les unités Batigest. De plus celle-ci ne devra pas être supérieure à la quantité en stock.

II- Mode Inventaire

Le mode Inventaire disposera des 3 mêmes boutons mais n’aura que 3 champs :

• Code article (qui sera rempli en scannant le code barre)
• Quantité
• Date

De plus, la modification de la base de donnée ne se fera qu’une fois tous les articles traités et à la date voulue.

III- Mode Modification

Le Mode Modification permettra de modifier le stock d’un article si l’on s’aperçoit que le chiffre affiché dans la base de données ne correspond pas avec le stock physique. Il fonctionnera avec les mêmes principes que les modes ci-dessus.

Merci d'avance pour votre aide!

Bonjour Polo,

Ok, tu ne commences pas par le plus facile!

Pour te donner une première piste, voici un exemple de code pour récupérer le contenu d'une base Access (non protégée) quelconque dans un fichier excel. Pour l'utiliser :

1/ Tu copies/colles ce code dans un module ordinaire d'un nouveau fichier excel, tu crées un bouton sur une feuille et tu lui affectes la macro "lister_tables"

2/ En cliquant sur le bouton créé, on te demande de choisir un fichier Access dans l'arborescence de ton ordi, et le code va créer autant de nouvelles feuilles que de tables contenues dans la base et coller le contenu de chaque table dans l'onglet correspondant.

Pour ton projet, plutôt que de copier ce contenu sur une feuille, il me semble qu'il sera plus pertinent de placer le contenu de la (des) table(s) dans un tableau de variables pour ensuite utiliser la bonne info au bon endroit. Et par ailleurs, tu pourra peut être mettre le chemin et le nom de la base en dur dans le fichier excel.

Commence avec ça, ensuite pour écrire depuis excel vers une base access, ce sera du code du même genre que je pourra te donner par la suite.

Et s'il faut créer la base access depuis excel, c'est possible également.

Pierre

Option Explicit

Sub lister_tables()
Dim NDF As String
Dim Cnx As Object, Cat As Object, Tbl As Object

    NDF = NDF_A_LIRE
    If Not NDF = "Faux" Then
        Set Cnx = CreateObject("ADODB.Connection")
        Cnx.Open "DRIVER={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ=" & NDF

        Set Cat = CreateObject("ADOX.Catalog")
        Set Cat.activeconnection = Cnx

        Set Tbl = CreateObject("ADOX.Table")
        For Each Tbl In Cat.Tables
            If Tbl.Type = "TABLE" Then
                Sheets.Add After:=ActiveSheet
                ActiveSheet.Name = Tbl.Name
                lirecontenu NDF, Tbl.Name
            End If
        Next

        Cnx.Close
        Set Cnx = Nothing
        Set Cat = Nothing
        Set Tbl = Nothing
    End If
End Sub

Function NDF_A_LIRE() As String
    ChDrive (Left(ActiveWorkbook.Path, 1))
    ChDir ActiveWorkbook.Path
    NDF_A_LIRE = Application.GetOpenFilename("Fichiers Access,*.mdb;*.accdb")
End Function

Sub lirecontenu(NDF As String, Tbl As String)
Dim Requete As String, result As Integer

    Requete = "SELECT * FROM [" & Tbl & "] "
    result = Query(Requete, NDF)
End Sub

Function Query(Requete As String, BDD As String) As Integer
Dim Cnx As Object, Rst As Object
Dim Col_SQL As Integer, i As Long, j As Integer

    On Error GoTo errhdlr
    Set Cnx = CreateObject("ADODB.Connection")
    Cnx.Open "DRIVER={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ=" & BDD

    Set Rst = CreateObject("ADODB.Recordset")
    Rst.Open Requete, Cnx, 3
    Query = Rst.RecordCount

    Col_SQL = Rst.Fields.Count - 1
    ReDim Ent(Col_SQL)
    For i = 0 To Col_SQL
        ActiveSheet.Cells(1, i + 1).Value = Rst.Fields(i).Name
    Next i

    If Not Query = 0 Then
        Rst.movefirst
        ActiveSheet.Range("A2").CopyFromRecordset Rst
    End If

    Cnx.Close
    Set Cnx = Nothing
    Set Rst = Nothing
    Exit Function

errhdlr:
    Query = -1
    If Not Rst Is Nothing Then
        If Rst.State = 1 Then Rst.Close
        Set Rst = Nothing
    End If

    If Not Cnx Is Nothing Then
        If Cnx.State = 1 Then Cnx.Close
        Set Cnx = Nothing
    End If
    MsgBox (Err.Description)
End Function

Bonjour,

Tout d'abord je vous remercie infiniment pour votre réponse rapide et pertinente! Après avoir effectuer ce que vous avez dit j'aurai quelques questions. Tout d'abord la BDD access avec laquelle je travaille est très importante et lourde à transférer si bien qu'Excel sature, or je n'ai besoin que de 4 tables a savoir ELEMENTDEF, ELEMENTSTOCK, ELEMENTMVTSTOCK et CHANTIERDEF. Est ce qu'il est possible de ne télécharger que ces tables là? D'autre part comment actualiser/rafraichir les informations entre la BDD access et l'application excel?

Merci pour votre aide

Ok Polo,

Déjà pour commencer, on peut se tutoyer.

Le code précédent n'était qu'un exemple d'interface Excel/Access, en fait pour un usage courant il est inutile de charger l'ensemble d'une table et encore moins de plusieurs tables.

Pour commencer, voici la fonction "passe-partout" (que j'utilise dans mes appli) qui va te permettre d'interroger ET de mettre à jour ta base (code à mettre dans un module quelconque) :

Private Rcd() As Variant

Function Query(Req As String, BDD As String) As Long
Dim Cnx As Object, Rst As Object
Dim T As Variant, Col_SQL As Integer, i As Long, j As Long

    On Error GoTo errhdlr
    Set Cnx = CreateObject("ADODB.Connection")
    Cnx.Provider = "MSDASQL"
    Cnx.Open "DRIVER={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ=" & BDD

    If Left(Req, 6) = "SELECT" Then
        Set Rst = CreateObject("ADODB.Recordset")
        Rst.Open Req, Cnx, 3

        Col_SQL = Rst.Fields.Count - 1
        ReDim Rcd(Col_SQL, 0)
        For i = 0 To Col_SQL
            Rcd(i, 0) = Rst.Fields(i).Name
        Next i

        Query = Rst.RecordCount
        If Not Query = 0 Then
            ReDim Preserve Rcd(Col_SQL, Query) 
            ReDim T(Col_SQL, Query - 1)
            Rst.MoveFirst
            T = Rst.GetRows
            For i = 0 To UBound(T, 1)
                For j = 0 To UBound(T, 2)
                    Rcd(i, j + Head) = IIf(IsNull(T(i, j)), "", T(i, j))
                Next j
            Next i
        End If
    Else
        Cnx.Execute Req
        Query = 0
    End If

    Cnx.Close
    Set Rst = Nothing
    Set Cnx = Nothing
    Exit Function

errhdlr:
    If Not Rst Is Nothing Then If Rst.State = 1 Then Rst.Close
    If Not Cnx Is Nothing Then If Cnx.State = 1 Then Cnx.Close
    Set Rst = Nothing
    Set Cnx = Nothing
    Query = -1
    MsgBox (Err.Description)
End Function

C'est une fonction qui va te permettre de passer n'importe quelle requête Sql compatible avec le VBA (le Vba n'accepte pas tout le Sql standard)

Ensuite voici 3 procédures de base (à placer par exemple dans le même module que la fonction précédente) :

Sub Insert_DB(Tbl As String, Head As String, Data As String)
Dim Req As String
    Req = "INSERT INTO [" & Tbl & "]"
    If Not Head = "" Then Req = Req & " (" & Head & ")"
    Req = Req & " VALUES (" & Data & ")"
    lig = Sql.Query(Req, BDD)

End Sub

Sub Update_DB(Tbl As String, UPD As String, Cond As String)
Dim Req As String
    Req = "UPDATE [" & Tbl & "] SET " & UPD & " WHERE " & Cond
    lig = Sql.Query(Req, BDD)

End Sub

Sub Delete_DB(Tbl As String, Cond As String)
Dim Req As String
    Req = "DELETE FROM [" & Tbl & "]  WHERE " & Cond
    lig = Sql.Query(Req, BDD)

End Sub

Comme indiqué, ça va te permettre de créer ou de mettre à jour ou de supprimer un enregistrement dans ta base "BDD"(au fait tu aura initialisé la variable BDD avant)

Donc par exemple pour créer la 100ème ligne dans la table ELEMENTDEF, il suffira d'écrire : (ici je suppose que la table contient 3 champs : Id, Champs1, Champs2)

Dim BDD as String

    BDD = "C:\dossier_truc\sous-dossier_bidule\BaseAccess.accdb" ' A écrire une seule fois au début de ton code et à personaliser bien sûr
    Insert_DB "ELEMENTDEF", "Id, Champs1, Champs2", "100,'data1', 'data2'"

Tu aura remarqué : les données textes sont entourées par des quotes simples contrairement aux données numériques.

Si tu as des dates, elles seront au format mm/jj/aaaa et entourées par des dièses, ex pour le 20 mai 2016 : #05/20/2016#

Pour mettre à jour cette 100ème ligne, on écrira un truc genre:

    Update_DB "ELEMENTDEF", "Champs1='Data1up', Champs2='Data2up'", "Id=100"

Pour supprimer cette 100ème ligne, on dira :

    Delete_DB "ELEMENTDEF", "Id=100"

"Ben oui, mais comment je fais pour savoir que l'enregistrement à créer suivant sera le 100ème?"

T'inkiet, il suffit d'interroger la base via une tite fonction :

Function Get_Max_Id(Tbl As String, Head As String) As Long
Dim Req As String
    Req = "SELECT MAX(" & Head & ") FROM [" & Tbl & "]"
    Get_Max_Id = Query(Req, BDD)
    If Rcd(0, 1) = "" Then Get_Max_Id = 0 Else Get_Max_Id = CLng(Rcd(0, 1))
End Function

Le numéro du prochain enregistrement s'obtient alors facilement par un truc genre :

Userform1.TextBox1.Value = Get_Max_Id("ELEMENTDEF", "Id") + 1

On peut donc interroger la base par des SELECT. Un exemple simple et utile, pour alimenter une liste déroulante à partir d'un champs d'une table, une tite fonction :

Function Get_Combo(Chps As String, Tbl As String) As Variant()
Dim Req As String
    Req = "SELECT DISTINCT " & Chps & " FROM [" & Tbl & "]" & _
            " WHERE NOT ISNULL(" & Chps & ")"

    Req = Req & " ORDER BY " & Chps

    If Query(Req, 0) > 0 Then Get_Combo = Application.Transpose(Rcd) _
    Else Get_Combo = Array("")

End Function

Et pour alimenter un combobox (qui sera donc en ordre alfa et sans doublon : cf ORDER BY et DISTINCT), c'est simplement :

Userform1.ComboBox1.List = Get_Combo("ELEMENTDEF", "Champs1")

Pour finir, un exemple de fonction tirée d'une de mes appli, pour te montrer que la requête Sql peut inclure des jointures et des champs calculés (on peut aussi faire des agrégations):

Function Get_EvnParRsc(id As Long, dt1 As Date, dt2 As Date, Gen As String, Cat As String) As Variant()

    Req = "SELECT E.Genre, E.Categ, E.Deb, E.Fin, E.Hfin-E.Hdeb, R.Nom " & _
            " FROM ([Evnmnts] AS E" & _
            " INNER JOIN [Assoc] AS A ON A.Id_Ev=E.Id)" & _
            " INNER JOIN [Ressources] AS R ON R.Id=A.Id_Re" & _
            " WHERE R.Id=" & id & _
            " AND ((clng(E.Deb) BETWEEN " & CLng(dt1) & " AND " & CLng(dt2) & " )" & _
            " OR (clng(E.Fin)  BETWEEN " & CLng(dt1) & " AND " & CLng(dt2) & ")" & _
            " OR (clng(E.Deb)<" & CLng(dt1) & " AND clng(E.Fin)>" & CLng(dt2) & "))"

    If Not Gen = "" Then Req = Req & " AND E.Genre='" & Gen & "'"

    If Not Cat = "" Then Req = Req & " AND E.Categ='" & Cat & "'"

    Req = Req & " ORDER BY E.Genre ASC, E.Categ ASC"

    If Query(Req) > 0 Then Get_EvnParRsc = Application.Transpose(Rcd) _
    Else Get_EvnParRsc = Array("")

End Function

Un appel à une fonction de ce genre renvoie un tableau à 2 dimensions qu'on utilise ensuite comme n'importe quel tableau ordinaire.

Voici donc les bases d'une interface Excel/Access (une bonne source pour la syntaxe Sql : http://sql.sh/)

Pour la suite, fais-toi plez

Pierre

Bonjour Pierre et désolé d'avoir mis du temps à répondre,

J'ai fait ce que tu m'as dit en définissant la variable BDD au début seulement une erreur de compilation indiquant "instruction incorrecte à l'extérieur d'une procédure" en surlignant en bleu la ligne BDD = "...."

J'ai mis du temps à capter comment marcher tout ça mais arrête moi si je me trompe, en gros là on défini des fonctions que je peux réutiliser dans les userform c'est ça?

Du coup comment pointer les 4 tables dans Access dont j'ai besoin ?

et Comment synchroniser la base access pour qu'elle reçoive les données entrées avec l'appli?

En tout cas merci de ce que tu fais pour moi et tes explications sont on ne peut plus claires!!

Ok,

Plutôt qu'un long discours, ci-joint une démo constituée de 2 fichiers : un fichier Excel sans aucune donnée et un fichier accdb contenant 2 tables (et quelques données fictives) :

* une table 'Clients' (Id, Nom, Prénom)

* et une table 'Information' (Id, Id_C, Nte) => Id=le n° de la fiche info, Id_C= le n° de la fiche 'Client' à laquelle la fiche est rattachée, et Nte=l'info relative au client

Les 2 tables sont donc reliées par un index : Id <=> Id_C, relation un-à-plusieurs classique.

Les 2 fichiers sont à décompresser dans un même dossier de son Pc.

Le nom de la base (+ chemin) est initialisé dans le module 'Thisworkbook'

L'ensemble des fonctions de liaison avec la base Access est dans le module 'Sql'

La démo propose 2 fonctions principales :

* création d'une nouvelle fiche => bouton 'nouvelle fiche'

* consultation/modif des fiches => bouton 'Liste'

Dans la liste : un double-clic sur une ligne ouvre la fiche client pour consultation/modification

Dans la fiche : plusieurs boutons pour ajouter/supprimer une info à relier, enregistrer les modif, supprimer la fiche.

La fiche affiche l'Id, le nom, le prénom et la liste des info reliées au client (Nb : dans cette liste, pour l'affichage, des colonnes sont masquées=>largeur=0)

Le tout est fonctionnel (en tous les cas sur mon PC), sans code 'exotique', mais peut être pas optimisé.

L'objectif est de montrer la mise en oeuvre d'une liaison Excel/Access (et non de proposer une appli qui a une utilité réelle)

Avec cet exemple tu devrais mieux voir comment ça fonctionne

Pierre

Okay!!!

Je commence à voir comment tout ce petit monde s'articule merci beaucoup!!

Je vais pouvoir avancer sur mon appli, je reviendrai vers toi si jamais je rencontre de nouvelles difficultés !!

Merci encore Pierre tu me sauves la vie