MS Access и 1С. Что, когда и зачем?
Разработка - Практика программирования
Немного слов
Время показало, что приложения для работы с электронными таблицами, в том числе и Microsoft Excel, имеют потрясающие возможности для сбора, систематизации и анализа различных данных. Трудно представить бизнес, где бы не использовали этот инструмент. Вне зависимости от размера компании в ней обязательно используют электронные таблицы в том или ином виде.
Все те, кто работают с платформой 1С и решениями на ее основе не по наслышке знают, что Excel является чуть ли не главным инструментом бухгалтеров, аналитиков, финансистов и многих других специалистов.
Коллеги, Вы только вспомните сколько было сделано выгрузок из 1С в Excel! Сколько человеко-часов на это было потрачено!
Нет, нет! Excel ни в коем случае не является заменой учетных систем 1С, ведь назначение у них совершенно разное. Уберите оружие! Он скорее инструмент локального пользования для более гибкой обработки данных, ведь в 1С это не всегда сделать также просто, как в Excel.
Но если все так хорошо, то почему статья будет про Access? Неужели им кто-то еще пользуется? И на самом деле пользуется!
Excel vs. Access
Эпичной битвы не будет! Мы просто сравним два отличных инструмента по 9 критериям и опишем ситуации, когда Access может быть более подходящим вариантом.
| Критерий | Excel | Access |
| Назначение | Создание графиков, диаграмм и различных моделей данных. | Приложение базы данных для сбора и сортировки данных. |
| Использование | Построение финансовых, статистических и других моделей, а также возможность проверки данных при вводе. | Сбор, сортировка и изменение информации в базах данных. |
| Размер хранилища | Имеет ограничения по количеству строк (1 048 576) и столбцов (16 384), а также другие ограничения. Подробнее смотреть здесь. | Ограничения на макс. размер базы (2 ГБ) и другие. Подробнее смотреть здесь. |
| Возможность доработки | Очень просто. Это может сделать любой пользователь Excel, конечно, если дело не касается макросов или других специфичных вещей. | Сложно, поскольку требуются знания по построению баз данных и других особенностей. |
| Применимость | Больше всего подходит для анализа данных (финансовых, статистических или любых других). | Больше подходит для хранения данных. Ориентировано больше на малый бизнес. |
| Изучение | Прост в изучении. | Тяжелее в изучении по сравнению с Excel. |
| Реляционный или линейный | Не реляционное хранилище данных. | Для хранения используется реляционная модель. |
| Знание программирования | В большинстве случаев знания программирования не нужны. | Знания программирования обязательны для работы с базами данных Access. |
И так, Access меньше ограничен в объемах выгрузки данных и позволяет строить реляционную модель хранения информации. Кроме того, можно делать произвольные SQL-запросы к таблицам для обработки данных. Во всем остальном Excel удобнее и эффективнее. Это если смотреть общую информацию, поэтому поклонников Access прошу не идти на несанкционированный митинг в комментариях! Тем более никто не мешает подключиться к базе Access из Excel и работать в последнем.
Таким образом, когда нужно выгрузить информацию из базы 1С в большом объеме и при этом у конечного потребителя нет навороченной СУБД, или просто стандарт работы через Access, или же есть реализованная на нем бизнес-логика, то почему бы не использовать его? Также иногда нужно обойти ограничение Excel в 1 миллион строк и выгрузить данные одним набором. В последнем случае база данных Access также может быть хорошим выбором.
Конечно, вместо Excel или Access есть масса других вариантов, но в зависимости от требований они не всегда могут подходить:
- CSV-файлы - их трудно анализировать, да и подходит это больше для последующей загрузки в другой источник данных.
- База SQL Server / PostgreSQL - отличный вариант, вот только не все конечные пользователи могут позволить себе настройку этого ПО.
- Разбить выгрузку Excel на несколько файлов? Отлично! Попробуйте собрать отчетность из 15 файлов Excel по 1 миллиону записей, тогда и поговорим!
- И др.
Далее поверхностно пробежимся по вопросу лицензирования и наконец-то перейдем к разработке.
Немного про лицензии
Этот вопрос очень важный, поскольку можно здорово напортачить, от чего в будущем могут быть проблемы из-за нарушения лицензионного соглашения Microsoft на их ПО. Дело тут вот в чем.
Выгрузка или другие действия в 1С должны выполняться на стороне сервера 1С, т.к. только там мы можем работать с запросами, таблицами значений или с системой компоновки данных. Конечно, есть еще толстый клиент и файловые базы, но сейчас они не представляют особого интереса, ведь тонкий клиент более предпочтительный вариант для прикладных решений. А для файловых баз проблема лицензирования офиса вообще не стоит, т.к. обычно все кто с этой базой работают уже имеют установленный офисный пакет. Получается, что для того, чтобы сервер 1С мог работать с базами данных Access нужно выполнить одно из следующих действий на сервере 1С:
- Установить полный пакет Microsoft Access.
- Установить пакет "Microsoft Access Database Engine 2016 Redistributable".
- И последний вариант - установить "Microsoft Access 2016 Runtime".
Самым простым вариантом выглядит установка полного пакета приложения MS Access на сервере, но это сильно нарушает лицензионное соглашение. В этом случае потребуется покупка лицензий на Office не только для сервера, но и клиентские лицензии для всех пользователей, которые работают с этим сервером. В нашем случае для всех, кто работает в 1С на этом сервере. А если пользователей 1000? И их становится все больше? Докупать лицензии? Это будет не дешево. В некоторых случаях лицензия может вообще запрещать такое развертывание, но на подробностях уже не будем останавливаться.
Второй вариант - это установка только ODBC-драйвера в пакете Microsoft Access Database Engine 2016 Redistributable. Да, это будет работать! Access устанавливать не нужно, а мы сможем работать с базой данных Access через ADO-соединение. Но и тут не все просто! Если перейти по ссылке и посмотреть детальную информацию, то там черным по белому написано, что этот пакет не может использоваться для:
- Для замены Ace (нас это сейчас не интересует).
- Для замены Jet OLEDB (из 1С тоже можно работать через Jet с базами Access, но это уже другая история).
- Как замена основных систем управления базами данных, электронными таблицами или документами.
- И самый главный пункт - использоваться как системная служба или программа серверной части приложения.
Фактически, этот пакет предназначен для клиентских компьютеров, которые подключаются к различным источникам данных
Третий вариант - установка Microsoft Access 2016 Runtime. Согласно информации на странице, для установки и распространения не требуется покупка дополнительных лицензий, т.к. этот пакет содержит лишь среду выполнения, которая используется для запуска уже готовых решений. Средства разработки в ней отсутствуют. При этом в состав пакета также входит установщик ODBC-драйвера, который нам и нужен.
На практике видел использование второго и третьего варианта, причем аудит от Microsoft не выявлял в этом случае никаких нарушений. Возможно, установка Microsoft Access Database Engine 2016 Redistributable формально и может являться нарушением соглашения, но по факту его никто не проверяет.
Все вышесказанное относится и к другим продуктам MS Office, в том числе и Excel, Word и т.д. Интересная информация по лицензированию есть здесь, можете прочитать там про "Access Runtime".
Нашли ошибку или не согласны что лицензирование работает именно так? Пишите в комментариях!
Простые примеры
В основном мы сосредоточимся на задачах выгрузки данных в Access. Операции загрузки также возможна, но она всегда достаточно простая и очень сильно завязана на условиях задачи (что и куда загрузить, как преобразовывать данные из базы и т.д.). Приведу лишь небольшой пример загрузки данных.
ФайлБазы = "D:\Каталог баз\ПримерБазы.accdb";
// Инициализация подключения к базе
СтрокаПодключения = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" + ФайлБазы;
Connection = Новый COMОбъект("ADODB.Connection");
Connection.Open(СтрокаПодключения);
// Формируем команды чтения данных из таблицы "Выгрузка_результата_компоновки"
Command = Новый COMОбъект("ADODB.Command");
Command.ActiveConnection = Connection;
Command.CommandText = "Select * FROM Выгрузка_результата_компоновки";
Command.CommandType = 1;
RecordSet = Command.Execute();
// Считываем все поля и выводим пользователю
ЗначенияСтрокой ="";
Пока RecordSet.EOF() = 0 Цикл
Для НомерПоля = 0 по Recordset.Fields.Count - 1 цикл
ЗначенияСтрокой = ЗначенияСтрокой + " " + Recordset.Fields(НомерПоля).Value;
КонецЦикла;
Сообщить(ЗначенияСтрокой);
ЗначенияСтрокой = "";
RecordSet.MoveNext();
КонецЦикла;
// Освобождаем ресурсы
RecordSet.Close();
Connection.Close();
Все просто - создаем соединение с базой данных Access через ADO, создаем команду чтений данных в виде набора и обрабатываем его. После освобождаем ресурсы.
С выгрузкой данных все куда интересней, потому что проблемы сложнее:
- ODBC-драйвер не позволяет создать пустую базу. Тут либо иметь уже готовый файл с нужными таблицами или придумывать альтернативный вариант (что и было сделано, но об этом позже).
- При выгрузке всегда одного набора данных задача упрощается, но что если нужно выгружать каждый раз разные наборы. Например, нужно выгружать отчет на СКД и учитывать, что настройки (выводимые поля) пользователь может изменять. То есть нам нужно научиться создавать таблицы в Access, сопоставляя типы 1С с доступными типами MS Access.
- Нужно учесть ограничения базы Access на различные типы, а также на сам ODBC-драйвер. Например, что выгрузка в Access не поддерживает даты меньше 1753 года, если используется ODBC-драйвер.
- Также необходимо учитывать максимальный размер базы данных в 2 ГБ. Тут в качестве решения может быть настройка максимального количества выгружаемых записей для одной базы. В случае необходимости выгрузка будет выполняться в несколько порций.
То есть на практике нужно быть готовым к выгрузке произвольных наборов данных. которые в 1С могут быть представлены таблицей значений, запросов или результатом компоновки данных отчета. Если бы этих проблем не стояло, то алгоритм выгрузки был бы примерно такой.
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| &ТекущаяДата КАК Дата,
| ""Привет из Access"" КАК Строка";
Запрос.УстановитьПараметр("ТекущаяДата", ТекущаяДата());
ТаблицаИсточник = Запрос.Выполнить().Выгрузить();
// Подготовленная база Access для выгрузки
ПутьКБазе = "C:\Access\ПростаяВыгрузка.accdb";
СтрокаПодключения = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" + ПутьКБазе;
СоединениеКБазе = Новый COMОбъект("ADODB.Connection");
СоединениеКБазе.Open(СтрокаПодключения);
ИмяТаблицы = "ПростаяВыгрузка";
Запись = Новый COMОбъект("ADODB.RecordSet");
ТекстЗапроса = "SELECT * FROM " + ИмяТаблицы;
Запись.Open(
// Текст запроса
ТекстЗапроса,
// Соединение с базой
СоединениеКБазе,
// Указывает тип курсора, используемого в записей объекта.
// CursorType (https://docs.microsoft.com/ru-ru/sql/ado/reference/ado-api/cursortypeenum?view=sql-server-2017)
// 1 = adOpenKeyset. Использует курсор набора ключей.
1,
// Тип блокировки
// LockTypeEnum (https://docs.microsoft.com/ru-ru/sql/ado/reference/ado-api/open-method-ado-recordset?view=sql-server-2017)
// 3 = adLockOptimistic (Указывает, оптимистической блокировки, записей.)
3
);
// Добавляем записи в таблицу базы Access
// В исходном файле первая колонка содержит дату,
// а во второй сохраняем строку.
Для Каждого СтрокаТаблицы Из ТаблицаИсточник Цикл
Запись.AddNew();
Запись.Fields(0).Value = СтрокаТаблицы.Дата;
Запись.Fields(1).Value = СтрокаТаблицы.Строка;
Запись.UpDate();
КонецЦикла;
СоединениеКБазе.Close();
СоединениеКБазе = Неопределено;
Но вернемся все же к реальным требованиям. Для упрощения разработки и сопровождения сделаем прокси-функцию, которая позволит выгружать в Access таблицы значений, запросы и результаты СКД.
Прокси, прокси, прокси
Начнем с описания что в итоге нужно получить. Интерфейс выгрузки будет представлен тремя функциями:
- ВыгрузитьТаблицуЗначений(КаталогВыгрузки, ИсточникДанных)
- ВыгрузитьЗапрос(КаталогВыгрузки, ИсточникДанных)
- ВыгрузитьРезультатКомпоновки(КаталогВыгрузки, СхемаКомпоновкиДанных, Настройки)
Примеры работы с ними ниже под спойлером.
Но как были решены все те проблемы, о которых говорилось выше? Ответим по порядку!
Создать базу данных можно двумя способами:
- Если на клиенте есть установленный MS Access, то базу можно создать с его помощью, в т.ч. и инициализировать нужные таблицы. Так мы получим шаблон базы данных нужной структуры, а позже его уже можно передать на сервер и работать с ним с помощью ODBC.
- Другой вариант - можно создать пустой файл базы данных Access без таблиц и сохранить его, например, в общий макет. На сервере для создания базы данных используем данные макета, а создание таблиц выполняем с помощью ODBC.
Но что на счет описания типов полей таблицы и как его получить для типов платформы 1С? В этом случае нужно обрабатывать несколько ситуаций:
- Тип значения поля составной.
- Тип значения "Строка" как с указанной длиной, так и неограниченная.
- Числовой тип разной длины и разрядности.
- Тип булево.
- Тип дата.
- Ссылочные типы.
- Остальные типы, включая служебные.
Общее соответствие типов можно сделать такое.
| Тип 1С | Тип Access |
| Составной тип (может включать примитивные типы и ссылочные) | CHAR(255), в случае превышения длины строки значение будет обрезаться. |
| Строка | CHAR(<Размер>). Размер определяется длиной строки в описании типа (макс. длина 255). Если это значение - представление какой-либо ссылки, но нужно получить длину поля представления (код, наименование) и присвоить его длине строки в Access. |
| Число | Если у числа явно указано, что оно может быть только целым, то используем "INTEGER". В остальных случаях "DOUBLE" с различной длиной и разрядностью дробной части. |
| Булево | BIT |
| Дата | DATETIME |
| Ссылочные и другие типы | CHAR(<Размер>). Длину указываем как 150 символов по умолчанию. |
По поводу проверки на ограничения типов, например для дат, можно использовать такие функции.
Ограничение базы данных Access на 2 Гб можно обойти, разбивая ее на несколько файлов. Тут либо разбивать по размеру базы, каждый раз проверяя текущий размер файла базы, либо задав ограничение на количество записей.
Полная реализация выгрузки с примерами доступна в репозитории на GitHub - "1C-Plus-MSOffice", где постепенно будут добавляться различные примеры, алгоритмы и инструкции по работе с продуктами MS Office из 1С. Планируется добавить такой функционал и инструкции:
- Выгрузка в Excel и Word на сервере без использования MS Office.
- Описание штатных возможностей платформы для выгрузки в Excel.
- Оптимизация выгрузки больших массивов данных в Excel (минимальное использование памяти и др.).
- Описание формата файлов документов Office.
- Использование Outlook и шифрования в нем.
- И др.
Содержимое репозитория не является готовым решением, а лишь помогает, объясняет и ускоряет разработку. Весь материал лишь признан помогать, а не выполнять работу за Вас (разработчиков).
Замечания, предложения, issues и pull-request'ы приветствуются!
После всего
Мы рассмотрели использование Access для выгрузки данных из баз 1С, некоторые нюансы лицензирования и небольшие примеры выгрузки и загрузки данных. Также продемонстрированы прокси-функции для выгрузки таких типов данных 1С как таблица значений, запрос и результат компоновки данных. Смотрите в репозиторий "1C-Plus-MSOffice", там будут добавляться новые функции, инструкции и различные фичи при работе с приложениями MS Office.
Спасибо, что что Вы все еще здесь! :)
Удачи!
Другие ссылки
Специальные предложения
См. также
Онлайн-курс "Технология выполнения проектов ERP-класса – процессный подход". Третий поток. Курс проходит с 21 января по 18 марта 2020 года. Промо
Курс разработан Внедренческим центром «Раздолье». Курс предназначен для подготовки аналитиков, архитекторов и руководителей проектов автоматизации процессов управления с использованием комплексных ИТ-систем (1С:ERP, 1С:УХ, 1С:КА, 1С:УТ). В основе курса лежит методика применения процессного подхода.
9000 рублей
Перенос данных КА 1.1 / УПП 1.3 => БП 3.0 (перенос остатков, документов и справочников из "1С:Комплексная автоматизация 1.1" / УПП 1.3 в "1С:Бухгалтерия 3.0"). Обновлен до версий КА 1.1.115.х, УПП 1.3.127.х! Промо
Разработка позволяет перенести остатки по всем счетам бух.учета в программу "1С:Бухгалтерия предприятия 8", ред. 3.0 на выбранную дату начала ведения учета. Также переносятся документы за период и вся необходимая справочная информация. Правила оперативно обновляю при выходе новых релизов. Рассылка обновлений правил бесплатно в течение 12 месяцев. Есть видеодемонстрация проведения переноса данных. Конфигурации при использовании обмена остаются полностью типовыми. Перенос данных возможен в Бухгалтерию 3.0 версии ПРОФ, КОРП или базовую.
24700 руб.
Функции СКД: ВычислитьВыражение, ВычислитьВыражениеСГруппировкойМассив 262
08.08.2019 18442 ids79 31
Перенос данных БП 3.0 => УТ 11 / КА 2 / ERP 2 (ЕРП) (перенос остатков, документов и справочной информации из "1С:Бухгалтерия предприятия 8", ред.3.0). Обновлено до БП 3.0.73.х, УТ 11.4.10.х, КА 2.4.10.х., ERP 2.4.10.х! Промо
Переносятся документы за выбранный период, справочная информация и остатки по счетам бух. учета в программу УТ 11 / КА 2 / ЕРП 2 (ERP). Переносятся все возможные виды операций ввода остатков на нужную дату. Есть отбор по периоду переноса документов и фильтр по организации, доступен выбор даты ввода остатков. Если нужно переносить что-то дополнительно, то обычно бесплатно добавляем это в перенос . Смотрите видеодемонстрацию со звуком - советами по переносу и рекомендациями настройки программ.
29700 руб.
Перенос документов и справочников ERP 2 / КА 2 / УТ 11 => БП 3.0 Промо
Перенос позволяет настроить собственный обмен данными между указанными программами, альтернативный предлагаемому фирмой 1С. Предоставляем техподдержку по всем вопросам данного обмена. Можем подключиться к вам удаленно для разбора ситуаций. Оперативно обновляем при выходе новых релизов 1С. Бесплатные обновления в течение полугода.
19700 руб.
Как настроить правильную техподдержку (helpdesk, service desk на коленке) 38
24.04.2019 9150 siddy 0
С 2020 года сервис «Продление поддержки конфигурации 1С:УПП» подорожает вдвое Промо
Успейте продлить поддержку УПП до повышения цен! Фирма «1С» предупредила об изменении цен на сервис «Продление поддержки конфигурации "1С:Управление производственным предприятием"». С 1 января 2020 года сервис подорожает в два раза.
Преобразование EXCEL в таблицу значений без COM и других извращений 213
18.04.2019 14726 9 Eret1k 43
Перенос документов, остатков и справочников КА 1.1 => КА 2 / УТ 11. Обновлено до КА 2.4.10.х и УТ 11.4.10.х! Промо
Более 130 компаний выполнили переход на КА 2 или УТ 11 с помощью нашей разработки! Позволяет перенести не только остатки и справочники (как типовая обработка), но и документы за нужный период времени. Предоставляем техподдержку, оперативно исправляем замечания, выпускаем обновления при выходе новых релизов программ 1С. Вы можете проверить разработку до покупки: сделаем бесплатный тестовый перенос из вашей базы КА 1.1 и предоставим доступ к базе-результату через веб-клиент!
29700 руб.
Новый раздел на Инфостарте - Electronic Software Distribution Промо
Инфостарт напоминает: на нашем сайте можно купить не только ПО, связанное с 1С. В нашем арсенале – ESD-лицензии на ПО от ведущих вендоров: Microsoft, Kaspersky, ESET, Dr.Web, Аскон и другие.
- Низкие цены, без скрытых платежей и наценок
- Оперативная отгрузка
- Возможность оплаты с личного счета (кешбек, обмен стартмани на рубли и т.п.)
- Покупки идут в накопления для получения скидочных карт лояльности Silver (5%) и Gold (10%)
Разработка и сценарное тестирование с Vanessa-ADD. Концепция, теория и сквозной пример создания сценария 226
09.01.2019 29464 Vladimir Litvinenko 69
Универсальные функции ЗУП 3.1 / ЗКГУ 3.1, которые помогут в разработке 506
14.11.2018 38946 GeterX 94
Подборка решений для взаимодействия со ФГИС «Меркурий» Промо
С 1 июля 2019 года все компании, участвующие в обороте товаров животного происхождения, должны перейти на электронную ветеринарную сертификацию (ЭВС) через ФГИС «Меркурий». Инфостарт предлагает подборку программ, связанных с этим изменением.
Автоматические и управляемые блокировки применительно к типовым конфигурациям 1С 129
10.11.2018 23788 ids79 40
Git + 1С. Часть 1. Как подключиться к команде разработки и начать использовать Git 281
18.10.2018 39556 stas_ganiev 72
Перенос данных КА 1.1 => ERP 2 (ЕРП) (обработка переноса документов, остатков и справочной информации из "1С:Комплексная автоматизация, ред. 1.1" в "1С:ERP Управление предприятием, ред 2"). Обновлен до КА 1.1.115.х и ERP 2.4.10.х Промо
Обработка позволяет переносить из КА 1.1 в ERP 2 документы за выбранный период и остатки. Типовая обработка от фирмы 1С документы не переносит. Также исправлены ошибки типовой обработки. При выходе новых релизов обновление высылается бесплатно в течение года. Разработка будет полезна фирмам-франчайзи, которые периодически выполняют такой перенос данных для заказчиков. Вы можете один раз приобрести обработку переноса, и потом бесплатно получать обновления в случае выхода новых релизов конфигураций 1С.
29700 руб.
Подборка программ для взаимодействия с ЕГАИС Промо
ЕГАИС (Единая государственная автоматизированная информационная система) - автоматизированная система, предназначенная для государственного контроля за объёмом производства и оборота этилового спирта, алкогольной и спиртосодержащей продукции. Инфостарт рекомендует подборку проверенных решений для взаимодействия с системой.
Очный семинар по регулярному менеджменту Александра Фридмана "Вы или Хаос", 12 декабря 2019 г. , Санкт-Петербург Промо
Семинар по регулярному менеджменту от Александра Фридмана для собственников, первых лиц и топов. Технология управленческого планирования, комплексного управления временем и другими ресурсами, выполнением поручений, делами, информацией, контактами (встречи-звонки-почта).
от 11000 до 29000 рублей
1СПАРК РИСКИ. Сервис оценки благонадежности контрагентов. Промо
СПАРК помогает предотвратить мошенничество со стороны компаний и предпринимателей, благодаря актуальным сведениям о компаниях и системе выявления факторов риска.Сервис позволяет управлять налоговыми рисками и комплексно оценивать благонадежность контрагентов.
Универсальный обмен между идентичными конфигурациями через REST интерфейс OData. Часть І: Справочники 96
11.05.2018 17633 V.Stavinsky 11
