Monday, October 31, 2011

New Sample: Query_A2K10_MultiValueFields

By AD Tejpal

    This sample db demonstrates query based approach to bulk appending / updating / make table actions involving multi-value  fields in Access 2010, without resorting to use of recordset or recordset2 objects, thus overcoming a known limitation associated with such fields.

    In this arrangement, an extra field named TempID is provided in the destination table. The append query is executed in two stages. In first stage, primary key values from source table are appended to TempID field in destination table. In 2nd stage, value elements of MVF field get appended, using an inner join between the source and destination tables (PK of source table equals TempID of destination table).

    Three styles are covered as follows:

  •     (a) Bulk appending of selected records (having multi-value fields) from one table to another.
  •     (b) Bulk updating of multi-value field elements (addition or replacement of values) in destination table based upon values held in source table.
  •     (c) Make table action covering multi-value fields based upon selected records in source table.  

Version: Access 2010 accdb file.

You can find the sample here:

No comments: