Converting from multi-value fields

[Toireasa]

Hi helpful people,

I have a question, which I can't find the answer to anywhere and that worries me that it might not be possible. If anyone can help me, I hope you guys can!

I set up a database using MS Access 2007 and, due to the complex nature of the data to be contained within, decided to set up the 'many-to-many' relationships using the multi-value lookups allowed within this version of MSAccess rather than with junction tables. However, I now want to 'upsize' the backend of the database to a Microsoft SQLServer system, which I presume does not support this feature.

So, is there a way to convert these multi-value lookups into junction tables without weeks of database redesign and data entry?

Thanks in advance,
Toi

[puppydogbuddy]

Mult-valued fields are available only if you convert to the new accdb file format. If you keep the old mdb format, you won't have to deal with converting multi-valued fields. However, once you have converted to the new accdb file format, there are no shortcuts that I am aware of.

Quote from Allen Browne: http://allenbrowne.com/Access2007.html
The relational structure behind multi-valued fields is not accessible.

Developers will find it harder to handle complex data types. Any generic procedure you write must be capable of handling fields that contain fields. You cannot use a table with a multi-valued field in an IN clause (i.e. in another database.)

see also: coverting to earlier file formats http://office.microsoft.com/en-us/ac...678311033.aspx