Wrox Programmer Forums
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old April 13th, 2005, 02:01 PM
Registered User
Join Date: Apr 2005
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Delete Query not working


I having trouble getting a delete query to work. I'm trying to delete my duplicate entries. My query returns the correct data to delete when I view it in datasheet view. However, when I run the query I get the dreaded "Could not delete from specified tables." Unique records in properties is set to yes, just like it should be. Can't figure out why the query won't run.

Here's the SQL language that Access created:
DELETE DISTINCTROW [Main Table].*, [Main Table].County, [Main Table].[Beg Book Number], [Main Table].Suffix, [Main Table].[Received Date]
FROM [Main Table], Duplicates_not_Shown AS 1
WHERE ((([Main Table].County)=[1].[county]) AND (([Main Table].[Beg Book Number])=[1].[beg book number]) AND (([Main Table].Suffix)=[1].[suffix]) AND (([Main Table].[Received Date])<>[1].[maxofreceived date]));

Any help would be great.


Old April 14th, 2005, 06:29 AM
Friend of Wrox
Join Date: Nov 2004
Posts: 248
Thanks: 0
Thanked 1 Time in 1 Post

There are several problems...

One is that you are deleting more than "*" in your field list. Another is that you can't use DISTINCTROW in the DELETE statement. And finally, you are not joining to Duplicates_not_shown, so the delete is trying to delete from both [Main Table] and Duplicates_not_shown.

If you're using the query builder, you can get rid of the extra fields by removing the check from the "Show:" row of the superfluous fields. But that won't solve the problem.

I think what you'll have to do is use a SELECT query that will make a list of IDs that you want to keep in your [Main Table]. Then create another query that has a delete statement like this..

DELETE * From [Main Table] WHERE ID Not In (Select ID From [the query that has the IDs you want to keep])

For example, if Duplicates_not_Shown is actually a list of the [Main Table] records you want to keep, just change that query to select only the ID feild. Then the delete query is...

DELETE * From [Main Table] WHERE ID Not In (Select ID from Duplicates_not_Shown)

Hopefully you do have an ID field (primary key) defined in [Main Table] that is unique for each record in [Main Table]. If not, you have a tough row to hoe. But you can still do it by Joining [Main Table] with a list of the [Main Table] records that you DO want to delete.

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group

Similar Threads
Thread Thread Starter Forum Replies Last Post
php/mysql delete button and delete query dungey PHP Databases 17 April 11th, 2009 12:24 PM
Delete Button not working in Wrox CMS VB Sojan80 BOOK: ASP.NET 2.0 Instant Results ISBN: 978-0-471-74951-6 3 June 16th, 2007 03:23 AM
delete is not working thas123 ADO.NET 1 January 6th, 2006 09:41 AM
Delete query not working Mitch PHP Databases 4 January 4th, 2006 08:00 AM
Edit/Delete ButtonColumn not working planoie ASP.NET 1.0 and 1.1 Professional 9 August 30th, 2004 07:39 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.