Read/write BLOB

Read and write BLOB (Binary Large OBject) data to OLE fields in the database. OLE fields are used for large binary images such as external files.
These fields transfer data to the table (opened as a recordset) in chunks. from: http://support.microsoft.com/kb/103257 with some enhancements

Public Function ExtractBLOB(recs As DAO.Recordset, FieldName As String, Destination As String, Optional SuppressRemoveOLEHeader As Boolean = False) As Long

Writes BLOB information stored in the specified table and field to the specified disk file.
The specified table with the OLE object field to contain the binary data must be open and the correct record navigated to prior to calling the ExtractBLOB() function.
Returns the number of bytes written to the Destination file.
This function automatically strips the OLE header appended by the bound OLE form controls, if present. See RemoveAccessOleHeader() for more information.

Public Function ExtractBLOB_ADO(recs As ADODB.Recordset, FieldName As String, Destination As String, Optional SuppressRemoveOLEHeader As Boolean = False) As Long

Identical to ExtractBLOB but uses an ADO recordset instead of DAO

Public Function ExtractBLOBToHexEncodedString(recs As DAO.Recordset, FieldName As String, Optional SuppressRemoveOLEHeader As Boolean = False) As String

Returns BLOB information stored in the specified table and field as a hex encoded string.
The specified table with the OLE object field to contain the binary data must be open and the correct record navigated to prior to calling the ExtractBLOB() function.
This function automatically strips the OLE header appended by the bound OLE form controls, if present. See RemoveAccessOleHeader() for more information.

Private Function RemoveAccessOleHeader(ByRef Data() As Byte)

When an OLE control on a form is bound to the OLE field in the table, images and other files can be cut and pasted and viewed/retrieved via the user interface.
However, in this scenario the OLE control adds a binary wrapper to the original binary data, and if the data is later read directly out of the table then the wrapper causes problems.
This function removes the OLE header from a bitmap image, if present, after which the binary data will be identical to what was originally pasted in. It does not work on other document types. It is unadvisable to use JPG in OLE fields anyway as it crashes Access if the JPG is over a certain size. This function is called by ExtractBlob on the first chunk of data read from OLE field to strip the header, if present, before saving the binary data. Note that the wrapper varies in size and content depending on the file type and can contain an embedded filesystem, so it is far from trivial to construct a wrapper to allow bound display of images.


Public Function StoreBLOB(Source As String, recs As DAO.Recordset, FieldName As String) As Long

Reads a BLOB from a disk file and stores the contents in the specified table and OLE-type field.
The specified table with the OLE object field to contain the binary data must be open and the correct record navigated to prior to calling the ReadBLOB() function.
Returns the number of bytes read from the Source file.


Public Function StoreBLOB_ADO(Source As String, recs As ADODB.Recordset, FieldName As String) As Long

Identical to StoreBLOB except that an ADO recordset is passed.


Public Function StoreBLOBFromHexEncodedString(HexString As String, recs As DAO.Recordset, FieldName As String) As Long

Reads a BLOB from a hexadecimal encoded binary data in a string and stores the contents in the specified table and OLE-type field.
The specified table with the OLE object field to contain the binary data must be open and the correct record navigated to prior to calling the ReadBLOB() function.
Returns the number of bytes read from the Source file.