Пример решении

2.1 Транзакции.

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

Под транзакцией понимается серия изменений, которые проводятся над данными и структурой БД. Если по какой-либо причине операции, входящие в текущую транзакцию не могут быть завершены, то система возвращается в исходное состояние.

Метод BeginTrans объекта Workspace начинает транзакцию, а завершает ее метод CommitTrans. Если по каким-либо причинам приложение не сможет обратится к методу CommitTrans, то система вернется в первоначальное состояние. Выполнение же этого метода делает все изменения необратимыми. Метод Roolback отменяет все изменения в рамках транзакции.

Access поддерживает параллельные и вложенные транзакции. При параллельных требуется создать еще один объект Workspace. Каждая транзакция

завершается независимо друг от друга. Количество вложений не может превышать 5.

Транзакции являются глобальными в рамках объекта Workspace. Т.о. они могут охватывать несколько БД и множество объектов, которые в них содержатся.

Для демонстрации работы транзакций создадим еще одну форму "Замена", которая имеет свойства подобные свойствам формы "Панель управления", и содержит поля для ввода имени таблицы, имени поля в таблице, значение, которое требуется заменить, и значение, на которое требуется заменить искомое.А также кнопку "Заменить". Таким образом она выполняет замену значения поля на новое. Поле управления для ввода имени поля есть поле со списком, у которого Row Source Type установлено в Field list для упрощения ввода (оно будет содержать список полей таблицы, в которой требуется изменить поле, и не допускает возможности ввода нового, а следовательно потенциально неправильного значения).

Шаблон этой формы приведен в приложении 1, рис.1

Значение списка устанавливается свойством "After Update" поля "Tab" (имя поля ввода название таблицы):

Sub Tab_AfterUpdate()

Me!List.RowSource=Me!Tab

End Sub

Свойство кнопки "Заменить" (имя кнопки Finder) связано со следующей процедурой:

Sub Finder_Click ()

Dim dbMyBase As Database, Criteria As String

Dim rcdTab As Recordset, i As Integer, intMsgRtn As Integer

Dim Wks As WorkSpace, j As Integer, k As Integer

Set Wks = DBEngine.Workspaces(0)

Set dbMyBase = DBEngine.Workspaces(0).Databases(0)

k = 0

'проверка ─ есть ли такая таблица в текущей БД

For i = 0 To dbMyBase.TableDefs.Count – 1

If (dbMyBase.TableDefs(i).Name = Me!Tab) = -1 Then

'Если есть, устанавливаем признак и 'открываем набор данных

Set rcdTab = dbMyBase.OpenRecordset(Me!Tab, DB_OPEN_DYNASET)

k = 1

Exit For

End If

Next i

'если таблицы нет, выводим сообщение

If k = 0 Then

intMsgRtn = MsgBox("Такой таблицы нет",38,"Внимание")

GoTo Ex

End If

'определяем критерий

Select Case rcdTab(Me!List).Type

Case DB_INTEGER, DB_BYTE, DB_LONG, DB_DOUBLE

Criteria = Me!List & "=" & Str(Me!Val)

Case DB_TEXT

Criteria = Me!List & "=" & Chr(34) & (Me!Val) & Chr(34) Case Else

MsgBox ("Данный тип поля не обрабатывается")

If intMsgRtn=MsgBox("Продолжаете работу?"), 33, "Внимание! ") Then

rcdTab.Close

Goto Ex

Else

Me!Tab = " "

Me!List = " "

Me!Val = " " Me!ChangeTo = " "

Exit Sub

End If

End Select

'ищем запись

rcdTab.FindFirst Criteria

k = 0

Wks.BeginTrans 'начинаем транзакцию

'пока есть записи, удовлетворяющие условию

Do Until rcdTab.Nomatch

rcdTab.Edit

'меняем значение поля

rcdTab(i) = Me!ChangeTo

rcdTab.Update

'считаем количество измененных записей

k = k + 1

rcdTab.FindNext Criteria

Loop

intMsgRtn = MsgBox("Заменено " & Str(k) & " записей!",

64, "Информация")

If k Then

If intMsgRtn=MsgBox("Сохранить изменения?",33,"Внимание") Then

Wks.CommitTrans 'завершить транзакцию

Else Wks.Rollback 'откат

End If

Else Wks.Rollback 'откат

End If

'закрываем набор записей

rcdTab.Close

'закрываем форму

DoCmd SetWarnings False

DoCmd Close A_FORM, "Замена"

DoCmd SetWarnings True

End Sub

2.2 Работа в режиме коллективного доступа посредством Access 2.0

Microsoft Access автоматически открывает БД с монопольным доступом.

При этом другим пользователям запрещается изменять объекты и содержимое БД. Для того чтобы открыть БД для общего доступа в сети, следует снять флажок "Exclusive" в диалоговом окне открытия БД.

Средства настройки оболочки Access позволяют устанавливать режимы по умолчанию для открытия БД и таблиц. Для этого используется команда <View/Options>. В диалоговом окне необходимо в списке "Multiuser/ODBC" выделить "Default Record Locking". После этого, используя список можно выбрать необходимый тип блокировки.

При выборе кнопки "No Locks" можно установить нежесткую блокировку записи, т.е. все редактируемые записи доступны для других пользователей, но если вы отредактируете запись и попытаетесь сохранить изменения, то столкнетесь с 2 вариантами:

─ кроме вас запись никто редактировал и сделанные изменения заносятся на диск;

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

Во втором случае появится окно сообщения, в котором предлагается 3 выхода:

─ игнорировать чужие изменения и записать на диск свои;

─ скопировать измененную запись в буфер обмена;

─ сохранить чужие изменения и отменить свои.

При выборе блокировки изменяемой записи для других пользователей будет заблокирована запись, которую вы редактируете, и соседние с ней, т.к. Access устанавливает блокировку для страницы (2 Кб). Эту блокировку называют "пессимистический".

При выборе блокировки всех записей остальные пользователи не могут открыть таблицу.

Если вы работаете с данными с помощью формы, то с помощью ее свойства RecordLocks можете изменять установки для текущего сеанса работы, отличающиеся от установленных по умолчанию. Это свойство присутствует у запросов и отчетов также.

Можно использовать свойство объекта Application для изменения режимов

блокировки:

Function Set_Option()

Dim pot As String

'установка пессимистический блокировки

Application.SetOption "Default record locking",2

pot = Application.GetOption("Default Record Locking") MsgBox(pot)

Set_Option = True

End Function

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

2.3 Использование технологии "клиент-сервер".

Технология "клиент-сервер" разделяет приложение на 2 части:

─ клиентская часть, которая обеспечивает легкий в использовании, обычно графический интерфейс;

─ сервер обеспечивает управление данными, разделение информации, администрирование и безопасность.

При этой технологии клиентское приложение формирует запрос к серверу БД, на котором выполняются все команды, а результаты команд посылаются потом клиенту для использования и просмотра(когда БД располагается на файл-сервере, его функции ─ хранение данных и обеспечение доступа к ним, а вся обработка производится в компьютере пользователя, т.е. если пользователю требуются 2 строки из таблицы объемом в сотни тысяч записей, то сначала вся таблица с файл-сервера передается на компьютер пользователя, а затем СУБД отбирает нужные записи).

В качестве сервера может выступать любой сервер БД, имеющий драйвер ODBC (Open Database Connectivity), ─ MS SQL Server, Oracle и т.д. ODBC находится как бы посередине между приложением, использующим данные, и самими данными, хранящимися в формате, которые невозможно обработать напрямую в приложении, и используются как средства коммуникации между

клиентом и сервером.

ODBC требует от разработчика только имя источника данных.

Для использования Microsoft Access в качестве сервера в сети необходимо иметь:

─ сетевое ПО, поддерживающее именованные каналы (named pipes), такое как Microsoft LAN Manager, Novell, Netware или Banyan VINES;

─ компьютер с установленным Microsoft Windows и со свободным местом на диске не менее 11Мб для установки Microsoft Access 2.0 плюс дополнительное место для баз данных пользователей;

─ рабочие станции с конфигурацией, требуемой для установки Microsoft Access.

Microsoft Access спроектирован таким образом, что может быть использован как в качестве самостоятельной СУБД на отдельной рабочей станции, так и в сети в режиме "клиент-сервер". Поскольку в Access к данным могут иметь одновременно несколько пользователей, в нем предусмотрены надежные средства защиты и обеспечения целостности данных. Вы можете заранее указать, какие пользователи или группы пользователей могут иметь доступ к объектам БД. Access автоматически обеспечивает защиту данных от одновременной их корректировки разными пользователями. Access также опознает и учитывает защитные средства других присоединенных к базе структур (таких, как БД Paradox, dBase, SQL).

В приложениях, ориентированных на небольшие рабочие группы, хранение и коллективный доступ может осуществляться при помощи только БД Access. Большие приложения для хранения данных используют специальный сервер (например, SQL Server), а Access на рабочей станции выступает в роли "клиента".

СУБД Access имеет наибольшее число способов взаимодействия с MS SQL Server. Рассмотрим два из них:

1. Присоединенные таблицы

--------- ------ ------------ ---------- ------------

¦ Access +--+ Jet +--+ Диспетчер +--+ Драйвер +--+ SQL Server¦ L--------- L------ ¦ ODBC ¦ ¦ ODBC ¦ L------------

L------------ L----------

Рис. 1. Схема организации работы с присоединенными данными

Всю работу вполняет процессор Access, пользователь может заниматься только обработкой данных, связывая таблицы на сервере с локальными таблицами, создавать запосы.

Диспетчер ODBC поддерживает связь между приложением и источником данных, обеспечивая информацией приложение и загружая драйверы динамически по мере необходимости.

Драйвер ODBC выполняет все вызовы ODBC функций, управляет всеми взаимодействиями между приложением и сервером, переводит SQL выражение на синтаксис источника данных.

2. Pass-through запросы:

----------- -----------------

¦ Access +---------------------------+ SQL Server ¦ L----------- L-----------------

Рис. 2. Схeма взаимодействия Access и SQL Server через Pass-through запросы.

При этом обращение идет напрямую к серверу на его языке, минуя процессор БД Access. Запрос, хотя и проходит через все компоненты ODBC, ни один из них реально не обрабатывает его. После того, как Вы присоединитесь к Вашему источнику данных, можно использовать набор SQL pass-through запросов.

Второй способ требует большего объема программирования, но в тоже время практически не ограничивает возможности по управлению внешними данными. Первый же способ гораздо легче в использовании, но с его помощью можно только модифицировать данные: редактировать, удалять и добавлять записи.

2.3.1. Использование присоединенных таблиц

Для использования присоединенных таблиц используйте команду меню <File/Attach Table>. Вы попадаете в диалоговое окно "Attach". После выбора в <Data SourсeL> типа файла "SQL DataBase" возникнет следующее диалоговое окно, в котором находится список всех источников данных "Data Sourсe", доступных на компьютере. Если нужного источника в списке нет, то с помощью кнопки <New> Вы попадаете в окно администратора ODBC, где и создаете требуемый источник.

После выбора источника данных Вам необходимо выбрать из списка доступных таблиц те, которые Вы хотите присоединить. Если в таблице нет уникального индекса, Вам будет предложено выбрать их самостоятельно, чтобы Jet Access мог однозначно передавать информацию на сервер.

Среди дополнений (File/Add-Ins) есть "Data Manager", который позволяет отслеживать работу с присоединенными таблицами.

Пример: В заданном источнике данных STUDY создается присоединенная таблица "Example".

Function CreateAttachTable()

Dim myDB AS Database, myTabDef AS TableDef

Set myDB = DBEngine, Workspaces.Database(0) Set myTabDef = myDB.CreateTableDef("Example") myTabDef.Connect = "ODBC;dsn=STUDY" myTabDef.SourceTableName = "country" myDB.TableDefs.Append myTabDef CreateAttachTable = True

End Function

2.3.2. SQL pass-through инструкции

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

Для того, чтобы создать запрос с помощью конструктора запросов, щелкните по корешку "Query" в окне БД, затем в меню <Query> выберите команду <SQL Query> и опцию <Pass-Through>. После выбора этого пункта меню больше не будет доступен режим конструктора QBE и SQL инструкции придется набирать вручную.

Чтобы получить доступ к диалогу свойств выберите команду <Properties> в меню <View>. В нем выберите имя источника данных. Свойства "ReturnRecords" уcтановите в "Yes" при выборке данных и в "No" при определении данных.

Для того, чтобы создавать запросы pass-through запросы используйте объект

QueryDef.

Основное отличие pass-through запроса от остальных наличие непустого свойства "Connect" и установка свойства "ReturnRecords".

Например, для получения выборки всех данных некоторой таблицы "example" при выше использованном источники данных введите следующей инструкцией в функцию Access Basic:

Dim DB As Database, Q1 As QueryDef, Rs As Recordset

Set DB = DBEngine.Workspaces.Database(0)

....

Set Q1 = DB.CreateQueryDef("Q1")

Q1.Connect = "odbc; dsn = STUDY" Q1.SQL = "SELECT*FORM Example" Q1.ReturnRecords = True Q1.Execute

Set Rs = DB.OpenRecordset("Q1",DB_OPEN_SNUPSHOT)

А для передачи данных

.....

Set Q1 = DB.CreateQueryDef("Q1")

Q1.Connect = "odbc; dsn = STUDY"

Q1.SQL = "APDATE Example SET account = 10 WHERE account = 2" Q1.ReturnRecords = False

Q1.Execute

Set Rs = DB.OpenRecordset("Q1",DB_OPEN_SNUPSHOT)

Но основное предназначение запросов SQL ─ Pass-trough не выборка данных, а запросы определения данных и выполнения хранимых на сервере процедур:

......

Set Q1 = DB.CreateQueryDef("Q1")

Q1.Connect = "odbc; dsn = STUDY"

Q1.SQL = "CREATE TABLE Example (Fild1 c(10)))" Q1.ReturnRecords = False

Q1.Execute