Ajout ligne table dans Access

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.

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

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

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") & "#"

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?

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

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

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.

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

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

J'ai reussi à corriger la syntaxe C'est parfait merci.

Juste un truc, est-ce possible d'afficher un message quand la requête a fonctionner pour prévenir l'utilisateur que c'est ok?

Ok, c'est cool!

Pour le message, on peut ajouter au tout début du code, après les déclarations de variables :

On Error GoTo errhdlr

et tout à la fin un truc genre :

    ' ...
    Cnx.Close
    Set Cnx = Nothing
    Set Rst = Nothing
    msgbox ("Bravo t'es trop fort! :)")
    Exit sub

errhdlr:
    msgbox ("C'est la loose! :(" & vbCrLf & Err.Description)
end sub

Ce qui te donne un message de félicitation si ok, et sinon la description de l'erreur

Pierre

Magnifique une petite dernière chose J'aimerai pouvoir modifier une colonne en particulier , est ce possible d'envoyer une cellule dans accès et préciser donc la ligne et la colonne?

Ben oui, bien sûr,

par exemple pour modifier le lot et la coloration :

dim upd as string, condition as string

' ...
maTable = "analyse"

upd = "lot=" & activesheet.range("A2").value    ' numérique => sans quote
upd = upd & ",coloration='" & activesheet.range("A6").value & "'"   ' texte=> avec quote

condition = "Id=" &  activesheet.range("A1").value

Requete = "UPDATE  [" & maTable & "] SET " & upd & " WHERE " & condition

Set Rst = Cnx.Execute(Requete)
' ... 

il est impératif ici de connaitre l'ID de l'enregistrement ("la ligne"), ici dans l'exemple j'ai supposé qu'il est en A1

Pierre

Merci Tout fonctionne

Bonjour,

Je reviens vers vous, car je souhaiterai pouvoir mettre à jour tous les champs d'une ligne d'une table, j'ai essayé de modifié la requête qu'on m'avais donné pour l'update d'un champ mais je n'arrive pas à trouver le système pour modifier tous les champs, pouvez-vous m'aider?

Merci.

Quelqu'un serait-il m'aider?

Merci

Ben il suffit de continuer avec le principe indiqué le 10 février en complétant la variable "upd" :

upd= "champs1=" & range_truc

upd=upd & ",champs2=" & range_machin

upd=upd & ",champs3=" & range_bidule

avec ou sans quote si texte ou numérique

ou avec # si date => upd=upd & ",datedujour=#" & "03/22/2016" & "#" (en inversant mois et jour car format US)

Pierre

Bonjour,

Je déterre post de 2 ans, mais il m'a très bien aide à l'époque

Je suis entrain de faire un nouveau fichier fonctionnant également avec une BDD acces depuis Excel, je dois comme à l'époque injecter des données dans une table access, je sais le faire pas de soucis mais je tombe sur un hic car c'est la première fois que j'ai le cas. Je dois injecter 60 données différentes dans 60 colonnes d'une tables access, le problème étant que quand je rentre ma ligne avec les différents nom des colonnes access en VBA, à un moment j'ai un retour à ligne obligatoire du codeur VBA qui termine ma ligne avec une apostrophe et continue ma ligne en dessous mais cela me poser un soucis car cela ne fait plus partie de la même variable non? Je peux faire comment? Je vais avoir le même problème avec la variable pour les données à injecter, elle va être trop longue et donc retour à la ligne.

Voici un exemple de la variable en question:

VARIABLE QUE J'AIMERAI :

Entete = "id, contrat, civilite, nom, prenom, societe, adresse, ville, telephone, mail, codeclient, datedelocation, lieudelocation, soltype, aidant, bachelaterale, article1, article2, article3, article4, article5, article6, article7, article8, article9, article10, article11, article12, article13, article14, article15, article16, article17, article18, article19, article20, aprix1, aprix2, aprix3, aprix4, aprix5, aprix6, aprix7, aprix8, aprix9, aprix10, aprix11, aprix12, aprix13, aprix14, aprix15, aprix16, aprix17, aprix18, aprix19, aprix20, prix1, prix2, prix3, prix4, prix5, prix6, prix7, prix8, prix9, prix10, prix11, prix12, prix13, prix14, prix15, prix16, prix17, prix18, prix19, prix20, aremise1, aremise2, aremise3, aremise4, aremise5, aremise6, aremise7, aremise8, aremise9, aremise10, aremise11, aremise12, aremise13, aremise14, aremise15, aremise16, aremise17, aremise18, aremise19, aremise20, remise1, remise2, remise3, remise4, remise5, remise6, remise7, remise8, remise9, remise10, remise11, remise12, remise13 , remise14, remise15, remise16, remise17, remise18, remise19, remise20"

MAIS LE codeur VBA ME LE FRACTIONNE AINSI:

Entete = "id, contrat, civilite, nom, prenom, societe, adresse, ville, telephone, mail, codeclient, datedelocation, lieudelocation, soltype, aidant, bachelaterale, article1, article2, article3, article4, article5, article6, article7, article8, article9, article10, article11, article12, article13, article14, article15, article16, article17, article18, article19, article20, aprix1, aprix2, aprix3, aprix4, aprix5, aprix6, aprix7, aprix8, aprix9, aprix10, aprix11, aprix12, aprix13, aprix14, aprix15, aprix16, aprix17, aprix18, aprix19, aprix20, prix1, prix2, prix3, prix4, prix5, prix6, prix7, prix8, prix9, prix10, prix11, prix12, prix13, prix14, prix15, prix16, prix17, prix18, prix19, prix20, aremise1, aremise2, aremise3, aremise4, aremise5, aremise6, aremise7, aremise8, aremise9, aremise10, aremise11, aremise12, aremise13, aremise14, aremise15, aremise16, aremise17, aremise18, aremise19, aremise20, remise1, remise2, remise3, remise4, remise5, remise6, remise7, remise8, remise9, remise10, remise11, remise12,"
remise13 , remise14, remise15, remise16, remise17, remise18, remise19, remise20"

MERCI

Bonjour,

Si ta base existe déjà, ce qui peut être élégant c'est de lire directement l'entête dans la table en question. Ce qui évite une écriture un peu lourde (donc source d'erreur).

Une ch'tite fonction générique:

Function EnteteBDD(BDD As String, Table As String) As String
Dim Cnx As Object, Rst As Object
Dim S As String, i As Long

    Set Cnx = CreateObject("ADODB.Connection")
    Cnx.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & BDD
    ' ou bien :
    'Cnx.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & BDD
    ' ou bien :
    'Cnx.Open "DRIVER={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ=" & BDD

    Set Rst = CreateObject("ADODB.Recordset")
    Rst.Open "SELECT * FROM " & Table, Cnx, 3

    For i = 0 To Rst.Fields.Count - 1
        S = S & Rst.Fields(i).Name & ", "
    Next i
    EnteteBDD = Left(S, Len(S) - 2)

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

L'appel se fait alors tout simplement:

sub Test
Dim Entete as string

    Entete = EnteteBDD("C:\chemin\Base.accdb", "Table_à_lire") ' A adapter évidemment

    Debug.print entete ' Pour visualiser le résultat
End sub

Trop facile, non?

Rechercher des sujets similaires à "ajout ligne table access"