Updating, Adding And Deleting Records In a Recordset

In this blog post we will be showing you how to update, add and delete records in a recordset.

Please download Updating, Adding And Deleting Records

Updating A Recordset

DAO

Sub DAOUpdating()
On Error GoTo ErrorHandler
'This sub-produre will add 'z' to the first name of
'the record that corresponds to TeacherID 5
Dim sql As String
Dim rs As DAO.Recordset

sql = "SELECT * FROM tblTeachers WHERE TeacherID=5"
'We are using a select statement that will return only
'one record (TeacherID 5)

Set rs = CurrentDb.OpenRecordset(sql)
'Open RecordSet

With rs

    If Not .BOF And Not .EOF Then
    'Ensure that the recordset contains records
    'If no records the code inside the if...end if
    'statement won't run
    
        .MoveLast
        .MoveFirst
        'Not necessary but good practice
        
        If .Updatable Then
        'It is possible that the record you want to update
        'is locked by another user. If we don't check before
        'updating, we will generate an error
        
            .Edit
            'Must start an update with the edit statement
            
            ![FirstName] = "z" & ![FirstName]
            'Another way of accessing the fields would be to use
            '.fields("FirstName") = z" & .fields("FirstName")
            
            .Update
            'And finally we will need to confirm the update
            
        End If
    End If
    
    .Close
    'Make sure you close the recordset...
End With

ExitSub:
    Set rs = Nothing
    '...and set it to nothing
    Exit Sub
ErrorHandler:
    Resume ExitSub

End Sub

ADODB

Sub ADODBUpdating()
On Error GoTo ErrorHandler
'This sub-produre will add 'z' to the first name of
'the record that corresponds to TeacherID 5
Dim sql As String
Dim rs As adodb.Recordset

sql = "SELECT * FROM tblTeachers WHERE TeacherID=5"
'We are using a select statement that will return only
'one record (TeacherID 5)

Set rs = New adodb.Recordset
rs.Open sql, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
'Open RecordSet

With rs

    If Not .BOF And Not .EOF Then
    'Ensure that the recordset contains records
    'If no records the code inside the if...end if
    'statement won't run
    
        .MoveLast
        .MoveFirst
        'Not necessary but good practice
        
        If .Supports(adUpdate) Then
        'It is possible that the record you want to update
        'is locked by another user. If we don't check before
        'updating, we will generate an error
            
            ![FirstName] = "x" & ![FirstName]
            'Another way of accessing the fields would be to use
            '.fields("FirstName") = z" & .fields("FirstName")
            
            .Update
            'And finally we will need to confirm the update
            
        End If
    End If
    
    .Close
    'Make sure you close the recordset...
End With

ExitSub:
    Set rs = Nothing
    '...and set it to nothing
    Exit Sub
ErrorHandler:
    Resume ExitSub

End Sub

Adding Records

DAO

Sub DAOAdding()
On Error GoTo ErrorHandler
'This sub-produre will add a new record to tblTeachers
Dim sql As String
Dim rs As DAO.Recordset

sql = "tblTeachers"
'The table we will be adding the record to is tblTeachers

Set rs = CurrentDb.OpenRecordset(sql)
'Open RecordSet

With rs
        
    .AddNew
    'Must start an update with the AddNew statement
    
    .Fields!FirstName = "Steve"
    .Fields!LastName = "Evets"
    .Fields!CreatedBy = 1 ' NOT NULL
    'Here we are adding someone called Steve Evets and adding
    'the number 1 to the CreatedBy field
    
    .Update
    'And finally we will need to confirm the update

    
    .Close
    'Make sure you close the recordset...
End With

ExitSub:
    Set rs = Nothing
    '...and set it to nothing
    Exit Sub
ErrorHandler:
    Resume ExitSub

End Sub

ADODB

Sub ADOAdding()
On Error GoTo ErrorHandler
'This sub-produre will add a new record to tblTeachers
Dim sql As String
Dim rs As adodb.Recordset

sql = "tblTeachers"
'The table we will be adding the record to is tblTeachers

Set rs = New adodb.Recordset
rs.Open sql, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
'Open RecordSet

With rs
    
    .AddNew
    'Must start an update with the AddNew statement
    
    .Fields!FirstName = "Robert"
    .Fields!LastName = "Trebor"
    .Fields!CreatedBy = 1  ' NOT NULL
    'Here we are adding someone called Robert Trebor and adding
    'the number 1 to the CreatedBy field
    
    .Save
    'To confirm the addition we need to use the Save command
   
    .Close
    'Make sure you close the recordset...
End With

ExitSub:
    Set rs = Nothing
    '...and set it to nothing
    Exit Sub
ErrorHandler:
    Resume ExitSub
End Sub

Deleting Records

DAO

Sub DAODeleting()
On Error GoTo ErrorHandler
'This sub-produre will delete the record that
'corresponds to TeacherID 7
Dim sql As String
Dim rs As DAO.Recordset

sql = "SELECT * FROM tblTeachers WHERE TeacherID=7"
'We are using a select statement that will return only
'one record (TeacherID 7)

Set rs = CurrentDb.OpenRecordset(sql)
'Open RecordSet

With rs

    If Not .BOF And Not .EOF Then
    'Ensure that the recordset contains records
    'If no records the code inside the if...end if
    'statement won't run
    
        .MoveLast
        .MoveFirst
        'Not necessary but good practice
        
        If .Updatable Then
        'It is possible that the record you want to update
        'is locked by another user. If we don't check before
        'updating, we will generate an error
        
            .Delete
            'The only command we need!
            'Be careful!!! Once a record is deleted, it is gone forever...
           
        End If
    End If
    
    .Close
    'Make sure you close the recordset...
End With

ExitSub:
    Set rs = Nothing
    '...and set it to nothing
    Exit Sub
ErrorHandler:
    Resume ExitSub
End Sub

ADODB

Sub ADODeleting()
On Error GoTo ErrorHandler
'This sub-produre will delete the record that
'corresponds to TeacherID 7
Dim sql As String
Dim rs As adodb.Recordset

sql = "SELECT * FROM tblTeachers WHERE TeacherID=8"
'We are using a select statement that will return only
'one record (TeacherID 7)

Set rs = New adodb.Recordset
rs.Open sql, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
'Open RecordSet

With rs

    If Not .BOF And Not .EOF Then
    'Ensure that the recordset contains records
    'If no records the code inside the if...end if
    'statement won't run
    
        .MoveLast
        .MoveFirst
        'Not necessary but good practice
        
        If .Supports(adDelete) Then
        'It is possible that the record you want to update
        'is locked by another user. If we don't check before
        'updating, we will generate an error
        
            .Delete
            'The only command we need!
            'Be careful!!! Once a record is deleted, it is gone forever...
           
        End If
    End If
    
    .Close
    'Make sure you close the recordset...
End With

ExitSub:
    Set rs = Nothing
    '...and set it to nothing
    Exit Sub
ErrorHandler:
    Resume ExitSub
End Sub

Related Posts

Looping Through a Recordset
What are Recordsets

As always, a site wouldn't be anything without its users so please feel free to comment! We welcome any comments you have on the material and any suggestions you may have for future content.


In our humble opinion, it is worth commenting just so you can play with Fun Captcha below!

16 Comments

  • saifuzaman says:

    HI, how do I query data from the table using DAO?

  • Farhan says:

    So helpfull code regarding ADODB , but if i want to pass textboxes value instead of hard codes it doesn’t work , or if i want to do it through quries insert and update .please write code for me.

  • srinivasan says:

    Hi How do I pass value from a combo list in the “SELECT * FROM tblTeachers WHERE TeacherID=5” instead of value 5? Thanks in advance.

  • Dilshad says:

    what if the data have to added,edited or deleted to and from more than one tables??

  • I am adding new records with DAO, but the new records do not appear in the combobox. I am using a subform to add and edit records, and in the combobox I am selecting old records to check or edit. The new records only are displayed in the combobox if I leave the current form and run it again.

  • Jean says:

    how would you add or edit records in a linked table (backend)?
    I can edit records and add new records on local tables, but i can’t get it to work on tables that are linked to backend database.
    Please help

  • Jack says:

    What If I Have one column named (Barcode) which contains barcodes(e.g. M231292391) and also text(Test), and I want just to keep the real barcodes, the rest of them(texts) I want to remove. How should I proceed in this case? The Delete code portion doesn’t work for me, or idk where should I insert it. Thank you

    • Robert Austin says:

      You probably just need to delete that column at the table level. Recordsets are for removing data on a record by record basis.

      • Jack says:

        At the table level(in Access) i deleted it manually, it`s ok, but I want to do something in the code to do this automatically,
        I have good codes between 9 and 12 chars, and codes that I want to omit that are maximum 6 chars. So I tried this in the section of the code which is for identifying the records :

        ‘1. filter according the date only
        ‘query the field RESULT.STIME for later filtering according the time
        strSQL = “SELECT RESULT.IDNO, RESULT.DATE, RESULT.ETIME FROM RESULT WHERE” & _
        ” (([RESULT.DATE]) BETWEEN DateValue(‘” & DateBegin & “‘) AND DateValue(‘” & DateEnd & “‘)) AND LEN(BarCode) = 10 ;”
        _____________________________________________
        So I inserted the condition of LEN(Barcode) to be equal to 10, and it works, but how do I make it to be equal in an interval, like between 9-12?
        Thanx

  • Chiranejevi says:

    if i update recordset, will it update table automatically?

  • ryan says:

    i update a query on my form but then on the database it updates all how can i fix the error reply ASAP ty… Godbless

  • DontDoDave says:

    Say I have a recordset based on this “SELECT * FROM tblTeachers WHERE TeacherID=5” and it returns no matching data. Can you use the else of the .BOF .EOF check to .AddNew?