Delete one Table from Another

[nickvans]

Hello everyone, I'm fairly new to VBA and MS Access (I'm using 2003) but my issue seems like a pretty straight forward one. I would like to delete all records found in one table from another one.

I have code which grabs values from a list box (which has as its source "tblNewModPart") then uses them in a Make-Table query which creates a table called "tblRemovedParts." I would like to delete all values from "tblRemovedParts" from "tblNewModPart."

I've tried setting up a Delete-query which contains the two tables joined by part number, but when I try to run the query, it pops up an error message saying "Specify the table containing the records you want to delete."

The code for the delete query is:

DELETE tblNewModPart.ModuleNum, tblNewModPart.PartNum, tblNewModPart.Description, tblNewModPart.Quantity
FROM tblRemovedParts INNER JOIN tblNewModPart 
ON tblRemovedParts.PartNum = tblNewModPart.PartNum;

How do I deal with this?

I've found references elsewhere saying to set the Unique Records property to Yes, but that doesn't seem to have helped.

Any assistance would be great. Thanks!

[nico5038]

Joining tables won't work in this case.

Best to make first a backup before running a query like this !

One general approach is to use a subquery like:

delete * from tblRemovedParts where PartNum in (Select PartNum from tblNewModPart)

This requires that all PartNum's to be removed are found in tblNewModPart.

Getting the idea?

Nic;o)

[nickvans]

Joining tables won't work in this case.

Best to make first a backup before running a query like this !

One general approach is to use a subquery like:

delete * from tblRemovedParts where PartNum in (Select PartNum from tblNewModPart)

This requires that all PartNum's to be removed are found in tblNewModPart.

Getting the idea?

Nic;o)

Thanks for the quick reply. I think I get it. Instead of trying to join the two tables, I'll instead just select all part numbers from the other table. I suppose thats what I was trying to do in the first place, but my knowledge of MS Access is pretty limited. Thanks!

[puppydogbuddy]

Hello everyone, I'm fairly new to VBA and MS Access (I'm using 2003) but my issue seems like a pretty straight forward one. I would like to delete all records found in one table from another one.

I have code which grabs values from a list box (which has as its source "tblNewModPart") then uses them in a Make-Table query which creates a table called "tblRemovedParts." I would like to delete all values from "tblRemovedParts" from "tblNewModPart."

I've tried setting up a Delete-query which contains the two tables joined by part number, but when I try to run the query, it pops up an error message saying "Specify the table containing the records you want to delete."

The code for the delete query is:

DELETE tblNewModPart.ModuleNum, tblNewModPart.PartNum, tblNewModPart.Description, tblNewModPart.Quantity
FROM tblRemovedParts INNER JOIN tblNewModPart 
ON tblRemovedParts.PartNum = tblNewModPart.PartNum;

How do I deal with this?

I've found references elsewhere saying to set the Unique Records property to Yes, but that doesn't seem to have helped.

Any assistance would be great. Thanks!

Try it this way after you have made backups of your mdb:

DELETE tblNewModPart.* FROM tblNewModPart  INNER JOIN tblNewModPart ON tblRemovedParts.PartNum = tblNewModPart.PartNum;

[puppydogbuddy]

oops! Sorry Nico........did not see your response.

[nico5038]

Keep me posted nickvans :-)

No problem PuppyDogBuddy, I see we agree on the backup :-)

Nic;o)

[nickvans]

Keep me posted nickvans :-)

No problem PuppyDogBuddy, I see we agree on the backup :-)

Nic;o)

Thanks for the advice. I got it working. I ended up needing to revisit the code that grabbed values from the list box (because I had screwed it up, inadvertently), and ended up simply passing the same values to a delete query as the make-table query.

If you're interested, here's the code.

 Private Sub buttonRemovePartFromNewModPart_Click()
 
DoCmd.SetWarnings False
 
'POPULATE AND RUN qryAppend_RemovedParts
'THIS SECTION OF CODE RUNS THE FIND QUERY BASED ON THE NEWMODPART MULTI-SELECT LIST
'IT REQUIRES "qryAppend_RemovedParts" WHICH CONTAINS "partnum/description/quantity"
    Dim Q As QueryDef, DB As Database
    Dim Criteria As String
    Dim ctl As Control
    Dim Itm As Variant
 
   ' Build a list of the selections.
   Set ctl = Me![lstNewModPart]
 
   For Each Itm In ctl.ItemsSelected
      If Len(Criteria) = 0 Then
         Criteria = Chr(34) & ctl.ItemData(Itm) & Chr(34)
      Else
         Criteria = Criteria & "," & Chr(34) & ctl.ItemData(Itm) _
          & Chr(34)
      End If
   Next Itm
 
   If Len(Criteria) = 0 Then
      Itm = MsgBox("You must select one or more items from the" & _
        " list above.", 0, "No Selection Made")
      Exit Sub
   End If
 
   ' Modify the Query.
   Set DB = CurrentDb()
   Set Q = DB.QueryDefs("qryAppend_RemovedParts")
   Q.SQL = "INSERT INTO tblRemovedParts ( PartNum, Description, Quantity) SELECT tblParts.PartNum, tblParts.Description, tblModPart.Quantity From tblParts INNER JOIN tblModPart ON tblParts.PartNum = tblModPart.PartNum Where (tblParts.[PartNum] In(" & Criteria & _
     ") AND (tblModPart.ModuleNum)=[Forms]![frmModSearch]![lstModList]);"
   Q.Close
   ' Run the query.
   DoCmd.OpenQuery "qryAppend_RemovedParts"
 
'END qryAppend_RemovedParts
 
'Delete all records matching "Criteria" from tblNewModPart
   ' Modify the Query.
   Set DB = CurrentDb()
   Set Q = DB.QueryDefs("qryDelete_NewModPartSelected")
   Q.SQL = "DELETE * FROM tblNewModPart Where tblNewModPart.[PartNum] In(" & Criteria & _
     ");"
   Q.Close
    DoCmd.OpenQuery "qryDelete_NewModPartSelected"
    Me.lstNewModPart.Requery
 
'Delete all records matching "Criteria" from tblAddedParts
   Set DB = CurrentDb()
   Set Q = DB.QueryDefs("qryDelete_AddedPartsSelected")
   Q.SQL = "DELETE * FROM tblAddedParts Where tblAddedParts.[PartNum] In(" & Criteria & _
     ");"
   Q.Close
    DoCmd.OpenQuery "qryDelete_AddedPartsSelected"
 
DoCmd.SetWarnings True
 
End Sub 

[nico5038]

Hmm, some remarks:
1) Using recordset processing is some 8 to 10 times slower as queries.
2) You can run into an "illegal" query when the Criteria string gets too long :-(

When you want a 100% solution using this code, better to have an additional table and fill that instead of appending the values to the Criteria field.
Next use:
delete * from tblX where ID in (select ID from tblTemp)

Getting the idea ?

Nic;o)

[nico5038]

Hmm, on second thoughts, when you have a listbox, better to use a subform and add a Yes/No field to the original recordsource of the listbox.
Now the use can set the YesNo field (no hassle with keeping CTRL or SHIFT pressed) and use for the delete:

delete * from tblX where ID in (select ID from tblTempSubform where YesNoField = True)

Better ? :-)

Nic;o)

[nickvans]

Hmm, some remarks:
1) Using recordset processing is some 8 to 10 times slower as queries.
2) You can run into an "illegal" query when the Criteria string gets too long :-(

When you want a 100% solution using this code, better to have an additional table and fill that instead of appending the values to the Criteria field.
Next use:
delete * from tblX where ID in (select ID from tblTemp)

Getting the idea ?

Nic;o)

I don't think I quite know what you're talking about. I understand what you said about the code taking much longer to process than a query, and I also understand the bit about running into problems if the string gets to long (unlikely in my case I think, as there are generally no more than two or three parts you'd want to remove from any given module, and each part number is only about 10 characters long. The string should have no problems up to 255 characters, right?)

To follow your suggestion, how would I go about adding each selection to a table, other than doing it as I am?

In regards to your second post: The reason I went with a listbox rather than a sub form is I have no experience with subforms (I'm sort of learning as I go here) and figured a list box would work just as well.

As always, your input is invaluable.

Thanks,

nickvans

[nico5038]

Your original question stated a deletion of all mathcing rows from a table, thus my warning about the string length. (Effectively a string defined within VBA will be upto some 64 k characters)

When you have a limited number of rows to delete this won't cause trouble.

Personally I prefer to have the user clicking checkboxes for selections as they are "more stable" as the selection in a listbox and it offers me the possibility to create a standard query for the deletion.

Subforms are very easy to create and Access will even propose a linking field to synchronize (filter) the subform rows by the ID of the masterform. Just give it a try or look into the Orderform of the Northwind.mdb sample database.

Nic;o)