Hi, Steve.
How does one store an array in VBA into a single field in Access?
One doesn't. Storing multiple values in a column of a record violates
Normalization rules that need to be applied to a relational database to
maintain data integrity.
I could
convert the entries of the array into a CSV string and store the data as a
string,
I would advise against this. Think about how hard it would be to pull up
all records that contained a 4 in that field where you had multiple values.
How would you parse the field's string in a SQL query to find the instance
of 4 but not 44, and not 14, and not 404, and every other possible
combination in each record? That database would be very time-consuming to
maintain. Time is money in the business world.
How about changing your mind about the design of your table to store this
array and create a second table, instead? You could split the array and
take each separate element and store it as a single value in a field in a
single record of that second table. You would have one record per element
in the array. A second field in each record could be used to identify the
contents in the array where the element came from (perhaps a record
identifier in the first table -- aka the "foreign key"?) and a third field
would, of course, be the primary key to identify each unique record in the
second table.
To later retrieve the elements from the array that were stored in the second
table, a query could be written that would join the two tables on the
foriegn key value.
HTH.
Gunny
See
http://www.QBuilt.com for all your database needs.
See
http://www.Access.QBuilt.com for Microsoft Access tips.
(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
