Lesson 27: Deleting a Record

	Deleting a Record

Using Microsoft Access or DAO

To delete a record from a set, the Recordset object is equipped with a method named Delete(). This method is quite easy to use but there are two main concerns you should have. First, when creating a record set, you should set a criterion that will be used to locate the record you want to delete: it is not a good idea to simply specify the name of a table or a query as the source of the set. Here is an example of deleting a record using the Microsoft Access Object library:

Private Sub cmdDeleteVideo_Click()
   Dim dbVideoCollection As Object
   Dim rstVideos As Object

   Set dbVideoCollection = CurrentDb
   Set rstVideos = dbVideoCollection.OpenRecordset("SELECT * FROM Videos WHERE Title = 'The Firm'")

   rstVideos.Delete
End Sub

When this code runs, it will look for a video titled The Firm in a table named Videos. If it finds it, it deletes it.

If you try deleting a record that doesn't exist, you would receive a 3021 error stating "No Current Record":

This means that you should check whether the record exists before deleting it. The second concern you would have is to specify what to do after the record has been deleted. In most cases, you should remember to move the position to a different record.

Using ADO

ADO essentially uses the same approach as Microsoft Access and DAO to delete a record. The concerns are also the same. Here is an example:

Private Sub cmdDeleteLast_Click()
    Dim rstVideos As ADODB.Recordset
    
    Set rstVideos = New ADODB.Recordset
    rstVideos.Open "SELECT * FROM Videos WHERE Title = 'Leap of Faith'", _
		   CurrentProject.Connection, _
                   adOpenDynamic, adLockPessimistic, adCmdText
                   
    rstVideos.Delete
    
    rstVideos.Close
    Set rstVideos = Nothing
End Sub


Previous	Copyright © Yevol, 2007	Next