View Single Post
  #33  
Old August 12th, 2006, 05:17 AM posted to microsoft.public.access.tablesdbdesign
Graham Mandeno
external usenet poster
 
Posts: 593
Default How to enforce subtypes/supertypes in Access 2000?

Hi Bob

Answers inline...

By the way, I think I found a slight bug in the Before_Update code you
supplied. What I have found is if I try to change the ContactType the
custom error message popups up as expected. If I then opt to delete
the existing record, everything is fine. The record is deleted and I
can still navigate the records using the record selector at the foot of
the main (Clients) form - again everything works as expected. But if I
opt to cancel the update instead, I end up getting the standard Access
message about not being able to change or delete the record (ie the one
I was getting before you came to the rescue :-) ).


That's annoying, isn't it? I reckon that this is a bug in Access. It
recognises that you have changed one of the fields in the relation but fails
to recognise that you have undone that change.

I have discovered a workaround, and that is to set the cascading updates
property on the relations between Contacts/Individuals and
Contacts/Organisations. That way, Access doesn't whinge when you change the
master side of the relationship - it just updates the foreign key field to
the same value as it had before. Of course, you can't really change the
value of Contacts.ContactTypeID because the constraint in the related table
allows only one valid value.

So... make this change and remove the extra Undo.

By the way, the correct form to use if you want to undo the entire form is:
Me.Undo

Me is already a form object, so Me is equivalent to Me.Form.

The reason you don't want to undo the entire form is that you might have
changed a whole lot of other fields in your Contacts record before
inadvertently changing the contact type. You are then stuck between the
rock and the hard place - either delete the related record and lose all the
indiv/org data, or undo the current record and lose all your changes.

Getting back to the ADO/DAO, my long term aim would be ultimately to
port my little Access program to VB.Net - purely because I'm a sucker
for punishment :-)


You certainly are :-)

this will be a complete rewrite. there is no easy way to convert an Access
app to .Net (or VB6 for that matter).

If I code everything using DAO now, will I need to re-code everything
into ADO later, or does VB.Net not care which one you reference?


I believe you can use the DAO object model from .Net - no reason why not.

On a different tangent, if I port everything over to VB.Net, do you
know if I will need extra licences or if I have to upgrade to a
developer edition of some sort before I can distribute my VB.Net
program with an .mdb file? Will my VB.Net or existing ms office
licences cover this sort of thing (ie distribution)? And what's the
deal with runtime files? Will they be packaged with my .mdb file if I
incorporate that into a VB.Net program?


No, you don't need any licences to open an MDB using .Net or VB6. All you
need is DAO360.DLL, but I'm pretty sure this comes with Windows anyway.

If you buy Visual Studio Tools for Office (VSTO) you get with it the Access
Developer Extensions (ADE) which allows you unlimited licence to distribute
the runtime version of Access, so you can install and run your Access app on
a computer that doesn't have Access (or even Office) installed.

Thanks for letting me pick your brain.


My pleasure :-)
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand