Delete query problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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
 
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***.
 
Back
Top