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
|
Option Compare Database
Public Sub sqlcmd(entity As Integer, lvl As String)
Dim db As Database
Dim rs As Recordset
Dim rs2 As Recordset
Dim tx As String
Set db = CurrentDb
tx = "Select ou_no, cd_fuid, ou_name as ou_short_name, ou_name, ou_start_dt, ou_end_dt, fnc_lvl_no as lvl from dbo_vw_ou where (" & lvl & " = " & entity & " and dbo_vw_ou.ou_name Not Like '*technical*' and dbo_vw_ou.Cd_Fuid<>'');"
Set rs = db.OpenRecordset(tx)
DoCmd.SetWarnings (False)
While rs.EOF = False
tx = "Select ou_no, fnc_lvl" & rs("lvl") - 1 & " as mother from dbo_vw_ou where ou_no = " & rs("ou_no") & ";"
Set rs2 = db.OpenRecordset(tx)
tx = "INSERT INTO TB_structure ( entity, fuid, entity_short_name, entity_name, valid_from, valid_to, relation_id, related_obj) Values (" & rs("ou_no") & ", " & Nz(rs("cd_fuid"), "000") & ", """ & Trim(rs("ou_short_name")) & """, """ & Trim(rs("ou_name")) & """, #" & rs("ou_start_dt") & "#, #" & rs("ou_end_dt") & "#, 'AAA1' ," & rs2("mother") & ");"
DoCmd.RunSQL (tx)
rs.MoveNext
Set rs2 = Nothing
Wend
DoCmd.RunSQL ("UPDATE TB_structure SET TB_structure.Related_obj = Null WHERE (((TB_structure.entity)= " & entity & "));")
DoCmd.SetWarnings (True)
rs.Close
Set rs = Nothing
Set db = Nothing
End Sub
Function gen_struct(entity As Integer)
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
DoCmd.SetWarnings False
Set rs = db.OpenRecordset("SELECT 'fnc_lvl' & [fnc_lvl_no] AS fnc_lvl FROM dbo_vw_ou WHERE ou_no = " & Chr$(34) & entity & Chr$(34) & ";")
sqlcmd entity, rs("fnc_lvl")
rs.Close
DoCmd.SetWarnings True
Set rs = Nothing
Set db = Nothing
End Function
Function regenerat(entity As Integer)
DoCmd.SetWarnings False
DoCmd.OpenQuery "del_users", acViewNormal, acEdit
DoCmd.SetWarnings False
DoCmd.OpenQuery "del_struct", acViewNormal, acEdit
DoCmd.SetWarnings False
Call gen_struct(entity)
DoCmd.SetWarnings False
DoCmd.OpenQuery "users", acViewNormal, acEdit
DoCmd.SetWarnings False
DoCmd.OpenQuery "upt_funct_man", acViewNormal, acEdit
DoCmd.SetWarnings False
DoCmd.OpenQuery "upt_funct_mem", acViewNormal, acEdit
DoCmd.SetWarnings True
End Function
Function updt(entity As Integer)
DoCmd.SetWarnings False
DoCmd.OpenQuery "mod_date_to_funct_man", acViewNormal, acEdit
DoCmd.SetWarnings False
DoCmd.OpenQuery "mod_date_to_funct_mem", acViewNormal, acEdit
DoCmd.SetWarnings False
DoCmd.OpenQuery "mod_date_to_struct", acViewNormal, acEdit
DoCmd.SetWarnings False
Call gen_struct(entity)
DoCmd.SetWarnings False
DoCmd.OpenQuery "upt_funct_man", acViewNormal, acEdit
DoCmd.SetWarnings False
DoCmd.OpenQuery "upt_funct_mem", acViewNormal, acEdit
DoCmd.SetWarnings True
End Function |
Partager