UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Using Recordset To Delete Data From Table, Access 2016    
 
   
ordnance1
post Jan 11 2017, 12:06 AM
Post#1



Posts: 473
Joined: 7-May 11



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
Go to the top of the page
 
adaytay
post Jan 11 2017, 05:03 AM
Post#2


UtterAccess VIP
Posts: 3,729
Joined: 7-October 03
From: Yorkshire, England.


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)

--------------------
Cheers,

Adz.
Go to the top of the page
 
ordnance1
post Jan 11 2017, 08:22 AM
Post#3



Posts: 473
Joined: 7-May 11



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?
Go to the top of the page
 
ordnance1
post Jan 11 2017, 08:42 AM
Post#4



Posts: 473
Joined: 7-May 11



Okay,

I just put toegther some test data and solution2 works perfectly. Thank You very much.
Go to the top of the page
 
adaytay
post Jan 11 2017, 10:46 AM
Post#5


UtterAccess VIP
Posts: 3,729
Joined: 7-October 03
From: Yorkshire, England.


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

--------------------
Cheers,

Adz.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    20th August 2017 - 04:55 AM