Solved

Delete records

Posted on 2011-03-17
7
252 Views
Last Modified: 2012-05-11
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
Comment
Question by:gigifarrow
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35158052
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
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 35158058
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35158154
<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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:gigifarrow
ID: 35159038
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35159062
explain what you mean by

<where to put it to archive with date>
0
 

Author Comment

by:gigifarrow
ID: 35159666
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35159719
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

Featured Post

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question