how to create an ole object from a file - Ms-access

Question

I have a table with an embedded picture(OLE) coulmn.

I Want to be able to insert a new record via a form with a browse option.

Anyway, I have a file name, And I need to turn it into an ole object and insert it into the form. how do I do that in VBA?

To clarify - I need to turn a file name, into an ole object with that file, and than insert it into the table.

Thanks, Fingerman.

EDIT:

Ok, as pointed out by @HansUp I need to explain. In my form, I have a bound OLE object that is not bound to a field, but to a dlookup function. I get the correct id into a controll via a query and a combo box - so the controller is bound to:

=DLookUp("picture","articles","id=" & [articles])

note that articles is not a field but a controller, I don't know if that does any diffrence.

Every time that controller is changed I use me.recalc so that the bound OLE may update it's value.

Anyway, I thought to do this just by VBA and a user entering a file adress,without using the controller but some kind of INSERT or somthing, but other options would be welcome.

If I am not clear, ASK! I will clarify and fix myself.

EDIT 2:

So how is the filename acquired or derived? Are you expecting to use the ArticleID? Is the picture always at an expected location with an expected file name? What exactly do you want to do if you don't use the Browse button? Are you looking for something fully automated based on the folder and file name or are you looking for something like drag and drop?

the filename is aquired via a browse option, I have it implented. for the sake of simplicty - let's say the user has to enter the file name themselves to a text box. now - I want that on a click of a button, I could insert that file name as an embedded ole object - to my database. I'm not looking for any automation nor drag and drop (But, If drag & drop works, It would be great). The first edit is about the ole controller since someone asked. he thought my question could be resolved using that controller - so I gave details on how I am desplaying the picture. I don't think it has any relevance but If someone could use that, it would be fine by me. I am expecting to use an articleID for the update - but again, I don't see how this relates to the question.

I am beginning to think this might be not possible..... :( This is unfortunte since the porblem is fairly stright-forward. you have a file-name, you need it embeeded as an OLE object in your database.

Answer 1

Before providing my answers, I'm going to take a quick gander at rephrasing your question and it's requirements. It sounds to me like you want to be able to load binary file objects, in this case pictures, using VBA, an OLE Object field in your table, and a Bound Object Frame.

Your best option is to stop trying to use a bound object frame because it has far too many limitations.

There are basically two recommended methods for what you're trying to do.

1) Store only a link to the image file and then use an image control (it can be bound to your picture field) to display the image.

2) Store the image in an OLE Object field using code to read the image in as binary data. When you need to display the image you'll need to write it out to a temp file and then you can set the Picture property on an Image Control to the full path and filename for the temporary image file. It will be up to you to manage the image file as a temp file. You could use Windows' temp directory or you can simply write out to the same file name every time you need to display an image.

Neither of these techniques are overly difficult. There's a really good article here to help you further understand what I'm talking about: http://www.jamiessoftware.tk/articles/handlingimages.html

Here's a function to read in binary data (in this case your picture file) and another function to write out binary data: http://www.ammara.com/access_image_faq/read_write_blob.html This works well for writing your picture out to a "temp" file. Then all you have to do is set the Picture property on your image control to be the file path and name of your temp file.

You can also read and write binary data using an ADO Stream object, together with an ADO RecordSet Object and ADO Connection Object. You'll have to set a reference in Access to Microsoft ActiveX Data Objects 2.8 Library.

Here's some code to add pictures to the database using ADO:

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


Private Sub Command0_Click()
    If IsNull(Me.txtFilePathAndName) = False Then
        If Dir(Me.txtFilePathAndName) <> "" Then
            If LoadPicIntoDatabase(Me.txtFilePathAndName) = True Then
                MsgBox Me.txtFilePathAndName & " was successfully loaded into the database."
            End If
        End If
    End If
End Sub

Edit1:

As per your request, here's code to lookup/load a picture for a given article. For the sake of consistency, I've also changed my table and field names above to better reflect your project and to match the code below. I tested this code and it worked properly for me.

Private Sub Command1_Click()
    If IsNull(Me.txtArticleID) = False Then
        If DCount("articleid", "tblArticles", "articleid = " & Me.txtArticleID) = 1 Then
            Dim rs As DAO.Recordset, sSQL As String, sTempPicture As String
            sSQL = "SELECT * FROM tblArticles WHERE ArticleID = " & Me.txtArticleID
            Set rs = CurrentDb.OpenRecordset(sSQL)
            If Not (rs.EOF And rs.BOF) Then
                sTempPicture = "C:MyTempPicture.jpg"
                Call BlobToFile(sTempPicture, rs("olepicturefield"))
                If Dir(sTempPicture) <> "" Then
                    Me.imagecontrol1.Picture = sTempPicture
                End If
            End If
            rs.Close
            Set rs = Nothing
        Else
            MsgBox "Article Not Found"
        End If
    Else
        MsgBox "Please enter an article id"
    End If
End Sub

Private Function BlobToFile(strFile As String, ByRef Field As Object) As Long
    On Error GoTo BlobToFileError

    Dim nFileNum As Integer
    Dim abytData() As Byte
    BlobToFile = 0
    nFileNum = FreeFile
    Open strFile For Binary Access Write As nFileNum
    abytData = Field
    Put #nFileNum, , abytData
    BlobToFile = LOF(nFileNum)

BlobToFileExit:
    If nFileNum > 0 Then Close nFileNum
    Exit Function

BlobToFileError:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, _
           "Error writing file in BlobToFile"
    BlobToFile = 0
    Resume BlobToFileExit

End Function