Каталог решений - MS Access и 1С. Что, когда и зачем?

Немного слов

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

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

Коллеги, Вы только вспомните сколько было сделано выгрузок из 1С в Excel! Сколько человеко-часов на это было потрачено!

Нет, нет! Excel ни в коем случае не является заменой учетных систем 1С, ведь назначение у них совершенно разное. Уберите оружие! Он скорее инструмент локального пользования для более гибкой обработки данных, ведь в 1С это не всегда сделать также просто, как в Excel.

Но если все так хорошо, то почему статья будет про Access? Неужели им кто-то еще пользуется? И на самом деле пользуется! 

Excel vs. Access

Эпичной битвы не будет! Мы просто сравним два отличных инструмента по 9 критериям и опишем ситуации, когда 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, создаем команду чтений данных в виде набора и обрабатываем его. После освобождаем ресурсы.

С выгрузкой данных все куда интересней, потому что проблемы сложнее:

1. ODBC-драйвер не позволяет создать пустую базу. Тут либо иметь уже готовый файл с нужными таблицами или придумывать альтернативный вариант (что и было сделано, но об этом позже).
2. При выгрузке всегда одного набора данных задача упрощается, но что если нужно выгружать каждый раз разные наборы. Например, нужно выгружать отчет на СКД и учитывать, что настройки (выводимые поля) пользователь может изменять. То есть нам нужно научиться создавать таблицы в Access, сопоставляя типы 1С с доступными типами MS Access.
3. Нужно учесть ограничения базы Access на различные типы, а также на сам ODBC-драйвер. Например, что выгрузка в Access не поддерживает даты меньше 1753 года, если используется ODBC-драйвер.
4. Также необходимо учитывать максимальный размер базы данных в 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 таблицы значений, запросы и результаты СКД.

Прокси, прокси, прокси

Начнем с описания что в итоге нужно получить. Интерфейс выгрузки будет представлен тремя функциями:

• ВыгрузитьТаблицуЗначений(КаталогВыгрузки, ИсточникДанных)
• ВыгрузитьЗапрос(КаталогВыгрузки, ИсточникДанных)
• ВыгрузитьРезультатКомпоновки(КаталогВыгрузки, СхемаКомпоновкиДанных, Настройки)

Примеры работы с ними ниже под спойлером.