Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Jan 2008
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Could not delete from specified table (Access 2007)

    I am trying to delete some values from a table that were added in error. The table contains two fields. ContactID and Product ID. Together, they form a composite key to the table. This is a junction table used to connect a Contact (in tblContacts) with a Product (in tblProducts) that they are responsible for. Unfortunately, during the data load, some contacts were assigned products in error. When I try to run a delete query, I receive the message "Could not delete from specified table". I assume it's because of the composite key, but there are 163 records that need deleting, and I'd really rather not do it manually!

    To determine which records need to be deleted, I am linking in the original imported data table and using a specific criteria (productID is null) in that table to weed out the invalid data. Not sure if that helps or not.

    I'm sure I'm missing something simple. What do I need to do to delete these records?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Could not delete from specified table (Access 2007)

    Could you post the SQL for the delete query that doesn't work?

  3. #3
    Star Lounger
    Join Date
    Jan 2008
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Could not delete from specified table (Access

    The SQL generated by the query is:
    DELETE tblImportedData.Casters, tblAssignProducts.ProductID,tblAssignProducts.*
    FROM tblProducts INNER JOIN ((tblImportedData INNER JOIN qryContacts ON tblImportedData.Name = qryContacts.ImportName) INNER JOIN tblAssignProducts ON qryContacts.ID =
    tblAssignProducts.ContactID) ON tblProducts.ID = tblAssignProducts.ProductID
    WHERE (((tblImportedData.Casters) Is Null) AND ((tblAssignProducts.ProductID)=6));

    I know the first line should just be DELETE tblAssignProducts.*, but I don't know where to go after that.

    I had to introduce a query because there is no ContactID in the original data, so I'm linking the tables by Lastname. Unfortunately, in the original data, Name was a field that contained both first name AND last name, and we split those up when loading the data into Access. The data came from Excel, and those guys seem to have no rules! <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Could not delete from specified table (Access

    If you open the query in design view, can you change the Delete option for the Casters column from From to Where, or does Access change it back to From automatically?

  5. #5
    Star Lounger
    Join Date
    Jan 2008
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Could not delete from specified table (Access

    It is already set to Where.

    The query has three columns in design mode.
    1) Casters (from tblImportedData) which is set to Delete WHERE with a condition of IS NULL
    2) ProductID (from tblAssignProducts) which is set to Delete WHERE with a condition of 6
    3) tblAssignProducts.* which is set to Delete FROM with no conditions

    That's what generates the SQL code I sent.

    The tables I'm using, all with Inner Joins, are tblImportedData, qryContacts, tblAssignProducts and tblProducts. They are joined by, respectively, Name to ImportName, ID to ContactID and ProductID to ID.

    Hope that helps.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Could not delete from specified table (Access

    Does this work?

    DELETE ContactID, ProductID
    FROM tblAssignProducts
    WHERE ContactID In (SELECT qryContacts.ID FROM qryContacts INNER JOIN tblImportedData ON qryContacts.ImportName = tblImportedData.Name WHERE tblImportedData.Casters Is Null) AND ProductID=6

    Please test on a copy of your database first!

  7. #7
    Star Lounger
    Join Date
    Jan 2008
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Could not delete from specified table (Access

    YES!

    That's perfect! <img src=/S/clever.gif border=0 alt=clever width=15 height=15>

    It's a good thing, too, since I've discovered in the interim that there are a lot more contacts with incorrectly-assigned products that will need to be corrected than I originally thought <img src=/S/bummer.gif border=0 alt=bummer width=15 height=15> . This certainly beats doing them all manually, and it will be accurate as well.

    Thanks again for coming through for me!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •