Re: Foreign key with SET NULL: possible?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Allen Browne (AllenBrowne_at_SeeSig.Invalid)
Date: 02/23/05 

Date: Wed, 23 Feb 2005 20:37:24 +0800

ADOX is very buggy and inconsistent, but I have that code working in ADOX
2.8 on JET 4.0.8618.0.

I agree that it is not a good idea to use cascade-to-null, especially since
the interface cannot show this kind and so anyone who must maintain the
database in the future has no visual clue that this kind of relation is in
use. If it were propertly implemented it might be a useful concept though.

Examples:
- Delete a category and all the related records become uncategorized but you
don't lose them.

- Delete a client, and all their invoices become "cash" i.e. the f.k.
ClientID is null because the client is now unknown. 

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Jamie Collins" <jamiecollins@xsmail.com> wrote in message
news:1109161801.844372.225620@z14g2000cwz.googlegroups.com...
> Allen Browne wrote:
>> JET 4 does support cascade-to-null.
>
> Thanks Allen. I can get it to work for ON DELETE (both DDL and ADOX)
> but not for ON UPDATE i.e.
>
>  .UpdateRule = adRISetNull ' (ON UPDATE SET NULL)
>
> returns an error.
>
> On reflection, ON UPDATE SET NULL would not be very useful anyhow. Not
> that I'm thinking of using ON DELETE SET NULL any time soon, either
> <g>. I'm just trying to establish what Jet does support so that my app
> can support the same features.
>
> Thanks again,
> Jamie.

Relevant Pages

  • Re: Foreign key with SET NULL: possible?
    ... Allen Browne wrote: ... I can get it to work for ON DELETE (both DDL and ADOX) ... ON UPDATE SET NULL would not be very useful anyhow. ... I'm just trying to establish what Jet does support so that my app ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Question on conversion to ADP
    ... When passthrough queries will become read/write instead of read-only? ... When will we get support for transactions on bound forms? ... support and the ADP projects for working with SQL-Server. ... absence of any graphical tool to see how your queries are executed by JET ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Internet Sync with TSI
    ... Server replication as I don't have a good feeling about Jet (or ... Just looking at Vista, Jet replication seems to have been ... that replication support is going to die out. ...
    (microsoft.public.access.replication)
  • Re: Foreign key with SET NULL: possible?
    ... JET 4 does support cascade-to-null. ... DDE query statement, but if you can I imagine it would need to be executed ... > This leaves me wondering whether the syntax is supported at all. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: WHAT IS LINKING USED FOR IN ACCESS 2000.
    ... Since 2000 is out of support, no patches have been released, and ... it may effect all Jet 4.0 users on the PC - including A2000 ... A2000 and dot net applications in some circumstances. ... The situation regarding linking to Excel data is complicated, ...
    (microsoft.public.access.externaldata)