使用VBA在Access字段中保存OLE对象 [英] Saving OLE Objects in Access field with VBA

问题描述

我知道这个主题已经广泛涵盖，但是我无法找到解决我特定问题的方法.

I know that this subject has been covered to large extend, but I have not been able to find a solution to my particular problem.

我有一个表，该表的OLE对象数据类型为列附件.该表的后端是附件列的VARBINARY(MAX)数据类型的SQL Server表.

I have a table with a column Attachments of OLE Object data type. The back end of the table is SQL Server table with VARBINARY(MAX) data type for Attachments column.

如果我在Access中的附件字段上单击鼠标右键，则会弹出一个菜单，其中包含插入对象... 的选项.在此路径下，我可以插入一个字段中的文件.

If I right-click on the Attachments field in Access, a menu pops-up with an option to Insert Object... Following this path I could insert a file in the field.

只需双击该字段即可打开以这种方式插入的文件以进行查看和编辑.

The file inserted this way could be opened for viewing and editing just by double-clicking the field.

现在.我需要使用VBA进行相同的操作.我需要获取文件列表，并将其插入到相应行的附件字段中.这不是一项艰巨的任务，因为众所周知，如何使用ADODB.Stream在字段中插入文件.以下是尝试该概念的简单代码:

Now. I need to do the same using VBA. I need to take the list of files and insert them in the Attachments field in the appropriate rows. This should not be a difficult task as it is widely known how to insert a file in a field using ADODB.Stream. The following is a simple code to try the concept:

Private Sub POC()

    Dim db As DAO.Database
    Dim rsa As DAO.Recordset
    Dim stream As ADODB.stream

    Set db = CurrentDb()
    Set rsa = db.OpenRecordset("ZipCodeAttachments", dbOpenDynaset, dbSeeChanges)

    rsa.MoveFirst
    rsa.MoveNext

    rsa.Edit

    Set stream = New ADODB.stream
    stream.Type = adTypeBinary
    stream.Open
    stream.LoadFromFile Application.CurrentProject.Path & "\Attachments\537.zip"

    rsa.Fields("Attachments").value = stream.Read

    rsa.Update
    rsa.Close

    Set rsa = Nothing
    Set dba = Nothing
End Sub

该代码在第二行的附件字段中插入一个文件.我可以验证是否已通过SSMS添加了该值.但是，当我尝试像之前对第一行所做的那样打开用于查看和编辑的字段时，这次出现错误:

The code inserts a file in the Attachments field of the second row. I could validated that value has been added via SSMS. However, when I try to open the field for viewing and editing as I did earlier with the first row, this time I am getting an error:

很明显，使用VBA保存文件的方式出了问题.

Clearly, there is something wrong with the way the file is saved with VBA.

我做错了什么?如何在VBA中获得与Access用户界面相同的结果?

What am I doing wrong? How to achieve the same result with VBA as I get with Access user interface?

推荐答案

如果要将文件存储为OLE包外壳程序对象，请进行一些GUI编码(使用OLE对象打开表单，然后使用该对象存储OLE对象)文件)是我所知道的唯一方法.

If you want to store a file as an OLE Package shell object, doing some GUI coding (opening a form with an OLE object, then using that to store the file) is the only way as far as I know.

创建一个名为 frmLoadOLEObj 的未绑定表单，在其上绑定一个名为 MyBoundOLEFrame 的OLE对象.

Create an unbound form called frmLoadOLEObj, with on it a bound OLE object called MyBoundOLEFrame.

在表单上，​​添加以下代码:

On the form, add the following code:

Public Sub SaveOLEObj(rs As DAO.Recordset, fldName As String, FileName As Variant)
    'Save the position of the recordset
    Dim bkmrk As Variant
    bkmrk = rs.Bookmark
    'Bind the form to the recordset
    Set Me.Recordset = rs
    'Move the form to the saved position
    Me.Bookmark = bkmrk
    'Bind the OLE frame to the field
    MyBoundOLEFrame.ControlSource = fldName
    MyBoundOLEFrame.Class = "Package"
    'Load the attachment into the OLE frame
    MyBoundOLEFrame.SourceDoc = FileName
    MyBoundOLEFrame.Action = acOLECreateEmbed
End Sub

然后，将文件添加到记录:

Then, to add a file to a record:

Dim rsa As DAO.Recordset
Set rsa = CurrentDb.OpenRecordset("ZipCodeAttachments", dbOpenDynaset, dbSeeChanges)
Dim frmOLE As New Form_frmLoadOLEObj
frmOLE.SaveOLEObj rs, "Attachments", Application.CurrentProject.Path & "\Attachments\537.zip"

如您所见，这是非常"hacky"的代码，因为它运行GUI操作，并且您在窗体上的代码不是窗体，而是真正的模块，但是您需要一个窗体来将控件置于因为没有表单就无法拥有控件.我宁愿有一天BLOB.

As you can see, this is very "hacky" code, because it runs GUI operations, and you have code on a form that is not a form, but really a module, but you need a form to put the control on because you can't have the control without the form. I'd rather have a BLOB any day.

这篇关于使用VBA在Access字段中保存OLE对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！