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

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.

JeffCoachman
AccessBasicLinkFormReportImages.mdb

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).

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, ...it, (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.

JeffCoachman

Note,...
What ste5an posted above:
https://www.experts-exchange.com/questions/28691948/Access-2010-Store-JPG-as-OLE-Object-using-filename-path-from-field-in-same-record.html?anchorAnswerId=40848480#a40848480
...is yet another reason to store the "Link" (full path), ..and not store the actual File.

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.

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.

Nope.

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.Open
    mstream.Type = adTypeBinary
    mstream.LoadFromFile sFilePathAndName

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

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


Cleanup:
    On Error Resume Next
    rs.Close
    mstream.Close
    Set mstream = Nothing
    Set rs = Nothing
    Set cn = Nothing

    Exit Function

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

End Function

Select allOpen 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.

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

Select allOpen 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?

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.

Alright.
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!
blob1.jpg

Ok, done it.  Ready for next step.

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'

Nick67

No one else gave an actual solution, so I had to develop it myself.  It works as requested.