VBA / SQL : INSERT INTO si ca n'existe pas, REPLACE si ca existe
Bonjour,
je developpe une application sous VBA qui servira à alimenter la base de donnée MySQL en references de pieces et ensemble CAO.
j'ai reussit à transferer les donnée à la BDD. par contre j'ai un petit soucis, c'est quand la reference existe dans la BDD je veux la remplacer la chose que je n'arrive pas à faire si non la rajouter.
mon programme actuel qui marche est le suivant :
Sub BDD ()
'***Données à rajouter dans la base des données ***
Dim numero As String
Dim description_courte As String
Dim description_longue As String
Dim revision As String
Dim cree_le As String
Dim modifie_le As String
Dim createur As String
Dim etat As String
Dim matiere As String
Dim peinture As String
Dim cout_estime As String
Dim projet As String
Dim Machine As String
Dim fournisseur As String
Dim cathegorie As String
numero = "1012 1001_V0"
description_courte ="Ensemble Godet"
description_longue = "RAS"
revision ="0"
cree_le ="2019-10-10"
modifie_le ="2019-10-10"
createur ="Ben"
etat ="En cours"
matiere ="RAS"
peinture ="N/A"
cout_estime ="RAS"
projet ="1012"
Machine ="Chargeuse sur pneus"
fournisseur ="BM"
cathegorie ="ipt"
' ----------------------------------------------------------------------------Mise à jour de la BBD
Dim cn As ADODB.Connection
Dim Server_Name As String
Dim Database_Name As String
Dim User_ID As String
Dim Password As String
Dim SQLStr As String
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
'------ Affectation de valeurs aux variables liés à la réservation & libération validation
in0_out1 = "0"
valide = "0"
'*** requet SQL ***
SQLStr = "insert into table01 (Numero,Description_courte,Description_longue,Revision,Cree_le,Modifie_le,Etat,Matiere,Peinture,Cout_estime,Projet,Machine,Fournisseur,Auteur,Cathegorie,in0_out1,valide) values ('" & numero & "','" & description_courte & "','" & description_longue & "','" & revision & "','" & cree_le & "','" & modifie_le & "','" & etat & "','" & matiere & "','" & etat & "','" & peinture & "','" & cout_estime & "','" & projet & "','" & Machine & "','" & fournisseur & "','" & cathegorie & "','" & in0_out1 & "','" & valide & "')"
Server_Name = "127.0.0.1"
Database_Name = "base"
User_ID = "root"
Password = "m02pas"
Set cn = New ADODB.Connection
'*** connexion ***
cn.Open "Driver={MYSQL ODBC 8.0 Unicode Driver};Server=" & Server_Name & ";Database=" & Database_Name & ";Uid=" & User_ID & ";Pwd=" & Password & ";Trusted_Connection=yes;"
rs.Open SQLStr, cn, adOpenStatic
'*** fermeture de la connexion ***
'rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
MsgBox "MAJ BDD Reussit"
' ----------------------------------------------------------------------------Fin de la Mise à jour de la BBD
End Sub
merci d'avance pour votre aide.
J'ai essayé ce code sur MySQL mais ca ne marche pas :
BEGIN
INSERT INTO table01 (Numero,Description_courte,Description_longue,Revision,Cree_le,Modifie_le,Etat,Matiere,Peinture,Cout_estime,Projet,Machine,Fournisseur,Auteur,Cathegorie,in0_out1,valide) values('1055 6004_V0','Description_courte','Description_longue','Revision','2019-10-10','2019-10-10','at','Matiere','Peinture','Cout_estime','Projet','Machine','Fournisseur','Auteur','Cie','0','0') WHERE NOT EXISTS (select* from table01 where Numero like '1055 6004_V0')
UPDATE table01 set 'Description_courte' ='hobbb' where 'Numero' like '1055 6004_V0' ..
COMMIT
Bonjour,
La manip est simple :
1/ faire une recherche de la référence :
"SELECT Numero FROM table01 WHERE Numero='1055 6004_V0'"
2/ tester le retour
3/ si retour => UPDATE
4/ si pas d'enregistrement retourné => INSERT
Pierre
J'avais essayé ca mais ca genere des erreur ODBC lié au RecordSet.
c'est pour ca je prefere que ca soit au niveau de l'instruction Sql.
Voici une proposition :
Dans le fichier Module_Mysql.txt du code générique pour se connecter à du Mysql. Il suffit de renseigner la version du driver utilisé et les bons identifiants de connexion. Ce code est à copier/coller dans un module ordinaire.
Avec ce code çà devient simple, du genre :
Req = "SELECT Numero FROM table01 WHERE Numero='1055 6004_V0'"
Connect_MySql
T = Select_Db(Req, 1)
Close_Cnx
Connect_MySql
If UBound(T) = 1 Then ' enregistrement n'existe pas
Req = "INSERT INTO table01 VALUES etc ..."
Else ' enregistrement existe
Req = "UPDATE table01 SET etc ... WHERE Numero='1055 6004_V0'"
End If
Cnx.Execute Req
Close_Cnx
Pierre
Merci Pierre pour ton temps et toutes tes explications.
J'ai essayé ton programme il est logique et bien claire.
par contre j'ai un probleme avec le Primary Key de ma base de donnéemais je ne sais pas ce que c'est comme probleme, voila le message d'erreur que j'ai eu apres avoir lancé le programme :
Option Explicit
Public Cnx As Object, Rst As Object
Dim Server_Name As String
Dim Database_Name As String
Dim User_ID As String
Dim Password As String
Dim SQLStr As String
Dim rs As ADODB.Recordset
'Set rs = New ADODB.Recordset
' ***********************************************************************
' ***** *****
' ***** CODE PierreP56 : http://tatiak.canalblog.com/ *****
' ***** *****
' ***********************************************************************
Sub b()
Dim Req As String
Dim T As Variant
Req = "SELECT Numero FROM table01 WHERE Numero='1055 6004_V0'"
Connect_MySql
T = Select_Db(Req, 1)
Close_Cnx
Connect_MySql
If UBound(T) = 1 Then ' enregistrement n'existe pas
Req = "insert into table01 (`Numero`,`Description_courte`,`Cree_le`,`Modifie_le`) values ('1055 6004_V0','nisrine','2019-10-10','2019-10-10')"
Else ' enregistrement existe
Req = "UPDATE table01 SET `Description_courte`='nisrine' WHERE Numero='1055 6004_V0'"
End If
Cnx.Execute Req
Close_Cnx
End Sub
Sub Connect_MySql(Optional x As Byte)
Server_Name = "127.0.0.1"
Database_Name = "base"
User_ID = "root"
Password = "m02pas"
Set Cnx = CreateObject("ADODB.Connection")
Cnx.Provider = "MSDASQL"
Cnx.Open "Driver={MYSQL ODBC 8.0 Unicode Driver};Server=" & Server_Name & ";Database=" & Database_Name & ";Uid=" & User_ID & ";Pwd=" & Password & ";Trusted_Connection=yes;"
Set Rst = CreateObject("ADODB.Recordset")
End Sub
Sub Close_Cnx(Optional x As Byte)
On Error Resume Next
If x > 0 Then Rst.Close
Cnx.Close
Set Cnx = Nothing
Set Rst = Nothing
End Sub
Function Select_Db(Req As String, Optional Head As Byte = 1) As Variant
Dim T As Variant, Rcd As Variant
Dim lig As Long, col As Long, i As Long, j As Long
On Error GoTo errhdlr
ReDim Rcd(1 To 1, 1 To 1)
Rst.Open Req, Cnx, 3
lig = Rst.RecordCount
If lig > 0 Then
Rst.MoveFirst
T = Rst.GetRows
col = Rst.Fields.Count
ReDim Rcd(1 To lig + Head, 1 To col)
For j = 0 To col - 1
Rcd(1, j + 1) = Rst.Fields(j).Name
For i = 0 To lig - 1
Rcd(i + 1 + Head, j + 1) = IIf(IsNull(T(j, i)), 0, T(j, i))
Next i
Next j
End If
Select_Db = Rcd
Exit Function
errhdlr:
Rcd(1, 1) = "Erreur n°" & Err.Number & vbCrLf & Err.Description
Select_Db = Rcd
End Function
merci d'avance
?
Essaye avec cette autre version du Delect_Db (peut être mieux adapté au MySql)
Function Select_Db(Req As String, Optional Head As Byte = 1) As Variant
Dim T As Variant, R As Variant, Rcd As Variant
Dim lig As Long, col As Long, i As Long, j As Long
On Error GoTo errhdlr
ReDim Rcd(1 To 1, 1 To 1)
Rst.Open Req, Cnx, 3
If Not (Rst.EOF And Rst.BOF) Then
Rst.MoveFirst
col = Rst.Fields.Count
R = Rst.GetRows
lig = UBound(R, 2)
ReDim Rcd(1 To lig + Head + 1, 1 To col)
For j = 0 To col - 1
Rcd(1, j + 1) = Rst.Fields(j).Name
For i = 0 To lig
Rcd(i + 1 + Head, j + 1) = IIf(IsNull(R(j, i)), 0, R(j, i))
Next i
Next j
End If
Select_Db = Rcd
Exit Function
errhdlr:
Rcd(1, 1) = "Erreur n°" & Err.Number & vbCrLf & Err.Description
Select_Db = Rcd
End Function
Pierre
Merci Pierre, mais c'est toujours le meme probleme avec le Primary Key de la BDD.
je ne comprends pas d'ou vient le probleme !
On peut essayer de modifier la valeur par défaut depuis excel avec avec un code genre :
Connect_MySql
Cnx.execute "ALTER TABLE table01 MODIFY `PK` int(11) NULL"
Close_Cnx
* même si il est auto_increment, ça mange pas de pain
* ceci dit si il est auto_increment, depuis excel, il est peut être nécessaire de lui injecter un n° id quand même
(via un SELECT MAX(PK), et en ajoutant 1 au résultat obtenu)
Pierre
Non en effet, une clé primaire ne peut pas être Null par défaut, j'ai pas bon sur ce point.
En revanche, je suggère de récupérer le MAX(PK) pour injecter ensuite une ligne complète avec la clé :
Dim Req As String, T As Variant, Idmax As Long
Req = "SELECT MAX(PK) FROM table01 "
Connect_MySql
T = Select_Db(Req, 1)
Idmax = T(2, 1) + 1
Close_Cnx
' et ensuite
Req = "INSERT INTO table01 VALUE (" & Idmax & ", et le reste des champs ...)"
Connect_MySql
Cnx.execute Req
Close_Cnx
Pierre