WARNING: Access 2007 MVFs vs Relationships Diagram

[Craig Alexander Morrison]

Warning to Access Developers about ACE.

DON'T use multivalued fields (MVF) they are worse than LookUp fields and
SubDatasheets, in fact they (MVFs) are a logical progression in the dumbing
down that started with these abominations.

Having seen and evaluated Access 2007 for a few months now I think this is a
terrible product for developers. If you must use it use Jet 4 or SQL Server
2000 or IBM DB2* as the backend, depending upon your requirements.

However if you are lumbered with fixing a total snafu from a "power-user"
please bear in mind the failure of the Access team to depict the MVF
(laughingly referred to as "complex data") from the ACE database engine in
the Relationships Window as the underlying three table structure that it
really is.

They say this will (as opposed to may) be available in a "future" version of
Access (Access XIII).

Suraj Poozhiyil (Program Manager, Microsoft) has said that the main reasons
for the MVF and the lack of representation of the MVF in the Releationships
window are:

1. for compatibility with SharePoint. (not the most popular product in the
world, to say the least)
2. this release is focussed on power users rather than developers.
3. and they could not give a F--- about developers that insist on using
Jet/ACE (I made that one up, didn't I?)

For those who have not been around the block a couple of times you should
know that both Access 95 and Access 2000 the other two major upgrade
releases were very badly botched. After many SPs to both Access 2000 and Jet
4 they finally got something usable although Access 95 was never any good at
all.

This information is based on Access Beta 2 (Public), although I do not
believe anything has changed in this area in the later builds.

--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited

BTW Codd's 2nd rule is called the guaranteed access rule "each and every
datum (atomic value) in a relational database is guaranteed to be accessible
by resorting to a combination of table name, primary key value and column
name". That is no longer possible with an MVF in an ACE database and SQL
stops working the way it is supposed to.

Small Business Solutions Provider

*DB2 Express-C, the developer-friendly alternative
http://www.ibm.com/cgi-bin/software/track0.cgi?i=53013&c=74646&o=1&ef=T&cn=8488201

 

[Albert D. Kallal]

Warning to Access Developers about ACE.

DON'T use multivalued fields (MVF) they are worse than LookUp fields and
SubDatasheets, in fact they (MVFs) are a logical progression in the
dumbing
down that started with these abominations.

I not sure how much of a warning users need. It is much like telling users
don't use macros, but go and learn how to write code.

There are MANY people who are not trained in programming, and actually get
by QUITE well using macros. Should we tell them not to use macros..and learn
how to write code? Perhaps we should, but those people would likely differ
on this issue.

Then there is the evil lookups. Again, literally MILLIONS AND MILLIONS of
people use lookup fields, and again, while I think they should be avoided,
....go ask those people that use lookups on a daily bases..and again find
them most useful. Lookups where built for these users...and I seen people
use them for years without so much as a problem...

As for MVF fields?, well, again, I see posts here practically every other
day with people asking how to store, or have things like favourite colors
(or food) saved into a field. For a novice, a solution to this problem takes
CONSIDERABLE amount of code right now to display a list of choices, and
allow the use to check off which ones they want. Again, the alternate you
give to the user is to tell them to build few tables..and then write a
WHOLE bunch of code. So, this have this little database, and need this one
feature. You give them this feature...and they go away happy.

Now, of course this feature is not for all...but, when you see those people
creating those pick lists, you have to ask what is the alternative you are
offering? In other words, what do you suggest for that user that just
created that picks list of favourite colors? It is like telling people don't
drive a automatic car...but learn to drive a standard car....

As for exposing the data in the relationships window? My bets are that many
of us will have write some utilities to do just that (Stephan Lebans..the
great access wizard has already code to build/display the relationships
window....my bets are in short order that this code will be extended to
display those extra tables...). (note, both ado, and dao can use the MVF
data in code..and they are exposed....).

So, I don't think a 5 bell alarm needs to be raised to users about using
lookup fields. They have their downsides from a developer point of
view..but, for users they are a time honoured feature that users like, and
use.

The same really applies to mulit-valued fields, and a there is going to be a
large number of people that use these without 2nd thought...just like
lookups...

Now, I am somewhat biased, as I used multi-valued databases for years..and
write about them here:
http://www.members.shaw.ca/AlbertKallal/Articles/fog0000000006.html


And, even eweek wrote the following:
Needed: A Relational Database for 'Normal' People
http://www.eweek.com/article2/0,1759,1618175,00.asp

 

[Craig Alexander Morrison]

Warning is for Relational Database Designers.

Not for old PICK cohorts, which I assume was your MVF exposure.

This is a discredited product and model.

Users may find it (MVF) useful I warned developers who might have to use
this merde to get a user out of the inevitable holes they will dig.

As an MVP I know you generally are supportive of MS and its stuff, I know
that the MVPs were all saying A95 was good to go and ignoring the
independent voices.

If anyone was to use Access 2007 for serious DEVELOPMENT then they should
ignore this MVF rubbish.

 

[Albert D. Kallal]

Warning is for Relational Database Designers.

Fair enough!!

Not for old PICK cohorts, which I assume was your MVF exposure.

Well, yes...jbbase, advanced revelation, IBM's U2, and of course pick.

This is a discredited product and model.

I shall leave the above issue for another day. I been involved over the
years in some very heated discussions. The *only* point I will make is
that MV systems as a general rule can map their data models and be
accessed by a traditional sql model. So, those MV systems can't be
all that bad, since you can now use sql against all of the major
vendors of mv systems. The discredited
part is IMHO a unfair characterization by the IT industry, I always
tried to fight this view.

As an MVP I know you generally are supportive of MS and its stuff, I know
that the MVPs were all saying A95 was good to go and ignoring the
independent voices.

Fair point in my case. However, the vocal parade against
MVF has generally been lead by MVP's. I can assume what has been
said in private discussions to MS makes your comments look like
they came from a saint!!

As for a95...well, never used it..but, we sure as the heck gave
access 2000 a rough ride for a long time. I don't think anyone
minced words with a2000, and it was likely the worst realise
ever. But, to be fair..when patched, a2000 is a fine product.

Anyway, I have no problem you pointing out the downfalls of MVF fields. I am
likely one of the few MVP's that thinks this is great feature.

 

[Tony Toews]

However if you are lumbered with fixing a total snafu from a "power-user"

Billable hours. <smile>

I won't use this feature. And given that you can't put any
additional fields on the "master" table this is of very limited use to
developers. We will almost certainly want at least one more field
such as inactive or date or something.

But I can see how this feature is useful for the Power User. This
gets them past a conceptual hurdle and having to build subforms.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Craig Alexander Morrison]

Albert my concern is concentrated on the fact that the internal three table
structure is not exposed in the Relationships Windows, Navigation Pane and
to SQL.

This violates the guaranteed access rule (mentioned earlier) and this is
unforgiveable. Jet was actually a good (albeit small scale) implementation
of the Relational Model.

With any luck they will complete the implementation in a future version of
Access/ACE however Vista is driving the deadline just as Windows 95 drove
the Access for Windows 95 release.

 

[Craig Alexander Morrison]

But I can see how this feature is useful for the Power User. This

gets them past a conceptual hurdle and having to build subforms.

Tony,

The multiple value control could have been introduced as a sort of subform
light without the need to break the guaranteed access rule. In other words
from the toolbox it could have been possible to select a multiple value
textbox and the table from which to get its values would be selected in a
tool wizard, if a join table did not exist it could be created.

Billable hours. <smile>

As I would tend to use what the user had thus far achieved as their user
requirements definition (rather than a foundation upon which to build)I
might find a reduction in the time needed for the requirements phase as they
would have been able to build a much deeper hole. (wg)

--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited

Small Business Solutions Provider