Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Modules _ Using Recordset To Delete Data From Table

Posted by: ordnance1 Jan 11 2017, 12:06 AM

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

Posted by: adaytay Jan 11 2017, 05:03 AM

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)

Posted by: ordnance1 Jan 11 2017, 08:22 AM

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?

Posted by: ordnance1 Jan 11 2017, 08:42 AM

Okay,

I just put toegther some test data and solution2 works perfectly. Thank You very much.

Posted by: adaytay Jan 11 2017, 10:46 AM

You are most welcome, good luck with the rest of your project!