Access 2007: where is the data related to multi-valued fields stor

[Fabien Delpierre]

The Office Online article at
http://office.microsoft.com/en-us/access/HA012337221033.aspx says this:
"You might wonder why Office Access 2007 allows you to store more than one
value in a field, when most relational database management systems forbid
this. The answer is that the database engine in Office Access 2007 doesn't
actually store the values in a single field. Even though what you see and
work with appears to be a single field, the values are actually stored
independently and managed in hidden, system tables. The Access database
engine handles this for you, automatically separating the data and bringing
it back together again to surface the values in one field."

I've found that if you display your database's system objects, then open
MSysComplexColumns, it contains the names of each multi-valued field in your
database along with a corresponding ID in the FlatTableID field.
Digging deeper, if you open another system object, MSysObjects, it features
an Id field under which you will find the value(s) you found in
MSysComplexColumns.FlatTableID. And on the same row in MSysObjects, you can
look under the Name field to find a rather long and complex name (in my case,
f_27EF79865A2D4291AC22F5CCF6FEC92E_Sales Region) which is related to your
multi-valued field.
I would assume that this is where Access stores the relevant data.

My question, then, is whether that mysterious table with the long cryptic
name can be opened in one way or another. I've tried the only two ways I can
think of, which are the OpenTable VBA method and a SQL query.

Before you ask, I don't actually need to open/view that table. I just want
to, for the sake of curiosity!

Does anybody know if this is possible or not?
Thank you!

 

[Allen Browne]

If anyone does know how, I'm sure we would all be interested, Fabien. AFAIK,
these are 'super-hidden', i.e. not available to mere mortals.

You seem to have a good understanding of how Access works, so I'm
questioning whether multi-valued fields (MVFs) are really suited to you.
IME, they create many more problems than they solve for developers who knows
what they are doing.

Examples:
- VBA, e.g. you have to re-write any generic code you have to handle
recordsets and fields (since a field may be a complex object (a recordset)
and not a discrete value.)

- SQL, e.g. a query may fail if the FROM Table1 IN
'C:\SomeFolder\SomeDB.ACCDB' if Table1 has MVFs.

- Concurrency: there are timing issues. OldValue doesn't work for a combo
bound to a MVF. (Just imagine what's going on when the current record is not
yet saved, but the related values in the superhidden table need updating as
well.)

- Filters: see http://support.microsoft.com/kb/926701

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message

 

[Fabien Delpierre]

Thank you Allen for your response!
I was secretly hoping that an Access MVP like you would notice my question.
I work in IT support and since Office 2007 is being deployed in my
environment, I was doing some homework/research on its new aspects (what's
new, what's gone, what's broken, etc.)
You're right, MVFs don't seem like such a good idea to me, and I would not
plan on using them even though they sound like a good idea at first. I figure
they might be useful in small, simple databases where the risk of running
into the issues you mentioned is low, but I know I'll stick to the classic
normalization rules I know. I don't like hidden stuff in my databases.
The truth is that I know that some of my users will be enchanted by that new
feature when they realize it's there -- and they will -- but will fail to
take its ins and outs into account, and it then falls upon me to fix it and
explain it to them. Lots of fun in perspective!
Thanks again for your response.

 

[Agile Consulting]

Explain ADO and RDO