Access 2010 - Store JPG as OLE Object using filename path from field in same record

Question

I have scoured the net for days trying to figure this out, but apparently my gaps in Access are too severe and the answer eludes me. Someone has apparently already answered this question, however I'm not able utilize the information.

My specific situation:

Table1 has 30,000+ rows and multiple columns. "Photo Path" is a text field with the path and filename of an image. "Photo" is an OLE Object field currently empty.

What I would like to do is store the image specified in "Photo Path" as an OLE object in "Photo".

Table1 Current State:

Name   -     Photo Path     - Photo
Impala - C:\Cars\Impala.jpg -
Jeep   - C:\Cars\Jeep.jpg   - 

Table1 Desired Result:

Name   -     Photo Path     - Photo
Impala - C:\Cars\Impala.jpg - LONG BINARY DATA
Jeep   - C:\Cars\Jeep.jpg   - LONG BINARY DATA 

I don't know how to execute FileToBlob() against my entire database using the generously provided code. The authors seem to expect me to use a form, which I was unable to get to work as well.

What I think I want is an SQL statement that will execute against every row in Table1 using FileToBlob() or something close to it.

I've tried variations of the following statement in the SQL Query to no avail.

SELECT Table1.[Photo Path], FileToBlob(Table1.[Photo Path],Table1.Photo) As Photo
FROM Table1;

Thank you for taking the time to read this and providing an answer.

Answer 1

Had to figure this one out for myself as there were no responses. For those may follow looking for an actual answer, here it is.

I modified the code that that I found to fit my specific problem.

Create a new module and put the code below in it. If by chance the code does not work, you can try going to Tools-->References and if not already selected, select "Microsoft DAO X.x Object Library" where X.x is the latest library. If it still doesn't run you'll have to check to see if you need to select any other references.

There are so many records to go through, I felt better doing this through code instead of a query that may take a long time to execute and one won't know what is going on. In the code I have it writing to the status bar in Access so you know where you are at (but if the files are small it will probably fly by, but at least you know it is working).

To run the code, just put your cursor anywhere in the routine and I first like to press F8 which steps into the code just to make sure I'm in the right routine. Then press F5 to run the rest of the code. If you want to create a form to run the code instead you can do that too. Just create a button and on the "on click" event add the code:

 call Load_Photo()

If you want to see the status updates, make sure the main access window is visible before you run the code (If you run from a form, it will already be there).

Note I renamed the field "Name" in Table1 to "strName" because "Name" is a reserved word. I'd suggest not using "Name" as a field name. You might be OK, but you could run into issues at some point, especially when referencing the field through code. If you choose not to change the field name, change the code.

Also note that the sample code provided stored as a binary. So if you create an Access form to show the records, the image will not automatically appear - there is some other manipulation necessary that I am not familiar with off hand.

Without further ado, here's the code to solution I was looking for:

Option Compare Database Option Explicit

Public Sub Load_Photo()
On Error GoTo LoadFileError

    Dim strSQL As String
    Dim rstTable As DAO.Recordset
    Dim strStatus As String
    Dim count As Integer
    Dim strFile As String
    Dim nFileNum As Integer
    Dim byteData() As Byte
    Dim varStatus As Boolean

'
'  In case something happens part way through the load, just load photos that have not been loaded yet.
'
    strSQL = "Select [strName], [Photo Path], [Photo] from Table1 Where [Photo] is null"
    Set rstTable = CurrentDb.OpenRecordset(strSQL)
    If rstTable.RecordCount > 0 Then
        rstTable.MoveFirst
        count = 0
        Do While Not rstTable.EOF
            strFile = rstTable![Photo Path]
            If Len(Dir(strFile)) > 0 Then
                nFileNum = FreeFile()
                Open strFile For Binary Access Read As nFileNum
                If LOF(nFileNum) > 0 Then
                    count = count + 1
'
' Show user status of loading
'
                    strStatus = "Loading photo " & count & " for " & rstTable![strName] & ":  " & rstTable![Photo Path]
                    varStatus = SysCmd(acSysCmdSetStatus, strStatus)
                    DoEvents
                    ReDim byteData(1 To LOF(nFileNum))
                    Get #nFileNum, , byteData
                    rstTable.Edit
                        rstTable![Photo] = byteData
                    rstTable.Update
                Else
                    MsgBox ("Error:  empty file, can't load for Name = " & rstTable![strName] & " and Photo Path = " & rstTable![Photo Path])
                End If
                Close nFileNum
            Else
                MsgBox ("Error:  File not found for Name = " & rstTable![strName] & " and Photo Path = " & rstTable![Photo Path])
            End If
            rstTable.MoveNext
        Loop
    End If

LoadFileExit:
    If nFileNum > 0 Then Close nFileNum
    rstTable.Close
    strStatus = " "
    varStatus = SysCmd(acSysCmdSetStatus, strStatus)
    Exit Sub

LoadFileError:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "Error on " & strFile
    Resume LoadFileExit

End Sub