Public Function FUN_CREATE_TABLE_GROUP_TREEVIEW(var_TreeView As Control)
'COMODITY_GROUP_TBL
Dim rst As ADODB.Recordset
Dim rst1 As ADODB.Recordset
' если таблицы ещё нет - создаём из COMODITY_GROUP_TBL
If FUN_IS_TABLE("GROUP_TREEVIEW_TBL") = False Then
GLB_con.Execute "SELECT COMODITY_GROUP_TBL.* INTO [GROUP_TREEVIEW_TBL]" _
& " From COMODITY_GROUP_TBL " _
& "WITH OWNERACCESS OPTION;"
End If
' ОЧИСТКА таблицы ПЕРЕД ЗАПОЛНЕНИЕМ
FUN_CLEAR_TABLE ("GROUP_TREEVIEW_TBL")
' переброс всех без родителей
GLB_con.Execute "INSERT INTO GROUP_TREEVIEW_TBL" _
& " SELECT COMODITY_GROUP_TBL.*" _
& " From COMODITY_GROUP_TBL" _
& " Where (((COMODITY_GROUP_TBL.GROUP_PARENT) = ""0"")) Or (((COMODITY_GROUP_TBL.GROUP_PARENT) = """")) Or (((COMODITY_GROUP_TBL.GROUP_PARENT) Is Null))" _
& " WITH OWNERACCESS OPTION;"
Recoursi: ' заново
Set rst = New ADODB.Recordset
Set rst1 = New ADODB.Recordset
' проверка на совпадене записей в обоих таблицах
rst1.Open "SELECT COMODITY_GROUP_TBL.GROUP_NAME" _
& " FROM COMODITY_GROUP_TBL LEFT JOIN GROUP_TREEVIEW_TBL ON COMODITY_GROUP_TBL.ID_GROUP = GROUP_TREEVIEW_TBL.ID_GROUP" _
& " WHERE (((GROUP_TREEVIEW_TBL.ID_GROUP) Is Null));", GLB_con, adOpenKeyset, adLockOptimistic
If rst1.EOF = True Then ' если таблицы одинаковы по кол. записей
'rst.Close
rst1.Close
Set rst = Nothing
Set rst1 = Nothing
' Заполнение дерева
Call FUN_TO_FILL_TREE_VIEW(var_TreeView)
Exit Function
End If
'открываем ВСЕХ в GROUP_TREEVIEW_TBL
rst.Open "SELECT GROUP_TREEVIEW_TBL.*" _
& " From GROUP_TREEVIEW_TBL" _
& " WITH OWNERACCESS OPTION;", GLB_con, adOpenKeyset, adLockOptimistic
' бегаем по таблице GROUP_TREEVIEW_TBL
Do Until rst.EOF
' и добавляем детей из COMODITY_GROUP_TBL.* для GROUP_TREEVIEW_TBL.ID_GROUP
On Error Resume Next
GLB_con.Execute "INSERT INTO GROUP_TREEVIEW_TBL" _
& " SELECT COMODITY_GROUP_TBL.*" _
& " From COMODITY_GROUP_TBL" _
& " Where (((COMODITY_GROUP_TBL.GROUP_PARENT) = '" & rst("ID_GROUP") & "'))" _
& " WITH OWNERACCESS OPTION;"
rst.MoveNext
Loop
GoTo Recoursi
End Function
|