Multi-Value Fields in Table Updates

[jim]

Each night, a scheduled task copies certain information from an MS
Access db to a MySQL db that serves as a data warehouse. I would like
to begin replicating multi-value fields from Access to MySQL, but when
I try I run into the "unable to insert multi-value fields..." error.

Does anyone know of a way around this? I would be satisfied with
being able to treat all values in the multi-value list as a single
text string with some sort of delimiter.

Thanks in advance for any insight.
Jim

[MGFoster]

jim wrote:

Each night, a scheduled task copies certain information from an MS
Access db to a MySQL db that serves as a data warehouse. I would like
to begin replicating multi-value fields from Access to MySQL, but when
I try I run into the "unable to insert multi-value fields..." error.

Does anyone know of a way around this? I would be satisfied with
being able to treat all values in the multi-value list as a single
text string with some sort of delimiter.

Thanks in advance for any insight.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Multi-value cells violate relational DB designs.

But......

If you want to transfer comma-delimited values as ONE value you will
have to delimit the string with either a single quote (') or a double
quote ("), depends on the target DB. Usually the single quote works for
all DBs. So, the SQL string would look like this:

UPDATE MySQL_table_name
SET MySQL_column_name = 'junk, stuff, other stuff, trash, crapola'
WHERE <criteria>

If you have the MySQL docs it probably has info on string delimiters.

--
MGFoster:::mgf00 <atearthlink <decimal-pointnet
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSNmrWYechKqOuFEgEQLOHACcDRJ2EZYGvRclq8b6mGeRQI Jz/e8AoI2c
LzDEQO0cVEeRQ57KM1szgOJ+
=atu0
-----END PGP SIGNATURE-----

[Tony Toews [MVP]]

MGFoster <[e-mail]:

>Multi-value cells violate relational DB designs.

But in A2007 it only appears to violate relational DB design. You can get at that
data to create a "junction" table such as more experienced folks would use or that
would work in A2003 or older. And you could then create the combined field you
mention although it would require a bit of work and some VBA code.

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
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

[jim]

Thanks for the responses. By way of additional back story, I'm using
Access as an interface with a SharePoint list (where the multiple
values are generated). I can easily return the corresponding values
for each id, but need to figure out how to concatenate those results
into a single field.

I understand the concept of string delimiters, but am having trouble
figuring out how to combine the individual records into a single
field. Access will render as a single, comma delimited field.
SharePoint exports as a single field delimited on ";#", but it's
passing that information to another table that's the real beast.

I'll see what I can find on junction tables and VBA for this. I fear
it may be over my head.

Thanks again for the responses.
Jim