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

Answer 1

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.