Delete records

I need to do a delete records but, I have several tables to do this to. I tried doing a query with all the tables but it wouldnt work. Is there a way I can delete the information from my tables and then archive the deleted information with a touch of a button?
gigifarrowAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Commented:
before deleting the records from table "A", insert the records for deletion to table "B"

private sub btnDel_click()

currentdb.execute "insert into B select A.* from A", dbfailonerror

currentdb.execute "delete * from A", dbfailonerror


end sub

another option is to mark the records using a YesNo field, you just then need to run an update query

0
 
Rey Obrero (Capricorn1)Commented:
before deleting the records from table "A", insert the records for deletion to table "B"

private sub btnDel_click()

currentdb.execute "insert into B select A.* from A"

currentdb.execute "delete * from A"


end sub

another option is to mark the records using a YesNo field, you just then need to run an update query


0
 
Jeffrey CoachmanMIS LiasonCommented:
<No points wanted>

Make sure you have a good error handler.

And with any " destructive" (delete) action, I always like to add in a comfirmation
(in case the user hits the button by mistake):

dim bytconfirm as byte
bytconfirm =msgbox("archive data, are you sure?",vbyesno+vbquestion)
If bytconfirm =vbno then
    exit sub
currentdb.execute "insert into B select A.* from A", dbfailonerror

currentdb.execute "delete * from A", dbfailonerror
end if

Again, no points wanted, as capricorn1 has answered your question directly.

;-)

JeffCoachman


0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
gigifarrowAuthor Commented:
Thanks for the help. I dont know how or where to put it to archive with date before its deleted
Private Sub Command166_Click()
Dim bytconfirm As Byte
bytconfirm = MsgBox("archive data, are you sure?", vbYesNo + vbQuestion)
If bytconfirm = vbNo Then
    Exit Sub
    End If
CurrentDb.Execute "insert into ttbl_FBCB2_Demographics_Survey select tbl_FBCB2_Demographics_Survey.* from tbl_FBCB2_Demographics_Survey", dbFailOnError

CurrentDb.Execute "delete * from tbl_FBCB2_Demographics_Survey", dbFailOnError
End Sub

0
 
Rey Obrero (Capricorn1)Commented:
explain what you mean by

<where to put it to archive with date>
0
 
gigifarrowAuthor Commented:
I need to save a copy of the deleted table with the date it was deleted. So,then I will have a backup. So, before I delete the table I must archive it first. I need to have one control button that can do all this. I hope this make since I apologize for the misunderstanding.
0
 
Rey Obrero (Capricorn1)Commented:
what you need is to add a datefield to table ttbl_FBCB2_Demographics_Survey, say name it DateArchived  Data Type Date/Time

you will then add an update query to update this field after the append query.

is this an option?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.