By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,027 Members | 1,273 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,027 IT Pros & Developers. It's quick & easy.

Delete one Table from Another

P: 62
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:
Expand|Select|Wrap|Line Numbers
  1. DELETE tblNewModPart.ModuleNum, tblNewModPart.PartNum, tblNewModPart.Description, tblNewModPart.Quantity
  2. FROM tblRemovedParts INNER JOIN tblNewModPart 
  3. 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!
Sep 2 '07 #1
Share this Question
Share on Google+
10 Replies


nico5038
Expert 2.5K+
P: 3,072
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)
Sep 2 '07 #2

P: 62
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!
Sep 2 '07 #3

puppydogbuddy
Expert 100+
P: 1,923
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:
Expand|Select|Wrap|Line Numbers
  1. DELETE tblNewModPart.ModuleNum, tblNewModPart.PartNum, tblNewModPart.Description, tblNewModPart.Quantity
  2. FROM tblRemovedParts INNER JOIN tblNewModPart 
  3. 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:

Expand|Select|Wrap|Line Numbers
  1. DELETE tblNewModPart.* FROM tblNewModPart  INNER JOIN tblNewModPart ON tblRemovedParts.PartNum = tblNewModPart.PartNum;
Sep 2 '07 #4

puppydogbuddy
Expert 100+
P: 1,923
oops! Sorry Nico........did not see your response.
Sep 2 '07 #5

nico5038
Expert 2.5K+
P: 3,072
Keep me posted nickvans :-)

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

Nic;o)
Sep 2 '07 #6

P: 62
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.

Expand|Select|Wrap|Line Numbers
  1.  Private Sub buttonRemovePartFromNewModPart_Click()
  2.  
  3. DoCmd.SetWarnings False
  4.  
  5. 'POPULATE AND RUN qryAppend_RemovedParts
  6. 'THIS SECTION OF CODE RUNS THE FIND QUERY BASED ON THE NEWMODPART MULTI-SELECT LIST
  7. 'IT REQUIRES "qryAppend_RemovedParts" WHICH CONTAINS "partnum/description/quantity"
  8.     Dim Q As QueryDef, DB As Database
  9.     Dim Criteria As String
  10.     Dim ctl As Control
  11.     Dim Itm As Variant
  12.  
  13.    ' Build a list of the selections.
  14.    Set ctl = Me![lstNewModPart]
  15.  
  16.    For Each Itm In ctl.ItemsSelected
  17.       If Len(Criteria) = 0 Then
  18.          Criteria = Chr(34) & ctl.ItemData(Itm) & Chr(34)
  19.       Else
  20.          Criteria = Criteria & "," & Chr(34) & ctl.ItemData(Itm) _
  21.           & Chr(34)
  22.       End If
  23.    Next Itm
  24.  
  25.    If Len(Criteria) = 0 Then
  26.       Itm = MsgBox("You must select one or more items from the" & _
  27.         " list above.", 0, "No Selection Made")
  28.       Exit Sub
  29.    End If
  30.  
  31.    ' Modify the Query.
  32.    Set DB = CurrentDb()
  33.    Set Q = DB.QueryDefs("qryAppend_RemovedParts")
  34.    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 & _
  35.      ") AND (tblModPart.ModuleNum)=[Forms]![frmModSearch]![lstModList]);"
  36.    Q.Close
  37.    ' Run the query.
  38.    DoCmd.OpenQuery "qryAppend_RemovedParts"
  39.  
  40. 'END qryAppend_RemovedParts
  41.  
  42. 'Delete all records matching "Criteria" from tblNewModPart
  43.    ' Modify the Query.
  44.    Set DB = CurrentDb()
  45.    Set Q = DB.QueryDefs("qryDelete_NewModPartSelected")
  46.    Q.SQL = "DELETE * FROM tblNewModPart Where tblNewModPart.[PartNum] In(" & Criteria & _
  47.      ");"
  48.    Q.Close
  49.     DoCmd.OpenQuery "qryDelete_NewModPartSelected"
  50.     Me.lstNewModPart.Requery
  51.  
  52. 'Delete all records matching "Criteria" from tblAddedParts
  53.    Set DB = CurrentDb()
  54.    Set Q = DB.QueryDefs("qryDelete_AddedPartsSelected")
  55.    Q.SQL = "DELETE * FROM tblAddedParts Where tblAddedParts.[PartNum] In(" & Criteria & _
  56.      ");"
  57.    Q.Close
  58.     DoCmd.OpenQuery "qryDelete_AddedPartsSelected"
  59.  
  60. DoCmd.SetWarnings True
  61.  
  62. End Sub 
Sep 3 '07 #7

nico5038
Expert 2.5K+
P: 3,072
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)
Sep 3 '07 #8

nico5038
Expert 2.5K+
P: 3,072
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)
Sep 3 '07 #9

P: 62
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
Sep 3 '07 #10

nico5038
Expert 2.5K+
P: 3,072
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)
Sep 4 '07 #11

Post your reply

Sign in to post your reply or Sign up for a free account.