possible to run append and delete query at same time?

[jazminecat]

Hi all, just a basic question, I hope. I have a db with two tables - I need to find all the records in table A with a matching serial number in table B, use those matching records from table A to populate a repot, dump them into an archive table, delete them from table A, all at the same time. This report will be run monthly, with new records being imported into table A and B all the time - hence the need for the archive showing what was pulled out of table A.

Anyone have any ideas the best way to go about this? I want to automate all of this onto a form, at the click of a button, but at this point, I just need to know the right way to go about it. Do I write an append query to add the records to table C (the archive) and then a delete query to delete the matching records from table A? Or is there a way to do it with one fell swoop?

Thanks for your expertise, as always!

 

[Remou]

I would imagine that even if you could do the whole thing in one fell swoop, you wouldn't. I think an append then delete, after checking that the append has worked, and using the records appended as the criteria for the delete, would be safe.

 

[Melagan]

I'm not sure if there is a way to do all of that in one SQL statement, but you can run multiple queries through code behind a command button easily enough. I think your approch with using multiple queries should be fine - here is how to excecute them.

Private Sub cmdOK_Click()

 DoCmd.OpenQuery "qryAPPEND_tbl-C", acViewNormal
 DoCmd.OpenQuery "qryDELETE_tbl-A", acViewNormal
 DoCmd.OpenReport "rptReport", acViewPreview

End Sub

~Melagan
______
"It's never too late to become what you might have been.

 

[jazminecat]

ok cool. That all makes perfect sense to me. Now just to add a wrench to the mix, the user has requested that the delete query involve and either/or scenario - she wants to delete everything from table A that has a matching serial number in table B, as well as everything from table A that has a description of XYZ. My code for the first bit is simple:

DELETE tblA.*, tblA.ID
FROM tblA
WHERE (((tblA.ID) In (Select ID from tblB)));

I need to add an OR statement in there such that my code would be

DELETE tblA.*, tblA.ID
FROM tblA
WHERE (((tblA.ID) In (Select ID from tblB)))
OR
tblA.Description = "XYZ"

any ideas? the items that match the second criteria could easily be included in the first criteria, so it's not an exclusive proposition.

thanks!

 

[MichaelRed]

please review the availble literature re TRANSACTIONS. it could avoid a bit of hte jibber-jabber ...




MichaelRed

 

[Remou]

I can almost guarantee that this is going to be an "Are you sure? Are you really, really sure?" situation, so perhaps you could create a temporary table to show to the user with a message saying about to delete such and such? Not forgetting Transactions, which I had, when you get round to the appending and deleting.

 

[jazminecat]

I'm not familiar with transactions - anyone have a link to a good concise explanation? The help file in access isn't much help on that topic.

I did think about the fact that the user could stop midstream, change their mind, or a power outage, anyting that could cause the data to be deleted before the report was run and printed out. So my sequence will be something like this:

1. save records from table A that have records from table B, as well as records from table A with XYZ in description to a holding table

2. pull records for report from table A that match serials in table B, and run report.

3. delete records from table A that have records from table B, as well as records from table with XYZ in description.

The holding table will be kept, for now, and a date field added to it to show which report date the records match - just in case. If it gets too unwieldy, or really does prove unnecessary, I'll start overwriting it every time. For now, I envision archiving the data in a backup db annually. We're in finance, and you know how that goes. save everything.

Does this cover me?

 

[Golom]

A much simplified overview of transactions may look like this

[COLOR=Green]' db is a DAO.Database Object.
' Similar concepts apply to ADO.[/color]
On Error GoTo TransActionFailed
db.BeginTrans

db.Execute "INSERT INTO tblA Select * From tblB"
db.Execute "Delete * From tblB"

db.CommitTrans
Exit Sub

TransactionFailed:

db.RollBack

If either of the "Execute" statements (Insert or Delete) returns an error then the database will be rolled back to the state as it was before either of them was executed. If they both execute without errors then the changes are commited and the database is updated.

Instead of using error trapping to decide to commit or rollback, you could ask the user.

[COLOR=Green]' db is a DAO.Database Object.
' Similar concepts apply to ADO.[/color]
Dim Inserts As Long, Deletes As Long
db.BeginTrans

db.Execute "INSERT INTO tblA Select * From tblB"
Inserts = db.RecordsAffected
db.Execute "Delete * From tblB"
Deletes = db.RecordsAffected

If MsgBox(Inserts & " records Inserted" & vbCrLf & _
          Deletes & " records Deleted" & vbCrLf & vbCrLf & _
          "Do you want to save these results?, vbYesNo) = vbYes Then
   db.CommitTrans
Else
   db.Rollback
End If

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[jazminecat]

AH, ....somewhere deep in the recesses of my brain i remember discussion transaction rollback now in my VB class. Ok, this makes perfect sense. I think I can make this work from here - thank you all SO much for your help and guidance on this one. You really helped me make sense of what seemed like a really bass-akwards way of doing something.