OLE Object File Path and Name Retrieval 1

[BakerUSMC]

Hello to all,

I have a database that contains an OLE Object field to display pictures. Well, now that the database is getting too large, I'd like to use hyperlinks to view.

I know how to setup the hyperlinks with no problem. But I'd like to be able to get the file path and name via VBA for each OLE object and put it into 2 other fields so that I can use the hyperlinks.

Does anyone know a way to get the file path and name of an OLE Object?

Thanks!!!!

 

[BakerUSMC]

Please, Can anyone help?

 

[VBslammer]

I've used this on images before and it worked ok, although the paths were returned in dos format, ie:

[tt]C:\DOCUME~1\UserName\MYDOCU~1\MYPICT~1\TekTips.gif[/tt]

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
     [green]' Convert string to Unicode.[/green]
     strChunk = StrConv(objOLE, vbUnicode)
     pathStart = InStr(1, strChunk, ":\", 1) - 1
  
     [green]' If mapped drive path not found, try UNC path.[/green]
     If pathStart <= 0 Then pathStart = InStr(1, strChunk, "\\", 1)
  
     [green]' 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.[/green]
     If pathStart > 0 Then
        pathEnd = InStr(pathStart, strChunk, Chr(0), 1)
        Path = mid(strChunk, pathStart, pathEnd - pathStart)
        GetLinkedPath = Path
     End If
  Else
     GetLinkedPath = Null
  End If
End Function

You can test it like this:

Sub GetOLEPath()
  Dim rst As DAO.Recordset
  Dim db As DAO.Database
  
  Set db = CurrentDb
  Set rst = db.OpenRecordset("Select [OLEfield] From [MyTable]")
  While Not rst.EOF
    Debug.Print GetLinkedPath(rst![OLEfield])
    rst.MoveNext
  Wend
  Set rst = Nothing
  Set db = Nothing
End Sub

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[BakerUSMC]

VBSlammer,

Thanks for your post... but a question: Where do I place the 2 pieces of code? In a module? Behind a command button?

Thanks again

 

[VBslammer]

Put them both in a standard module, then type:
[tt]GetOlePath[/tt]
in the immediate window. If it works the paths will be debug.printed to the immediate window. You need to change the SQL in the test procedure so it references an actual OLE field in you table.

If it works, you can change the procedure to write the values into your table instead of the debug window.

Private Sub cmdUpdatePaths_Click()
  Dim rst As DAO.Recordset
  Dim db As DAO.Database
  Dim strPath As String
  
  Set db = CurrentDb
  Set rst = db.OpenRecordset("Select [OLEfield], [NewPathField] From [MyTable]")
  While Not rst.EOF
    strPath = GetLinkedPath(rst![OLEfield])
    If Len(strPath) > 0 Then
      rst.Edit
      rst.Fields("NewPathField") = strPath
      rst.Update 
    End If
    rst.MoveNext
  Wend
  Set rst = Nothing
  Set db = Nothing
End Sub

VBSlammer

"You just have to know which screws to turn." - Professor Bob