By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,463 Members | 774 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,463 IT Pros & Developers. It's quick & easy.

Multi-Value Fields in Table Updates

P: n/a
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
Sep 23 '08 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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-----
Sep 24 '08 #2

P: n/a
MGFoster <me@privacy.comwrote:
>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/
Sep 24 '08 #3

P: n/a
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
Sep 25 '08 #4

This discussion thread is closed

Replies have been disabled for this discussion.