Full Version: Using Recordset To Delete Data From Table
UtterAccess Forums > Microsoft® Access > Access Modules
ordnance1
Through some researching on-line I have developed this recordset that returns the AbsenceID's (field(2)) from my query qry_Unmatch_Test_AAAA. What I would like to do is then delete the records in tbl_DaysOff that have the corresponding AbsenceID. I am trying not to create a temporary table.

Any help as always would be much appreciated.

CODE
Option Compare Database

Public Sub daoRecordset()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strValue As String

Set db = CurrentDb
Set qdf = db.QueryDefs("qry_Unmatch_Test_AAAA")

Set rs = qdf.OpenRecordset

If Not (rs.EOF And rs.BOF) Then
Do While Not rs.EOF
    strValue = rs.Fields(2).Value
Debug.Print strValue
rs.MoveNext

Loop

rs.Close
Set rs = Nothing
Set qdf = Nothing
Set db = Nothing

End If
End Sub
adaytay
I'd do something like the following:

CODE
Option Compare Database

Public Sub daoRecordset()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strValue As String, strSQL As String

Set db = CurrentDb
Set qdf = db.QueryDefs("qry_Unmatch_Test_AAAA")

Set rs = qdf.OpenRecordset

If Not (rs.EOF And rs.BOF) Then
Do While Not rs.EOF
    strValue = rs.Fields(2).Value
    strSQL="DELETE FROM tbl_DaysOff WHERE AbsenceID='" & strValue & "'"
    CurrentDB.Execute strSQL, dbSeeChanges + dbFailOnError
'Debug.Print strValue
rs.MoveNext

Loop

rs.Close
Set rs = Nothing
Set qdf = Nothing
Set db = Nothing

End If
End Sub


Alternatively, an alternative would be to go fully SQL to massively simplify the code and remove the need for the recordset in the first place.
(I'm assuming that rs.fields(2) is called AbsenceID):

CODE
DELETE FROM tbl_DaysOFF WHERE AbsenceID IN (SELECT AbsenceID FROM qry_Unmatch_Test_AAAA)
ordnance1
Thanks for your reply.

I try your solutions this evening.

I do have a question. If I use solution 2 (the straight SQL) and if qry_Unmatch_Test_AAAA returns more than 1 AbsenceID will solution 2 delete all of the returned AbsenceID's?
ordnance1
Okay,

I just put toegther some test data and solution2 works perfectly. Thank You very much.
adaytay
You are most welcome, good luck with the rest of your project!
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.