473,320 Members | 1,974 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Why can't I delete from an updatable query

Hi, Access officionados,

I'm new here, so please cut me slack/gently tell me off if I'm out of line
or in the wrong place.

OK, here's something that seems silly (and is also problematic to me).

I have a query which is updateable when viewed as a datasheet in Access (you
can insert, delete, update). Using queries, I can insert and update, but not
delete.

A delete query against this query gives the error 'Could not delete from
specified tables', even though I can do the same thing by viewing qb in
datasheet mode, selecting all rows and pressing Delete.

Here's pseudocode-SQL for a simplified version of the system with the
problem:
CREATE TABLE a (
a_id AutoNumber PRIMARY KEY,
a_data Text(50)
);
CREATE TABLE b (
a_id Number REFERENCES a (a_id) ON DELETE CASCADE,
b_id Number,
b_data Text(50),
PRIMARY KEY (a_id, b_id)
);
CREATE Query q AS
SELECT a.a_id AS a_a_id, a_data, b.a_id as a_id, b_id, b_data
FROM a INNER JOIN b ON a.a_id = b.a_id;

Now, you can delete from q in datasheet mode, but not with something like:
DELETE FROM q;

My practical questions are these: How can I programmatically delete from q?
Or do I have to generate a page of SQL in all my scripts to do what q is
meant to do?

But I'm also interested in other people's reactions to this bug/feature, and
even if anyone wants to try justifying the behaviour...

Cheers,
Dave
Nov 13 '05 #1
2 2547
On Mon, 09 Aug 2004 09:30:18 GMT, "Dave Burt" <bu*****@hotmail.com>
wrote:
Hi.
Yes, interesting. It seems the updateable property is being set to
true inconsistently with how the execute works.
However you shouldn't have a problem. When you delete manually from
the query datasheet it is actually only deleting from table b, which
you can with a query like

DELETE b.*
FROM a INNER JOIN b ON a.a_id = b.a_id
WHERE (((a.a_data)="aaa1"));

Or you can delete from table a if you want the cascaded deletes in
table b.

Excuse me for top-posting
David
Hi, Access officionados,

I'm new here, so please cut me slack/gently tell me off if I'm out of line
or in the wrong place.

OK, here's something that seems silly (and is also problematic to me).

I have a query which is updateable when viewed as a datasheet in Access (you
can insert, delete, update). Using queries, I can insert and update, but not
delete.

A delete query against this query gives the error 'Could not delete from
specified tables', even though I can do the same thing by viewing qb in
datasheet mode, selecting all rows and pressing Delete.

Here's pseudocode-SQL for a simplified version of the system with the
problem:
CREATE TABLE a (
a_id AutoNumber PRIMARY KEY,
a_data Text(50)
);
CREATE TABLE b (
a_id Number REFERENCES a (a_id) ON DELETE CASCADE,
b_id Number,
b_data Text(50),
PRIMARY KEY (a_id, b_id)
);
CREATE Query q AS
SELECT a.a_id AS a_a_id, a_data, b.a_id as a_id, b_id, b_data
FROM a INNER JOIN b ON a.a_id = b.a_id;

Now, you can delete from q in datasheet mode, but not with something like:
DELETE FROM q;

My practical questions are these: How can I programmatically delete from q?
Or do I have to generate a page of SQL in all my scripts to do what q is
meant to do?

But I'm also interested in other people's reactions to this bug/feature, and
even if anyone wants to try justifying the behaviour...

Cheers,
Dave


Nov 13 '05 #2
Thanks for your thoughts.

What do you mean by "the updateable property being set to true
inconsistently with how the execute works"?

Just a minor correction: that SQL doesn't quite work - you need DISTINCTROW:

DELETE DISTINCTROW b.*
FROM a INNER JOIN b ON a.a_id = b.a_id
WHERE (((a.a_data)="aaa1"));

What is the point of DISTINCTROW anyway? It's certainly not defined in SQL
standards, and it seems to me just to complicate things. Anyway...

And what performance penalty will I pay for the following alternative?

DELETE FROM b
WHERE EXISTS
(SELECT *
FROM q
WHERE (conditions)
AND b.a_id = q.a_id
AND b.b_id = q.b_id);

I prefer this because in my particular application, q calculates a number of
columns that I'd like to build a general WHERE clause against, and use that
in multiple ways, selecting and updating as well as deleting from q.

I guess the question remains, too, what's wrong with:

DELETE DISTINCTROW * FROM q;

?

"David Schofield" <d.***************@blueyonder.co.uk> wrote:
Hi.
Yes, interesting. It seems the updateable property is being set to
true inconsistently with how the execute works.
However you shouldn't have a problem. When you delete manually from
the query datasheet it is actually only deleting from table b, which
you can with a query like

DELETE b.*
FROM a INNER JOIN b ON a.a_id = b.a_id
WHERE (((a.a_data)="aaa1"));

Or you can delete from table a if you want the cascaded deletes in
table b.

Excuse me for top-posting
David

On Mon, 09 Aug 2004 09:30:18 GMT, "Dave Burt" <bu*****@hotmail.com>
wrote:
... query which is updateable when viewed as a datasheet in Access (you
can insert, delete, update). Using queries, I can insert and update, but notdelete.

A delete query against this query gives the error 'Could not delete from
specified tables', even though I can do the same thing by viewing qb in
datasheet mode, selecting all rows and pressing Delete.

Here's pseudocode-SQL for a simplified version of the system with the
problem:
CREATE TABLE a (
a_id AutoNumber PRIMARY KEY,
a_data Text(50)
);
CREATE TABLE b (
a_id Number REFERENCES a (a_id) ON DELETE CASCADE,
b_id Number,
b_data Text(50),
PRIMARY KEY (a_id, b_id)
);
CREATE Query q AS
SELECT a.a_id AS a_a_id, a_data, b.a_id as a_id, b_id, b_data
FROM a INNER JOIN b ON a.a_id = b.a_id;

Now, you can delete from q in datasheet mode, but not with something like: DELETE FROM q;

<snip>
Nov 13 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Mario Zoratti | last post by:
Hello, everybody, Please be so kind to explain how it is possible to make a query pass through in Access 97 vs SQL server updatable. The query is "SELECT CDC. * FROM CDC": it is very simple and...
2
by: Johnny M | last post by:
I have been using Access since office 4.3. I have just upgraded to Office 2003 and am having issues with creating updatable queries. If I link one table to a selection query based on the same...
2
by: Apple | last post by:
I had create a query with one table and one query, and then create form with that query using wizard, but I can't append or edit data in that form & query. Please help!
6
by: GaryDave | last post by:
My school registration database has not been quite right after a recent compact and repair (done while I was away). Though most of the many forms and subforms are working normally, one form in...
4
by: adolph | last post by:
I created 2 tables, each with an autonumber primary key. Fields are: ID (autonumber Primary key) Number (single) Color (Text) FName (text)in one table and LName (text)in the other What I'm...
3
by: jallegue | last post by:
I am working with MS-Access 2002. The two tables that I am working with are: dbo_IDX_FRS_account_bal_by_month ==> this is a linked table to SQL == local table The query that is executed is...
0
by: CC | last post by:
Dear all, Following Many-to-Many table structure is part of my database ----------------------------------- TbGeneral (dossier information) PK: dsr_id TbDossierSample PK: dsr_sample_id
5
by: MLH | last post by:
SQL String #1 DELETE * FROM tblPreliminaryVINs Where PVIN In (Select SerialNum From tblVehicleJobs); SQL String #2 DELETE tblPreliminaryVINs.* FROM tblPreliminaryVINs INNER JOIN tblVehicleJobs...
8
by: Michel Esber | last post by:
Hello, Env: DB2 V8 LUW FP16 running Linux create Table X (machine_id varchar(24) not null, ctime timestamp not null); create index iFoo on X (MACHINE_ID, CTIME) allow reverse scans; alter...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.