ACC: How to Retrieve the Path for Linked OLE Objects (7.0/97) (170531)

MORE INFORMATION

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 which 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 a result that consists of 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 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.