Организация работы MS Access-приложения с MySQL

Почему MySQL?
Преимущества MySQL перед mdb-файлом слишком очевидны, чтоб долго на них останавливаться. Это преимущество полноценного SQL-сервера перед файл-серверной рептилией. Для работы с базой данных , хранящейся в файле -- mdb, нужно иметь полный доступ к нему. Вот тебе, бабушка, и удалённый доступ, и надёжность данных. Мне приходилось в двух организациях менять сетевые карты всем клиентским компьютерам, чтоб файл mdb не разрушался, выдавая Нераспознаваемый формат базы данных (хорошо хоть MS Access, начиная с версии 2000, научился их восстанавливать). О транзакциях, триггерах и других чертах современной базы данных разговор даже не идёт: эти черты даже не имитируются в mdb -- файлах.

Но MS Access может взаимодействовать и с другими SQL-серверами, и Microsoft подталкивает нас к взаимодействию с MS SQL Server, снабжая нас для этого всем необходимым. Но я выбрал "мускул", руководствуясь следующими причинами.

-- Официальная бесплатность для разработчика
-- Возможность легально скачать дистрибутив (от 16 до 50 мегабайт в зависимости от вида дистрибутива) из Интернета
-- Кроссплатформенность (не надо беспокоиться, под какой ОС работает сервер)
-- Простота установки (вплоть до простого копирования!) и обновления
-- Наличие полной и внятной документации
-- То есть MySQL -- это сервер, который всегда с тобой

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

1. Установить на сервере MySQL
2. Настроить поддержку кириллицы для MySQL
3. Создать базу данных Вашего приложения
4. Установить драйвер ODBC на все машины, на которых работает Ваше приложение
5. В случае MS Access 2003 -- установить обновления для корректной работы ODBC
6. Изменить код приложения -- для автоматического подключения таблиц

Рассмотрим эти этапы. Изложение я буду вести по принципу sapienti sat (умному достаточно), и прошу прощения у тех, кто не смог извлечь из моих инструкций пользу -- постараюсь ответить на письма (адрес Вы найдёте на странице http://db-maker.narod.ru/contactrus.htm). Итак...

Установка на сервере MySQL

Предполагаем, что сервер у Вас на Windows. Если это не так -- Вам нужно обращаться к документации (http://dev.mysql.com/doc/refman/4.1/en/installing.html).
Идёте на страницу http://dev.mysql.com/downloads/, первый раздел MySQL Community Server, выбираете рекомендуемый, т.е. Current Release (Recommended) (на 8 февраля 2007 это был MySQL 5.0), и скачиваете версию для операционной системы Вашего сервера.

Для Windows есть три типа дистибутива

-- Windows Essentials (x86) -- дистрибутив для Windows 2000 и выше
-- Windows (x86) ZIP/Setup.EXE -- дистибутив для более ранних версий
-- Without installer (unzip in C:\) -- версия, устанавливаемая простым копированием
При установке с дистибутива следуйте инструкциям инсталлятора; ряд настроек мы всё равно зададим сами в конфигурационнома файле -- об этом следующий параграф.

Запускается MySQL под WIndows двумя способами:

Запуском файла mysqld.exe
Запуском службы, созданной при инсталляции (если Вы при инсталляции указали, что нужно создать службу MySQL)

Настройка поддержки кириллицы для MySQL

При работе MySQL под Windows Вы можете конфигурировать его параметры с помощью файла мy.ini

Документация (http://dev.mysql.com/doc/refman/5.1/en/mysql-config-wizard-editing.html) утверждает, что его лучше размещать в папке Windows (C:\WINDOWS или C:\WINNT), но у меня он прекрасно работает в корневой папке сервера MySQL (G:\MySQL).

Приведу пример конфигурационного файла мy.ini, позволяющий подерживать кириллицу. В этом файле позволяются комментарии, начинающиеся с # -- чем я и воспользуюсь для отметки строк, отвечающих за кириллицу.

Вам придётся изменить в этом файле пути для своего сервера, о чём смотрите комментарии.

Итак, конфигурационный файл мy.ini.

[client]
port        = 3306

#Конфигурационные параметры для сервера MySQL
[mysqld]
port        = 3306
socket        = /tmp/mysql.sock
skip-locking
key_buffer = 16K
max_allowed_packet = 1M
table_cache = 4
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 64K
# Установка кириллицы на сервере
default-character-set=cp1251   #Указание кодировки
character-sets-dir=g:/mysql/share/charsets   #Указание пути к папке кодировок (скорректируйте для своего сервера!)

server-id    = 1

# Конфигурационные параметры для программы резервного копирования
[mysqldump]
quick
max_allowed_packet = 16M
# Установка кириллицы на сервере
default-character-set=cp1251   #Указание кодировки
character-sets-dir=g:/mysql/share/charsets   #Указание пути к папке кодировок (скорректируйте для своего сервера!)

# Конфигурационные параметры для программы-клиента mysql.exe
[mysql]
no-auto-rehash
# Установка кириллицы на сервере
default-character-set=cp1251   #Указание кодировки
character-sets-dir=g:/mysql/share/charsets   #Указание пути к папке кодировок (скорректируйте для своего сервера!)

[isamchk]
key_buffer = 8M
sort_buffer_size = 8M

[myisamchk]
key_buffer = 8M
sort_buffer_size = 8M

[mysqlhotcopy]
interactive-timeout

Создать базу данных Вашего приложения

Подключение к базе данных осуществляется от имени пользователя. После инсталляции на сервере присутсвует один пользователь root@localhost с пустым паролем (в ранних версиях был ещё один -- root@'%', но в последних версиях его уже нет. Думаю, по соображениям безопасности). Так что первіе действия Вам придётся осуществить на сервере.

Входите в командную строку Windows (Start/Пуск → Run/Выполнить → cmd), устанавливаете текущим каталогом подкаталог bin каталога MySQL (в моём случае это G:\mysql\bin):
F:\>G:
G:\>cd mysql\bin

и набираете команду подключения к MySQL

G:\mysql\bin>mysql -uroot

Хост и порт приняли значения по умолчанию -- соответственно localhost и 3306, пароля у root пока нет (сейчас мы это исправим). И появилось приглашение MySQL вводить команды SQL:
G:\mysql\bin>mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.27-community

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

Первое, что мы проделаем -- изменим пароль root: это очевидное требование безопасности.

mysql> update mysql.user set password=Password('ВашПароль') where user='root' and host='localhost';
Query OK, 0 rows affected (0.06 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.08 sec)

mysql>

Пароль изменён. Но наш сеанс идёт ещё под старым паролем. Прервём наш сеанс и зайдём уже с новым паролем.

mysql> quit;
Bye

G:\mysql\bin>mysql -uroot -p
Enter password: *********

Если мы нигде не ошиблись -- мы снова видим приглашение MySQL и начинаем работу. Предполагается, что приложение работает в локальной сети, IP-адреса в которой начинаются с 192.168 (в противном случае надо скорректировать нижеследующую инструкцию grant).

mysql> /*создадим пользователя, под которыми будет заходить приложение MS Access */
mysql> grant all privileges on ИмяБазыДанных.* to 'логин'@'192.168%' identified by 'пароль';
Query OK, 0 rows affected (0.03 sec)

mysql> /*Создадим базу данных нашего приложения*/
mysql> create database ИмяБазыДанных;
Query OK, 1 row affected (0.00 sec)

mysql> /*Создаём таблицы*/
mysql>

Создавать таблицы в командной строке MySQL слишком утомительно. Поэтому Вам лучше выбрать один из двух оптимальных путей.

Если у Вас есть база данных в mdb-файле, воспользуйтесь инструкциями флеш-ролика по адресу http://www.mysql.com/products/tools/migration-toolkit/tutorials/AccessMigrationTutorial.html
Если Вы создаёте базу данных с нуля, Вам лучше в текстовом файле перечислить инструкции create table для вновь создаваемых таблиц. Синтаксис инструкции create table изложен по адресу http://dev.mysql.com/doc/refman/4.1/en/create-table.html, надеюсь, Вы разберётесь в нём самостоятельно. Затем запускаете в командной строке Windows команду

G:\mysql\bin>mysql -uroot -p ИмяБазыДанных<Путь к файлу с инструкциями create table

У Вас будет запрошен пароль и, если Вы нигде не ошиблись, таблицы будут созданы

Установка драйвер ODBC на все машины, на которых работает Ваше приложение

Идёте на страницу http://dev.mysql.com/downloads/, ищете раздел Connector/ODBC - MySQL ODBC driver, выбираете рекомендуемый, т.е. Generally Available (GA) release (на 8 февраля 2007 это Connector/ODBC 3.51 ), и скачиваете инсталлятор.

Запускаете инсталлятор, никаких значимых для установки вопросов он не задаст. О каких-либо проблемах с установкой этого драйвера я даже не слышал.

В случае MS Access 2003 -- установка обновления для корректной работы ODBC

MS Access 2003 имеет дефект работы с ODBC под Windows 2000 (под XP не пробовал, но, насколько мне известно, ситуация идентична). Лечится это установкой пакета обновлений Jet 4.0 SP8 для Windows 2000 Этот пакет имеет версии для различных языковых версий Windows (обратите внимание -- не MS Access, а именно Windows). Скажем, для русскоязычной версии Windows 2000 пакет обновлений Jet 4.0 SP8 находится по адресу
http://www.microsoft.com/downloads/details.aspx?displaylang=ru&FamilyID=1e268b39-533e-48b0-b8d7-1781befde1f8

Изменить код приложения -- для автоматического подключения таблиц

Вручную создавать источник ODBC и подключать к нему таблицы -- это не методы профессиональной разработки. Приведу примеры кода VBA для создания источника ODBC и подключения к нему таблиц

Создание источника ODBC
Создаётся или перезаписывается ODBC-источник следующей инструкцией:

DBEngine.RegisterDatabase ИмяИсточникаODBC, ИмяODBC-драйвера, True, СтрокаПараметровПодключения

Строка параметров подключения может содержать следующие параметры (см. http://dev.mysql.com/doc/refman/5.0/en/myodbc-configuration-connection-parameters.html):

user    -- Логин пользователя
server -- Хост, на котором запущен MySQL сервер, к которому мы обращаемся
database --   База данных по умолчанию.
option --  Указание опций источника ODBC. Возможные значения этого параметра приведены в таблице на стр. http://dev.mysql.com/doc/refman/5.0/en/myodbc-configuration-connection-parameters.html
port -- TCP/IP порт, используемый, если MySQL-сервер не на localhost.
stmt   -- SQL-инструкция, выполняемая при подключении к MySQL. Важное применение -- установка кодировки (в нашем случае -- cp1251), которую будет применять к данным MS Access
password   Пароль, соответствующий логину (user).
socket   The Unix socket file or Windows named pipe to connect to if server is localhost.

Пример

Dim connect as String
connect="server=192.168.1.1;user=manager;password=zorro;port=3306;stmt=set names cp1251"
DBEngine.RegisterDatabase "tm", "MySQL ODBC 3.51 Driver", True, connect

Обратите внимание на параметр stmt: именно он позволяет MS Access корректно поддерживать кириллицу.

Удаление всех связанных таблиц
'Удаление всех связанных таблиц
Function Drop_linked_tables() As Boolean
On Error GoTo oblom
Dim TD As TableDef
Dim tableName As String

For Each TD In CurrentDb.TableDefs
    If (left(TD.connect, 5) = "ODBC;") Or (left(TD.connect, 10) = ";DATABASE=") Then
            
        tableName = TD.Name
        CurrentDb.TableDefs.Delete (tableName)
    End If
Next TD
CurrentDb.TableDefs.Refresh

Drop_linked_tables = True

Exit Function
oblom:
    Drop_linked_tables = False

End Function

Подключение таблиц MySQL через ODBC

'Подключение таблиц MySQL через ODBC

Function StartConnectMySQL(ByVal login As String,ByVal odbcName As String,ByVal db As String) As Boolean
On Error GoTo oblom

Call Drop_linked_tables 'Удалили все связанные таблицы

Dim newTD As TableDef

Dim ws As Workspace
Set ws = CreateWorkspace("ws_MySQL", "", "", dbUseODBC)
Dim connect As String
connect = "ODBC;database=" & db & ";DSN=" & odbcName
Dim MySQL As DAO.Connection
Set MySQL = ws.OpenConnection(odbcName, dbDriverComplete, False, connect)

Dim query As String
query = "use " & db
MySQL.Execute (query)
query = "show tables"
Dim rst As DAO.Recordset
Set rst = MySQL.OpenRecordset(query, dbOpenDynaset)

Do While Not rst.EOF
        Set newTD = CurrentDb.CreateTableDef(rst(0))
        newTD.connect = "ODBC;DSN=" & odbcName & ";database=" & db
        newTD.SourceTableName = rst(0)
        CurrentDb.TableDefs.Append newTD
        rst.MoveNext
Loop

StartConnectMySQL = True

Exit Function
oblom:
    StartConnectMySQL = False

End Function

Заключение

Мы рассмотрели весь процесс перехода приложения MS Access на взаимодействие c MySQL. Результат окупает усилия, которые потребуются от вас на этом пути: Вы получите приложение, где уже решены следующие задачи:

Защита базы данных от повреждения пользователями
Запись действий пользователя (лог-файлы)
Кроме того, Вам теперь доступны следующие возможности:

-- Разработка view, stored procedures, triggers
-- Использование транзакций
-- Взаимодействие с WEB-приложением
-- Удалённая настройка сервера баз данных

Надеюсь, я чем-то помог Вам на пути к этим возможностям...