Delete query problem

N

notDave

I've been doing simple joins and turning them into Delete queries for a long
while, but after taking a few months off and trying the same thing they've
stopped working. While researching the problem I've found the "distinctrow"
issue described below. My question is this: When did this become necessary?

I've never had to do it before. Did something in my defaults change? Was
this an upgrade to Access that did it? Something had to change, and if that
was me then I may need to check into a hospital somewhere because I'm not
right in the head.

Here's what I found that solved the issue I was having;

"Try adding the word DISTINCTROW right after the DELETE and before the table
name from which we delete (over the join).

Just for my continued education... If you are using Design View and not SQL
to write the query where would you "tell" it to add the DISTINCTROW command?

Nevermind the previous question... I see you have to choose "Yes" for Unique
Records in the properties box."

Thanks in advance for any help available...

~notDave
 
M

Michel Walsh

Hi,

DISTINCTROW lightly changed between 95 and 97, I think. The query designer
has also changed in writing it "by default", or "not" (in recent versions),
for a DELETE query over a join.

A DELETE query based on a join does not really use the DISTINCTROW in
itself, but for its side effect to make "tracking" of the involved records
made easier. While it is not required to use DISTINCTROW in a DELETE query
that does not involve the table through a join, Jet 4.0 (and probably 3.51
too) requires the DISTINCTROW.


An alternative is to use an IN clause in the WHERE clause, rather than to
use a join.



Hoping it may help,
Vanderghast, Access MVP
 
N

notDave

Thanks very much for the info... and for the tip about recent versions.
Something definately changed because all of our other, older, delete queries
are all set to Yes on the Unique Records... and I know we didn't have to do
that ourselves. That step just snuck up on us I guess! Thanks again.

~notDave
 
M

Michel Walsh

Hi,


for comprehension, should add:

A DELETE query based on a join does not really use the DISTINCTROW in
itself, but for its side effect to make "tracking" of the involved records
made easier. While it is not required to use DISTINCTROW in a DELETE query
that does not involve the table through a join. Jet 4.0 (and probably 3.51
too) requires the DISTINCTROW ***when a join is involved***.
 
Top