Results 1 to 4 of 4

Thread: Delete Query

  1. #1
    Lounger
    Join Date
    Apr 2001
    Location
    California, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delete Query

    I'm trying to run the following query:
    DELETE Assessor.*, [Assessor].[Parnum]
    FROM Assessor INNER JOIN NewMonth ON [Assessor].[Parnum]=[NewMonth].[Parnum]
    WHERE (([assessor].[parnum]=[newmonth].[parnum]));
    This worked previously, but now I'm getting an error message: "Could not delete tables." The Jet error message is 3086. It says either the table is set to Read Only or I don't have permissions. Both tables are on my hard drive. I created both of them. I'm logged in as Admin in NT. I installed the Access Workgroup Administrator some time ago, tinkering with it, but this was before the last time the query ran successfully. The table I'm trying to delete records from is the "master" and contains 36,000+ records consisting of property ownership information. I'm trying to delete the records in this table that match those in the New table. I want to replace the Old records with the New records. After the delete, I run an append.

    Any ideas what's going on and how I can correct this?

  2. #2
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Query

    Check out the SQL statement for the query, and see if the statement begins:

    DELETE DISTINCTROW

    If it doesn't, either make the edit directly in the SQL, or go back to design view for the query, right-click in the grey area above the grid, and choose Properties. Change the Unique Records property to Yes. Of course, make a backup on the table before you actually run the delete, and post back whether this resolves the issue.
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  3. #3
    Lounger
    Join Date
    Apr 2001
    Location
    California, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Query

    That did it! Somehow I'm not sure how this changed, but thanks. Could you briefly explain what the effective difference is in the two versions: with DistinctRow and without?

  4. #4
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Query

    I'm sorry and a touch embarassed to say that I'm not exactly sure in a relational sense why that's a necessary step. <img src=/S/blush.gif border=0 alt=blush width=15 height=15> That said, I've seen it only be a necessary step when the delete query involves a join between multiple tables.

    Anybody else able to better illuminate the situation and give both of us a schooling in relational theory?
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

Posting Permissions

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