Вопрос касается организации баз данных. Но поскольку вопрос является теоретическим, а реализовываться будет на VBA в Excel, я задаю его на этом форуме.
Я разрабатываю небольшую систему учёта документов для организации. Всё уже работает, но захотелось иметь возможность контроля изменений и реализации отката этих изменений. Система работает на связке Excel-VBA + база данных Access, подключаемая через ADODB. Работа системы организована следующим образом: 1. по нажатии кнопки на Ribbon выводится таблица с данными из базы; 2. двойным кликом на строке вызывается форма редактирования строки (Записи); 3. при нажатии на кнопку <Принять> на форме происходит фиксация изменений и обновление таблицы.
На данном этапе при любом изменении Записи, Запись переписывается в базе данных, замещая старые данные:
Код
Dim rs As New ADODB.Recordset
….
rs.Open ………..
If rs.RecordCount > 0 Then
rs.MoveFirst
rs.Update ……….
rs.UpdateBatch adAffectCurrent
End If
rs.Close
Теперь хочу сделать так, чтобы при любом изменении Записи, она не переписывалась, а добавлялась новая Запись с тем же ключом, новым временем изменения (добавления) и пользователем, что внёс эти изменения. Пользователи всегда будут видеть только «последнее состояние записи». Звучит красиво и несложно, но возникает одно большое «но»: засорение базы многими копиями объекта и, как следствие, увеличение объёма базы Напрашивается необходимость принять правило, что надо удалять дублирующиеся Записи со сроком давности более, чем ХХХХ. Для этого необходимо периодически сканировать таблицы в базе данных для поиска таких Записей. Если при программировании на «взрослых» языках программирования можно выделить отдельный процесс, занимающийся таким важным делом, то на VBA придётся встраивать сканирование в основной процесс, замедляя основные операции. Прошу вас высказать своё мнение, подкинуть новую идею, дать совет по вариантам реализации и организации этого процесса.
Skif-F, доброго утра!) Сразу скажу, знаком с VBA весьма слабо)) Первое, о чём я подумал - а почему не делать скан-проверку либо при первой выгрузке базы, либо при закрытии файла? Ну либо настроить расписание тем же макросом, чтобы она выполнялась тогда, когда не будет мешать… А ещё вариант вести log-журнал в txt и "старьё" с данными о замене грузить туда - тогда и чистить базу не придётся, и дублей в ней не будет, и вся инфа о корректировках сохранится. Да и инфа, которая, по сути, важна только разработчику и админу базы (вам) будет в отдельном месте (почта, диск и т.д.), как ей и положено по логике БД быть))
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Skif-F написал: еперь хочу сделать так, чтобы при любом изменении Записи, она не переписывалась, а добавлялась новая Запись
Тем самым, коллега, вы нарушаете реляционный подход, смешивая понятия данных о документах и истории изменений этих данных. По существу, вам нужны две разные таблицы. Таблица данных о документах и таблица истории изменений. В Access 2010 и выше появились триггеры, которые позволяют при любом изменении (вставке, обновлении, удалении) настроить в таблице документов писать автоматически данные предшествующего состояния или записывать состояние до, состояние после - зависит от того, как вы это хотите видеть, в такую таблицу истории. Будет время, вечером постараюсь выложить пример. Таким образом вы разведёте хранение не особенно актуальной для производительности исторической информации и основной таблицы документов, плюс, не придётся переделывать код для обеспечения функциональности отбора самой последней по дате записи о документе. По поводу же размера, просто периодически либо чистите таблицу истории, сохраняя, например 20 последних изменений, либо сбрасывая историю в другую базу (например, по годам).
Андрей VG написал: Тем самым, коллега, вы нарушаете
Две головы всегда лучше, чем одна!
Цитата
Андрей VG написал: По существу, вам нужны две разные таблицы
Идея принята. И она мне нравится! В самом деле, у меня почти не изменится основной код, а все нюансы спрячутся в дополнительный код по управлению изменениями.
Цитата
Андрей VG написал: просто периодически либо чистите таблицу истории
Читая Ваше сообщение, я подумал, что обязанность по очистке таблицы истории изменений от старья можно вменить учётной записи администратора, выдав ему для этого специальную кнопку <Очистить базу>. Спасибо!
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Изначально хотелось использовать MS SQL. Выбор в пользу Access пал вследствие отсутствия опыта работы с MS SQL и наличия в Access графической оболочки, позволяющей заранее собрать базу, не нагружая свою программу лишним кодом. Кстати, вопрос. Надо ли устанавливать SQL Server для использования всех вкусностей MS SQL или же можно так же работать через ADODB, ничего дополнительно не устанавливая?
Андрей VG написал: Боятся, увы, использовать и причины находят ...учитывая наличие Microsoft SQL Management Studio...
В первую очередь - это всё деньги (про пиратство здесь не будем), которые для небольшой подрядной организации могут много значить. А Excel, хочешь-не хочешь, везде стоит, да и к базе Access через ADODB на любом компьютере цепляешься, не зависимо от наличия установленного Access'а Во вторую - когда это не твоя основная обязанность, а занимаешься всем этим факультативно (зачастую по ночам), то установка, настройка, администрирование и изучение MS SQL Server'а могут стать очень большой головной болью!
Skif-F написал: В первую очередь - это всё деньги (про пиратство здесь не будем), которые для небольшой подрядной организации могут много значить.
MS SQL Express - бесплатный (кстати, в отличие от Access) Средство разработки (Managment Studio) так же не требует лицензии. Даже VisualStudio можно совершенно бесплатно использовать без нарушения лицензии.
Цитата
Skif-F написал: Андрей, а что посоветуете: отдельную таблицу в этой же базе или всё-таки другую базу?
Для себя, в обоих случаях, не вижу особой разницы.
p/s не призываю вникать ms sql. Просто для информации
База же на легальном сформирована, а Access Runtime бесплатен тоже. Да и, учитывая скорее всего использование 32битной версии и версии базы 2003 (впрочем гадаю на кофейной гуще), то движок для этого дела есть по умолчанию и на Windows 10. Так что, рассматривая вариант а ля встроенная к файлу Excel с макросами база, тут особых вариантов нет, разве что SQLite For Excel, на индексах раза 3 быстрее Access отрабатывает, но не привычная работа с базой, и бинарный Like, к сожалению. Хотя SQL поприятнее, плюс в отличие от Access 2003 есть триггеры. Для стационарно многопользовательской, без сомнения SQL Server Express смотрится предпочтительнее, уж больно много вкусного.
Цитата
Skif-F написал: что посоветуете: отдельную таблицу в этой же базе или всё-таки другую базу?
Это зависит от объёмов и способов использования. Если предполагается интенсивная работа с запросами к таблице истории, то лучше в одной. Если изредка то в зависимости от объёма данных и способа организации заливки данных из таблицы документов. У Access грустно с размером базы не более 2гигабайт. В этом вопросе сложно что-то рекомендовать в общем - получится сферический конь в вакууме.