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, 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
Tags

18 Comments

  1. Using you code, but instead of using TeacherID=5, i want to use cell value from Excel sheet. It is picking the value from the cell but not searching the record in my access database to update the fields. Can u please help me out. Thanks

    Public Const Conn As String = “Data Source= C:\Users\Atif\Desktop\New Database\Sample.accdb;”

    Sub ADODBUpdating()
    On Error GoTo ErrorHandler
    Dim sql As String
    Dim rs As ADODB.Recordset
    Dim cn As New ADODB.Connection
    Dim id As String

    id = Range(“H1”)

    cn.Open “Provider=Microsoft.ACE.OLEDB.12.0; ” & Conn

    sql = “SELECT * FROM Table1 WHERE Invoice = ” & id
    ‘We are using a select statement that will return only
    ‘one record (TeacherID 5)

    Set rs = New ADODB.Recordset
    rs.Open sql, cn, 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

    ![Client] = Range(“B5”).Value
    ‘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

  3. 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.

  4. 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.

  6. 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.

  7. 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

  8. 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

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

      • 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

  10. 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

  11. 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?

Leave a Reply

Your email address will not be published. Required fields are marked *

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube