Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Aug 2002
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delete Query (2000)

    <img src=/S/cool.gif border=0 alt=cool width=15 height=15> Thanks to those who suggested the Append/Delete queries for my last question. I have the Append working fine, but I'm having problems with the Delete. I'm using a constant table with the beginning and ending dates specified to find the records in question. When I click on the View button the records I'm searching for do appear. But when I run the query I receive "Could not delete from specified tables." This indicates the security may be set incorrectly. I verified the ID I'm using is part of the Users group as well as the Admins group that can do everything. I also opened the table and manually deleted a row to verify the security and it worked this way. Any thoughts as to what may be hanging this up?

    Thanks.

  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 (2000)

    In design view for your delete query, right-click in the gray area in the top pane where your table(s) is displayed, and choose Properties. In the Query Properties box, check the value set for "Unique Records". If it's set to "No", try changing it to "Yes" and run the query.
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  3. #3
    New Lounger
    Join Date
    Aug 2002
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Query (2000)

    That did not work. I just figured this out. My query consists of 2 table with nothing joining them. The first table is the current data and the second table is the constant table with the date range. If I remove the constant table Access prompts me with the starting and ending date as specified in my criteria field. If I enter the dates via Access's prompts the records get deleted. It appears to be something with the constant table being listed as one of the tables in the query.

    I listed the constant table in my Append query to eliminate the Access prompts and it works as expected there.

    Thanks,
    Mike

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

    Re: Delete Query (2000)

    Adding the contstant table to the query makes it non-updatable. A delete query must be updatable.

    Try removing the constant table from the query design and changing the criteria to somthing like the following:

    Between DLookUp("StartDate","tblConstant") And DLookUp("EndDate","tblConstant")

    where tblConstant is the name of the constant table, and StartDate and EndDate the name of the fields in this table.

  5. #5
    New Lounger
    Join Date
    Aug 2002
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Query (2000)

    <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30> Thanks! Though you need to put brackets around the field names. [startdate] Now on to the next step!

Posting Permissions

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