Видеоуроки бесплатно!

Visual Basic: Добавление/Изменение/Удаление/Обновление записей в таблицах Access

Сказки на ночь

VBA: Добавление/Удаление/Изменение/Обновление записей в таблицах Access

  

Добавление записи в таблицу Access
Пользователи А и Б вводят информацию о перемещениях товаров в форму. Фактическое добавление записи в таблицу Access возложено на макрос AddTransfег.
При добавлении записи в таблицу Access макрос AddTransf ег выполняет следующую последовательность действий.
1. Создание объекта соединения.
2. Создание объекта набора данных для обращения ктаблице tblTransfer.
3. Вызов метода AddNew для добавления новой записи в таблицу tblTransfer.
4. Установка значения каждого поля новой записи таблицы tblTrans f ег.
5. Вызов метода Update для внесения изменений в базу данных.
6. Закрытие объектов набора данных и соединения.
Ниже приведен код метода AddTransfег.

Sub AddTransfer(Style As Variant,  FromStore As Variant, _
ToStore As Variant,  Qty As Integer)
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
MyConn = ThisWorkbook.Path & Application.PathSeparator &
"transfers.mdb"
'  Открыть соединение.
Set cnn = New ADODB.Connection
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open MyConn
End With
'  Создать объект набора данных.
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseServer
'  Открыть набор данных.
rst.Open Source:="tblTransfer", _
ActiveConnection:=cnn, _
CursorType:=adOpenDynamic, _
LockType:=adLockOptimistic, _
Options:=adCmdTable
' Добавить новую запись.
rst.AddNew
' Определить значения полей новой записи. Первые 4 поля
' задаются пользователем с помощью формы. В поле "Дата"
'  вносится текущее значение даты.
rst("Товар")   = Style
rst("ИзМагазина")   = FromStore
rst("ВМагазин")   = ToStore
rst("Количество")   = Qty
rst("Дата")   = Date
rst("Отправлен")   = False
rst("Получен")   = False
'  Внести изменения в базу данных.
rst.Update
'  Закрыть объекты набора данных и соединения.
rst.Close
cnn.Close
End Sub


Извлечение записей из таблицы Access
Чтобы извлечь записи из таблицы Access, определите набор данных, указав в качестве источника строку SQL-запроса, задающую требуемый критерий отбора. Скопировать записи из таблицы Access на рабочий лист Excel поможет метод Excel VBA CopyFromRecordset. Следующий макрос извлекает из таблицы tblTransfer все записи, в которых значение поля Отправлен равно False, и помещает их на рабочий лист. Строка f rmTransConf. Show выводит на экран пользовательскую форму, которая применяется для обновления записей и рассматривается в следующем разделе.
Sub GetUnsentTransfers()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim WSOrig As Worksheet
Dim WSTemp As Worksheet
Dim sSQL As String
Dim FinalRow As Long
Set WSOrig = ActiveSheet
'  Создать SQL-запрос для извлечения записей,
'  соответствующих неотправленным товарам.
sSQL = "SELECT Идентификатор,  Товар,  ИзМагазина,  ВМагазин, _
Количество, Дата FROM tblTransfer"
sSQL = sSQL &   " WHERE Отправлен=РАЬЗЕ
'  Задать путь к файлу Transfers.mdb.
MyConn = ThisWorkbook.Path & Application.PathSeparator
& "transfers.mdb"
Set cnn = New ADODB.Connection
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open MyConn
End With
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseServer
rst.Open Source:=sSQL, ActiveConnection:=cnn, _
CursorType:=AdForwardOnly,   LockType:=adLockOptimistic,
Options:=adCmdText
'  Создать новый рабочий лист.
Set WSTemp = Worksheets.Add
WSTerop. Select
Range("Al:Fl").EntireColumn.Clear
'  Создать строку заголовков.
Range("Al:Fl").Value = Array("Идентификатор", "Товар",
"ИзМагазина",   "ВМагазин",   "Количество",   "Дата")
'  Скопировать записи из набора данных на рабочий лист.
Range (*'А2 11) . CopyFromRecordset rst
'  Закрыть набор данных и соединение.
rst.Close
cnn.Close
'  Отформатировать отчет.
FinalRow = Range("A65536").End(xlUp).Row
' Удалить временный рабочий лист, если в результате
' запроса было возвращено пустое множество записей.
If FinalRow = 1 Then
Application.DisplayAlerts = False
WSTemp.Delete
Application.DisplayAlerts = True
WSOrig.Activate
MsgBox 11 Неподтвержденных перемещений товаров нет"
Exit Sub
End If
'  Задать формат ячеек столбца F как М.Д.ГГ.
Range("F2:F" & FinalRow).NumberFormat = "m/d/y"
'  Отобразить форму.
frmTransConf.Show
Удалить временный рабочий лист.
Application.DisplayAlerts = Fal
WSTemp.Delete
Application.DisplayAlerts = True
End Sub

Метод CopyFromRecordset копирует на рабочий лист Excel только строки с данными, а строку заголовка необходимо создавать вручную. Результат выполнения макроса GetUnsentTransfers до отображения пользовательской формы показан на рис. 19.3.
VBA: Добавление/Удаление/Изменение/Обновление записей в таблицах Access

Рис. 19.3. Метод CopyFromRecordset копирует на рабочий лист Excel результат извлечения записей из таблицы tblTransfer


Обновление записей таблицы Access
Чтобы обновить существующую запись таблицы Access, создайте набор данных, содержащий одну-единственную запись. Обычно для этого необходимо, чтобы пользователь выбрал требуемую запись (а значит и ее уникальный идентификатор — ключ) с помощью формы. Измените значение требуемого поля с помощью свойства Fields и внесите обновленную запись в базу данных с помощью метода Update. Рассмотренный ранее макрос GetUnsentTransfers помещает записи таблицы tblTransfer на рабочий лист, а затем выводит на экран пользовательскую форму frmTransConf. Метод инициализации формы User Form_Initialize загружает все записи с текущего рабочего листа в список, поддерживающий множественный выбор (значение свойства MultiSelect равно True):
Private Sub UserForm_Initialize()
OrigBook = ActiveWorkbook.Name
'  Загрузить записи в список.
FinalRow = 06113(65536,   1).End(xlUp).Row
If FinalRow > 1 Then
Me.lbXlt.RowSource = "A2:F" & FinalRow
End If
End Sub

Форма, показанная на рис. 19.4, отображает записи, соответствующие неотправленным товарам. Чтобы изменить значение поля Отправлен требуемых записей с Fa] se на True, выделите их и щелкните на кнопке Подтвердить.
VBA: Добавление/Удаление/Изменение/Обновление записей в таблицах Access

Рис. 19.4. Пользовательская форма отображает записи, соответствующие неотправленным товарам (значение ПОЛЯ Отправлен равно False). Чтобы отразить факт отправки товаров в базе данных, выделите требуемые записи и щелкните на кнопке Подтвердить

Ниже приведен код, выполняющийся в результате щелчка на кнопке Подтвердить. Ключевым фрагментом этого кода является создание SQL-запроса, использующегося для отбора единственной записи таблицы tblTransfer с помощью поля Идентификатор.
Private Sub cbConfirm_Click()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
'  Если в списке не содержится
'  ни одной записи,  вывести сообщение.
CountSelect = О
For х = 0 То Me.lbXlt.ListCount - 1
If Me.lbXlt.Selected(x) Then
CountSelect = CountSelect + 1
End If
Next x
If CountSelect = 0 Then
MsgBox "Перемещаемых товаров нет. Чтобы закрыть форму,
щелкните на кнопке Выход."
Exit Sub
End If
' Установить соединение с базой данных transfers.mdb.
MyConn = ThisWorkbook.Path & Application.PathSeparator &
"transfers.mdb"
Set cnn = New ADODB.Connection
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open MyConn
End With

'  Обновить записи таблицы tblTransfer.
For х = 0 То Me.lbXlt.ListCount - 1
If Me.lbXlt.Selected(x) Then
ThisID = Cells(2 + x,   1).Value
'  Обновить запись с идентификатором ThisID.
'  Создать SQL-запрос.
sSQL = "SELECT * FROM tblTransfer Where
Идентификатор=" & ThisID
Set rst = New ADODB.Recordset
With rst
.Open Source:=sSQL, ActiveConnection:=cnn,
CursorType:=adOpenKeyset,  LockType:=adLockOptimistic
'  Обновить поле.
.Fields("Отправлен").Value = True
.Update
.Close
End With
End If
Next x
'  Закрыть объекты набора данных и соединения.
cnn.Close
Set rst = Nothing
Set cnn = Nothing
'  Закрыть пользовательскую форму.
Unload Me
End Sub 


Удаление записей таблицы Access
Чтобы удалить существующую запись таблицы Access, создайте SQL-код, удаляющий запись на основе значения ее уникального идентификатора. Установите соединение с базой данных и выполните SQL-код с помощью метода Execute, как показано ниже:
Public Sub DeleteRecord(RecID)
'  Установить соединение с базой данных transfers.mdb.
MyConn = ThisWorkbook.Path & Application.PathSeparator _
& "transfers.mdb"
With New ADODB.Connection
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open MyConn
.Execute "Delete From tblTransfer Where _
Идентификатор = " & RecID
.Close
End With
End Sub
 
Уважаемый посетитель, Вы зашли на сайт как незарегистрированный пользователь. Мы рекомендуем Вам зарегистрироваться либо войти на сайт под своим именем.


Дата: 19 марта 2014   |   Опубликовал: Admin   |   Просмотров: 3867   |   Комментарии (0)

Уважаемый , статьи ниже возможно будут интересны вам:

Посетители, находящиеся в группе Гости, не могут оставлять комментарии к данной публикации.