ACC2000: How to Retrieve the Path for Linked OLE Objects

• Microsoft Access 2000

SUMMARY

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.

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 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.

REFERENCES

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.

Additional query words:

Keywords          : kbole kbdta IntpOle 
Version           : WINDOWS:2000
Platform          : WINDOWS 
Issue type        : kbhowto 

Last Reviewed: May 18, 1999