В дополнение к DAO или ADO, Access предоставляет способ "прямого" управления данными в таблице прямым выполнением команд SQL - добавить данные (INSERT), обновить данные (UPDATE) и удалить данные (DELETE). Можно легко написать приложение, которое не будет использовать ни DAO, ни ADO, следовательно никаких "заморочек" со этими ссылками при распространении приложения на другие компьютеры сети у Вас не будет (конечно, источники записей форм должны быть связаны с таблицами).
С другой стороны, хотя это будет вполне работоспособно, но слишком опрометчиво использовать прямое выполнение команд SQL в мультипользовательской среде, однако для сохранения различных настроек приложения подойдет вполне. Причем, никакой разницы нет - в линкованных таблицах сохраняются значения или в локальных.
В одном из моих довольно больших приложений все настройки клиентских приложений запоминаются в специальных таблицах базы данных описанным образом, и могу утверждать, что работает все более чем нормально ;).
У Access'a имеется метод .Execute, которому и требуется передавать заранее собранную SQL-строку для выполнения.
SQL [es kju:'el], ['si:kwel] Structured Query Language: язык структурированных запросов, язык SQL. Вот не поленился и даже в транскрипции написал - как хотите, так и говорите. Указал специально, чтобы при чтении статьи у Вас закрепилось внутреннее произношение ;)
В принципе, инструкции SQL, человеку более, менее понимающему английский язык, понятны сразу, вот только реализация всегда кроется в тонких и не всегда очевидных деталях. Освещению таких деталей я и посвятил статью.
Сначала возьмем очень простой пример. Допустим, у нас есть справочная таблица tblРайоны, состоящая из всего двух полей:
КодРайона тип Счетчик
Район тип Текстовый
И у нас имеется форма, с текстовым полем txtРайон, значение из которого необходимо добавить (INSERT) в таблицу:
Код очень простой - сначала формируем текстовую строку и передаем ее методу .Execute:
Dim strSQL As String
' процедура добавления в таблицу нового района
strSQL = "INSERT INTO tblРайоны([Район]) VALUES " & _
"('" & Me.txtРайон.Value & "');"
CurrentDb.Execute strSQL
Сразу хочу обратить пристальное внимание на проверку передаваемого значения на NULL, если передать NULL (поле txtРайон не заполнено пользователем), то метод выполнится как ни в чем не бывало, но изменений в таблице tblРайоны не произойдет (не удивляйтесь потом, что ничего не работает ;).
Поэтому сразу будем делать правильно:
Dim strSQL As String
' процедура добавления в таблицу нового района
If Not IsNull(Me.txtРайон.Value) Then
strSQL = "INSERT INTO tblРайоны([Район]) VALUES " & _
"('" & Me.txtРайон.Value & "');"
CurrentDb.Execute strSQL
Else
MsgBox "Вы должны заполнить поле [Район]", vbExclamation, "Не указан район"
End If
Вот теперь и проверяем на NULL, заодно и пользователя целенаправленно предупреждаем.
Как видно из примера, полем типа Счетчик (КодРайона) мы управлять не можем, да и не надо нам это, Access самостоятельно управляет этим полем (автоматом наращивает значение).
Теперь начнем удалять (DELETE) запись из таблицы
Dim strSQL As String
strSQL = "DELETE [Район] from tblРайоны WHERE [Район] = " & Me.txtРайон.Value & ";"
CurrentDb.Execute strSQL
В общем-то код на удаление записи прост, но непроста будет реализация на практике: необходимо требовать от пользователя точного написания названия района (в текстовом поле), и если будет ошибка или опечатка, то инструкция выполнится, но ничего не произойдет (условие не будет соответствовать). Да и зачем пользователя нагружать писаниной, если эти значения уже записаны в таблице ? Логичней будет предложить выбрать из списка удаляемое значение (ну и спросить, конечно, уверен ли?) и, уже абсолютно точно зная искомое значение, удалить нужную запись. Это, конечно, уже из области проектирования интерфейсов, но при реализации данного метода все равно придется делать "правильно".
Лично я вижу удаление так: в форме создаем список из двух столбцов (первый столбец скрываем, зачем код записи пользователю видеть-то?), связанный с таблицей tblРайоны и где-нибудь рядом кнопку [Удалить], на событие [Нажатие кнопки] которой вешаем код
Dim strSQL As String
' процедура удаления из таблицы выбранного в списке района
If Not IsNull(Me.lstРайон.Column(0)) Then
strSQL = "DELETE [Район] from tblРайоны WHERE [КодРайона] = " & Me.lstРайон.Column(0) & ";"
CurrentDb.Execute strSQL
' обновим список
Me.lstРайон.Requery
Else
MsgBox "Выберите район из списка для удаления", vbExclamation, "Не выбран район"
End If
Мы считываем из первого столбца списка lstРайон.Column(0) (нумерация столбцов в списках всегда начинается с 0) код записи, однозначно определяющий удаляемую запись в таблице и передаем ее инструкции за удаление.
Проверка на NULL заодно позволяет предотвратить ошибку, когда вообще строка в списке районов не выделена. Обратите внимание на обновление списка районов после удаления записи - пользователю приятно видеть, что выбранный район пропал ;).
Обычно я рисую список, текстовое поле (для указания новых или обновляемых значений) прямо над ним и три кнопки рядом со списком [Добавить], [Обновить], [Удалить]. Думаю, с реализацией таких интерфейсов у Вас не должно быть никаких проблем.
Заметьте, что Access автоматически удалит и поле счетчика КодРайона, что логично - сам наращивает, пусть сам и удаляет.
Строку на удаление можно подать и так:
strSQL = "DELETE * from tblРайоны WHERE [КодРайона] = " & Me.lstРайон.Column(0) & ";"
Звездочка (*) означает ВСЁ - всё, что есть в этой записи. Этот способ удаления отлично действует, когда полей в записи много - нет нужды указывать каждое удаляемое поле.
Ну и последний кит - инструкция на обновление (UPDATE)
Dim strSQL As String
' процедура обновления соответствующей записи в таблице
If Not IsNull(Me.txtРайон.Value) And Not IsNull(Me.lstРайон.Column(0)) Then
strSQL = "UPDATE tblРайоны SET [Район] = '" & Me.txtРайон.Value & "' WHERE [КодРайона] = " & Me.lstРайон.Column(0) & ";"
CurrentDb.Execute strSQL
' обновим список
Me.lstРайон.Requery
Else
MsgBox "Выделите район из списка, реквизиты которого необходимо изменить", vbExclamation, "Не выбран район для изменения"
End If
Раз у нас инструкция на обновление - необходимо проверять как поле txtРайон, в котором указано новое значение, так и строку в списке lstРайон (выбрана ли?). И, конечно, списочек обновить, чтобы изменения показать.
Вначале я рассмотрел простые примеры, чтобы Вы сразу не убежали ;). Теперь займемся плотнее - усугубим примеры управлением несколькими полями записи. Принцип абсолютно такой же, просто к написанию инструкции надо подойти немного тщательнее.
Теперь увеличим размер таблицы tblРайоны:
КодРайона тип Числовой
НаимПодразд тип Текстовый
ИндексПодразд тип Текстовый
ГородПодразд тип Текстовый
УлицаПодразд тип Текстовый
ДомПодразд тип Текстовый
КомПодразд тип Текстовый
ТелефонПодразд тип Текстовый
ПрефиксПротПодразд тип Текстовый
ПрефиксКвитПодразд тип Текстовый
НачальникПодраздЗвание тип Текстовый
НачальникПодраздФИО тип Текстовый
НачальникРУВДРайона тип Текстовый
РайонПоУмолчанию тип Числовой
Внушает ? Будем работать теперь с ней:
Dim strSQL As String
Dim lngMaxValue As Long
' процедура добавления в таблицу нового подразделения
If Not IsNull(Me.txtНаимПодразд.Value) Then
lngMaxValue = DMax("[КодРайона]", "tblРайоны") + 1 ' типа счетчика что-то
strSQL = "INSERT INTO tblРайоны ([КодРайона], [НаимПодразд], [ИндексПодразд], [ГородПодразд], [УлицаПодразд], [ДомПодразд], [КомПодразд], [ТелефонПодразд], [ПрефиксПротПодразд], [ПрефиксКвитПодразд], [НачальникПодраздЗвание], [НачальникПодраздФИО], [НачальникРУВДРайона]) VALUES " & _
"('" & lngMaxValue & "', '" & _
Me.txtНаимПодразд.Value & "', '" & _
Me.txtИндексПодразд.Value & "', '" & _
Me.txtГородПодразд.Value & "', '" & _
Me.txtУлицаПодразд.Value & "', '" & _
Me.txtДомПодразд.Value & "', '" & _
Me.txtКомПодразд.Value & "', '" & _
Me.txtТелефонПодразд.Value & "', '" & _
Me.txtПрефиксПротПодразд.Value & "', '" & _
Me.txtПрефиксКвитПодразд.Value & "', '" & _
Me.txtНачальникПодраздЗвание.Value & "', '" & _
Me.txtНачальникПодраздФИО.Value & "', '" & _
Me.txtНачальникРУВДПодразд & "');"
CurrentDb.Execute strSQL
' обновим список
Me.lstПодразд.Requery
End If
В этом примере мы сами управляем полем кода подразделения - с помощью агрегатной функции DMax вычисляем максимальное значение в столбце кодов таблицы tblРайоны и прибавляем единицу. Практически то же самое делает Access (если поле типа Счетчик), но есть большая разница, если мы удалим подразделение, то код удаленного подразделения Access повторно использовать не будет!. Объясню на пальцах: у нас было создано подразделение с автоматическим присвоением (счетчиком) кода с цифрой 3, потом мы его удалили, так вот, логично было бы следующему подразделению присвоить освобожденную цифру 3, нет Access присвоит уже цифру 4. Чтобы не допустить напрасного расходования кодов (и пропусков в списке) я и решил взять автоматическое присвоение кодов на себя.
Обратите внимание мы проигнорировали поле РайонПоУмолчанию. Ну проигнорировали и проигнорировали - значит туда ничего не будет записано.
Рекомендую оформлять такие длинные примеры именно по приведенной мною схеме - легче будет понять пример и выявить ошибку.
Процедура удаления записи с любым значением полей уже становится тривиальной ;)
Dim strSQL As String
' процедура удаления из таблицы выбранного в списке подразделения
If Not IsNull(Me.lstПодразд.Column(0)) Then
strSQL = "DELETE * from tblРайоны WHERE [КодРайона] = " & Me.lstПодразд.Column(0) & ";"
CurrentDb.Execute strSQL
' обновим список
Me.lstПодразд.Requery
Else
MsgBox "Выберите подразделение из списка для удаления", vbExclamation, "Не выбрано подразделение"
End If
К слову, если подать на выполнение такую строку:
strSQL = "DELETE * from tblРайоны;"
то ВСЕ записи в таблице будут удалены! Мы использовали удаление без всяких условий и получим пустую таблицу. Иногда и это нужно ;)
Вот примерчик на обновление записи:
' процедура обновления соответствующей записи в таблице
If Not IsNull(Me.txtНаимПодразд.Value) And Not IsNull(Me.lstПодразд.Column(0)) Then
strSQL = "UPDATE tblРайоны SET [НаимПодразд] = '" & Me.txtНаимПодразд.Value & "', " & _
"[ИндексПодразд] = '" & Me.txtИндексПодразд.Value & "', " & _
"[ГородПодразд] = '" & Me.txtГородПодразд.Value & "', " & _
"[УлицаПодразд] = '" & Me.txtУлицаПодразд.Value & "', " & _
"[ДомПодразд] = '" & Me.txtДомПодразд.Value & "', " & _
"[КомПодразд] = '" & Me.txtКомПодразд.Value & "', " & _
"[ТелефонПодразд] = '" & Me.txtТелефонПодразд.Value & "', " & _
"[ПрефиксПротПодразд] = '" & Me.txtПрефиксПротПодразд.Value & "', " & _
"[ПрефиксКвитПодразд] = '" & Me.txtПрефиксКвитПодразд.Value & "', " & _
"[НачальникПодраздЗвание] = '" & Me.txtНачальникПодраздЗвание.Value & "', " & _
"[НачальникПодраздФИО] = '" & Me.txtНачальникПодраздФИО.Value & "', " & _
"[НачальникРУВДРайона] = '" & Me.txtНачальникРУВДПодразд.Value & "' " & _
" WHERE [КодРайона] = " & Me.lstПодразд.Column(0) & ";"
CurrentDb.Execute strSQL
' обновим список
Me.lstПодразд.Requery
Else
MsgBox "Выделите район из списка, реквизиты которого необходимо изменить", vbExclamation, "Не выбран район"
End If
Опять в коде выше игнорируем поле РайонПоУмолчанию, у нас для него припасена отдельная команда:
CurrentDb.Execute "UPDATE tblРайоны SET [РайонПоУмолчанию] = '" & Me.lstПодразд.Column(0) & "';"
что произойдет? во ВСЕ записи таблицы в поле РайонПоУмолчанию добавится значение из первого столбца списка lstПодразд. Но это то, мне и нужно было, если Вам необходимо обновить поле только в определенной записи, дополните эту инструкцию SQL условием WHERE.
Т.е. мы запросто можем обновлять и сразу весь столбец таблицы - поле во всех записях таблицы сразу!
Раньше мы заранее формировали инструкцию SQL в переменной strSQL, а в этом примере скармливаем методу .Execute непосредственно. Никаких различий в исполнении не будет, но, уверяю Вас, формирование строки заранее в переменной удобнее - можно посмотреть составленную инструкцию SQL командой MsgBox strSQL (перед передачей ее на выполнение) или распечатать ее для анализа в окне Immediate в режиме пошаговой отладки - ? strSQL. И с точки зрения оформления кода использование переменных более "правильно" ;).
А если нам сначала надо проверить, есть ли такая запись в таблице, и если есть, тогда обновить, а если нет, тогда добавить новую запись? Нет проблем, все решается тривиально:
Dim strSQL As String
' сначала надо поискать в таблице значение txtКодНарушения может быть мы уже что-то заполняли под этим кодом
If IsNull(DLookup("[КодНарушения]", "tblОпись", "[КодНарушения] = " & Forms!frmНарушения.txtКодНарушения)) Then
' и если не найдено соответствие, тогда процедура добавления в таблицу нового определения
strSQL = "INSERT INTO tblОпись ([КодНарушения], [ПротОтстранУправленТС], [ПротМедОсвидетельств], [МедАкт], [Флажки], [ОбъясненияШт], [AddField1], [AddField2], [AddField3]) VALUES " & _
"('" & Forms!frmНарушения.txtКодНарушения & "', '" & _
Me.txtПротОтстранУправленТС.Value & "', '" & _
Me.txtПротМедОсвидетельств.Value & "', '" & _
Me.txtМедАкт.Value & "', '" & _
Me.intФлажки.Value & "', '" & _
Me.txtОбъясненияШт.Value & "', '" & _
Me.txt13.Value & "', '" & _
Me.txt14.Value & "', '" & _
Me.txt15.Value & "');"
CurrentDb.Execute strSQL
Else
' и если такой код найден, тогда обновляем поля
strSQL = "UPDATE tblОпись SET [ПротОтстранУправленТС] = '" & Me.txtПротОтстранУправленТС.Value & "', " & _
"[ПротМедОсвидетельств] = '" & Me.txtПротМедОсвидетельств.Value & "', " & _
"[МедАкт] = '" & Me.txtМедАкт.Value & "', " & _
"[Флажки] = '" & Me.intФлажки.Value & "', " & _
"[ОбъясненияШт] = '" & Me.txtОбъясненияШт.Value & "', " & _
"[AddField1] = '" & Me.txt13.Value & "', " & _
"[AddField2] = '" & Me.txt14.Value & "', " & _
"[AddField3] = '" & Me.txt15.Value & "' " & _
"WHERE [КодНарушения] = " & Forms!frmНарушения.txtКодНарушения & ";"
CurrentDb.Execute strSQL
End If
Сначала мы пытаемся найти с помощью функции DLookup найти запись с искомым значением поля КодНарушения и если получаем NULL (функция DLookup вернула NULL), тогда удовлетворяющего значения поля не найдено - переходим к добавлению новой записи, иначе, если поле с таким значением в таблице где-то есть, переходим к обновлению. От функции DLookup нам требуется лишь подтверждение - найдено или нет искомое значение в указанном поле таблицы (конечно, искать необходимо только по полю с уникальными значениями (КодНарушения), чтобы избежать неоднозначности.
Засим откланиваюсь и надеюсь, что пелену тумана над непонятными инструкциями SQL немного развеял. Если что-нибудь интересное встречу или вспомню, обязательно добавлю. Теперь Ваше время экспериментировать - в инструкциях SQL удивительно сочетаются и мощь и удобство и простота.