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

• Microsoft Access versions 7.0, 97

SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

WARNING: ANY USE BY YOU OF THE CODE PROVIDED IN THIS ARTICLE IS AT YOUR OWN RISK. Microsoft provides this code "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose.

Microsoft Access provides no way to determine the path for a linked OLE object stored in a table. Therefore, the recommended approach is to create an additional text field in the table and 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, it is possible that 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.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

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.

REFERENCES