Ajout ligne table dans Access

Y compris Power BI, Power Query et toute autre question en lien avec Excel
Répondre
b
boulezor
Membre habitué
Membre habitué
Messages : 51
Inscrit le : 14 juin 2012
Version d'Excel : 2010

Message par boulezor » 8 février 2016, 17:27

Bonjour à tous,

J'ai un problème sur un fichier que j'essaye de mettre en place. J'aimerai depuis excel ajouter une ligne à une table access via une macro ou plus simple si c'est possible, et en reprenant certaines cellules bien précise d'une feuille excell.

Est-ce possible?

Merci.
Avatar du membre
pierrep56
Membre impliqué
Membre impliqué
Messages : 1'197
Appréciations reçues : 197
Inscrit le : 18 juin 2014
Version d'Excel : 2016

Message par pierrep56 » 8 février 2016, 19:25

Bien sûr c'est possible!

Avec un code genre :
Dim Cnx As Object, Rst As Object
Dim Col_SQL As Integer, i As Long, j As Integer
Dim Requete As String    

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

    Requete = "INSERT INTO [" & maTable & "] (" & Entete & ") VALUES (" & Data & ")"

    Set Rst = Cnx.Execute(Requete)
    Cnx.Close
    Set Cnx = Nothing
    Set Rst = Nothing
dans lequel :
* Chemin & BDD est du genre "c:\monDossier\Mabase.accdb"
* MaTable est le nom de la table
* Entete est du genre "Id, Champs1, Champs2, ..."
* Data est du genre Range("A1") & "," & Range("C2") & "," ...
sans oublier les quotes si champs texte

Pierre
b
boulezor
Membre habitué
Membre habitué
Messages : 51
Inscrit le : 14 juin 2012
Version d'Excel : 2010

Message par boulezor » 9 février 2016, 09:28

Merci pour ces infos, c'est super mais je suis un peu nulle avec la syntaxe, je n'arrive pas à faire fonctionner la macro

 Cnx.Open "DRIVER={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ=" & "C:\Users\benedicte\Documents\analyse.accdb"

    Requete = "INSERT INTO [" & analyse & "] (" & N°Enregistr, Lots, type, Date d'analyse, PH, Densité, Coloration, T°, Alcool, Gout, Hectolitre, Saturation, A/C, FT/BT, & ") VALUES (" & Range("A1") & "," & Range("A2")& "," & Range("A3")& "," & Range("A4")& "," & Range("A5")& "," & Range("A6")& "," & Range("A7")& "," & Range("A8")& "," & Range("A9")& "," & Range("A10")& "," & Range("A11")& "," & Range("A12")& "," & Range("A13") ")
Est-ce possible de me dire les fautes de syntaxe? Et pour le numéro d'enregistrement automatiquement, je fais comment ? Access le crée de lui même? Dois je le renseignement alors dans les entêtes de la BDD dans la macro?

Tu parles de quotes pour du texte, il y a en effet des colonnes de texte dans access, je dois faire comment alors?

Merci
Avatar du membre
pierrep56
Membre impliqué
Membre impliqué
Messages : 1'197
Appréciations reçues : 197
Inscrit le : 18 juin 2014
Version d'Excel : 2016

Message par pierrep56 » 9 février 2016, 11:43

Bonjour Boulezor,

Pour commencer, en effet il faut créer le numéro d'enregistrement (qui ne fait pas ici tout seul).
Pour cela, tu peux utiliser la fonction suivante, que tu mets tel que dans un coin d'un module quelconque :
Function Max_Id(Table As String, Head As String) as long
Dim Cnx As Object, Rst As Object
Dim requete As String, T As Variant

    Max_Id = 0
    Set Cnx = CreateObject("ADODB.Connection")
    Cnx.Provider = "MSDASQL"
    Cnx.Open "Driver={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ=" & "C:\Users\benedicte\Documents\analyse.accdb"

    requete = "SELECT MAX(" & Head & ") FROM [" & Table & "]"

    Set Rst = CreateObject("ADODB.Recordset")
    Rst.Open requete, Cnx, 3

    If Not Rst.RecordCount = 0 Then
        ReDim T(Rst.Fields.Count - 1, Rst.RecordCount - 1)
        Rst.MoveFirst
        T = Rst.GetRows
        If Not IsNull(T(0, 0)) Then Max_Id = T(0, 0)
    End If

    Cnx.Close
    Set Rst = Nothing
    Set Cnx = Nothing
End Function
(l'idée est de trouver le n° maxi existant dans la table pour déterminer le n° suivant)

pour la suite je te suggère d'utiliser des variables pour composer la requête :
dim Id as long, Entete as string, Data as string

De cette façon :
1/ on récupère le 'N°Enregistr' auquel on ajoute 1 (pour le n° suivant) :
Id=Max_Id("analyse", "N°Enregistr")+1
2/ on écrit l'entête :
entete="N°Enregistr, Lots, type, Date d'analyse, PH, Densité, Coloration, T°, Alcool, Gout, Hectolitre, Saturation, A/C, FT/BT"
3/ on écrit les data :
data= Id & "," & Range("A1") & "," & Range("A2")& "," & Range("A3") ...
* pour les champs numérique ce sera du type : & "," & Range("A2") & "," & ' sans quote
* pour les champs texte ce sera du type : & ",'" & Range("A2") & "'," & ' avec quote
4/ et enfin on compose la requête :
Requete = "INSERT INTO [" & maTable & "] (" & Entete & ") VALUES (" & Data & ")"

pour avoir au final un truc du genre :
Dim Cnx As Object, Rst As Object
Dim Col_SQL As Integer, i As Long, j As Integer
Dim Requete As String, Id as long, maTable as string, Entete as string, Data as string    

    Set Cnx = CreateObject("ADODB.Connection")
    Cnx.Open "DRIVER={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ=" & "C:\Users\benedicte\Documents\analyse.accdb"

    maTable="analyse"

    Id=Max_Id(maTable, "N°Enregistr")+1

    entete="N°Enregistr, Lots, type, Date d'analyse, PH, Densité, Coloration, T°, Alcool, Gout, Hectolitre, Saturation, A/C, FT/BT,"

    data= Id & ","  & Range("A1") & ",'" & Range("A2")& "'," & Range("A3") ' /!\à finaliser correctement/!\

    Requete = "INSERT INTO [" & maTable & "] (" & Entete & ") VALUES (" & Data & ")"

    Set Rst = Cnx.Execute(Requete)
    Cnx.Close
    Set Cnx = Nothing
    Set Rst = Nothing
(le nombre d'item de 'data' doit correspondre exactement au nombre d'item de 'entete')

Bonne journée
Pierre

PS. pour les champs date, il faut les entourer de dièses : & "#" & Range("A3") & "#"
b
boulezor
Membre habitué
Membre habitué
Messages : 51
Inscrit le : 14 juin 2012
Version d'Excel : 2010

Message par boulezor » 9 février 2016, 13:39

Merci pour cette aide :)

Il y a par contre une erreur quand je relance la requête avec le n° d'enregistrement, Excel me mets en fluo le code :

Rst.Open Requete, Cnx, 3

dans la function MAX-ID,

Pourquoi?
b
boulezor
Membre habitué
Membre habitué
Messages : 51
Inscrit le : 14 juin 2012
Version d'Excel : 2010

Message par boulezor » 9 février 2016, 14:30

j'ai simplifié le nom de mes colonnes access pour éviter les espace, cela a passé une étape on dirait, mais il me dit erreur de syntaxe mais laquelle?
Sub Macro1()

Dim Cnx As Object, Rst As Object
Dim Col_SQL As Integer, i As Long, j As Integer
Dim Requete As String, Id As Long, maTable As String, Entete As String, Data As String

    Set Cnx = CreateObject("ADODB.Connection")
    Cnx.Open "DRIVER={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ=" & "C:\Users\benedicte\Documents\analyse.accdb"

    maTable = "analyse"

    Id = Max_Id(maTable, "id") + 1

    Entete = "id, lots, type, date, ph, densite, coloration, temperature, alcool, gout, hectolitre, saturation, ac, ftbt,"

    Data = Id & "," & Range("A1") & ",'" & Range("A2") & "'," & Range("A3") & "'," & Range("A4") & "'," & Range("A5") & "'," & Range("A6") & "'," & Range("A7") & "'," & Range("A8") & "'," & Range("A9") & "'," & Range("A10") & "'," & Range("A11") & "'," & Range("A12") & "'," & Range("A13")

    Requete = "INSERT INTO [" & maTable & "] (" & Entete & ") VALUES (" & Data & ")"

    Set Rst = Cnx.Execute(Requete)
    Cnx.Close
    Set Cnx = Nothing
    Set Rst = Nothing
 
End Sub
Avatar du membre
pierrep56
Membre impliqué
Membre impliqué
Messages : 1'197
Appréciations reçues : 197
Inscrit le : 18 juin 2014
Version d'Excel : 2016

Message par pierrep56 » 9 février 2016, 15:20

Ok, 2 points :

* l'entête ne doit pas finir par une virgule:
Entete = "id, lots, type, date, ph, densite, coloration, temperature, alcool, gout, hectolitre, saturation, ac, ftbt"
* les champs doivent être délimités par des quotes des 2 côtés!
Data = Id & "," & Range("A1") & ",'" & Range("A2") & "','" & Range("A3") & "','" & Range("A4") & "','" & Range("A5") & "','" & Range("A6") & "','" & Range("A7") & "','" & Range("A8") & "','" & Range("A9") & "','" & Range("A10") & "','" & Range("A11") & "','" & Range("A12") & "','" & Range("A13") & "'"
pour obtenir un résultat genre 2,3,'truc','bidule','machin' etc ...

Remarque, dans ce cas précis on peut écrire plus simplement :
data=id & "," & Range("A1").value
for i = 2 to 13
    data=data & ",'" & range("A" & i).value & "'"
next i
et ce serait mieux de spécifier la feuille par ex: activesheet.range("A" & i).value

Pierre
j
jmd
Fanatique d'Excel
Fanatique d'Excel
Messages : 10'599
Appréciations reçues : 250
Inscrit le : 8 décembre 2007
Version d'Excel : 365 + PowerBI

Message par jmd » 9 février 2016, 15:30

bonjour à vous

attention : il peut être extrêmement dangereux de manipuler des tables Acces (ajout/modif/suppression d'enregistrements) si dans Access il y a des scripts associés à ces tables.
Apprenez les fonctions d'Excel.
Exemple "Mettre sous forme de tableau", TCD, "Récupérer des données".
Apprendre les fonctionnalités "récentes".
b
boulezor
Membre habitué
Membre habitué
Messages : 51
Inscrit le : 14 juin 2012
Version d'Excel : 2010

Message par boulezor » 9 février 2016, 15:58

Pas de script dans Access donc pas de soucis, par contre toujours une erreur de syntaxe :( Je ne comprends pas.
Sub Macro1()

Dim Cnx As Object, Rst As Object
Dim Col_SQL As Integer, i As Long, j As Integer
Dim Requete As String, Id As Long, maTable As String, Entete As String, Data As String

    Set Cnx = CreateObject("ADODB.Connection")
    Cnx.Open "DRIVER={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ=" & "C:\Users\benedicte\Documents\analyse.accdb"

    maTable = "analyse"

    Id = Max_Id(maTable, "id") + 1

    Entete = "id, lots, type, date, ph, densite, coloration, temperature, alcool, gout, hectolitre, saturation, ac, ftbt"

    Data = Id & "," & Range("A1").Value
    For i = 2 To 13
    Data = Data & ",'" & ActiveSheet.Range("A" & i).Value & "'"
    Next i
    
    Requete = "INSERT INTO [" & maTable & "] (" & Entete & ") VALUES (" & Data & ")"

    Set Rst = Cnx.Execute(Requete)
    Cnx.Close
    Set Cnx = Nothing
    Set Rst = Nothing
 
End Sub
Avatar du membre
pierrep56
Membre impliqué
Membre impliqué
Messages : 1'197
Appréciations reçues : 197
Inscrit le : 18 juin 2014
Version d'Excel : 2016

Message par pierrep56 » 9 février 2016, 17:52

Quelques vérif possibles :
* vérifie l'orthographe des noms des champs, de la base, de la table
* est-ce que tes cellules contiennent des apostrophes? dans ce cas, il faut écrire :
Data & ",'" & replace(ActiveSheet.Range("A" & i).Value,"'","''") & "'"
* teste la chaine de sortie Data (genre un 'msgbox Data' avant la requete), pour voir s'il n'y a pas d'anomalie
* teste la chaine de sortie 'requete' pour voir si c'est conforme à la syntaxe sql
Répondre
  • Sujets similaires
    Réponses
    Vues
    Dernier message