Charlotte Foust
cfoust at infostatsystems.com
Wed Jul 13 10:54:11 CDT 2005
Try adding the DISTINCTROW keyword to your query (or set the unique records property to true in the query properties) and see if that makes a difference. It often does. Charlotte Foust -----Original Message----- From: Kaup, Chester [mailto:Chester_Kaup at kindermorgan.com] Sent: Wednesday, July 13, 2005 8:46 AM To: Access Developers discussion and problem solving Subject: RE: [AccessD] Cannot delete from specified tables Datasheet view shows several hundred records to delete so something else is happening to prevent the records from being deleted. Thanks for the idea. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Lavsa, Rich Sent: Wednesday, July 13, 2005 10:40 AM To: Access Developers discussion and problem solving Subject: RE: [AccessD] Cannot delete from specified tables I've recently had this very error 3086. I found no suggestions to my solution however came to realize that there was no records being returned thus causing the error, or so I believed this to be my cause. Because I call this query from code, I captured this error and exit the sub without any issues. To find out if you are returning any records, open the query in design mode then simply click on the "Datasheet View", this will show you what your results will be, but will not execute the delete command. Anyone else have any similar experiences with a delete query. My delete query contained a sub-query and was not doing any joins. Rich -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mike & Doris Manning Sent: Wednesday, July 13, 2005 11:12 AM To: 'Access Developers discussion and problem solving' Subject: RE: [AccessD] Cannot delete from specified tables It is highly possible that with the joined tables, you've created a non-updatable query. Doris Manning Database Administrator Hargrove Inc. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Kaup, Chester Sent: Wednesday, July 13, 2005 11:07 AM To: Access Developers discussion and problem solving Subject: RE: [AccessD] Cannot delete from specified tables Thanks for the suggestion but I still get a message cannot delete from specified tables. I don't understand why since this a stand alone database on my local computer (C drive). -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mike & Doris Manning Sent: Wednesday, July 13, 2005 9:55 AM To: 'Access Developers discussion and problem solving' Subject: RE: [AccessD] Cannot delete from specified tables Try DELETE [tbl Allocations].* FROM [tbl Allocations] INNER JOIN [tbl Patterns to delete from Allocation table] ON ([tbl Allocations].TargetPID = [tbl Patterns to delete from Allocation table].TargetPID) AND ([tbl Allocations].SourceNAME = [tbl Patterns to delete from Allocation table].SourceNAME) WHERE ((([tbl Allocations].SourceNAME)=[tbl Patterns to delete from Allocation table]![SourceNAME]) AND (([tbl Allocations].TargetPID)=[tbl Patterns to delete from Allocation table]![TargetPID])); Doris Manning Database Administrator Hargrove Inc. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Kaup, Chester Sent: Wednesday, July 13, 2005 9:32 AM To: accessd at databaseadvisors.com Subject: [AccessD] Cannot delete from specified tables When I try to run the sql below I get the message cannot delete from specified tables. The tables are native to a stand alone access DB. I have tried many combinations but still no luck. DELETE [tbl Allocations].*, [tbl Allocations].SourceNAME, [tbl Allocations].TargetPID FROM [tbl Allocations] INNER JOIN [tbl Patterns to delete from Allocation table] ON ([tbl Allocations].TargetPID = [tbl Patterns to delete from Allocation table].TargetPID) AND ([tbl Allocations].SourceNAME = [tbl Patterns to delete from Allocation table].SourceNAME) WHERE ((([tbl Allocations].SourceNAME)=[tbl Patterns to delete from Allocation table]![SourceNAME]) AND (([tbl Allocations].TargetPID)=[tbl Patterns to delete from Allocation table]![TargetPID])); Chester Kaup Engineering Technician Kinder Morgan CO2 Company, LLP Office (432) 688-3797 FAX (432) 688-3799 -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com