Access 2007 compatibillity and connectivity issues.

[Guest]

I use access 2007 and its new features attachment and multivalued field. In
the futute i plan the records that have been gathered to the access 2007
database to insert them in a larger more sofisticated database such as oracle
or sql etc. Would it be a problem in this insertion?

 

[Allen Browne]

Yes: multi-valued fields (MVFs) will be a problem later.

MVFs violate the atomicity of the data, so if you use them in Access, you
will be okay in non-database stores (such as Sharepoint), but shot if you
want to store the data in a real database.

MVFs are a pain in Access anyway. Examples:
- If you try to write generic code that loops through the fields of a
recordset, you must handle the possibility that the field you are examining
is not atomic data, but is actually a recordset within a recordset.
- You can use an IN clause to manipulate a table in another database if it
contains an MVF.
- Access fails to handle the OldValue property correctly for a combo bound
to an MVF.
- Although Access does use a relational structure internally to manage MVFs,
you cannot get at this structure.

You will be far better served to create a related table and store the values
there.

 

[Arvin Meyer [MVP]]

I use access 2007 and its new features attachment and multivalued field. In
the futute i plan the records that have been gathered to the access 2007
database to insert them in a larger more sofisticated database such as
oracle
or sql etc. Would it be a problem in this insertion?

In addition to Allen's answer, I might add that no mainstream database
system supports multi-value fields. The only other database that I am aware
of that does support them is Filemaker. I do not believe that multi-value
data is transferable in either direction with Filemaker. So, if you use
multi-value fields, you are stuck with no upgrade (or downgrade) path until
you put the data in a related table. It is easier to do it correctly in the
first place.

If your final output is Sharepoint, then you have little choice since
Sharepoint only supports lists, not relational tables.

 

['69 Camaro]

Hi, Tom.

I use access 2007 and its new features attachment and multivalued field.
In
the futute i plan the records that have been gathered to the access 2007
database to insert them in a larger more sofisticated database such as
oracle
or sql etc.

Oracle can handle the equivalent of multivalue fields by using nested
tables. Microsoft doesn't yet have a version of SQL Server that can
accommodate the equivalent of multivalue fields, and I haven't heard any
announcements of plans to do so in future versions.

Would it be a problem in this insertion?

There is no automated way to migrate the data from an ACE table with
multivalue fields to an Oracle table with nested tables. To give you an
idea of the difficulty and pain involved, grasp your upper lip between your
right index finger and thumb and pull your lip up over your head and stretch
it until it reaches between your shoulder blades. The difficulty and pain
and suffering you have endured is only about half of what you will endure
trying to insert the rows with multivalue fields from the ACE table to the
Oracle table with nested tables, and then trying to retrieve that data later
for your reports.

I would suggest normalizing your tables properly, adding appropriate foreign
key constraints, and using the free data migration tools built by either
Oracle or Microsoft to automatically migrate the data into the larger, more
sophistocated database engine of your choice when you later have the need.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[David W. Fenton]

MVFs violate the atomicity of the data, so if you use them in
Access, you will be okay in non-database stores (such as
Sharepoint), but shot if you want to store the data in a real
database.

Now, hold on. Since the data is actually stored properly, and you
have programmatic access to the real data structure, shouldn't it be
possible to migrate to other data stores with an intermediate step
to expose the background structure? That is, you'd create real
tables, import data from the behind-the-scenes table, then remove
the MVF from your table. Then everything will import.

If you're not truly contemplating moving to a different back end, I
see no reason to plan for it now.

That said, I'm not sure I'd utilize multi-value fields if I were
using A2K7 -- I'd probably continue doing it with my own join
tables.

Perhaps it's a field type for end users and not for developers, who
ought to know how to manage it without the help of this
simplification.

 

[David W. Fenton]

- Although Access does use a relational structure internally to
manage MVFs, you cannot get at this structure.

Um, yes you can, via DAO.

 

[Sascha Trowitzsch]

Um, yes you can, via DAO.

Ok, you can get the field values but not the table.
An internal attachment table is named e.g. s =
"f_470F0E6145984475BD9840CB91BE2ECB_photo"
But
? CurrentDb.TableDefs(S) .Name
throws "Element not found."
I tried anything but had no access to the internal table.

Sascha

 

[Larry Daugherty]

Microsoft is motivated to sell lots of copies of Office Pro. That
motivation trumps "slavish adherence to the rules for relational
database design". To help them sell Access to novices they put in
"features" that will help novices over some early hurdles with
apparent ease at the expense of relational integrity. A couple of the
more egregious examples are Lookup Fields in Tables and now
Multivalued Fields.

At the level of user visibility the rule regarding field "atomocity"
has been broken. The tables created with those constructs in fields
are now corrupt. Yes, MS has handled things properly way down under
the hood but the data is presented in a dishonest way. Their
solutions only percolate up one level. The novice can create
something useful at the level of the tables themselves and the Forms
but you can't build Queries and Reports on those constructs in a
straightforward way.

It's been a while since I visited www.mvps.org/access but there was a
diatribe there when last I looked regarding Lookup Fields in tables.
It should be joined by another in the same vein regarding Multivalued
fields.

Since the reason for being of these Access newsgroups is developers
helping developers I try to keep things on the directly beneficial
level to the Original Poster (and the other posters and the lurkers).
Should the question be asked directly or it becomes evident that OP is
using one of the nasty constructs I advise them against it and suggest
that removing them and re-designing their date be the first step in
remediation of their issue.

If I were in the position of the MS Office Product Manager or the
Access Product Manager, I would very likely adopt their motivations
and do things much as they do. Being a developer, my motivation is to
provide robust applications of maximum benefit to the client at least
cost.

HTH

 

[Sascha Trowitzsch]

Now, hold on. Since the data is actually stored properly, and you
have programmatic access to the real data structure, shouldn't it be
possible to migrate to other data stores with an intermediate step
to expose the background structure? That is, you'd create real
tables, import data from the behind-the-scenes table, then remove
the MVF from your table. Then everything will import.

A workaround to do this:
- Export a table with attachment fields to XML. Include embedded schema
information.
- Import the XML in Access 2003. Two tables will be created. The blob table is
named according the attachment field.

There will only be a little error because of the unknown type jetcomplexType but
tables are imported correctly. Issue: What's missing is a relation between the
two imported tables.

Sascha