使用窗體更新表中的數據非常容易。只需針對想要更新的表的欄位,在窗體上放置控件。例如,下圖顯示了 frmSales。frmSales 上的控件可以更新 tblSales、tblSalesLineitems 和 tblSalesPayments 中的數據,因爲這些字段直接綁定到 frmSales 上的控件。

但有時,你可能希望更新某個未顯示在窗體上的欄位。例如,在 frmSales 中輸入信息時,tblCustomers 中最後銷售日期的欄位 ( LastSalesDate ) 應該進行更新以反映聯繫人購買某種產品的最新日期。當輸入一筆銷售時,LastSalesDate 欄位的值將是 frmSales 上 txtSaleDate 控件的值。

由於聯繫人的最後銷售日期引用 frmSales 上的 txtSaleDate 控件,因此,你不希望用戶必須兩次輸入該值。從理論上來說,可將 LastSalesDate 欄位作爲一個計算欄位放置在窗體上,在用戶輸入銷售日期後進行更新,但顯示該欄位將會比較混亂,並與當前銷售的商品不相關。

處理 tblCustomer 中的 LastSaleDate 欄位更新的最佳方式是使用 VBA 過程。可使用 VBA 代碼來更新某條記錄中的各個欄位、添加新記錄或刪除記錄。

☛ 使用 ADO 更新記錄中的欄位

可使用 AfterUpdate 事件過程更新 LastSalesDate ( 如下所示 )。該過程使用 ADO 語法直接對 tblCustomers 進行操作。

Private Sub Form_AfterUpdate()

  Dim rsContacts As ADODB.Recordset
  Dim sSQL As String
    
  On Error GoTo ErrHandler
    
  If Not IsNull(Me.cboCustomerID.Value) Then
    If Not IsNull(Me.txtSaleDate.Value) Then
        
      sSQL = "SELECT * FROM tblCustomers WHERE CustomerID = " _
          & Me.cboCustomerID.Value
      
      Set rsContacts = New ADODB.Recordset
      rsContacts.Open sSQL, CurrentProject.Connection, _
          adOpenDynamic, adLockOptimistic
      
      If Not rsContacts.EOF Then
          rsContacts!LastSalesDate = Me.txtSaleDate.Value
          rsContacts.Update
      End If
      
      rsContacts.Close
      Set rsContacts = Nothing
    
    End If
  End If

ErrExit:
  Exit Sub
    
ErrHandler:
  MsgBox "Error is " & Err.Description & " in " & Me.Name
    
End Sub

用於訪問和操縱 Access 資料庫中的數據的編程語法是 ADO。ADO 定義很多不同對象,每個對象都有一組屬性和方法,用於執行各種面向數據的操作。

ADO 不是一種編程語言,而是專爲數據訪問而設計的一種 VBA 語法。語法僅指在 VBA 代碼中用於完成特定任務的單詞和短語。

ADO 是從各種位置訪問數據的通用方法。到目前爲止你已經看到的示例顯示瞭如何使用 Access 更新本地 Access 資料庫中的數據。所有表、查詢、窗體和報表都存儲在位於桌面上的一個文件夾中或文件服務器上單個 Access 數據庫文件中。但作爲常規的資料庫開發工具,Access 可與所有種類的資料庫進行交互。你可以在一個 Access 資料庫中開發窗體和報表,而這些窗體和報表從可能位於本地桌面或遠程文件服務器的另一個 Access 資料庫中獲取其數據。你甚至可以鏈接到非 Access 服務器資料庫,例如 Oracle 和 SQL Server,與鏈接到 Access 資料庫一樣輕鬆。

作爲數據訪問接口,ADO 允許編寫程序以操縱本地或遠程資料庫中的數據。使用 ADO,你可以執行很多資料庫功能,包括查詢、更新、數據類型轉換、編制索引、鎖定、驗證以及事務管理。

下面提供一個過程代碼片段,顯示如何使用 ADO Recordset 對象打開表:

Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset

rs.ActiveConnection = CurrentProject.Connection
rs.Source = "tblContacts"
rs.CursorType = adOpenDynamic
rs.LockType = adLockOptimistic

rs.Open

ADO Recordset 對象提供了 Open 方法用於從表或查詢檢索數據。記錄集只不過是資料庫表中的一組記錄或查詢返回的一組記錄。

Open 方法具有四個參數,如下所述:

Source:要打開的數據源。Source 可以是某個表的名稱、某個查詢的名稱或檢索記錄的 SQL 語句。當引用表時,該表可以是本地表,也可以是鏈接表。

ActiveConnection:與某個資料庫連接。連接是與資料庫內部對象進行接觸的通信線路。CurrentProject.Connection 引用當前 Access 資料庫。

CursorType:光標指的是指向記錄的一個或一組指針。可將光標認爲是 ADO 跟蹤記錄的方式。根據用於檢索數據的屬性設置,ADO 光標可能會僅在記錄中向前移動 ( adOpenForwardOnly ) 或允許前後移動 ( adOpenDynamic )。動態光標 ( adOpenDynamic ) 允許雙向移動,而 adOpenForwardOnly 只允許向前移動。指定 Recordset 對象中使用的光標類型如下表所示:

LockType:確定在更新時 ADO 如何鎖定記錄。adLockOptimistic 允許其他用戶處理通過 ADO 代碼鎖定的記錄,而 adLockPessimistic 會將記錄完全鎖定,在對記錄數據進行更改時,其他用戶無法對該記錄進行處理。LockType 的種類說明如下:

① adLockBatchOptimistic :指示樂觀批量更新。 對於批量更新模式來說是必需的。許多應用程序會一次提取大量的行,然後需要進行協調更新,其中包括要插入、更新或刪除的整個行集。 使用批處理遊標時,只需往返服務器一次,進而提高更新性能和減少網絡流量。 藉助批處理遊標庫,可創建靜態遊標,然後斷開與數據源的連接。 此時,可對行進行更改,然後重新連接,並在批處理中將更改發佈到數據源。

② adLockOptimistic :指示提供程序使用樂觀鎖定 – 僅在你調用 Update 方法時鎖定記錄。 這意味着,其他用戶可能會在你編輯記錄與調用 Update 期間更改數據,這會產生衝突。 在衝突可能性較低或者可輕鬆解決衝突的情況下,請使用這種鎖類型。

③ adLockPessimistic :指示悲觀鎖定 ( 逐條記錄 )。 提供程序會採取必要操作來確保成功編輯記錄,通常是在編輯後立即鎖定數據源中的記錄。 當然,這意味着在你開始編輯後,記錄對其他用戶不可用,直到你調用 Update 來釋放鎖定爲止。在無法對數據進行併發更改的系統中 ( 例如在預留系統中 ),請使用該類型的鎖。

④ adLockReadOnly :指示只讀記錄。 不能更改數據。 只讀鎖是 ” 最快 ” 的鎖類型,因爲它不需要服務器維護記錄上的鎖。

⑤ adLockUnspecified :不指定鎖的類型。

通過更精簡的方式重新編寫 ADO 語句,如下所示:

Dim adRs As ADODB.Recordset

Set adRs = New ADODB.Recordset

adRs.Open "tblCustomers", CurrentProject.Connection, adOpenDynamic, adLockOptimistic

上面的示例中,記錄集屬性作爲 Open 語句的一部分進行設置。每種語法都是正確的,具體選擇哪一種,完全取決於開發人員的喜好。此外,由於我們直接訪問表,因此,無法爲數據指定 ORDER BY。數據很可能會按照某種無法預測的順序返回。

下面列出了另一個根據 CustomerID 提取單筆記錄的示例:

Dim adRs As ADODB.Recordset

Set adRs = New ADODB.Recordset

adRs.ActiveConnection = CurrentProject.Connection
adRs.Source = _
  "SELECT * FROM tblCustomers WHERE CustomerID = 17"
adCursorType = adOpenDynamic
adLockType = adLockOptimistic

adRs.Open

上例中,Source 屬性是一個 SQL SELECT 語句。用於提取記錄的 SQL 語句會根據 CustomerID 條件返回單筆記錄。由於 LockType 屬性設置爲 adLockOptimistic,因此,用戶可以更改記錄中的數據。

CursorType 和 LockType 都是可選的。如果不指定 CursorType 或 LockType,默認情況下,ADO 會將記錄集創建爲 adOpenFoewardOnly / adLockReadOnly 類型記錄集。這種類型的記錄集無法更新。如果需要對記錄集中的數據進行更改,必須瞭解各種 CusorType 和 LockType 組合以及它們如何影響記錄集的功能。

使用 ADO 時,幾乎完全是通過 Recordset 對象與數據進行交互。記錄集由包含字段的行組成,就像資料庫表一樣。打開記錄集後,便可以開始處理其行和欄位中的值。

當打開可更新的記錄集 ( 通過使用 adOpenDynamic 或 adOpenKeySet 光標類型並指定 adLockOptimistic 鎖定類型 ) 時,該記錄集將在編輯模式下打開。

在數據表視圖中打開的表與記錄集的一個主要差別在於,記錄集不提供其包含的數據的可視表示形式。數據表會爲你提供數據的行和列,甚至包含列標題,使你可以瞭解基礎表中欄位的名稱。

記錄集僅存在於內存中。沒有一種簡單的方式可以對記錄集中的數據進行可視化。作爲開發人員,必須始終了解欄位名稱、行計數以及其他對應程序非常重要的數據屬性。

在使用數據表和記錄集時,只有一條記錄處於活動狀態。在數據表中,活動記錄通過行中的顏色差異來指示。記錄集不具備這種可視輔助功能,因此,必須始終了解記錄集中的哪條記錄是當前的活動記錄。

ADO 和 DAO 都提供了很多用於跟蹤記錄集中記錄的方式,以及用於在記錄集中進行移動的各種技術。此外,瞭解記錄集中的字段名稱以及修改每個欄位中的數據也非常容易。

在更改記錄集的任何字段中的數據之前,需要確保位於要編輯的記錄中。當某個記錄集打開時,當前記錄是該記錄集中的第一條記錄。如果記錄集不包含任何記錄,則該記錄集的 EOF 屬性爲 True。

警告:

如果嘗試在不包含任何記錄的記錄集中操縱數據,將發生運行錯誤。請務必在打開記錄集後立即檢查 EOF 屬性的值,如下所示:

Set adRs = New ADODB.Recordset
adRs.Open "tblCustomers" ...

If Not adRs.EOF Then
   'Okay to process records
End If

如果代碼移動到 EOF ( MoveNext ) 或 BOF ( MovePrevious ) 以外,將發生錯誤。編寫的代碼應該始終在執行移動方法後檢查 EOF 和 BOF 屬性。

更改記錄後,使用記錄集的 Update 方法將該記錄提交到資料庫。Update 方法會將數據從內存緩衝區複製到記錄集,覆蓋原始記錄。整個記錄都將被替換,而不僅是更新的欄位。當然,記錄集中的其他記錄不會受到更新操作的影響。

當移動到另一條記錄或者關閉記錄集時,對 ADO 記錄集所作的更改將自動保存。此外,如果關閉記錄集或者結束聲明該記錄集或父資料庫的過程,也會保存編輯的記錄。但是,你應該使用 Update 方法以提高代碼的可讀性和可維護性。

使用記錄集的 CancelUpdate 方法可以取消對 ADO 記錄集的待定更改。如果撤銷對某條記錄所做的更改非常重要,必須在移動到 ADO 記錄集中的另一條記錄前發出 CancelUpdate 方法,因爲移離某條記錄會提交更改,從而無法再進行撤銷。

處理完記錄集以後將其關閉是一種非常好的做法,需要注意的是,Recordset 對象被顯式設置爲無任何對象 ( 如:Set rsContacts = Nothing ) 以將記錄集從內存中清除。省略這個重要的步驟可能會導致 ” 內存泄漏 “,因爲如果不顯式地將 ADO 對象設置爲 Nothing 並放棄,那麼它會一直保留在內存中。

☛ 添加新記錄

使用 ADO 向表中添加一筆記錄,就如同更新一條記錄一樣輕鬆。使用 AddNew 方法可以向表中添加一條新紀錄。下面的代碼顯示了一個用於向 tblCustomers 表中添加新客戶的 ADO 過程:

Public Sub AddNewContact(sFirstName As String, sLastName As String)
  
    Dim adRs As ADODB.Recordset
    Set adRs = New ADODB.Recordset
    
    adRs.Open "tblCustomerContacts", CurrentProject.Connection, _
        adOpenDynamic, adLockOptimistic
    
    With adRs
        .AddNew  'Add new record
        
        'Add data:
        .Fields("LastName").Value = sLastName
        .Fields("FirstName").Value = sFirstName
        
        .Update  'Commit changes
    End With
    
    adRs.Close
    Set adRs = Nothing

End Sub

如示例中所示,使用 AddNew 方法類似於使用 ADO 編輯記錄集數據。AddNew 會爲新紀錄創建一個緩衝區。執行 AddNew 後,你將會爲新紀錄的字段分配值。Update 方法將會將新紀錄添加到記錄集的末尾處,然後添加到基礎表中。

☛ 刪除記錄

要從表中刪除記錄,可使用 ADO 方法 Delete。下面的代碼顯示了用於從 tblCustomers 表中刪除記錄的過程:

Public Sub DeleteContact(ContactID As Long)
  
    Dim adRs As ADODB.Recordset
    Dim sSQL As String
    
    Set adRs = New ADODB.Recordset
    
    sSQL = "SELECT * FROM tblCustomerContacts " _
        & "WHERE ID = " & ContactID & ";"
    
    adRs.Open sSQL, CurrentProject.Connection, _
        adOpenDynamic, adLockOptimistic
    
    With adRs
        If Not .EOF Then
            .Delete  'Delete the record
        End If
    End With
    
    adRs.Close
    Set adRs = Nothing
  
End Sub

注意:

請不要在 Delete 方法後面緊跟 Update。一旦執行了 Delete 方法,記錄便會立即從記錄集中永久刪除。

使用 ADO 刪除記錄不會觸發刪除對話框。一般來講,使用 ADO 代碼對數據所做的更改不需要進行確認,因爲確認會中斷用戶的工作流。這意味着,作爲開發人員,應該由你來負責確保刪除操作正確無誤,然後再繼續進行操作。記錄刪除後,便無法再撤銷對基礎表所做的更改。但是,Access 仍然會強制實施參照完整性。如果嘗試刪除違反參照完整性的記錄,將會收到錯誤。

☛ 刪除多個表中的相關記錄

當編寫 ADO 代碼以刪除記錄時,需要了解應用程序的關聯關係。包含要刪除的記錄的表可能會與另一個表具有一對多關係。

在下面的表的關係圖中,tblSales 具有兩個關聯的相關表,分別是 tblSalesLineItems 和 tblSalesPayments。

” 編輯關係 ” 對話框顯示了 tblSales 和 tblSalesLineItems 之間的關係的設置狀況。關係類型爲一對多 ( 1 : M ),並強制實施參照完整性。一對多關係意味着父表 ( tblSales ) 每條記錄可能在子表 ( tblSalesLineItems ) 中具有一條或多條對應的記錄。父表中的每條記錄必須唯一,比如,不能有兩條 InvoiceNumber 、SalesDate 和其他信息完全相同的銷售記錄。

在一對多關係中,每條子記錄 ( 在 tblSalesLineItems 中 ) 必須與父表 ( tblSales ) 中的一條記錄 ( 並且只能有一條記錄 ) 相關聯。但是,tblSales 中的每條銷售記錄可能與 tblSalesLineItems 中的多條記錄相關聯。

當針對一對多關係強制實施參照完整性時,即會告訴 Access,對於 tblSales 表中的記錄,如果 tblSalesLineItems 表中存在具有相同發票編號值的記錄,則不能將其刪除。如果 Access 遇到違反參照完整性的刪除請求,那麼它會顯示一條錯誤消息,並且刪除操作將會取消,除非在 ” 編輯關係 ” 對話框中啓用了級聯刪除。

在絕大多數情況下,最好使用 Active 欄位 ( Yes/No 數據類型 ) 來指示父記錄狀態。當訂單下達時,Active 欄位設置爲 Yes,僅當訂單已經取消或完成時,才會設置爲 No。你也可能會向 tblSales 表中添加一個 CancellationDate 欄位,並將其設置訂單被取消的日期。如果 CancellationDate 爲空,則表示訂單尚未取消。

當編寫 ADO 代碼以刪除記錄時,需要首先檢查以確定包含要刪除的記錄的表與資料庫中的其他任何表之間是否存在任何一對多關係。如果存在相關表,在 Access 允許你刪除父表中的記錄之前,需要先刪除相關表中的記錄。

幸運的是,你可以編寫單個過程以刪除相關表和父表中的記錄。下面是 frmSales 中的 cmdDelete 命令按鈕對應的代碼:

Private Sub cmdDelete_Click()

  Dim lAnswer As Long
  
  Const sSQL_DELPMTS As String = _
    "DELETE * FROM tblSalesPayments WHERE InvoiceNumber = "
  Const sSQL_DELLINE As String = _
    "DELETE * FROM tblSalesLineitems WHERE InvoiceNumber = "
    
  If Me.NewRecord Then
    Me.Undo
  Else
      
    lAnswer = MsgBox("Are you sure you want to delete this Invoice?", _
      vbQuestion + vbYesNo, "Delete Invoice")
    
    If lAnswer = vbYes Then
        
      'Delete all payments for this invoice
      CurrentDb.Execute sSQL_DELPMTS & Me.InvoiceNumber.Value & ";"
      
      'Delete all line items for this invoice
      CurrentDb.Execute sSQL_DELLINE & Me.InvoiceNumber.Value & ";"
      
      'Now delete the invoice record
      DoCmd.RunCommand acCmdSelectRecord
      DoCmd.RunCommand acCmdDeleteRecord
    
    End If
  
  End If
  
End Sub

cmdDelete_Click 事件過程將刪除 tblSalesPayments、tblSalesLineItems 和 tblSales 中具有與當前發票編號匹配的發票編號記錄。

cmdDelete_Click 中的第一個語句 ( If Me.NewRecord Then ) 使用 NewRecord 屬性來確定當前銷售記錄是否爲新紀錄。如果是新紀錄,Me.Undo 將回滾對記錄所做的更改。如果當前記錄不是新紀錄,該過程將顯示一個消息框,確認用戶確實想要刪除該記錄。如果用戶單擊 ” 是 ” 按鈕,該過程便會將該記錄從表中刪除。

通過兩個常量 sSQL_DELPMTS 和 sSQL_DELLINE 保存分別用於在 tblSalesPayments 和 tblSalesLineItems 中查找和刪除具有與 frmSales 上發票編號匹配的記錄的 SQL 語句。發票編號連接到常量的結尾,並且以參數形式傳遞到 CurrentDb 的 Execute 方法。可將查詢的名稱或 SQL 語句作爲參數傳遞到 Execute 方法。Execute 方法只是運行指定的查詢或 SQL 語句。

注意:

如果查詢或 SQL 語句包含 WHERE 子句,但 Execute 方法找不到任何滿足 WHERE 條件的記錄,不會發生任何錯誤。但是,如果查詢或 SQL 語句包含無效的語法或無效的欄位或表名,Execute 方法將失敗,並引發錯誤。

在刪除 tblSalesPayments 和 tblSalesLineItems 表中的記錄後,便可以刪除 tblSales 表中的記錄。

Access 范例:Access 2016 VBA 代碼編寫範例