How to work with Access attachment field from Windows Forms?

Question

In Visual Studio, I have my data source set up from a local Access database file. One of the tables in that database has, among other things, several different fields for attachments (utilizing Access' attachment data type).

Firstly, Visual Studio has them marked as string data types, which seems incorrect, but when I go to change it, there doesn't seem to be an applicable data type: Visual Studio data types

What would be the appropriate data type to select?

Secondly, what control would be appropriate to represent this field on a form? Obviously a TextBox won't cut it, but I don't know what the best way to have users upload and download these attachments would be - I'm quite new to winforms.

If coding is part of your answer, be aware that I'm using VB.net (I know, I know). Visual Studio version is the Community 2019 Edition 16.7.1

None of the built-in Data UI is appropriate for the attachment data type. You'll have to create a custom control handling the multiple files it might contain, [store](https://stackoverflow.com/questions/18764314/storing-an-image-into-an-attachment-field-in-an-access-database) and [read](https://stackoverflow.com/questions/25864092/extracting-files-from-an-attachment-field-in-an-access-database) them manually. — Martheen, Aug 15 '20 at 01:23
Those posts suggest using the DAO object model. There ***is*** a way to query the hidden child table (attachment columns in access are multiple-value - you can have 1 or 15 attachments for that given column) Behind the scenes there is a regular access table. And if you use the tips below, then you don't have to use the DAO file save/write, but can write just like using a varbinary say with sql server. The ACE query processor can expose the additional columns, and it often done in Access, and it turns out the same approach works in .net with oleDB as below shows. — Albert D. Kallal, Aug 15 '20 at 04:39

Albert D. Kallal · Accepted Answer · 2020-08-15T15:00:18.867

A VERY interesting question.

And the answer is yes you can rather easy pull that attachment data.

The first thing to realize is the the attachment table is in fact "behind" the scenes a plain jane child table. Remember, that attachment column (child table) can have 1 or 15 files attached.

The trick to deciphering this magic is to fire up the query builder in Access, and drop in that table.

You see this

Note how a "set" of columns exists for each attachment (3 columns).

Now, if in a query you do NOT include one of the 3 columns? Then you get 1 row for each row in the query.

With, above, lets go

Select * from tblAttach.

I have ONE row in this table, but you can see two attachments!!

Now the magic trick:

If you INCLUDE any of the sub table columns, then behind the scenes the data engine will do a LEFT JOIN of this hidden child table (in fact, it not hidden anymore!!!).

So, we can do this for a query:

SELECT ID, FirstName, LastName, 
MyBinFiles.FileName, 
MyBinFiles.FileType, 
MyBinFiles.FileData
FROM tblAttach;

So, just including ANY ONE of the sub column name will cause Access(ACE) to do a left join. (you don't use a join - just the ACT of any of the 3 columns appearing will cause access to left join).

So, the parent columns (id, FirstName, LastName) will "repeat" for each child row in the attachment table (the table is hidden in Access - you can't get the name, but with this trick, well - we don't care.

You get this now:

And that file data column? It is a bytes() array of the whole file.

So, now, lets jump to .net.

Drop in a grid view. We have this code

And then a button - code behind that button is this:

   Dim con As New OleDb.OleDbConnection(My.Settings.Test44)

    Dim strSQL As String
    strSQL = "SELECT ID, FirstName, LastName, " &
             "MyBinFiles.FileName," &
             "MyBinFiles.FileType" &
             " FROM tblAttach"

    ' "tblAttach.MyBinFiles.FileData," &

    Dim oReader As New OleDb.OleDbDataAdapter(strSQL, con)
    Dim rstData As New DataTable
    oReader.Fill(rstData)

    Me.DataGridView1.DataSource = rstData

    Dim btn As New DataGridViewButtonColumn()
    DataGridView1.Columns.Add(btn)
    btn.HeaderText = "Export"
    btn.Text = "Export File"
    btn.Name = "btn"
    btn.UseColumnTextForButtonValue = True

Ok, we now have this in .net:

(note again - two rows!!! - so that MAGIC join occurred!!!)

Now, the button code to export the file?

We have this:

Private Sub DataGridView1_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellClick

    If e.ColumnIndex = 5 Then
        ' export data
        With DataGridView1.Rows(e.RowIndex)

            Call ExportFile(.Cells(0).Value, .Cells(3).Value, .Cells(4).Value)

        End With
    End If

End Sub

Note that the PK column of the child table is NOT exposed. In effect the PK (id) of the main table + the fileName = PK of full row.

And yes, this DOES mean that you can't attach two files of the same name to a given single row - it is not allowed in Access - the UI and engine prevents this.

So, in above, I passed the ID and FileName. And I did NOT include the binary (byte()) column in the display grid query - it will cause "nasty" - but if attachments were pictures? Then yes, code examples that pull/display binary picture(s) from SQL server (or Access/ACE) will in fact work.

In my example, you can see the file extension is pdf (so the type column is the original file extension - in most cases!!!). So, pdf files were being stored here.

So, the code now to export that one attachment out to a standard windows file?

The code is really the same as when doing this with SQL server and a varbinary column.

We get this:

Sub ExportFile(id As Integer, strFile As String, strType As String)

    Dim con As New OleDb.OleDbConnection(My.Settings.Test44)

    Dim strSQL As String
    strSQL = "SELECT ID, " &
             "MyBinFiles.FileName," &
             "MyBinFiles.FileType," &
             "MyBinFiles.FileData" &
             " FROM tblAttach " &
             " WHERE ID = " & id &
             " AND MyBinFiles.FileName = '" & strFile & "'"

    Dim oReader As New OleDb.OleDbDataAdapter(strSQL, con)
    Dim rstData As New DataTable
    oReader.Fill(rstData)

    Dim strSaveAsFile As String = "c:\test\Files\" & strFile & "." & strType
    Dim bytefile As Byte() = rstData.Rows(0).Item("MyBinFiles.FileData")
    File.WriteAllBytes(strSaveAsFile, bytefile)

End Sub

So, note this time we DID include the FileData. And note how I just write out the data (byte()) array to disk.

The end result will be working windows file that you can open. In my case pdf, but it could have been a picture, or say a word file.

The question should be reopened to allow this answer to be marked as the answer. — Gustav, Aug 15 '20 at 07:31
I've previously tried a similar approach, but ran into trouble with files where Access compresses the data (e.g. bmp, tiff, list can be found [here](https://support.microsoft.com/en-us/office/attach-files-and-graphics-to-the-records-in-your-database-d40a09ad-a753-4a14-9161-7f15baad6dbd)). Could you check if your current approach works for those file types? — Erik A, Aug 15 '20 at 11:55
Further to comment from @ErikA – PDF files are a special case. See [this answer](https://stackoverflow.com/a/25867795/2144390) for details. — Gord Thompson, Aug 15 '20 at 15:53
Thank you Gord. While my code does work, it DOES look like the byte() array has to be de-compressed by ACE for this to REALLY work. So, my example does work for PDF files, but only because PDF is "more tolerant". I will in a bit of time, edit and update my post wiith working code. The tip/trick to get the columns and extra rows is STILL handy for multi-valued columns, but to be fair, in the case of attachments, then the issue of compression comes into play. (in fact, it seems that above ALWAYS de-compresses the file - so it works. But for compressed formats, it fails since it also decompressed — Albert D. Kallal, Aug 15 '20 at 16:08

How to work with Access attachment field from Windows Forms?

1 Answers1

Linked