sQuery="UPDATE (q_tbl_DCR INNER JOIN tbl_DCR " & _ "ON q_tbl_DCR.SB_N° = tbl_DCR.SB_N°) " & _ "INNER JOIN tbl_Extract_Suprem " & _ "ON (q_tbl_DCR.SB_7 = tbl_Extract_Suprem.[SB Number]) " & _ "AND (q_tbl_DCR.SB_rev = tbl_Extract_Suprem.[Sb Revision]) " & _ "SET tbl_DCR.chp1 = tbl_Extract_Suprem!chp1, " & _ "tbl_DCR.chp2 = tbl_Extract_Suprem!chp2, " & _ "tbl_DCR.chp3 = tbl_Extract_Suprem!chp3;"
"Pour ce qui est de la requete qui permet de ne prendre que les 7 premiers caractères je ne la vois pas dans la requete mise à jours."
sQuery = "UPDATE ( q_tbl_DCR INNER JOIN tbl_DCR " & _ "ON q_tbl_DCR.SB_N° = tbl_DCR.SB_N°) " & _ "INNER JOIN tbl_Extract_Suprem " & _ "ON (q_tbl_DCR.SB_7 = tbl_Extract_Suprem.[SB Number]) " & _ "AND ( q_tbl_DCR .SB_rev = tbl_Extract_Suprem.[Sb Revision]) " & _ "SET tbl_DCR.chp1 = tbl_Extract_Suprem!chp1, " & _ "tbl_DCR.chp2 = tbl_Extract_Suprem!chp2, " & _ "tbl_DCR.chp3 = tbl_Extract_Suprem!chp3;"
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre questionsQuery="INSERT INTO Table1 ( SBrev, DM, nom_DM, Auteur, nom_Auteur ) " & _ "SELECT Table2.SB & '-' & Table2.rev AS SBrev, " & _ "Table2.DM, Table2.nom_DM, Table2.Auteur, Table2.nom_Auteur " & _ "FROM Table2;"
Private Sub Command27_Click() Dim sQuery As String sQuery = "INSERT INTO tbl_DCR ( SB_N°, SB_Designer, SB_Designer_WorkCenter, DM/DFM, DM/DFM_WorkCenter, SB_Author, SB_Author_WorkCenter ) " & _ "SELECT tbl_Extract_Suprem.SB Number & '-' & tbl_Extract_Suprem.SB Revision AS SB_N°, " & _ "tbl_Extract_Suprem.SB_Designer, tbl_Extract_Suprem.SB_Designer_WorkCenter, tbl_Extract_Suprem.DM/DFM, tbl_Extract_Suprem.DM/DFM_WorkCenter, tbl_Extract_Suprem.SB_Author, tbl_Extract_Suprem.SB_Author_WorkCenter " & _ "FROM tbl_Extract_Suprem;" End Sub
'Dim db As DAO.Database 'Dim rs1 As DAO.Recordset 'Set db = CurrentDb 'Set rs1 = db.OpenRecordset ("SELEC * FROM tbl_Extract_Suprem")
Private Sub Command27_Click() Dim db As DAO.Database Dim sQuery As String Set db = CurrentDb sQuery = "INSERT INTO tbl_DCR ( SB_N°, SB_Designer, SB_Designer_WorkCenter, DM/DFM, DM/DFM_WorkCenter, SB_Author, SB_Author_WorkCenter ) " & _ "SELECT tbl_Extract_Suprem.SB Number & '-' & tbl_Extract_Suprem.SB Revision AS SB_N°, " & _ "tbl_Extract_Suprem.SB_Designer, tbl_Extract_Suprem.SB_Designer_WorkCenter, tbl_Extract_Suprem.DM/DFM, tbl_Extract_Suprem.DM/DFM_WorkCenter, tbl_Extract_Suprem.SB_Author, tbl_Extract_Suprem.SB_Author_WorkCenter " & _ "FROM tbl_Extract_Suprem;" 'éxécution de la transaction On Error Goto err_insert DBEngine.Workspaces(0).BeginTrans db.Execute sQuery, dbFailOnError DBEngine.Workspaces(0).CommitTrans MsgBox "Procédure effectuée avec succès..",vbOkOnly Exit Sub 'si erreur err_insert: 'annulation de la transaction DBEngine.Workspaces(0).Rollback MsgBox Err.Description End Sub
Le probleme qui risque de se poser c'est que SB_N° (appelé SBrev dans mon premier messages) est un numéro du style XX-XXXX-XX. si je fais une boucle avec incrémentation, va-t-il etre intuitif et ne pas beuguer à cause des "-"?
Private Sub Command27_Click() Dim db As DAO.Database Dim rsSource As DAO.Recordset, rsDest As DAO.Recordset 'declares une variable Integer pour une incrémentation Dim i As Integer Set db = CurrentDb Set rsSource= db.OpenRecordset ("SELEC * FROM tbl_Extract_Suprem",dbOpenDynaset) Set rsDest= db.OpenRecordset ("SELEC * FROM tbl_Destination",dbOpenDynaset) With rsSource On Error Resume Next .MoveLast .MoveFirst 'vérifier si la table source n' est pas vide If .Bof And .Eof Then MsgBox "Table source vide." Exit Sub End If On Error Goto err_insert 'On parcours la table source,et on insère un à un tout es les lignes Do While Not .Eof i=i+1 rsDest.AddNew rsDest!SBrev=!SB & "-" & !rev 'ça reste donc à revoir rsDest!DM=!DM rsDest!nom_DM=!nom_DM rsDest!Auteur=!Auteur rsDest!nom_Auteur=!nom_Auteur 'tu peux rajouter des champs ici .Update 'ligne suivante .MoveNext Loop End With rsSource.Close Set rsSource=Nothing rsDest.Close Set rsDest=Nothing MsgBox "Terminé.." Exit Sub 'encas d' erreur err_insert: MsgBox Err.Description End Sub
Private Sub Command27_Click() Dim db As DAO.Database Dim sQuery As String Set db = CurrentDb sQuery = "UPDATE tbl_DCR " & _ "set [DCR.SB_N°] = = [SUP.SB Number] & " - " & [SUP.SB Revision], " & _ "[DCR.DM/DFM] = [SUP.DFM Name]," & _ "[DCR.DM/DFM_WorkCenter] = [SUP.D(F)M Work center] " & _ "[DCR.SB_Designer] = [SUP.SB Prep Resp]," & _ "[DCR.SB_Designer_WorkCenter] = [SUP.SB Designer Work center] " & _ "[DCR.SB_Author] = [SUP.Author name]," & _ "[DCR.SB_Author_WorkCenter] = [SUP.SB author leader Work center] " & _ " FROM tbl_DCR DCR INNER JOIN tbl_Extract_Suprem SUP ON DCR.SB_N° = [SUP.SB Number] & " - " & [SUP.SB Revision] ;" 'éxécution de la transaction On Error GoTo err_insert DBEngine.Workspaces(0).BeginTrans db.Execute sQuery, dbFailOnError DBEngine.Workspaces(0).CommitTrans MsgBox "Procédure effectuée avec succès..", vbOKOnly Exit Sub 'si erreur err_insert: 'annulation de la transaction DBEngine.Workspaces(0).Rollback MsgBox Err.Description End Sub
Dim A As String Dim B As String A = [tbl_DCR.SB_N°] B = Left (A,7)
Private Sub Command27_Click() Dim db As DAO.Database Dim sQuery As String Set db = CurrentDb sQuery = "UPDATE tbl_DCR " & _ "set Left(DCR.SB_N°,7) = SUP.SB Number" & _ "[DCR.SB_rev] = [SUP.SB Revision], " & _ "[DCR.DM/DFM] = [SUP.DFM Name]," & _ "[DCR.DM/DFM_WorkCenter] = [SUP.D(F)M Work center] " & _ "[DCR.SB_Designer] = [SUP.SB Prep Resp]," & _ "[DCR.SB_Designer_WorkCenter] = [SUP.SB Designer Work center] " & _ "[DCR.SB_Author] = [SUP.Author name]," & _ "[DCR.SB_Author_WorkCenter] = [SUP.SB author leader Work center] " & _ " FROM tbl_DCR DCR INNER JOIN tbl_Extract_Suprem SUP ON B SUP.SB Number and [DCR.SB_rev] [SUP.SB Revision] ;" 'éxécution de la transaction On Error GoTo err_insert DBEngine.Workspaces(0).BeginTrans db.Execute sQuery, dbFailOnError DBEngine.Workspaces(0).CommitTrans MsgBox "Procédure effectuée avec succès..", vbOKOnly Exit Sub 'si erreur err_insert: 'annulation de la transaction DBEngine.Workspaces(0).Rollback MsgBox Err.Description End Sub