Export OLE fields in Microsoft Access

I found on the Internet a number of discussions on exporting embedded OLE fields out of Microsoft Access but either there was no solution or you have to buy some software to complete the task. I was aware of a free solution but was too busy to write it up until now.

In the olden days of Windows, there was no binary objects or attachments in Access, OLE objects was the way to go. OLE stands for Object Linking and Embedding(1, 2) and you can just treat all types of different documents — Word, Excel, PowerPoint as one single data type — OLE. As we will see, in an Access form, you can embed and edit that file within the form. When I discovered this feature back in Access 97 where GUI was still a new thing, I was impressed. Windows 95 was the first Microsoft operating system that booted into a GUI by default. This OLE embedding in Access form demonstrated the power GUI that I had never thought of before.

Back to the task, to create a simple illustration, I create a table with a primary key and an OLE object field in Access:

And most of the OLE field functions are provided in the form. Some people tried to export the field using DAO, Recordset, AppendChunk and GetChunk in VBA but it was quite difficult. I would suggest to open up the database in Microsoft Access and use form instead. With the Form Wizard, we can quickly create the following form:

With an empty record, open the mouse right-click menu above OLEField, select “Insert Object” and then you can create the object listed in the dialog box shown above and embed that object in the OLE field. If you double click OLEField, you can have the corresponding application embedded in the form and actually edit the file on the fly.

If you already have an OLE object embedded in the field, the right-click menu will allow you to open the object in its corresponding application in a separate window, then you can manually save the document within that application. Export done!

So if you just have a few records, you can do it manually, but if you have hundreds of record, you may need to automate this export process. With reference to https://www.tek-tips.com/viewthread.cfm?qid=1240667, we can create a button CmdExport in the form with the following code:

Private Sub CmdExport_Click()
    Set OLEobj = Me.OLEField
    OLEobj.SaveAs "Test" & Me.ID.Value & ".docx"
    Set OLEobj = Nothing
End Sub

When you press this button, a Word document file will be saved to the “Document” folder. The name of the file includes the primary key, as we are going to export many records and I have to make sure the file name is unique.

The next part is to loop through the records in the form. With reference to https://stackoverflow.com/questions/6360051/access-move-to-next-record-until-eof, we can create another button CmdLoop with the following code:

Private Sub CmdLoop_Click()
    While Me.CurrentRecord <= Me.Recordset.RecordCount
        DoEvents
        CmdExport_Click
        DoEvents
        DoCmd.GoToRecord Record:=acNext
    Wend
End Sub

You can start at whatever position within the records inside the form and click this button. The program should export all the OLE objects from the position on to the end of the records. Manipulating OLE objects can be slow, in some systems you need to add some DoEvents to keep out errors.

One shortcoming of the method above is that not all OLE object support the “.SaveAs” method. Another way to phrase this problem is that you have to customise the code according to the object model of the OLE object.

Let’s take Excel as an example. For Excel, the OLE object that you can add to the OLE field is only an Excel worksheet, saving just the worksheet is not a complete Excel file and so the Excel application will not open it. One possible solution is to copy the worksheet in the OLE field into a new Excel workbook, then save that workbook. You can use the following code:

Private Sub CmdExport_Click()
    Me.OLEField.Object.ActiveSheet.Copy
    With Me.OLEField.Object.Application.ActiveWorkbook
        .SaveAs FileName:="Test" & Me.ID.Value & ".xlsx", FileFormat:=51
        .Close SaveChanges:=False
    End With
End Sub

(Just for completeness, the name of the constant for FileFormat:=51 is xlOpenXMLWorkbook. I just do not want to add a reference in VBA for something so trivial.)

In conclusion, to export OLE objects in a Microsoft Access file, open this file with the Access application and create a corresponding form with all relevant fields. Use the VBA code provided above and you should be able to export some of the most common file types in an automated fashion. Good luck.