Solved

Delete records

Posted on 2011-03-17
7
251 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Handle Apostrophes in VBA SQL - Part 2 10 48
What is needed to become a DBA? 7 55
2 Global Vars, 1 List Box 4 32
How do you think this website does searches? 5 34
Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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.

734 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