C# read attachments from Access .accdb

Question

I have to read a table from .accdb file and migrate it to a database. I can't install MS Access on the server where the migration will be executed! Currently I use ODBC

OdbcConnection DbConnection = new OdbcConnection("DSN=SAMPLE_ISAM");
DbConnection.Open();
OdbcCommand DbCommand = DbConnection.CreateCommand();
DbCommand.CommandText = "SELECT Attachments FROM SomeTable";
OdbcDataReader DbReader = DbCommand.ExecuteReader();
while (DbReader.Read())
{
    object att = DbReader["Attachments"];
}
DbReader.Close();
DbCommand.Dispose();
DbConnection.Close();

SAMPLE_ISAM is pointed to the accdb file. This works well for the simple data types, but for attachments it gets only the file name(I also need the bytes).

As I said MS Access can't be installed so Interop DAO is not an option.

Is there any way to get the attachments? Other technologies and programing languages are also acceptable.

Check this answer: http://stackoverflow.com/questions/25864092/extracting-files-from-an-attachment-field-in-an-access-database — Steve, Feb 28 '15 at 20:28
*"MS Access can't be installed so Interop DAO is not an option"* - Not true. If the server has the Access Database Engine installed - which it must have for you to use ACE ODBC or ACE OLEDB - then you can use the ACE DAO data access layer with the COM reference "Microsoft Office 14.0 Access Database Engine Object Library". If you wanted to *automate* an instance of Access then yes, you'd need the full Access application installed, and you'd use the "Microsoft Access 14.0 Object Library" COM reference in your .NET project. However, Access *automation* is not required for ACE DAO data access. — Gord Thompson, Mar 01 '15 at 10:44

score 4 · Accepted Answer · edited May 23 '17 at 11:33

Thanks to Steve I found this Extracting files from an Attachment field in an Access database

So the working code is:

            OdbcConnection DbConnection = new OdbcConnection("DSN=SAMPLE_ISAM");
            DbConnection.Open();
            OdbcCommand DbCommand = DbConnection.CreateCommand();
            DbCommand.CommandText = "SELECT Attachments.FileData, ID, Attachments.FileName FROM Complaints WHERE ID IN(29,30)";
            OdbcDataReader DbReader = DbCommand.ExecuteReader();
            int fCount = DbReader.FieldCount;
            while (DbReader.Read())
            {
                byte[] bytes = (byte[])DbReader[0];
                Int32 ID = (Int32)DbReader[1];
                string name = (string)DbReader[2];
                File.WriteAllBytes(@"D:\files\" + name, bytes.Skip(20).ToArray());
            }
            DbReader.Close();
            DbCommand.Dispose();
            DbConnection.Close();

using System.Linq; is required for Skip(20) (See the link). The meta data is 20 bytes for pdf and jpg. Note that it may vary for other file types.

*"The meta data is 20 bytes for pdf and jpg. Note that it may vary for other file types."* - Yes, it does. In fact, for two JPEG files with identical contents the metadata will be a different size depending on the file extension (".jpg" vs. ".jpeg"). — Gord Thompson, Mar 01 '15 at 10:35

score 2 · Answer 2 · answered Jun 15 '17 at 19:36

i'm not sure what the header format is, but the first byte will tell you the length of the header, to extend evgeni's example:

        OdbcConnection DbConnection = new OdbcConnection("DSN=SAMPLE_ISAM");
        DbConnection.Open();
        OdbcCommand DbCommand = DbConnection.CreateCommand();
        DbCommand.CommandText = "SELECT Attachments.FileData, ID, Attachments.FileName FROM Complaints WHERE ID IN(29,30)";
        OdbcDataReader DbReader = DbCommand.ExecuteReader();
        int fCount = DbReader.FieldCount;
        while (DbReader.Read())
        {
            byte[] bytes = (byte[])DbReader[0];
            Int32 ID = (Int32)DbReader[1];
            string name = (string)DbReader[2];
            File.WriteAllBytes(@"D:\files\" + name, bytes.Skip((int)bytes[0]).ToArray());
        }
        DbReader.Close();
        DbCommand.Dispose();
        DbConnection.Close();

but i don't think this will work if there is more than one file in the attachment...suspect the header also tells how many attachments there are at offset 5, but more tests are needed to see if this is the case.

C# read attachments from Access .accdb

2 Answers2