vba 如何从文件创建 ole 对象 - Ms-access

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

提问by fingerman

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

我有一张带有嵌入图片（OLE）的表格。

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?

无论如何，我有一个文件名，我需要把它变成一个ole对象并插入到表单中。我如何在 VBA 中做到这一点？

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

澄清一下 - 我需要将文件名转换为带有该文件的 ole 对象，然后将其插入表中。

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:

好的，正如@HansUp 所指出的，我需要解释一下。在我的表单中，我有一个绑定的 OLE 对象，它没有绑定到字段，而是绑定到 dlookup 函数。我通过查询和组合框将正确的 id 输入到控件中 - 因此控制器绑定到：

=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.recalcso that the bound OLE may update it's value.

每次更改控制器时，我都会使用，me.recalc以便绑定的 OLE 可以更新它的值。

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.

无论如何，我想只通过 VBA 和用户输入文件地址来做到这一点，而不使用控制器，而是使用某种 INSERT 或其他东西，但欢迎其他选项。

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

如果我不清楚，问！我会澄清并修复自己。

EDIT 2:

编辑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?

那么文件名是如何获取或派生的呢？您是否希望使用 ArticleID？图片是否始终位于具有预期文件名的预期位置？如果您不使用“浏览”按钮，您到底想做什么？您是在寻找基于文件夹和文件名的完全自动化的东西，还是在寻找诸如拖放之类的东西？

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.

文件名是通过浏览选项获得的，我已经实现了。为简单起见 - 假设用户必须自己在文本框中输入文件名。现在 - 我想要点击一个按钮，我可以将该文件名作为嵌入的 ole 对象插入到我的数据库中。我不是在寻找任何自动化或拖放（但是，如果拖放有效，那就太好了）。自从有人问起，第一次编辑是关于 ole 控制器的。他认为我的问题可以使用该控制器解决 - 所以我详细说明了我如何显示图片。我不认为它有任何相关性，但如果有人可以使用它，我会很好。我希望使用 articleID 进行更新 - 但同样，我不明白这与问题有什么关系。

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.

我开始认为这可能是不可能的..... :( 这很不幸，因为问题相当直接。您有一个文件名，您需要将它作为 OLE 对象嵌入到您的数据库中。

回答by HK1

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.

在提供我的答案之前，我将快速重新表述您的问题及其要求。在我看来，您希望能够加载二进制文件对象，在这种情况下是图片，使用 VBA、表中的 OLE 对象字段和绑定对象框架。

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.

1）只存储图片文件的链接，然后使用图片控件（可以绑定到你的图片字段）来显示图片。

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.

2) 使用代码将图像存储在 OLE 对象字段中，以将图像作为二进制数据读取。当您需要显示图像时，您需要将其写入临时文件，然后您可以将图像控件上的图片属性设置为临时图像文件的完整路径和文件名。您可以将图像文件作为临时文件进行管理。您可以使用 Windows 的临时目录，或者您可以在每次需要显示图像时简单地写出相同的文件名。

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

这些技术都不是太难。这里有一篇非常好的文章可以帮助您进一步了解我在说什么：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.htmlThis 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.

这是一个读入二进制数据的函数（在这种情况下是你的图片文件）和另一个写出二进制数据的函数：http: //www.ammara.com/access_image_faq/read_write_blob.html这适用于将你的图片写到一个“临时”文件。然后您所要做的就是将图像控件上的图片属性设置为临时文件的文件路径和名称。

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.

您还可以使用 ADO Stream 对象以及 ADO RecordSet 对象和 ADO 连接对象来读取和写入二进制数据。您必须在 Access 中设置对 Microsoft ActiveX 数据对象 2.8 库的引用。

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

下面是一些使用 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:

编辑1：

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