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

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 already apparently 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

Open in new window

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;

Open in new window

Thank you for taking the time to read this and providing an answer.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
If I were you, I would move to the "Attachment" datatype in Access.
This is only available if you move to the new .accdb format.

Or simply "Link" to the file, (since you already have the Path.)
Linking will not cause as much bloat and overhead as "storing" the file in the database (like OLE and attachment datatypes)
simple sample is attached.

ste5anSenior DeveloperCommented:
Have you calculated the required space? 30k * 2MB = 0,6GB.  

Just to be sure that you don't hit the 2GB limit (1GB when hosted on Azure).
Jeffrey CoachmanMIS LiasonCommented:
The issues with "storing" the file in the database are:
1. It will always "bloat" the database.  This is true to a lesser extent with the new attachment datatype, ...but it still happens.
2. The need always arises to "extract" the file. This is tricky with OLE fields, ...but somewhat easier with attachment datatypes.
3. Users are never really sure if they are working with a stored file of or a linked file.
If the file is on the server and you "embed" it, you are really creating a "Copy" of the file.
Thus, when users open the"Copy" in the database, and edit it, ...the changes are not reflected in the file on the server.
Complicating this more is the fact that you can also "Link" the file using an OLE Datatype, but you have to be aware of the little "Link" check-box when you are going through he wizard.

Al in all,, most developers will opt to  "Link" to the file (as the sample I posted illustrates)
As cool as the new Attachment datatype is,, (currently), is not supported by any other database application.
So when you have to upgrade, you cannot convert this to SQL Server, My SQL, Oracle, ...etc

BTW, you can search here, on Google or YouTube for hits on: "Access Attachment DataType"

Hope this helps.

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Jeffrey CoachmanMIS LiasonCommented:
What ste5an posted above: yet another reason to store the "Link" (full path), ..and not store the actual File.
srellAuthor Commented:
Unfortunately, I don't have an option other than what I asked for.  This is an existing program written to work with OLE Blobs only.  That's why I'm looking for the solution to the question asked above.  Fortunately, the images are all on average 100K ea and I'm using accdb instead of mdb.
This is an existing program written to work with OLE Blobs only.
And just what does the program do with those OLE BLOBS?
I'd post a question about re-writing that to work with files instead.

There is very little you cannot do with images using the WIA library.
And at the moment, you do not have the OLE BLOBS, so I am confused as to how existing code is built to use non-existent data.

Care to sketch us a bigger picture of your issues?
Because you really don't want Blobs or attachments.
They are both evil, one a lesser evil than the other.
srellAuthor Commented:
For those who are curios - I have two commercial programs of which I am not the author.  They both use blobs.  Unfortunately, they do not have a migration option from one DB to the other.  I am forced to export from the old program data to an excel spreadsheet.  The new program does not create DBs, so I must use access to create a new ACCDB and import the data from the spreadsheet.  Fortunately, the old program exports the photos as files and provides a unique links them in the spreadsheet.  The new program uses the new ACCDB and expects photos to be in an OLE Blob.

I wish I had other options, but unfortunately this is the hand I was dealt and I need to work within those constraints.
What I think I want is an SQL statement that will execute against every row in Table1 using FileToBlob() or something close to it.


What your other contributor provided is code to run down a recordset of the filepaths and create a BLOB from each file.
And that is what you need, is VBA code, and a place to kick it off from, typically a button on a form.
Since the form is a one-off, it doesn't need to be pretty --since you'll likely delete it after it is done.

But what's asked in the link and what you really need to do are different.
This is the code needed, from your link
Private Function LoadPicIntoDatabase(sFilePathAndName As String) As Boolean
On Error GoTo ErrHandler

    'Test to see if the file exists. Exit if it does not.
    If Dir(sFilePathAndName) = "" Then Exit Function

    LoadPicIntoDatabase = True

    'Create a connection object
    Dim cn As ADODB.Connection
    Set cn = CurrentProject.Connection

    'Create our other variables
    Dim rs As ADODB.Recordset
    Dim mstream As ADODB.Stream
    Set rs = New ADODB.Recordset

    'Configure our recordset variable and open only 1 record (if one exists)
    With rs
        .LockType = adLockOptimistic
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .Open "SELECT TOP 1 * FROM tblArticles", cn
    End With

    'Open our Binary Stream object and load our file into it
    Set mstream = New ADODB.Stream
    mstream.Type = adTypeBinary
    mstream.LoadFromFile sFilePathAndName

    'add a new record and read our binary file into the OLE Field
    rs.Fields("olepicturefield") = mstream.Read

    'Edit: Removed some cleanup code I had inadvertently left here.

    On Error Resume Next
    Set mstream = Nothing
    Set rs = Nothing
    Set cn = Nothing

    Exit Function

    MsgBox "Error: " & Err.Number & " " & Err.Description
    LoadPicIntoDatabase = False
    Resume Cleanup

End Function

Open in new window

On your form, you're going to put a button.
The button will create a recordset with all the proper data and filenames
The code will then progress down that recordset, calling LoadPicIntoDatabase once for each record, popping the file into the OLE field.

Alternatively, you can bind all the data to the form.
You can then make the form walk from first record to last, and the code to load the OLE object can be in the Current event.
srellAuthor Commented:
Thanks for the reply.  No sure how to implement (what I was referring to earlier as my lack of experience with Access).

I know about F-11 and to add your code to a new module.  I then saved the module.  It showed up in access under "Unrelated Objects".

I then went to Create -> Form and add a button.  In the Event property sheet, I choose "Event Procedure" and then click the three dots "...".  It is looking for a Command or a function with _click in it.  Can you please explain how to create the button correctly that points to the function?

Do I simply create a click function that calls LoadPicIntoDatabase()?  If so, what does that command look like?

Private Sub Form_Click()
    Call LoadPicIntoDatabase(Table1.Photo Path)
End Sub

Open in new window

Also, I don't see in your code where it pulls the path from "Photo Path".  I see it's a variable that is passed to the function.  How do I get it to automatically look at "Photo Path:?
but apparently my gaps in Access are too severe
With due respect, we aren't talking about gaps, as that suggests solidity with some deficiencies.
You are missing some pretty fundamental skills, though its clear
I see it's a variable that is passed to the function.
you have some facility with things other than Access.

So, let's start with the basics: Can you create a query that will have Photo, PhotoPath and whatever is the primary key for the table?  And then can you use the Form Wizard to create a continuous form using that query as its source?
srellAuthor Commented:
Yes, have that on the screen now.

I should mention that I tried calling the function by adding a button then in the On Click I added ==[LoadPicIntoDatabase], which returns an error.  So I figured it was something I was doing wrong.
Are the form headers and footer visible?
If not, make them visible.
Add a command button to the header
Right-click the button
Name it cmdCreateBlobs
Use the dropdown of the Click event to make that say [Event Procedure]
Then click the three dots at the end of the line.
The code window will open

You will be in
Private Sub cmdCreateBlobs_Click()

End sub

Post an image when you get that far!
srellAuthor Commented:
Ok, done it.  Ready for next step.

srellAuthor Commented:
Well, it's apparent that no-one here knows how (or is willing) to do this and can only offer the same advise (which wasn't the question) about how evil it is to work with blobs just like all the other websites I visited.  Especially Nick67 who talks up a good story but has me sitting right where I've been sitting for the past 10 days (your silence speaks volumes).  You know, when someone is asking for a solution, they really are just looking for a solution, not why the question they're asking is wrong and then to tell them that they should be asking another question that has already been answered numerous times on this website and other websites (laughable really).   Now that I actually need this to continue my overall project, I've taken the time learn enough about Access and come up with a solution on my own (I was hoping for some time savings by getting help from a community of Access experts).  Up until 2 weeks ago, I had never touched Access.

For those who 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
        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)
                    ReDim byteData(1 To LOF(nFileNum))
                    Get #nFileNum, , byteData
                        rstTable![Photo] = byteData
                    MsgBox ("Error:  empty file, can't load for Name = " & rstTable![strName] & " and Photo Path = " & rstTable![Photo Path])
                End If
                Close nFileNum
                MsgBox ("Error:  File not found for Name = " & rstTable![strName] & " and Photo Path = " & rstTable![Photo Path])
            End If
    End If
    If nFileNum > 0 Then Close nFileNum
    strStatus = " "
    varStatus = SysCmd(acSysCmdSetStatus, strStatus)
    Exit Sub
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "Error on " & strFile
    Resume LoadFileExit
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Well, old son,  I missed your 24-Jun-15 post.  Since then, I've dropped $2000 and 1000 miles worth of travel and missed three days at work over an alternator dropped in my van.  I apologise.  I am glad you got it flanged up.  I got that you are locked into blobs and using Access as an ETL platform.  Again,  I am sorry I missed your post, and sorrier yet that you did not 'bump'

srellAuthor Commented:
No one else gave an actual solution, so I had to develop it myself.  It works as requested.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.