Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004

    Question Unanswered: cannot run delete query

    I have a query basically is to delete all records from a table that exists in another table.

    but when run, I got an error:
    Cannot delete from specified table.

    Please help, the table is like this:

    DELETE [Benefits].*
    FROM [Benefits ] INNER JOIN FirstRecordsTODelete ON ([Benefits without duplicates].[Sub Plan Cat Desc] = FirstRecordsTODelete.[FirstOfSub Plan Cat Desc]) AND ([Benefits without duplicates].[Sub Plan Desc] = FirstRecordsTODelete.[FirstOfSub Plan Desc]) AND ([Benefits without duplicates].[Building Desc] = FirstRecordsTODelete.[Building Desc]) AND ([Benefits without duplicates].[First Name] = FirstRecordsTODelete.[First Name]) AND ([Benefits without duplicates].[Last Name] = FirstRecordsTODelete.[Last Name]);


  2. #2
    Join Date
    Mar 2010
    Although your syntax is kind of confusing with the spaces in it(I usually steer clear of using spaces with field/table names)...I think you should probably try to use a where clause to single out the records you want to remove. In your code you only make use of the INNER JOIN.

    Also since you want to remove whole records, you could just use "DELETE FROM" instead of placing the Benefits.* in there..just a thought

  3. #3
    Join Date
    May 2005
    Nevada, USA
    Provided Answers: 6
    Also, I see 3 tables referenced where there should only be 1:

    [Benefits ]
    [Benefits without duplicates]

  4. #4
    Join Date
    Dec 2004
    Madison, WI
    Can you delete the record(s) manually from the table? If not, check relationship/constraints. If so, simplify your delete query and then add criteria (1st only adding 1 criteria, then another, then another until you get to a working delete query.)

    As Nkryption pointed out, make your life a LOT easier and don't name your fields with spaces (or other non-alpha characters such as: !@#$%^&*(). (and as a rule (even though you didn't do this), avoid using reserved names such as calling a field: Date or Name or Field, etc...etc...)

    Did you design this SQL statement using the query designer or did you just type in the syntax? I'd recommend FIRST designing the delete query using the query designer (a wonderful but sometimes not used tool for creating sql statements!) Then change it from design view to SQL view and copy/paste your sql statement into your code and correct any extra syntax such as quotes.
    Last edited by pkstormy; 04-07-10 at 18:40.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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