STOCKAGE DE CHAMPS D'UN FICHIER DANS UN TABLEAU POUR REMPLISSAGE DE BASE MYSQL

Dim record As New adodb.Recordset
'CONNEXION A LA BASE DE DONNÉS
Dim Conn As New adodb.Connection 'Connecteur à la base
Dim Chaine As String 'Chaîne de connexion à ODBC


Private Type article
        codeArticle As String * 15 'correspond au premier champs
        designationArticle As String * 35
        designationAnglaise As String * 35
        designation2 As String * 35
        unité_de_mesure As String * 2
        codeSource As String * 1
        prixVente As String * 14
        coutUnitaireStandart As String * 14
        cycleDappro As String * 4
        dateModifEdition As String * 11
        edition As String * 4
        FormatPlan As String * 1
        dateDernMAJ As String * 11
        dateCreation As String * 11
        unitéAchat As String * 2
        coeff As String * 13
        unitedeVente As String * 2
        coeffUV As String * 13
        codeFamilleProduit As String * 2
        codeFamilleArticle As String * 4
        groupeProduction As String * 2
        caractere1 As String * 2
    End Type
    


Private Function FillArticles(LaLigne As String) As article

Dim TesDonnees() As String
Dim TmpArticle As article

TesDonnees = Split(LaLigne, ";")'créer un tableau avec les données 

With TmpArticle
    .codeArticle = TesDonnees(0)
    .designationArticle = TesDonnees(1)
    .designationAnglaise = TesDonnees(2)
    .designation2 = TesDonnees(3)
    .unité_de_mesure = TesDonnees(4)
    .codeSource = TesDonnees(5)
    .prixVente = TesDonnees(6)
    .coutUnitaireStandart = TesDonnees(7)
    .cycleDappro = TesDonnees(8)
    .dateModifEdition = TesDonnees(9)
    .edition = TesDonnees(10)
    .FormatPlan = TesDonnees(11)
    .dateDernMAJ = TesDonnees(12)
    .dateCreation = TesDonnees(13)
    .unitéAchat = TesDonnees(14)
    .coeff = TesDonnees(15)
    .unitedeVente = TesDonnees(16)
    .coeffUV = TesDonnees(17)
    .codeFamilleProduit = TesDonnees(18)
    .codeFamilleArticle = TesDonnees(19)
    .groupeProduction = TesDonnees(20)
End With

FillArticles = TmpArticle

'REMPLISSAGE DE LA TABLE         
       With TmpArticle
        'On crée la requête
        req2 = "INSERT INTO article VALUES ('" _
                & Replace(Trim(.codeArticle), "'", "\'") & "','" _
                & Replace(Trim(.designationArticle), "'", "\'") & "','" _
                & Replace(Trim(.designationAnglaise), "'", "\'") & "','" _
                & Replace(Trim(.unité_de_mesure), "'", "\'") & "','" _
                & Replace(Trim(.codeSource), "'", "\'") & "','" _
                & Replace(Trim(.edition), "'", "\'") & "','" _
                & Replace(Trim(.FormatPlan), "'", "\'") & "');"

        End With
        'On met à jour la base
        If Asc(TmpArticle.codeArticle) <> 0 Then
               With record
                    .ActiveConnection = Conn
                    .Source = req2
                    .Open
               End With
        End If

End Function



Private Sub form_Load()

'Initialisation
Chaine = "driver={MySQL ODBC 3.51 Driver};" _
& "database=ordre_de_fabrication;" _
& "server=localhost;" _
& "uid=root;" _
& "pwd=123456;" _
& "port=;" _
& "option=;" _
& "stmt=;"

'Connexion à MySQL
With Conn
.ConnectionString = Chaine
.Open
End With


Dim LaLigne As String
Dim LesArticles() As article
ReDim LesArticles(0)
Open "chemin du fichier/fichier" For Input As #1

    While Not EOF(1)
        Line Input #1, LaLigne
        LesArticles(UBound(LesArticles)) = FillArticles(LaLigne)
        ReDim Preserve LesArticles(UBound(LesArticles) + 1)
    Wend

Close #1

End Sub