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
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
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
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
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?