[AccessD] Cannot delete from specified tables

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



More information about the AccessD mailing list