Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2006
    Posts
    4

    Unanswered: How to Retrieve the Path for Linked OLE Objects

    How to Retrieve the Path for Linked OLE Objects in MS Access 2003 ?
    There is a Linked OLE Object column defined in MS Access table.
    I would like to see the linked OLE object path by executing the SQL query in MS Access 2003.

  2. #2
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    ' This code was originally written by Dev Ashish.
    ' It is not to be altered or distributed,
    ' except as part of an application.
    ' You are free to use it in any application,
    ' provided the copyright notice is left unchanged.
    '
    ' Code Courtesy of
    ' Dev Ashish
    '
    '
    Function fGetLinkPath(strTable As String) As String
    Dim dbs As Database, stPath As String

    Set dbs = CurrentDb()
    On Error Resume Next
    stPath = dbs.TableDefs(strTable).Connect
    If stPath = "" Then
    fGetLinkPath = vbNullString
    'can change this to currentdb.name
    Else
    fGetLinkPath = right(stPath, Len(stPath) _
    - (InStr(1, stPath, "DATABASE=") + 8))
    End If
    Set dbs = Nothing
    End Function

  3. #3
    Join Date
    Dec 2006
    Posts
    4
    I created the function fGetLinkPath() and exexuted the SQL query in MS Access 2003. It did not return the file path. It returns null value. I passed table name as parameter value to the function.

  4. #4
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    did u pass the name of the linked table? or local table?

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    isn't this going in the wrong direction?
    looks to me that Ashish's code uses the name of a linked table to return the file in which the original of the linked table resides.

    here we are looking for data from a field within a table.

    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Dec 2006
    Posts
    4
    yes, it is going in the wrong direction.
    I want to retrieve the Path from Linked OLE Object column in MS Access 2003. I tried the solution given for MS Access 2000 database at the following link.

    http://support.microsoft.com/kb/199066

    The solution mentioned at the above link does not work for MS Access 2003.
    I will really appreciate if some one can provide me the solution.

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Getting path/name

    Maybe this piece of code will help (there's a lot of other stuff mixed in as I copied everything):

    Public Function GetFilePath(strPath As String)
    ' Comments : Returns the path and the file name excluding the extension part of a string
    ' Parameters: strPath - string to parse
    ' Returns : path and name
    Dim SearchChar As String
    Dim strPathLength As Long
    Dim retval As Long
    Dim strFilePath As String
    strPathLength = Len(strPath)
    SearchChar = "."
    retval = InStr(1, strPath, SearchChar)
    strFilePath = left(strPath, retval - 1)
    GetFilePath = strFilePath
    End Function
    Function GetNamePart(strIn As String) As String
    Dim intCounter As Integer
    Dim strTmp As String
    For intCounter = Len(strIn) To 1 Step -1
    If MID$(strIn, intCounter, 1) <> "\" Then
    strTmp = MID$(strIn, intCounter, 1) & strTmp
    Else
    Exit For
    End If
    Next intCounter
    GetNamePart = strTmp
    End Function
    Function GetPathPart(strPath As String) As String
    ' Comments : Returns the path part of a string
    ' Parameters: strPath - string to parse
    ' Returns : path part
    Dim intCounter As Integer
    For intCounter = Len(strPath) To 1 Step -1
    If MID$(strPath, intCounter, 1) = "\" Then
    Exit For
    End If
    Next intCounter
    GetPathPart = left$(strPath, intCounter)
    End Function
    Function CanOpenTable(strDatabase As String, strTable As String) As Boolean
    End Function
    Public Function NzE(VIN As Variant, Optional strException)

    On Error GoTo Err_NzE

    If IsNull(VIN) Or VIN = "" Then
    If IsNull(strException) Then
    NzE = Null
    Exit Function
    End If
    If IsMissing(strException) Then strException = Null
    If Not strException = "" Then
    NzE = strException
    Else
    If IsNull(strException) Then
    NzE = 0
    Else
    NzE = ""
    End If
    End If
    Else
    NzE = VIN
    End If


    Exit_NzE:
    Exit Function

    Err_NzE:
    MsgBox Err.Description & " (NzE)"
    Resume Exit_NzE:

    End Function
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Dec 2006
    Posts
    4
    I executed the function GetFilePath() but did not return the file path.
    Linked Object type column value is converted it to string and passed it as a parameter to the function GetFilePath(). It did not work.

  9. #9
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Not sure as this was old code I copied.
    Perhaps
    Public Function GetFilePath(strPath As String)
    needs to be
    Public Function GetFilePath(strPath As String) as string
    and you would send it a string
    (ie

    dim SP as variant
    SP = getfilepath(txtMyLongPathName)

    where txtMyLongPathName = c:\MyPrograms\MyTest\MyFile.txt

    I think this was old code I copied from an old mdb.
    Last edited by pkstormy; 12-20-06 at 09:55.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •