• Delete Query

    Author
    Topic
    #355161

    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?

    Viewing 0 reply threads
    Author
    Replies
    • #523346

      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.

      • #523348

        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?

        • #523366

          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. blush 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?

    Viewing 0 reply threads
    Reply To: Delete Query

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: