Is this query updatable?

[Fred Boer]

I can select and delete a record on my form, and the record appears to have
been deleted, that is to say, it no longer is displayed by the form.
However, if I go back to the underlying tables, the record is still there! I
suspect I might have a non-updateable query. After a long, frustrating
battle with Access Help (Can anyone tell me "updatable" doesn't find
information on the subject?!...arrrghh!). I *think* I have found some
information, however, before I dive into all of that information, I'd
appreciate it if someone could tell me if I am heading in the right
direction. Here's the SQL:

SELECT Tbl_Library.*, [tbl_Library].[dewey] & " / " &
UCase(Left([AuthorLastName],3)) AS CallNumber
FROM Tbl_Library INNER JOIN (Tbl_Author INNER JOIN Tbl_BookAuthor ON
Tbl_Author.Author_ID = Tbl_BookAuthor.Author_ID) ON Tbl_Library.Book_ID =
Tbl_BookAuthor.Book_ID
WHERE (((Tbl_BookAuthor.PrimaryAuthor)=-1))
ORDER BY Tbl_Library.Book_ID;

If it *is* a problem with updateability, some suggestion about how to work
around this would also be greatly appreciated!

Many thanks!
Fred Boer

 

[Van T. Dinh]

I think the problem is that when you try to delete a row, the row is
actually a combination of 3 Records from 3 constituent Tables so which
Record (from which Table) do you want Access / JET to delete?

I seen this behaviour before and admittedly, Access/JET should give you some
indication but it doesn't.

 

[Michel Walsh]

Hi,

The query "seems" updateable. Can you execute it in the query designer?
there, in data view, can you delete the record?

Note that if you have data referential integrity without cascade delete,
THAT may forbid the deletion. As example, if you force the relation for a
BookAuthor.BookID to be in Library.BookID, but did not cascade the
deletion, then, you can't delete a record in Library that would break that
relational condition, leaving an orphan, a record in BookAuthor with a
BookID not in Library.BookID anymore.

That can be something else too,... such as transaction, where you delete in
the transaction, but then, roll it back instead of committing it. Not having
an updateable query is just the most typical "problem" in the scenario you
described, not the only one.



Hoping it may help,
Vanderghast, Access MVP

 

[Fred Boer]

Dear Van:

Thank you for your response! The tables are joined with Referential
Integrity and Cascade Delete, so I *thought* that would be all I needed to
do, but your answer has made me think about what is actually happening.

I was under the mistaken belief that I was deleting a record from the "main"
table (in which case the cascade delete would take care of the "secondary"
table deletion). When you say "delete a row" it makes the problem more
obviously one where JET is trying to delete two things at once. BTW, in
looking more closely at the tables, it appears that JET actually *does* do a
deletion. It deletes the record in Tbl_BookAuthor, but *not* the record in
Tbl_Library.

However, this leaves me with the problem of how to manage a delete in this
situation. Any suggestions? Force the users to use a custom delete button
that uses the current Book_ID to identify the record? Is there some way to
make a query like this updateable?

Thanks again!
Fred

I think the problem is that when you try to delete a row, the row is
actually a combination of 3 Records from 3 constituent Tables so which
Record (from which Table) do you want Access / JET to delete?

I seen this behaviour before and admittedly, Access/JET should give you some
indication but it doesn't.

--
HTH
Van T. Dinh
MVP (Access)

I can select and delete a record on my form, and the record appears to have
been deleted, that is to say, it no longer is displayed by the form.
However, if I go back to the underlying tables, the record is still

there!

I
suspect I might have a non-updateable query. After a long, frustrating
battle with Access Help (Can anyone tell me "updatable" doesn't find
information on the subject?!...arrrghh!). I *think* I have found some
information, however, before I dive into all of that information, I'd
appreciate it if someone could tell me if I am heading in the right
direction. Here's the SQL:

SELECT Tbl_Library.*, [tbl_Library].[dewey] & " / " &
UCase(Left([AuthorLastName],3)) AS CallNumber
FROM Tbl_Library INNER JOIN (Tbl_Author INNER JOIN Tbl_BookAuthor ON
Tbl_Author.Author_ID = Tbl_BookAuthor.Author_ID) ON Tbl_Library.Book_ID =
Tbl_BookAuthor.Book_ID
WHERE (((Tbl_BookAuthor.PrimaryAuthor)=-1))
ORDER BY Tbl_Library.Book_ID;

If it *is* a problem with updateability, some suggestion about how to work
around this would also be greatly appreciated!

Many thanks!
Fred Boer

 

[Fred Boer]

Dear Michel:

Thanks for helping! I tried executing the query in the query designer and it
didn't do the deletion there, either. Actually, to be more precise, it
appears to delete the record in Tbl_BookAuthor, but not the record in
Tbl_Library.

I did have cascade delete set, so I don't think that was the issue; also,
I'm not using transactions (well, <chuckle> I don't *think* I'm using
transactions...). If it *is* a non-updateable query, on the other hand, is
there a good way to work around it?

Thanks for the suggestions...


Fred

Hi,

The query "seems" updateable. Can you execute it in the query designer?
there, in data view, can you delete the record?

Note that if you have data referential integrity without cascade delete,
THAT may forbid the deletion. As example, if you force the relation for a
BookAuthor.BookID to be in Library.BookID, but did not cascade the
deletion, then, you can't delete a record in Library that would break that
relational condition, leaving an orphan, a record in BookAuthor with a
BookID not in Library.BookID anymore.

That can be something else too,... such as transaction, where you delete in
the transaction, but then, roll it back instead of committing it. Not having
an updateable query is just the most typical "problem" in the scenario you
described, not the only one.



Hoping it may help,
Vanderghast, Access MVP

I can select and delete a record on my form, and the record appears to have
been deleted, that is to say, it no longer is displayed by the form.
However, if I go back to the underlying tables, the record is still

there!

I
suspect I might have a non-updateable query. After a long, frustrating
battle with Access Help (Can anyone tell me "updatable" doesn't find
information on the subject?!...arrrghh!). I *think* I have found some
information, however, before I dive into all of that information, I'd
appreciate it if someone could tell me if I am heading in the right
direction. Here's the SQL:

SELECT Tbl_Library.*, [tbl_Library].[dewey] & " / " &
UCase(Left([AuthorLastName],3)) AS CallNumber
FROM Tbl_Library INNER JOIN (Tbl_Author INNER JOIN Tbl_BookAuthor ON
Tbl_Author.Author_ID = Tbl_BookAuthor.Author_ID) ON Tbl_Library.Book_ID =
Tbl_BookAuthor.Book_ID
WHERE (((Tbl_BookAuthor.PrimaryAuthor)=-1))
ORDER BY Tbl_Library.Book_ID;

If it *is* a problem with updateability, some suggestion about how to work
around this would also be greatly appreciated!

Many thanks!
Fred Boer

 

[John Vinson]

I was under the mistaken belief that I was deleting a record from the "main"
table (in which case the cascade delete would take care of the "secondary"
table deletion). When you say "delete a row" it makes the problem more
obviously one where JET is trying to delete two things at once. BTW, in
looking more closely at the tables, it appears that JET actually *does* do a
deletion. It deletes the record in Tbl_BookAuthor, but *not* the record in
Tbl_Library.

I believe that when you have a one-to-many (-to-many) query as the
recordsource of a Form, that a Delete query deletes from the "manyest"
side table.

However, this leaves me with the problem of how to manage a delete in this
situation. Any suggestions? Force the users to use a custom delete button
that uses the current Book_ID to identify the record? Is there some way to
make a query like this updateable?

Normally one would not base a Form on a query of this type! It's a lot
simpler if you base the Form on tbl_Library directly, with a Subform
based on tbl_BookAuthor (or possibly on tbl_BookAuthor joined to
tbl_Authors). As it is, you'll see the same book repeatedly as you
browse through the records in the form, since there will be one group
record for each author.

 

[Fred Boer]

Dear John:

Thanks! I have redone the form and put in a subform. Simple and effective. I
don't really know *why* I didn't think of using a subform: perhaps it was
because I was only adding a single new control to an existing form. More
likely it was that, given a choice between a complicated, misguided approach
and a simple, straightforward approach, I will inevitably choose the former!
<g>

Many thanks, also, to Van and Michel!

Fred