Cascading Delete...#Delete found in table...what the heck is going on!

[Andre Laplume via AccessMonster.com]

I am converting a Crystal Report which I believe looks at SQL server data
(for which I am only secured to read) to a similar MsAccess routine. There
is a root table linked to numerous other tables with outer joins. We want
to find all entries in the root table that have no corresponding entries in
the all the other tables. Let me cite a simpler example:


TableA links to TableB on 'key'. Use an outer join because you want all
records in TableA for which no entry exists in TableB. So you pull into
the query TableA.key with a condition TableB.key Is Null.


Here's the problem. When I run this in Crystal Reports I get, say, 50
records. When I run it in MsAccess I get, say, 52 records. When I look at
the values of TableB.key in MsAccess (for the extra two records) I do NOT
see a blank/null (which Crystal appears to see) but rather: #Deleted. So
somehow the Crystal software inteprests whatever is actually in the table
as Null (which is desirable) where as Access does not. Problem is I can
not figure out to qualify on #Delete....I can not use: Is Not Null, that
would bring in other records I do not want......I am guessing the powers
that be are tagging deleted records and holding onto them.....again out of
my control...EVER RUN INTO THIS, IS THERE A WAY TO ELIMINATE THOSE RECORDS
TAGGED AS DELETED?

 

[Dave Patrick]

These may help.

http://support.microsoft.com/default.aspx?scid=kb;en-us;257487
http://support.microsoft.com/default.aspx?scid=kb;en-us;269580
http://support.microsoft.com/default.aspx?scid=kb;en-us;269444
http://support.microsoft.com/default.aspx?scid=kb;en-us;257487

--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
|I am converting a Crystal Report which I believe looks at SQL server data
| (for which I am only secured to read) to a similar MsAccess routine.
There
| is a root table linked to numerous other tables with outer joins. We want
| to find all entries in the root table that have no corresponding entries
in
| the all the other tables. Let me cite a simpler example:
|
|
| TableA links to TableB on 'key'. Use an outer join because you want all
| records in TableA for which no entry exists in TableB. So you pull into
| the query TableA.key with a condition TableB.key Is Null.
|
|
| Here's the problem. When I run this in Crystal Reports I get, say, 50
| records. When I run it in MsAccess I get, say, 52 records. When I look
at
| the values of TableB.key in MsAccess (for the extra two records) I do NOT
| see a blank/null (which Crystal appears to see) but rather: #Deleted.
So
| somehow the Crystal software inteprests whatever is actually in the table
| as Null (which is desirable) where as Access does not. Problem is I can
| not figure out to qualify on #Delete....I can not use: Is Not Null, that
| would bring in other records I do not want......I am guessing the powers
| that be are tagging deleted records and holding onto them.....again out of
| my control...EVER RUN INTO THIS, IS THERE A WAY TO ELIMINATE THOSE RECORDS
| TAGGED AS DELETED?
|
| --
|

 

[Andre Laplume via AccessMonster.com]

Thanks, so I am at IT's mercy to get the server up to date. I assume there
is no way to exclude such records from a query....?

Thanks!

 

[Dave Patrick]

Not that I'm aware of.

--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| Thanks, so I am at IT's mercy to get the server up to date. I assume
there
| is no way to exclude such records from a query....?
|
| Thanks!
|
| --
|

 

[Guest]

Hi Andre,

Most of the fixes in Dave's message actually seem to be things that you
would do on your PC, such as upgrading Jet or the ODBC driver, so you may
want to try those to see if they fix the problem.

If not, or if you want to try this first anyway, you may want to try a pass
thru query. I would assume that the server will see the database properly,
so a pass thru query will probably execute properly.

HTH, Ted Allen

 

[Andre Laplume via AccessMonster.com]

The pass thru works but I want the Access one for ease of use. I'll try to
figure out whta I need to update on my PC....however I guess many of us
will need to do this so that throws the ball back to IT....and a long wait.

 

[Guest]

Yeah, I know the frustration, I have to deal with the same thing here. But,
if it makes you feel any better, I have found that it is very important to
have everyone running the same version of Jet (actually service pack number)
when working in a network environment in Access, so you may see other side
benefits if some of your users are currently running different service packs.