Генерация дерева элементов в иерархическом представлении в MS Access через компонент TreeView

ActiveX компонент TreeView в MS Access служит для отображения сложных данных, отображающихся в виде списка или дерева. Уровень вложенности элементов дерева не ограничен. Для работы с этим компонентом нужно подключить ссылку (References) на Microsoft Windows Common Controls 6.0 (SP6).

Для работы с деревом потребуется создать таблицу например с именем Table1. В ней укажем поля — AutoID, Name, ParentID. Пусть будет для удобства автоинкрементное поле AutoID (это необязательно, но тогда придется его вводить вручную соблюдая уникальность значений по этому столбцу). Скрипт, который позволит Вам быстро создать таблицу для примера в MS Access:

 CREATE TABLE Table1  (  AutoID AUTOINCREMENT  PRIMARY KEY,  Name TEXT,  ParentID INTEGER )

Для удобства работы мы будем применять класс cTreeClass. (Не путайте с обычным модулем!) Назвать модуль класса можете, как хотите, главное не забыть потом правильно к нему обратиться в формах (через создание объекта — ключевое слово New). Наш модуль класса назван с именем «cTreeClass».
Преимущество данного модуля класса, как Вы уже заметили, в том что он цепляется на любую таблицу, если она содержит хотя бы три поля (ключ, название, код родителя).

  Option Compare Database ' Объявляем класс Tree с событиями Public WithEvents Tree As TreeView Public tbl As String Public fldParent As String Public fldKey As String Public fldText As String Public createKey As Long  Const Start As String = "0"  ' "0" - это значение ParentID, от которого будет строится дерево.   '  Допускается, что в таблице будет несколько таких значений, от которых построятся разные ветки  '  Как видно, мы запросто ему можем присвоить текстовое значение  Private Sub Class_Initialize() 'Инициализируем переменные класса для работы с таблицей 'Tbl = "Tbl" 'fldParent = "Parent" 'fldKey = "Key" 'fldText = "Text" End Sub  ' События при управлении левой кнопкой мыши Private Sub Tree_Click() '    MsgBox Tree.SelectedItem.Key End Sub  'Добавление основного узла Public Sub AddBaseNode(Key As String, Text As String)     idx = Tree.Nodes.Add(, , Key).Index     With Tree.Nodes(idx)         .Text = Text     End With End Sub  'Добавление дочернего узла Public Sub AddNode(Parent As String, Key As String, Text As String)     idx = Tree.Nodes.Add(Parent, tvwChild, Key).Index     With Tree.Nodes(idx)         .Text = Text     End With End Sub  'Очистка дерева Public Sub ClearNode()     Tree.Nodes.Clear End Sub  Public Sub GenerateRecursive(Parent As String) Dim r As DAO.Recordset Dim Key As String Dim Par As String Dim Text As String '========================================================================' '                РЕКУРСИВНАЯ ГЕНЕРАЦИЯ ДЕРЕВА                    ' '========================================================================' Set r = CurrentDb.OpenRecordset("SELECT * FROM " & tbl & _     " WHERE " & fldParent & "='" & Parent & "';", dbOpenDynaset) If r.EOF And r.BOF Then Else     r.MoveFirst     While Not r.EOF         Key = "key" & r.Fields(fldKey)         Par = "key" & r.Fields(fldParent)         Text = r.Fields(fldText)         If r.Fields(fldParent) = Start Then             AddBaseNode Key, Text         Else             AddNode Par, Key, Text         End If         GenerateRecursive r.Fields(fldKey)         r.MoveNext     Wend End If '======================================================================== r.Close Set r = Nothing End Sub  'Генерация дерева из таблицы Public Sub GenerateTree() Dim r As DAO.Recordset Dim Key As String Dim Par As String Dim Text As String  ClearNode  GenerateRecursive Start ' "0"  End Sub  'Получить код элемента Public Function GetKey() As Long     GetKey = DelKeyStr(Tree.SelectedItem.Key) End Function  'Удалить префикс Private Function DelKeyStr(Text As String) As Long Dim stroka As String     stroka = Right(Text, Len(Text) - 3) DelKeyStr = CLng(stroka) End Function  'Добавить ветку Public Sub AddTblNode(Parent As String, Text As String) Dim Key As String Dim Par As String Dim LastId As Long  CurrentDb.Execute "INSERT INTO " & tbl & " ( , " & fldParent & _ " ) SELECT """ & Text & """ AS Txt, " & DelKeyStr(Parent) & " AS Prn;" LastId = DMax(fldKey, tbl, "")  createKey = LastId Key = "key" & LastId If DelKeyStr(Parent) = 0 Then     AddBaseNode Key, Text Else     AddNode Parent, Key, Text End If End Sub  'Обновить ветку Public Sub UpdateTblNode(Key As String, UpdText As String) CurrentDb.Execute "UPDATE " & tbl & " SET " & fldText & "=""" & UpdText & """ WHERE " & fldKey & "=" & _ DelKeyStr(Key) & ";" Tree.Nodes.Item(Key).Text = UpdText End Sub  'Удалить ветку Public Sub DelTblNode(Key As String) CurrentDb.Execute "DELETE * FROM " & tbl & " WHERE " & fldKey & "=" & _ DelKeyStr(Key) & ";" Tree.Nodes.Remove Key End Sub  'Рекурсивное удаление ветки (если есть дочерние и внучатые ветки) Public Sub RecursiveDelTblNode(Key As String) Dim r As Recordset  Set r = CurrentDb.OpenRecordset("SELECT * FROM " & tbl & _     " WHERE " & fldParent & "=" & DelKeyStr(Key) & ";", dbOpenDynaset) If r.EOF And r.BOF Then     CurrentDb.Execute "DELETE * FROM " & tbl & " WHERE " & _         fldKey & "=" & DelKeyStr(Key) & ";"     Tree.Nodes.Remove Key Else     r.MoveFirst     While Not r.EOF         RecursiveDelTblNode "key" & r.Fields(fldKey)         r.MoveNext     Wend     CurrentDb.Execute "DELETE * FROM " & tbl & " WHERE " & _         fldKey & "=" & DelKeyStr(Key) & ";"     Tree.Nodes.Remove Key End If  r.Close Set r = Nothing End Sub    

В самой форме, где добавлен компонент TreeView, мы в загрузку помещаем объект с ссылкой на наш класс и инициализируем наши переменные:

 Private Sub Form_Load()   Dim tr As Object  Set tr = New cTreeClass  Set tr.Tree = Me.xTree.Object  tr.tbl = "Table1"  tr.fldKey = "AutoID"  tr.fldParent = "ParentID"  tr.fldText = "Name"     tr.GenerateTree  End Sub

Чтобы назначить обработчик на дерево нужно будет написать такой код:

 Private Sub TrView_Click()  MsgBox Me.xTree.SelectedItem.Key End Sub

При нажатии на любой элемент в списке мы получим сообщение с номером присвоенного ключа. 

Дата публикации: 2015-06-11 21:05:54 MS AccessVBA

Отзывы:

Дано : База данных Access 2016 с таблицей, где перечислены разделы (подразделения или другие данные, которые можно представить в виде иерархии).

Задача : на Access-форме построить иерархическое дерево на базе указанной выше таблице.

Скачать пример базы в формате Access Допустим имеется таблица подразделений вуза (tblDepartment) в формате:

intID — strDepartmentName — intParentID Где,

intID — идентификатор подразделения,

strDepartmentName   — наименование подразделения,

intParentID — идентификатор родительского подразделения.

Корневой элемент будет ссылаться в качестве «родителя» на самого себя.

Также для удобства в конструкторе таблицы в поле родителя можно задать подстановку, ссылаясь на эту же таблицу:

Текст запроса для подстановки:

SELECT tblDepartment.strDepartmentName AS Подразделение tblDepartment_1.strDepartmentName AS Родитель FROM tblDepartment LEFT JOIN tblDepartment AS tblDepartment_1 ON tblDepartment.intParentID = tblDepartment_1.intID ORDER BY tblDepartment.strDepartmentName;

После этого в колонке родителя мы сможем выбирать не идентификатору, а по имени подразделения (но в колонке будет по-прежнему храниться идентификатор).

В итоге мы сможем заполнять таблицу следующим образом:

Теперь построим дерево. Для этого создаем пустую форму в режиме конструктора и выберем пункт «Элементы ActiveX»:

В списке выбираем элемент «Microsoft TreeView Control (6.0)»

Выбранный элемент добавляем на форму в нужном месте нужного размера:

Далее переходим в режим кода Visual Basic и добавляем следующий код:

Dim rsCommon As ADODB.Recordset Set rsCommon = New ADODB.Recordset rsCommon.Open «SELECT DP.intID, DP.intParentID, DP.strDepartmentName FROM tblDepartment DP WHERE DP.intID = DP.intParentID ORDER BY DP.strDepartmentName» CurrentProject.Connection adOpenKeyset adLockOptimistic If Not rsCommon.EOF Then TreeViewDep.Nodes.Add Str(rsCommon(«intID»)) & «$KEY» rsCommon(«strDepartmentName») strRoot = Str(rsCommon(«intID»)) TreeViewDep.Nodes.Item(strRoot & «$KEY»).Expanded = True Private Sub AddNode(ByVal ParentID As String) Set rsCommon = New ADODB.Recordset rsCommon.Open «SELECT DP.intID, DP.intParentID, DP.strDepartmentName FROM tblDepartment DP WHERE DP.intID DP.intParentID AND DP.intParentID = » & ParentID & » ORDER BY DP.strDepartmentName» CurrentProject.Connection adOpenKeyset adLockOptimistic Do While Not rsCommon.EOF     TreeViewDep.Nodes.Add ParentID & «$KEY» tvwChild Str(rsCommon(«intID»)) & «$KEY» rsCommon(«strDepartmentName»)     TreeViewDep.Nodes.Item(Str(rsCommon(«intID»)) & «$KEY»).Expanded = True     AddNode (Str(rsCommon(«intID»)))

Сохраняем код и форму. Теперь можно запускать форму в режиме просмотра. Должно отобразиться дерево:

Если возникает ошибка: User-defined type not defined (на строке кода ADODB.Recordset), то в VBA в меню Tools — References нужно добавить компонент Microsoft ActiveX Data Objects:

И после этого сделать компиляцию — Debug — Compile.

Как вариант можно использовать таблицу, расположенную в базе на MS SQL Server. Для этого достаточно создать связь с таблицами. Все остальные действия по построению дерева будет аналогичны. В этом случае также возможен обход дерева на стороне MS SQL Server.

Если Вам понравилась статья, пожалуйста, поставьте лайк, сделайте репост или оставьте комментарий. Если у Вас есть какие-либо замечания, также пишите комментарии.

Полгода назад написал бандл ClosureTable для фреймворка Laravel 3. Поводом для написания стала вот эта замечательная презентация Билла Карвина о способах хранения и обработки иерархических данных в MySQL с использованием PHP. Итак. Существует несколько шаблонов проектирования баз данных для хранения и обработки иерархических структур:

• Adjacency List («список смежности»)
• Materialized Path («материализованный путь»)
• Nested Sets («вложенные множества»)
• Closure Table («таблица связей»)

Что такое Closure Table

Суть данного шаблона проектирования заключается в том, что связи между сущностями хранятся в отдельной таблице, тогда как основная таблица содержит только данные самих сущностей.

Таблица связей должна содержать как минимум два поля:

• ссылку на предка (ancestor)
• ссылку на потомка (descendant)

Пусть мы работаем над созданием очередной SuperPuper CMS и приступили к разработке модуля редактирования текстовых страниц. Нам понадобятся две таблицы:

• pages будет содержать данные о странице
• pages_treepath будет содержать данные об иерархии страниц

Схема таблиц БД В качестве примера используем следующую иерархию страниц:

Выборка потомков

Вот такой SQL-запрос у нас получится, если мы захотим выбрать все страницы раздела «О компании».

SELECT * FROM pages p JOIN pages_treepath t ON (p.id = t.descendant) WHERE t.ancestor = 1 

Ветка полученного результата. Стрелки указывают на связи между страницами «Descendant» означает «потомок», а «ancestor» — предок. Соответственно, чтобы получить все дочерние страницы, мы присоединяем таблицу связей pages_treepath при условии, что идентификатор страницы id имеет то же значение, что и ссылка на потомка descendant. При этом ссылка ancestor на родительскую страницу равняется 1, идентификатору страницы «О компании».

Выборка предков

А теперь снизу вверх: посмотрим всех «родителей» у страницы «Корпоративный».

SELECT * FROM pages p JOIN pages_treepath t ON (p.id = t.ancestor) WHERE t.descendant = 11 

В этом случае наоборот. Мы ищем страницы выше по иерархии, поэтому присоединяем таблицу связей с условием, что идентификатор страницы id должен равняться ссылке на предка ancestor, а выборку осуществляем по ссылке на потомка descendant, в нашем случае равной 11.

Вставка нового элемента

Можно добавить новую вакансию. Данные ценности в нашем случае не представляют, так что посмотрим на сам запрос.

INSERT INTO pages VALUES (12, 'Менеджер по продажам',         '',         'Требуется офигенный менеджер по продажам',         '0000-00-00 00:00:00',         '0000-00-00 00:00:00')   INSERT INTO pages_treepath (ancestor, descendant)     SELECT ancestor, 12 FROM pages_treepath     WHERE descendant = 4     UNION ALL     SELECT 12, 12 

С первым запросом все ясно — это простая вставка новых данных. А вот второй запрос стоит разобрать по порядку, так что давайте посмотрим, что тут происходит.

Связи между элементами после вставки новой вакансии

SELECT ancestor, 12 FROM pages_treepath WHERE descendant = 4 

Выполнив этот запрос, мы получим следующий список связей:

 ------------------------- | ancestor | descendant | ------------------------- |    4     |     12     | |    1     |     12     | ------------------------- 

Добавим к предыдущему запросу еще один путем объединения:

SELECT ancestor, 12 FROM pages_treepath WHERE descendant = 4 UNION ALL SELECT 12, 12 

В список связей добавится связь-ссылка страницы на саму себя:

 ------------------------- | ancestor | descendant | ------------------------- |    4     |     12     | |    1     |     12     | |   12     |     12     | ------------------------- 

Как видите, данный SELECT-запрос позволяет установить связи между новой страницей и всеми её предками. ancestor — всегда ссылка на предка, descendant — ссылка на потомка. INSERT-запрос, написанный вначале, вставляет полученный результат в таблицу pages_treepath.

Удаление элемента

А теперь закроем вакансию веб-дизайнера.

DELETE FROM pages_treepath WHERE descendant = 6   DELETE FROM pages WHERE id = 6 

Здесь всё просто. Сначала мы удаляем все связи, где ссылка на потомка равняется 6 (страница «Веб-дизайнер»), а затем удаляем и саму страницу.

Удаление вложенного дерева

Вдруг так случилось, что с некоторых пор компания ABC перестала разрабатывать сайты. Нам понадобится выполнить вот такой запрос, чтобы удалить соответствующий подраздел:

DELETE FROM pages WHERE id IN (     SELECT descendant FROM (         SELECT descendant FROM pages p         JOIN pages_treepath t ON p.id = t.descendant         WHERE t.ancestor = 7     ) AS tmptable )   DELETE FROM pages_treepath WHERE descendant IN (     SELECT descendant FROM (         SELECT descendant FROM pages_treepath         WHERE ancestor = 7     ) AS tmptable ) 

В отличие от предыдущего запроса, этот несколько сложнее и сначала удаляются сами страницы и уже после этого связи между ними (поскольку последние активно используются при удалении первых).

Сложность запросов отчасти объясняется тем, что MySQL не позволяет выполнять запрос на удаление записей с условием WHERE, в котором содержится выборка SELECT из той же таблицы. В случае с MySQL мы вынуждены поместить SELECT-запросы во временную таблицу. А в общем случае наши запросы выглядели бы так:

DELETE FROM pages WHERE id IN (     SELECT descendant FROM pages p     JOIN pages_treepath t ON p.id = t.descendant     WHERE t.ancestor = 7 )   DELETE FROM pages_treepath WHERE descendant IN (     SELECT descendant FROM pages_treepath     WHERE ancestor = 7 ) 

Если вы внимательно посмотрите на вложенный SELECT-запрос в DELETE-запросе из таблицы pages, то обнаружите, что мы уже рассматривали подобный запрос. Этот от предыдущего отличается только идентификатором страницы. В результате выборки мы получаем все дочерние страницы раздела «Сайты» (включая сам раздел), а затем удаляем все страницы с полученными идентификаторами.

После того, как страницы удалены, остаётся удалить связи между ними. Для этого находим все ссылки на потомков descendant, где ссылка на предка равняется идентификатору страницы «Сайты».

Уровень вложенности

Еще в таблицу связей можно добавить поле, контролирующее уровень вложенности элементов. Это поле позволит составлять более простые запросы на выборку непосредственных предков или непосредственных потомков. Например:

SELECT * FROM pages p JOIN pages_treepath t ON (p.id = t.descendant) WHERE t.ancestor = 4 AND t.level = 2 

Схема таблиц БД Продолжение следует.

Этот совет Java Swing иллюстрирует метод создания и наполнения дерево. Простой тест, чтобы увидеть, как мы можем построить дерево и заполнить ее. Это приложение также использует пользовательский подготовки отчетов и редакторов.

Похожие записи:

Как сделать форму поиска в access? Как сделать форму главной в access? Как сделать фильтр в отчете access? Как сделать условие на значение в access?