You mentioned in your comments that you are having trouble using DAO. You need to add a reference to the Access Engine similar to this image (your version number may be different):
Then add this Imports statement to your code:
Imports DAO = Microsoft.Office.Interop.Access.Dao
An example of using DAO to manipulate the Attachment fields can be found in this SO question Storing an image into an Attachment field in an Access database.
If you don't mind using a hack, you can add/delete/extract attachments using OleDB. The following utility class is a result of hacking session I participated in on the MSDN forum about six years ago. I found that Byte array you retrieve from the AttachFieldName.FileData field is a header prefixed array. The first 4 bytes define an integer offset to actual file data. This allows you to recreate the attached file. I also discovered that if you prefix your file data with 8 bytes (the first four again being the offset to the file data and the remaining four left as zero) that Access will then accept it for storage. It will expand this header with miscellaneous values that are not important to you and rewrite the first 4 bytes with the new offset. I know this technique works for Access 2007 and 2010, but I have not been tested on newer versions. Also, as far as I know, you still need use the data offset in DAO to extract the original file; loading an attachment is easier due to the Field2.LoadFromFile command.
I apologize for this code dump, but getting the commands setup properly is a bit of a pain. Therefore I wrote this library to simplify things.
Imports System.Data.OleDb
Public NotInheritable Class Attachments
Private Const prependLength As Int32 = 8
''' <summary>Expands the attachment field name to the 2 fields that need to be retrieved</summary>
''' <param name="attachmentFieldName"></param>
''' <returns>"attachmentFieldName.FileData, attachmentFieldName.FileName"</returns>
Public Shared Function ExpandAttachmentFieldNames(attachmentFieldName As String, tableName As String) As String
Return String.Format("[{0}].[{1}].FileData, [{0}].[{1}].FileName", tableName, attachmentFieldName)
End Function
''' <summary>Prepends the 8 byte header block required by Access to store the bytes that comprise the fiel</summary>
''' <param name="source"></param>
''' <returns>8 bytes + source</returns>
Public Shared Function PrependFileDataHeader(source As Byte()) As Byte()
' Through reverse engineering, it has been determined that while length of the
' actual data header varies based on file type.
' This header always begins with the first 4 bytes representing the offset to the actual data bytes.
' The bytes from index 4 (zero based) to the beginning of the data appear to represent the file type
' and other undetermined information.
' If an 8 byte field in prepended to the data bytes, with the first 4 bytes representing the
' length of 8, it has been found that that Access will accept it. The second 4 bytes will be expanded
' by Access and filled by it. The initial four bytes is modified to the new data offset.
Dim ret As Byte() = New Byte(0 To (prependLength + source.Length) - 1) {}
Dim lengthBytes As Byte() = BitConverter.GetBytes(prependLength)
Array.ConstrainedCopy(lengthBytes, 0, ret, 0, lengthBytes.Length)
Array.ConstrainedCopy(source, 0, ret, prependLength, source.Length)
Return ret
End Function
''' <summary>Extracts the bytes that define the attached file</summary>
''' <param name="fileData">the header prepended bytes array returned by Access</param>
''' <returns>the bytes defining the attached file</returns>
Public Shared Function ExtractFileBytes(fileData As Byte()) As Byte()
Dim dataOffset As Int32 = BitConverter.ToInt32(fileData, 0)
Dim databytes(0 To (fileData.Length - dataOffset) - 1) As Byte
Array.ConstrainedCopy(fileData, dataOffset, databytes, 0, fileData.Length - dataOffset)
Return databytes
End Function
''' <summary>
''' Takes an Access FileData byte array and returns a stream of it contained file.
''' </summary>
''' <param name="fileData">the attachment data as received from Access</param>
''' <returns>MemoryStream constrained to the contained file data</returns>
Public Shared Function FileDataAsStream(fileData As Byte()) As IO.MemoryStream
Dim dataOffset As Int32 = BitConverter.ToInt32(fileData, 0)
Dim datalength As Int32 = fileData.Length - dataOffset
Return New IO.MemoryStream(fileData, dataOffset, datalength)
End Function
''' <summary>
''' Takes FileData Byte() from Access and writes its contained file to the passed stream.
''' </summary>
''' <param name="fileData">the attachment data as received from Access</param>
''' <param name="strm">stream to copy file contents to</param>
''' <remarks></remarks>
Public Shared Sub WriteFileDataToStream(fileData As Byte(), strm As IO.Stream)
Dim dataOffset As Int32 = BitConverter.ToInt32(fileData, 0)
Dim datalength As Int32 = fileData.Length - dataOffset
strm.Write(fileData, dataOffset, datalength)
End Sub
''' <summary>
''' Copies entire stream to an Access FileData Byte()
''' </summary>
''' <param name="strm">source stream to wrap in Access FileData Byte()</param>
''' <returns>An Access FileData Byte()</returns>
''' <remarks></remarks>
Public Shared Function FileDataFromStream(strm As IO.Stream) As Byte()
Dim ret As Byte() = Nothing
If strm.CanSeek Then
Dim dataLength As Int32 = CInt(strm.Length)
strm.Position = 0
ret = New Byte(0 To (prependLength + dataLength) - 1) {}
Dim lengthBytes As Byte() = BitConverter.GetBytes(prependLength)
Array.ConstrainedCopy(lengthBytes, 0, ret, 0, lengthBytes.Length)
strm.Read(ret, prependLength, dataLength)
Else
Throw New IO.IOException("Stream must be seekable.")
End If
Return ret
End Function
''' <summary>
''' Copies data from read file to an Access FileData Byte()
''' </summary>
''' <param name="filePath">Full path to source file</param>
''' <returns>An Access FileData Byte()</returns>
Public Shared Function FileDataLoadFile(filePath As String) As Byte()
Dim ret As Byte() = Nothing
Dim fi As New IO.FileInfo(filePath)
If fi.Exists Then
Dim strm As IO.Stream = IO.File.OpenRead(filePath)
ret = FileDataFromStream(strm)
Else
Throw New IO.FileNotFoundException(filePath)
End If
Return ret
End Function
''' <summary>
''' Prepares a OleDBCommand with parameters to add an attachment to record
''' </summary>
''' <param name="connection">OleDbConnection to use</param>
''' <param name="attachmentFieldname">Name of attachment field</param>
''' <param name="tableName">DB Table name</param>
''' <param name="attachmentFileData">the raw bytes that comprise the file to attach</param>
''' <param name="attachmentFileName">a name for this attachment</param>
''' <param name="pkName">the primary key field name</param>
''' <param name="pkType">the type of the primary key, typically OleDbType.Integer</param>
''' <param name="pkValue">primary key value of the racord to add attachment to</param>
''' <returns>prepared OleDbCommand</returns>
Public Shared Function AddAttachmentCommand(connection As OleDbConnection,
attachmentFieldname As String,
tableName As String,
attachmentFileData As Byte(),
attachmentFileName As String,
pkName As String,
pkType As OleDbType,
pkValue As Object) As OleDbCommand
Dim insertCommand As New OleDb.OleDbCommand()
insertCommand.CommandText = String.Format("INSERT INTO [{0}] ([{0}].[{1}].FileData, [{0}].[{1}].FileName) VALUES (?, ?) WHERE ([{0}].[{2}]=?)", tableName, attachmentFieldname, pkName)
insertCommand.Connection = connection
' Parameter Order: FileData, FileName, pk
Dim paramData As New OleDbParameter("FileData", OleDbType.Binary)
paramData.Value = PrependFileDataHeader(attachmentFileData)
insertCommand.Parameters.Add(paramData)
Dim paramName As New OleDbParameter("FileName", OleDbType.WChar)
paramName.Value = attachmentFileName
insertCommand.Parameters.Add(paramName)
insertCommand.Parameters.Add(pkName, pkType).Value = pkValue
Return insertCommand
End Function
''' <summary>
''' Prepares an OleDBCommand that removes the specified attachment from a record.
''' </summary>
''' <param name="connection">OleDbConnection to use</param>
''' <param name="attachmentFieldname">Name of attachment field</param>
''' <param name="tableName">DB Table name</param>
''' <param name="attachmentFileName">the attachment name as received from Access</param>
''' <param name="pkName">the primary key field name</param>
''' <param name="pkType">the type of the primary key, typically OleDbType.Integer</param>
''' <param name="pkValue">primary key value of the racord to delete attachment to</param>
''' <returns>prepared OleDbCommand</returns>
Public Shared Function DeleteAttachmentCommand(connection As OleDbConnection,
attachmentFieldname As String,
tableName As String,
attachmentFileName As String,
attachmentFileData As Byte(),
pkName As String,
pkType As OleDbType,
pkValue As Object) As OleDbCommand
' Note: Eventhough Access acts like FileName is the pk for attachments,
' we need to include filedata in contraints, orelse all attachments for record are deleted.
Dim deleteCommand As New OleDbCommand()
deleteCommand.CommandText =
String.Format("DELETE [{0}].[{1}].FileData From [{0}] WHERE ( ([{0}].[{2}] = ?) AND ([{0}].[{1}].FileName = ?) AND ([{0}].[{1}].FileData = ?) )",
tableName, attachmentFieldname, pkName)
deleteCommand.Connection = connection
' Parameter Order: pk, FileName
Dim paramPK As OleDbParameter = deleteCommand.Parameters.Add(pkName, pkType)
paramPK.Value = pkValue
Dim paramName As New OleDbParameter("FileName", OleDbType.WChar)
paramName.Value = attachmentFileName
deleteCommand.Parameters.Add(paramName)
Dim paramData As New OleDbParameter("FileData", OleDbType.Binary)
paramData.Value = attachmentFileData
deleteCommand.Parameters.Add(paramData)
Return deleteCommand
End Function
End Class ' Attachments
Public NotInheritable Class Connect
Public Shared Function ConnectionString(dbFilePath As String) As String
Return String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='{0}';Persist Security Info=True", dbFilePath)
End Function
End Class
For the purpose of the demo usage code shown below based on your table schema, the above classes are in the namespace AccessUtilities. The Button handler adds a new record and an attachment. The Button2 handler demonstrates how to delete an attachment. I have never figured out how to modify (Update) an attachment. If you need to do that, just delete the existing one and add a new one with the revised info.
' Table Schema
' Field Type
' -------------- --------------------
' ID AutoNumber
' ClientName Text
' Attachments Attachment
Public Class Form1
Const pkName As String = "ID"
Const pkDataType As OleDb.OleDbType = OleDb.OleDbType.Integer
Const attachmentFieldName As String = "Attachments"
Const tableName As String = "MyTable"
Const dbPath As String = "C:\Users\UserName\Documents\DBs\Access\AttachmentDemoTest.accdb"
' adds a empty record and returns its PK (ID)
Private Function AddNewRecord(clientName As String) As Int32
Dim ret As Int32
Using conn As New OleDb.OleDbConnection(AccessUtilities.Connect.ConnectionString(dbPath))
Dim insertCommand As New OleDb.OleDbCommand()
insertCommand.CommandText = String.Format("INSERT INTO {0} (ClientName) VALUES (?)", tableName)
Dim paramDesc As OleDb.OleDbParameter = insertCommand.Parameters.Add("Description", OleDb.OleDbType.VarWChar)
paramDesc.IsNullable = True
paramDesc.Value = DBNull.Value
insertCommand.Connection = conn
conn.Open()
ret = insertCommand.ExecuteNonQuery
If ret = 1 Then ' one record inserted, retrieve pk
insertCommand.CommandText = "Select @@IDENTITY"
insertCommand.Parameters.Clear()
ret = CInt(insertCommand.ExecuteScalar)
End If
conn.Close()
End Using
Return ret
End Function
Private Sub AddRecordAndAttachment(clientName As String, fileBytes As Byte(), fileName As String)
Dim id As Int32 = AddNewRecord(clientName)
' adding an attachment only needs a way to refer to the record to attach to
' this is done using the pk (ID)
Using conn As New OleDb.OleDbConnection(AccessUtilities.Connect.ConnectionString(dbPath))
Dim attachCommand As OleDb.OleDbCommand = AccessUtilities.Attachments.AddAttachmentCommand(conn, attachmentFieldName, tableName, fileBytes, fileName, pkName, pkDataType, id)
conn.Open()
Dim result As Int32 = attachCommand.ExecuteNonQuery()
conn.Close()
End Using
End Sub
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
' demo adding new record and attachment
Dim fileBytes As Byte() = IO.File.ReadAllBytes("C:\Users\UserName\Pictures\BottleTop.png")
AddRecordAndAttachment("Fred Inc.", fileBytes, "FredsPic.png")
End Sub
Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
Dim id As Int32 = 1 ' assumed - change as needed
' need to retrieve FileData and FileName from an attachment
Using conn As New OleDb.OleDbConnection(AccessUtilities.Connect.ConnectionString(dbPath))
' retrieve all attachments for id
Dim selectCommand As New OleDb.OleDbCommand
selectCommand.CommandText = String.Format("Select {0} From [{1}] Where [{1}].[{2}]=?", AccessUtilities.Attachments.ExpandAttachmentFieldNames(attachmentFieldName, tableName), tableName, pkName)
selectCommand.Parameters.Add("ID", OleDb.OleDbType.Integer).Value = id
selectCommand.Connection = conn
Dim dt As New DataTable
Dim da As New OleDb.OleDbDataAdapter(selectCommand)
da.Fill(dt)
If dt.Rows.Count > 0 Then ' we have attachments
' delete the 1st attachment retrieved
Dim accessFileData As Byte() = CType(dt.Rows(0).Item(String.Format("{0}.{1}.FileData", tableName, attachmentFieldName)), Byte())
Dim accessFileName As String = CStr(dt.Rows(0).Item(String.Format("{0}.{1}.FileName", tableName, attachmentFieldName)))
Dim deleteCommand As OleDb.OleDbCommand = AccessUtilities.Attachments.DeleteAttachmentCommand(conn, attachmentFieldName, tableName, accessFileName, accessFileData, pkName, pkDataType, id)
conn.Open()
Dim result As Int32 = deleteCommand.ExecuteNonQuery() ' if = 1 then attachment deleted
conn.Close()
End If
End Using
End Sub
End Class
You may find the following article of interest as well.
Using multivalued fields in queries
