vasilaev/accesstosqliteconnector

Предоставляет возможности SQL от движка SQLite в MS Access

Проект предназначен для демонстрации подключения таблиц Access как виртуальные таблицы в SQLLite3. Возможности запросов SQLite шире чем у Acccess, становятся доступны CTE таблицы, рекурсивные запросы, нумерация строк, частичная агрегация, оконные функции и тд. В целом SQL в SQLite является достаточным что бы на нем писать программы, например решение задачи 5 букв.

С таблицами Аccess поддерживаются операторы SELECT, DELETE, INSERT, UPDATE. Для последних трех обязательно должен быть ключевой столбец с типом целое число, для него должен быть создан индекс (на 1 столбец).

Есть поддержка прилинкованных таблиц, но работа по уникальным индексам с ними будет медленнее чем с обычными таблицами.

Внимание! Решение пока не является законченным промышленным. Не выполняйте запросы над чувствительными данными или делайте резервные копии.

Установка

Проверялась работа только в 32 разрядном MS Office. Движок написан на VBScript и подключается к Access через компонент ScriptControl. Данное решение связано с тем что DLL скомпилирована по стандарту cdecl, для VBA нужно что бы было stdcall.

Нативный движок SQLite3 представлен в виде DLL (sqlite3.dll). Можно скачать актуальную версию или версию с большими возможностями (нужно 32 разрядная) с официального сайта SQLite. Версия должна быть не младше 3.10.0.

Для работы с DLL используется компонент DynamicWrapperX. для проверки установлен компонент или нет запустите файл SQLite.vbs. Если выполнится без ошибок значит дополнительно ни чего устанавливать не нужно. Если появилась ошибка, то необходимо зарегистрировать библиотеку dynwrapx.dll как COM объект. Для этого скопируйте файл в постоянное место и выполните команду (Подробнее)

regsvr32.exe <путь-к-компоненту>\dynwrapx.dll — для всех пользователей.
regsvr32.exe /i <путь-к-компоненту>\dynwrapx.dll — для текущего пользователя.

В 64 битной системе в фоне создается окно для работы с 32 битными скриптами.

Начало работы

Добавьте класс SQLiteEngine.cls в проект. При необходимости поправьте пути до файлов sqlite3.dll и SQLite.vbs в функции Class_Initialize.

Пример работы

'Создаем движок
Set Engine = New SQLiteEngine

'Открываем пустую БД в памяти. Можно открыть и физическую
Set SQLite_Connection = Engine.OpenDataBase(":memory:")

'Добавляем возможность подключать виртуальные таблицы из Access
SQLite_Connection.AttachAccessDB CurrentDb(), ""

'Для того что бы можно было работать с таблицами нужно создать их виртуальную копию
SQLite_Connection.Execute "Create virtual table if not exists MyTable using Access(MyTable)"

'Дальше можно открывать набор данных, который по составу методов похож на обычный Recordset
Set SQLite_Recorset = SQLite_Connection.OpenRecordset("select * from MyTable", 0)
If Not SQLite_Recorset.EOF Then
  Debug.Print "Name = [" & SQLite_Recorset.Fields(0).Name & "] Value = [" & SQLite_Recorset.Fields(0).Value & "]"
End If
SQLite_Recorset.Close
Set SQLite_Recorset = Nothing

'для удаления таблиц используйте команду 
SQLite_Connection.Execute "drop table if exists MyTable"

'Хорошим тоном является все закрыть за собой
SQLite_Connection.Close  

Ограничения

Поддержка русского языка частичная,

• Поля на кириллицы становятся регистр зависимыми. т.е. как записано в БД так к ним и нужно обращаться.
• Не работают функции Lower Upper.

В SQLite всего 4 базовых типа данных

• Integer - 32 битное целое
• Double - вещественное двойной точности
• Text - Строки
• Blob - Двоичные строки

В данном проекте поддерживаются только первые три.

Тип данных Access	Тип данных SQLite	Примечание
Boolean, Byte, Integer и Long	Integer	True приводится к 1, False к 0
Date, Timestamp, Time	Double	В SQLLite Есть поддержка даты и времени но их кодирование отличается от кодирования в Access. Если нужно сравнить дату Access с датой SQLLite к первой нужно прибавить 2415018.5.
Double, Float, Single	Double
Все остальные	Text

Результаты запроса нельзя куда-либо вывести, только программная обработка.

Вывод отладочных сообщений

Для вывода отладочных сообщений в файл используйте метод LogToFile класса SQLiteEngine

Engine.LogToFile CurrentDb().Name & ".log"

Так же сообщения можно перенаправить в любое другое место. Для этого создайте объект с методом Output принимающий единственный параметр - текстовое сообщение лога.

Например вывод в текстовое поле на форме:

Public Sub Output(text)
  If Me.log.Value <> "" Then Me.log.Value = Me.log.Value & (vbCrLf & text) Else Me.log.Value = Me.log.Value & text
End Sub

Затем подключите логирование

Engine.ScriptControl.Run "SetPrintProvider", Me

Комментарии к Demo

Тестовая БД для быстрой демонстрации. Основная форма предоставляет графический интерфейс к основным функциям.

Описание элементов управления:

• Кнопка "Создать подключение SQLite". Создает подключение и инициализирует ресурсы. Эту кнопку жмем первой
• Кнопка "Подключить таблицы ACCESS". Создает линки в SQLite на таблицы Access.
• Кнопка "Показать список доступных таблиц" - Выводит в окно вывода информации список доступных таблиц. Если ничего не менять, то будет доступно три таблицы из Access
• Кнопка "Показать лог" - Показывает или прячет окно логирования
• Кнопка "Очистить лог" - Очищает поле с логом
• Кнопка "Включить логирование" - Выводит в окно лога отладочные сообщение об этапах выполнения запроса
• Поле "Введите запрос" - Окно ввода для запросов. Перед вводом запросов нужно прожать кнопки "Создать подключение SQLite" и "Подключить таблицы ACCESS"
• Кнопка "Выполнить" - Выполняет запрос из поля "Введите запрос" и выводит результаты выполнения в окно вывода информации.
• Кнопка "Следующий пример" - Выводит из таблицы SQLExamples в поле "Введите запрос" очередной пример и отправляет его на выполнение. Каждый пример начинается с постановки в комментарии и затем идет SQL запрос с решением.
• Список вывода информации. Сюда выводится результат выполнения запроса. Размер колонок можно менять, таская их за границы.