Read attachment from Access DB: Item not found in this collection

Question

I have an Access database (@"Provider=Microsoft.ACE.OLEDB.12.0) which contains a table called FTLH_DBF. The table is structured as follows:

+----+--------+--------+--------+--------------------------+
| ID | M_TYPE | M_NAME | M_DESC |          M_FILE          |
+----+--------+--------+--------+--------------------------+
|  1 |      0 | Spot   | Blabla | (Attachment: Spot.xml)   |
|  2 |      1 | Hedge  | Blabla | (Attachment: Hedge.xml)  |
|  3 |      2 | Unwind | Blabla | (Attachment: Unwind.xml) |
+----+--------+--------+--------+--------------------------+

By design, the code makes sure that the attachment name M_FILE is equal to M_NAME + .xml, as well as that no duplicate M_NAME can be found in the table.

I am trying to read the attachment back after having saved it (successfully, I can see the content of my attachment in the Access' viewer). In order to do this, I've been following this answer: basically I want to read the attachment and save it into a file in the process' folder (that will later be deleted by another function).

This is my method supposed to do this (the input templateName, according to the table above, would be Spot, Hedge, Unwind...):

    public void dumpAttachmentToFile(string templateName)
    {
        string fileName = templateName + ".xml";
        var dbe = new DBEngine();
        Microsoft.Office.Interop.Access.Dao.Database db = dbe.OpenDatabase(DB_LOCATION);
        Recordset rstMain = db.OpenRecordset(
                "select M_FILE from FTLH_DBF where M_NAME = '" + templateName + "';",
                RecordsetTypeEnum.dbOpenSnapshot);
        Recordset2 rstAttach = rstMain.Fields["M_FILE"].Value;
        while ((!fileName.Equals(rstAttach.Fields["M_FILE"].Value)) && (!rstAttach.EOF))
        {
            rstAttach.MoveNext();
        }
        if (rstAttach.EOF)
        {
            Console.WriteLine("Not found.");
        }
        else
        {
            Field2 fld = (Field2)rstAttach.Fields["M_FILE"];
            fld.SaveToFile(fileName);
        }
        db.Close();
    }

When I execute this code, I get the following runtime error:

System.Runtime.InteropServices.COMException
  HResult=0x800A0CC1
  Message=Item not found in this collection.
  Source=MxML-Factory
  StackTrace:
   at Microsoft.Office.Interop.Access.Dao.Fields.get_Item(Object Item)
   at MxML_Factory.Database.DBConnection.dumpAttachmentToFile(String templateName) in C:\Users\Matteo\source\repos\MxML-Factory\MxML-Factory\Database\DBConnection.cs:line 182
   at MxML_Factory.Business.MxML.Load(String m_name) in C:\Users\Matteo\source\repos\MxML-Factory\MxML-Factory\Business\MxML.cs:line 73
   at MxML_Factory.Utils.BrowseClassMapper.OpenFormInstance(Object TriggerClass, String loadingKey, DatabaseBrowse callback) in C:\Users\Matteo\source\repos\MxML-Factory\MxML-Factory\Utils\BrowseClassMapper.cs:line 24
   at MxML_Factory.WinForms.DatabaseBrowse.OpenInstance(String objectKey) in C:\Users\Matteo\source\repos\MxML-Factory\MxML-Factory\WinForms\DatabaseBrowse.cs:line 66
   at MxML_Factory.WinForms.DatabaseBrowse.HandleKeyPress(Object sender, KeyEventArgs e) in C:\Users\Matteo\source\repos\MxML-Factory\MxML-Factory\WinForms\DatabaseBrowse.cs:line 91
   [...previous calls of the stack which cannot be guilty...]

... on the following line of code:

while ((!fileName.Equals(rstAttach.Fields["M_FILE"].Value)) && (!rstAttach.EOF))

The object rstAttach contains a collection Fields, but all I can see in it is a property count = 6 (can't see the content, and I can't figure out why).

If I open my Access table, I can see there is the attachment I'm looking for (in this case, my fileName is Spot.xml:

It seems I'm missing something obvious, but I can't figure out what I'm doing wrong... any tip to investigate further?

First of all, shouldn't you check for EOF **before** inspecting contents of the recordset in the short-circuited AND condition of the `while` statement? If for any reason the filename is not found, then when the recordset goes past the end while looping, it will next try to access the field of a non-existent record. This isn't an answer to your specific data, rather just valid recordset-loop logic. — C Perkins, Apr 03 '19 at 02:28
What we don't see is the value of `filename` (or `templateName`) variable. If I'm not mistaken, String.Equals is by default case sensitive. Even if in this case your strings are identical, you should probably specify CurrentCultureIgnoreCase or OrdinalIgnoreCase values to Equals. — C Perkins, Apr 03 '19 at 02:34
If you look closely at the [other answer](https://stackoverflow.com/questions/25864092/extracting-files-from-an-attachment-field-in-an-access-database/25867795#25867795) which you linked to, notice that the `rstAttach` field names are "FileName" and "FileData". They should remain as such in your code since they are automatically generated field names and do not change. Your code has substituted both field names for `M_FILE`. (You could have discovered these by looping through the fields collection and printing the Field2.Name properties.) — C Perkins, Apr 03 '19 at 03:08
I previously had posted an answer, but then realized that this question (and my answer) really is just a duplicate of the question and answer that you already linked to... the only problem being that the proper field names were incorrectly replaced. `while ((!rstAttach.EOF) && (!fileName.Equals(rstAttach.Fields["FileName"].Value, OrdinalIgnoreCase)))` — C Perkins, Apr 03 '19 at 03:12
@CPerkins you're right, that was indeed a stupid mistake I did :) I thought that `FileName` and `FileData` were custom fields, not static names of the object. I changed as you said and it worked! I'm hence accepting your suggestion of duplicate, it is indeed an exact duplicate at this point. Thanks a lot for your help! — Matteo NNZ, Apr 03 '19 at 05:50

score 0 · Answer 1 · answered Apr 03 '19 at 03:00

See the Microsoft Docs for Field2.SaveToFile method for an example in VBA (which is easily converted to C# interop code).

The sub-recordset in the M_FILE field of the primary recordset contains a specific set of field names. First of all you could have discovered these by looping through the fields collection and printing the Field2.Name properties. The link above indicates that at least 2 of the field names are FileName and FileData. The short of it is that the loop should be

while ((!rstAttach.EOF) && (!fileName.Equals(rstAttach.Fields["FileName"].Value, OrdinalIgnoreCase)))

Also, the lines which actually save the file should be

Field2 fld = (Field2)rstAttach.Fields["FileData"];
fld.SaveToFile(fileName);

I just noticed that the other answer which you link to has the proper field names, so there is really nothing significantly different in this code. This question and answer thus become just a duplicate of that question.

Read attachment from Access DB: Item not found in this collection

1 Answers1