Introduction

Starting MS Access 2007 database developers got opportunity to use multivalued table fields (more) in databases of .accdb  format. This article explains how you can operate with such fields and query them by AnyDAC for Delphi.

Multivalued field kinds

Multivalued fields (also known as lookup fields) can optionally be split into the three categories:

• Fields based on a table or a query
• Fields based on value list
• Attachment fields

The first kind of fields is naturally foreign keys to master tables and thus won’t be reviewed in this article. Such fields are of the same data types as master tables’ primary keys (for example, NUMBER) and have no pre-defined structure.

Multivalued fields based on value list

Fields of this kind are defined in the database as TEXT columns; however they have a pre-defined structure that in turn consists of a single column:

Selecting data from such fields won’t be different from selecting data from ordinary columns:

SELECT MeetingID, Participants FROM Meetings
 
MeetingID     Participants
---------     ----------------------
2345          John Doe;Chris Johnson
1278          James Simpson

“Participants” is a multivalued field based on a list of string values. When multiple values are chosen by a user for a certain row, they are returned split by a semicolon.

To flatten the field values the following syntax can be used:

SELECT MeetingID, Participants.Value FROM Meetings
 
MeetingID     Participants
---------     ----------------------
2345          John Doe
2345          Chris Johnson
1278          James Simpson

Inserting a new value for the field in a certain row is done by the following statement:

INSERT INTO Meetings(Participants.Value)
VALUES('Sarah Brown')
WHERE MeetingID = 2345

Usage of WHERE clause is important as otherwise all the table rows will be updated with the new value.
This will result in:

MeetingID     Participants
---------     ----------------------------------
2345          John Doe;Chris Johnson;Sarah Brown
1278          James Simpson

Updating a certain value is done by the following construction:

UPDATE Meetings
SET Participants.Value = 'Peter Lee'
WHERE (Participants.Value = 'Sarah Brown') and (MeetingID = 2345)
 
MeetingID     Participants
---------     --------------------------------
2345          John Doe;Chris Johnson;Peter Lee
1278          James Simpson

Deleting a certain value is possible using the following statement:

DELETE Participants.Value
FROM Meetings
WHERE Participants.Value = 'Chris Johnson'

In opposite case the following construction will delete the entire row:

DELETE FROM Meetings WHERE Participants.Value = 'Chris Johnson'

Attachment fields

Fields of this kind (more) represent a nested table that holds file attachments to the parent table rows. SQL syntax for selecting, updating and deleting values from such fields is the same as for the other multivalued fields.

The nested table consists of the following self-described columns:

Note that MS Access ODBC driver does not support inserting or updating the FileData value that leads to inability to attach or modify the attached files.

The following query by default will return the file names stored in the table:

SELECT ApplicantID, Resumes FROM Applicants 
 
ApplicantID    Resumes
-----------    ---------------------------------
3456           AndrewCV_eng.doc;AndrewCV_rus.doc
3457           PeterCV_eng.docx

“Resumes” is the attachment field, which is determined by AnyDAC as an ordinary TEXT column. The first row in the example has the two files attached.

More interest can be expressed to the way of reading and storing the files outside of the database using AnyDAC.

The following example stores all files attached to a certain record into a temp folder:

procedure SaveAttachments;
var
  sFile, sCatalog: String;
  oBlobStream: TStream;
  oFile: TFileStream;
  bLen: Byte;
begin
  with ADConnection1.Params do begin
    Clear;
    Add('DriverID=MSAcc');
    Add('Database=c:\DB\Attach.accdb');
  end;
  ADConnection1.Connected := True;
  ADQuery1.Connection := ADConnection1;
  with ADQuery1 do begin
    SQL.Text := 'SELECT Resumes.FileName as FileName, Resumes.FileData as FileData ' +
                'FROM Applicants WHERE ApplicantID = 3456';
    Open;
    First;
    sCatalog := 'c:\Temp';
    while not Eof do begin
      sFile := sCatalog + '\' + Fields.FieldByName('FileName').AsString;
      oBlobStream := CreateBlobStream(Fields.FieldByName('FileData'), bmRead);
      oFile := TFileStream.Create(sFile, fmCreate);
      try
        oBlobStream.Read(bLen, 1);
        oBlobStream.Position := bLen;
        oFile.CopyFrom(oBlobStream, oBlobStream.Size - bLen);
      finally
        oBlobStream.Free;
        oFile.Free;
      end;
      Next;
    end;
  end;

Note that all the files extracted from the database will have a service header in front of the user data.
The length of the header may vary from 18 – 22 bytes. The precise count of header bytes is stored in the very first byte of the file.

More reading

• Guide to multivalued fields

• Using multivalued fields in queries

• Attach files and graphics to the records in your database
  

Summary

As you can see the SQL syntax for operating with multivalued fields has a few differences in comparison to work with ordinary columns.

AnyDAC supports working with such fields out of the box. MS Access developers can easily save the stored files from attachment fields outside the database using AnyDAC.