Lavsa, Rich
Rich_Lavsa at pghcorning.com
Wed Jul 13 12:34:03 CDT 2005
I tried the DistinctRow and checked to see if it was updateable. I did do testing on a statement that returns no records to delete and yes I agree that it simply exits without error. However I noticed that in those cases if you did a view datasheet you saw a single blank record, and in the case where I get an error I get no record at all.. Just the titles of the fields. After typing the above comments I thought of something... The subquery is actually querying a stored procedure, which is not updatable. So I simulated that query using Linked Tables to SQL Server which seemed to do the trick. So from this I gather that the subquery must be updateable as well, of which I do not understand as it is reading order no's from a different database just to be used as criteria for the delete query. If it is only being used as criteria, why would it need to be updateable as well?? I open to suggestions. Rich -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mcgillivray, Don [ITS] Sent: Wednesday, July 13, 2005 11:56 AM To: Access Developers discussion and problem solving Subject: RE: [AccessD] Cannot delete from specified tables A delete query against an empty recordset simply exits without error. "DELETE * FROM tblMyTable WHERE tblMyTable.MyField = 2;" simply runs and exits if there are no MyFields in tblMyTable with values equal to 2. No error. I'd say that Doris's suspicion that the recordset is non-updateable is the more likely cause of Chester's problem. You may want to re-visit your code to ensure that you're not missing some other circumstance that is causing your delete queries to fail. Don -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Lavsa, Rich Sent: Wednesday, July 13, 2005 8: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