Converting MS Access OLE Object[Image] to BLOB

We have saved image in the Access database as OLE Object. It is showing “Microsoft Photo Editor 3.0 Photo / Package” in the table. We want to convert it into Binary Data to the MYSQL table. Please help us to solve this problem.

Thanks in advance

Modules & VBA forum was the right place for this posting. That is why it got unanswered till now.

You save the image files as binary data in the field of the table. Don't save it as OLE. A procedure to save the .jpg image files contained in a folder named 'FolderForImages' in the working directory is given below. I suppose that the KeyFields of the table to which you want to store the images contain the names of the image files without the .jpg part. Please feel free to ask any clarifications

Public Sub ReadFile_WriteToTable()
On Error GoTo Err_ReadFile_WriteToTable
Const BlockSize = 32768
Dim BaseSource As String, Source As String
Dim NumBlocks As Integer, SourceFile As Integer, i As Integer
Dim FileLength As Long, LeftOver As Long
Dim FileData As String
Dim rs As ADODB.Recordset, Cn As ADODB.Connection, srsQL As String
Set rs = New ADODB.Recordset: Set Cn = New ADODB.Connection
Set Cn = CurrentProject.Connection
BaseSource = CurrentProject.Path & "\FolderForImages/"
srsQL = "SELECT TableToWrite.KeyField,TableToWrite.ImageField FROM TableToWrite ORDER BY TableToWrite.KeyField"
rs.Open srsQL, Cn, adOpenDynamic, adLockPessimistic
Do While Not rs.EOF
Source = BaseSource & rs!KeyField & ".jpg"
SourceFile = FreeFile
Open Source For Binary Access Read As SourceFile
FileLength = LOF(SourceFile)
If FileLength > 0 Then
' Calculate number of blocks to write and leftover bytes.
NumBlocks = FileLength \ BlockSize
LeftOver = FileLength Mod BlockSize
'First write the number bytes left over to make balance as whole blocks
FileData = String$(LeftOver, 32)
Get SourceFile, , FileData
rs!ImageField.AppendChunk (FileData)
'Remaining bytes are in whole blocks of size equal to 'Blocksize'
'The number of such blocks are 'NumBlocks'
'Write these blocks of bytes using a For..Next loop
FileData = String$(BlockSize, 32)
For i = 1 To NumBlocks
Get SourceFile, , FileData
rs!ImageField.AppendChunk (FileData)
Next i
rs.Update
Close SourceFile
'------------------------------------------------------------------------------------
Else
MsgBox "No file " & Source & "in the source directory of Image files"
End If
rs.MoveNext
Loop
rs.Close
Exit_ReadFile_WriteToTable:
Exit Sub
Err_ReadFile_WriteToTable:
MsgBox Err.Number & " - " & Err.Description
Resume Exit_ReadFile_WriteToTable
End Sub

Geekay: Thanks for your reply.
I had the same problem at work. Luckily I found this thread, when i was searching the web. The code seems logic and i will try your solution asap.

Question: You wrote in the code below ADODB connection string. I use a MySQL ODBC 3.51 driver to astablish the Access frontend to the Mysql Database. Is it still possible to use the code? My guess is that somashekhark uses that driver too.