Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Oct 2002
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delete Query (97 & 2000)

    Trying to create a delete query, using two tables. Table 1 - contains only phone numbers, Table 2, contains Name, Address and Phone Number. I do a Join, with only equal records. When I run the query, I get a Recordset is not updateable. How can I delete these records.

    tnks
    glenn

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Delete Query (97 & 2000)

    OK, are you using Access 97 or 2000? In 2000, you'll need to set the UniqueRecords property of the query to Yes or add the DISTINCTROW keyword in the SQL to make it updateable. In A97, that was the default.
    Charlotte

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

    Re: Delete Query (97 & 2000)

    Charlotte,

    UniqueRecords was true by default in Access 95 and earlier versions, but not in Access 97 - see ACC97: Changes with DISTINCTROW in Microsoft Access 97.

  4. #4
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Delete Query (97 & 2000)

    I had same problem in a current project - could not delete records using joined tables in delete query - I tried all sorts of approaches but did not think of using a subquery to select records for deletion. Your solution worked in excellent fashion. Thanx!

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

    Re: Delete Query (97 & 2000)

    <P ID="edit" class=small>(Edited by HansV on 23-Oct-02 13:07. Corrected SQL statement (there was a problem with Lounge tags))</P>I would do this with two queries. I hope you have some kind of ID field (primary key). In the following, I have assumed that you want to delete records from Table 1. If you want to delete from Table 2, adapt the instructions accordingly.

    (1) Create a select query based on Table 1 and Table 2. Have it display the ID of the records you want to delete. This query is not updateable. Let's call it qrySelectID
    (2) Create a delete query based on Table 1 that deletes those records whose ID is returned by the select query. Its SQL is

    DELETE * FROM <!t>[Table 1]
    WHERE ID In (SELECT ID FROM qrySelectID)

    You must replace Table 1 by the actual name of the table, and ID by the name of the primary key field.

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Delete Query (97 & 2000)

    I dodged using 95, but I've found that delete queries that worked in 97 require the addition of the DISTINCTROW keyword before they'll work in 2000. There is a help article in 2000 from the user interface that covers the reasons a query might not be updateable. It can be found by typing "When can I update data from a query" (without the quotes) into the Answer Wizard's "What would you like to do" box.
    Charlotte

  7. #7
    New Lounger
    Join Date
    Oct 2002
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Query (97 & 2000)

    It worked like a charm. Thank you for your help

Posting Permissions

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