Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 257
  • Last Modified:

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?
0
gigifarrow
Asked:
gigifarrow
  • 4
  • 2
1 Solution
 
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
 
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
 
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
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
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.

Join & Write a Comment

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now