ACC2000: How to Retrieve the Path for Linked OLE Objects

Microsoft Access does not have a feature that enables you to determine the path for a linked OLE object stored in a table. The recommended approach to accomplish this task is to create an additional Text field in the table and to store the path to the linked OLE object in that field.

This article demonstrates how to use a Visual Basic for Applications procedure to retrieve the path for a linked OLE object from the object's OLE header. However, this procedure may not work in some circumstances. Storing the path in a separate Text field when you link the OLE object is the best way to ensure that you will be able to retrieve the path later.

Microsoft Access stores the path of a linked OLE object as part of the object's definition in the OLE object field. Because the definition of OLE object storage is not documented, the following procedure searches the object's OLE header for characters consistent with a file path.

Specifically, the procedure searches for the first occurrence of the string ":\", which indicates a mapped drive path. From this, it derives the drive letter by obtaining the character that immediately precedes the ":\" occurrence. Then, it derives the remainder of the path by searching for the first null character following the ":\" occurrence. If the procedure cannot find an occurrence of the string ":\", it searches for the first occurrence of the string "\\", which indicates a UNC path. From this, it derives the remainder of the path by searching for the first null character following the "\\" occurrence. If the procedure does not find an occurrence of "\:" or "\\", it assumes the object is not linked and returns a Null value.

NOTE: It is possible for the OLE header to contain an occurrence of either string pattern in a location that does not store the path to the linked object. If this occurs, the procedure returns garbage characters rather than the path to the linked object.

To programmatically determine the path for a linked OLE object, follow these steps:

1. Open the sample database Northwind.mdb.
2. Create a new module and type the following line in the Declarations section if it is not already there:

   Option Explicit
   					

3. Type the following procedure:

   Function GetLinkedPath(objOLE As Variant) As Variant
      Dim strChunk As String
      Dim pathStart As Long
      Dim pathEnd As Long
      Dim path As String
      If Not IsNull(objOLE) Then
         ' Convert string to Unicode.
         strChunk = StrConv(objOLE, vbUnicode)
         pathStart = InStr(1, strChunk, ":\", 1) - 1
   
         ' If mapped drive path not found, try UNC path.
         If pathStart <= 0 Then pathStart = _
                          InStr(1, strChunk, "\\", 1)
   
         ' If either drive letter path or UNC path found, determine
         ' the length of the path by searching for the first null
         ' character Chr(0) after the path was found.
         If pathStart > 0 Then
            pathEnd = InStr(pathStart, strChunk, Chr(0), 1)
            path = Mid(strChunk, pathStart, pathEnd - pathStart)
            GetLinkedPath = path
            Exit Function
         End If
      Else
         GetLinkedPath = Null
      End If
   End Function
   					

4. Close and save the module as Module1.
5. Open the Categories form in Design view.
6. Add a text box with the following properties:

      Text box:
      Name: RetrieveLinkedPath
      ControlSource: =GetLinkedPath([Forms]![Categories]![Picture])
      Width: 2.5"
   					

7. Open the Categories form in Form view, and go to a new record.
8. In the Category Name field, type Test. (The Category Name field is a required field.)
9. Click the Picture OLE object frame so that it is the selected control.
10. On the Insert menu, click Object.
11. In the Insert Object dialog box, select Create From File, and then click Browse.
12. Browse your Windows folder, select a .bmp file, and then click Open.
13. Click to select the Link check box, and then click OK.
14. On the Records menu, click Save Record, or move the focus by clicking another control.
    
    Note that after the OLE object is inserted, the path to the linked object is displayed in the text box.

For more information about the StrConv function, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type StrConv in the Office Assistant or the Answer Wizard, and then click Search to view the topic.