Solved

Delete records

Posted on 2011-03-17
7
250 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

749 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