1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120
| Private Sub Bouton1_Click()
'ouverture table chemin et import des fichiers excel
Dim chemin, basebua As Variant
Dim bds, esp, chsql, param
Dim SQL1 As String
Dim nmcl As Variant
SQL1 = "Delete * From T_BUA"
DoCmd.RunSQL SQL1
SQL1 = "Delete * From Nmcl"
DoCmd.RunSQL SQL1
SQL1 = "Delete * From R02_MRPRM_FAB"
DoCmd.RunSQL SQL1
SQL1 = "Delete * From R03_MFCONF_FAB"
DoCmd.RunSQL SQL1
SQL1 = "Delete * From R04_MFGARN_FAB"
DoCmd.RunSQL SQL1
SQL1 = "Delete * From R05_MRPRM_TRANSF"
DoCmd.RunSQL SQL1
SQL1 = "Delete * From R06_MFCONF_TRANSF"
DoCmd.RunSQL SQL1
SQL1 = "Delete * From R07_MFGARN_TRANSF"
DoCmd.RunSQL SQL1
SQL1 = "Delete * From R09_MTPRM_ACH"
DoCmd.RunSQL SQL1
SQL1 = "Delete * From R10_MRAFF_FAB"
DoCmd.RunSQL SQL1
SQL1 = "Delete * From R11_MFCONF_FAB"
DoCmd.RunSQL SQL1
SQL1 = "Delete * From R12_MAPCHM_FAB"
DoCmd.RunSQL SQL1
SQL1 = "Delete * From R13_MAPCM_ACH"
DoCmd.RunSQL SQL1
SQL1 = "Delete * From R14_MASST_ACH"
DoCmd.RunSQL SQL1
SQL1 = "Delete * From R15_MAPCM_ACH"
DoCmd.RunSQL SQL1
'Mise à jour de la table BUA
Set bds = CurrentDb
Set esp = DBEngine.Workspaces(0)
chsql = "SELECT * FROM [T_Fichier]"
Set param = bds.OpenRecordset(chsql)
basebua = Trim$(param.EMPL_Fichier)
DoCmd.TransferSpreadsheet acImport, , "T_BUA", basebua, True
'Mise à jour de la table des nomenclatures
Set bds = CurrentDb
Set esp = DBEngine.Workspaces(0)
chsql = "SELECT * FROM [T_Fichier]"
Set param = bds.OpenRecordset(chsql)
nmcl = Trim$(param.nmcl)
DoCmd.TransferSpreadsheet acImport, , "Nmcl", nmcl, True
'FAIT
SQL1 = "INSERT INTO R02_MRPRM_FAB SELECT T_BUA.* FROM T_BUA WHERE INV_ITEM_ID Not Like 'L*' AND (GROUPE Like 'MRPRM*' Or GROUPE Like 'IF100') AND CODE_SOURCE = '1' ORDER BY T_BUA.INV_ITEM_ID"
DoCmd.RunSQL SQL1
'FAIT
SQL1 = "INSERT INTO R03_MFCONF_FAB SELECT T_BUA.* FROM T_BUA WHERE INV_ITEM_ID Not Like 'L*' AND (GROUPE Like 'MFCONF' or GROUPE like 'MFLOT') AND CODE_SOURCE = '1' ORDER BY T_BUA.INV_ITEM_ID"
DoCmd.RunSQL SQL1
'FAIT
SQL1 = "INSERT INTO R04_MFGARN_FAB SELECT T_BUA.* FROM T_BUA WHERE INV_ITEM_ID Not Like 'L*' AND GROUPE Like 'MFGARN' AND CODE_SOURCE = '1' ORDER BY T_BUA.INV_ITEM_ID"
DoCmd.RunSQL SQL1
'FAIT
SQL1 = "INSERT INTO R05_MRPRM_TRANSF SELECT T_BUA.* FROM T_BUA WHERE INV_ITEM_ID Not Like 'L*' AND (GROUPE Like 'MRPRM*' or GROUPE Like 'IF100') AND CODE_SOURCE = '2' ORDER BY T_BUA.INV_ITEM_ID"
DoCmd.RunSQL SQL1
'FAIT
SQL1 = "INSERT INTO R06_MFCONF_TRANSF SELECT T_BUA.* FROM T_BUA WHERE INV_ITEM_ID Not Like 'L*' AND (GROUPE Like 'MFCONF' or GROUPE like 'MFLOT') AND CODE_SOURCE = '2' ORDER BY T_BUA.INV_ITEM_ID"
DoCmd.RunSQL SQL1
'FAIT
SQL1 = "INSERT INTO R07_MFGARN_TRANSF SELECT T_BUA.* FROM T_BUA WHERE INV_ITEM_ID Not Like 'L*' AND GROUPE Like 'MFGARN' AND CODE_SOURCE = '2' ORDER BY T_BUA.INV_ITEM_ID"
DoCmd.RunSQL SQL1
'FAIT
SQL1 = "INSERT INTO R09_MTPRM_ACH SELECT T_BUA.* FROM T_BUA WHERE INV_ITEM_ID Not Like 'L*' AND (GROUPE Like 'MTPRM*' or GROUPE like 'IT100') ORDER BY T_BUA.INV_ITEM_ID"
DoCmd.RunSQL SQL1
'FAIT
SQL1 = "INSERT INTO R10_MRAFF_FAB SELECT T_BUA.* FROM T_BUA WHERE (GROUPE Like 'MRAFF' or GROUPE like 'MRPRES' or GROUPE like 'MRWAG') ORDER BY T_BUA.INV_ITEM_ID"
DoCmd.RunSQL SQL1
'FAIT
SQL1 = "INSERT INTO R11_MFCONF_FAB SELECT T_BUA.* FROM T_BUA WHERE INV_ITEM_ID Like 'L*' AND GROUPE Not Like 'MRAFF' AND GROUPE Not like 'MRWAG' AND GROUPE Not like 'MRPRES' AND GROUPE not Like 'MASST' AND GROUPE Not Like 'MAPCHM' AND GROUPE Not like 'MAPCM' AND GROUPE Not Like '*100' ORDER BY T_BUA.INV_ITEM_ID"
DoCmd.RunSQL SQL1
'FAIT
SQL1 = "INSERT INTO R12_MAPCHM_FAB SELECT T_BUA.* FROM T_BUA WHERE INV_ITEM_ID Like 'L*' AND GROUPE Like 'MAPCHM' ORDER BY T_BUA.INV_ITEM_ID"
DoCmd.RunSQL SQL1
'FAIT
SQL1 = "INSERT INTO R13_MAPCM_ACH SELECT T_BUA.* FROM T_BUA WHERE INV_ITEM_ID Like 'L*' AND (GROUPE Like 'MAPCM' or GROUPE like 'CA100' or GROUPE like 'IA100') ORDER BY T_BUA.INV_ITEM_ID"
DoCmd.RunSQL SQL1
'FAIT
SQL1 = "INSERT INTO R14_MASST_ACH SELECT T_BUA.* FROM T_BUA WHERE INV_ITEM_ID Like 'L*' AND GROUPE Like 'MASST' ORDER BY T_BUA.INV_ITEM_ID"
DoCmd.RunSQL SQL1
'FAIT
SQL1 = "INSERT INTO R15_MAPCM_ACH SELECT T_BUA.* FROM T_BUA WHERE INV_ITEM_ID Not Like 'L*' AND (GROUPE Like 'MAPC*' or GROUPE like 'IA100' or GROUPE like 'CA100') ORDER BY T_BUA.INV_ITEM_ID"
DoCmd.RunSQL SQL1
DoCmd.RunMacro "M_maj_Table_liste_article"
DoCmd.RunMacro "M_maj fichiers xls"
DoCmd.RunMacro "Autoexec"
MsgBox ("Vous venez de mettre à jour VERA.")
End Sub |
Partager