Parse Linked Filename

[mtompkins]

I can use a bit of help with the following:

I am trying to pull the linked filename from:

Dim dbs As Database, stPath As String

    Set dbs = CurrentDb()
    On Error Resume Next
    stPath = dbs.TableDefs(strTable).Connect
    If stPath = "" Then
        fGetLinkPath = vbNullString
    Else
        fGetLinkPath = stPath
End If
    Set dbs = Nothing
End Function

Which results in the correct string in for format of:

"Text;DSN=Kck5 Link Specification;FMT=Delimited;HDR=NO;IMEX=2;CharacterSet=437;DATABASE=C:\Documents and Settings~~~~"

The only part of this text that I need is after the = from DSN to the first space (which represents the linked CSV filename).

If someone would help me write the parse statement I'd be grateful.

 

[RoyVidar]

Which version are you on?

For 2000+, the the following should work:

[tt]fGetLinkPath = mid$(stPath,instrrev(strPath,"=")+1)[/tt]

For prior versions, you could fetch the a 2000 compatible function from RickSpr's faq faq705-4342, or you'd need to loop the string using the InStr function.

Roy-Vidar

 

[mtompkins]

Thanks Roy -

This is what happens when you don't code Access as often as you'd like - you forget the easy ones - and this is of course perfect - especially because we can use the equals sign as the reference point.

Thanks much.

 

[mtompkins]

Clearly I'm tired - I replied before I tried...

First I edited

Your code
fGetLinkPath = mid$(stPath,instrrev(strPath,"=")+1)

To...

fGetLinkPath = MID(stPath, InStrRev(stPath, "=") + 1)

This, however, does not parse the limited text needed, and also brings us to the "=" detected after "Character Set", which is

 

[RoyVidar]

I'll need to start reading a bit better, thought you where after the file name. It's "Kck5" you're after?

[tt]dim lstart as long
dim lstop as long
lstart=instr(stpath," ")
lstop=instr(stpath,"=")
fGetLinkPath = MID$(stPath,lstart,lstop-lstart)[/tt]

Roy-Vidar

 

[mtompkins]

Here's what I went with for those intested in the final:

Function fGetLinkPath(strTable As String) As String
Dim dbs As Database, stPath As String
Dim lSide As Single
Dim rSide As Single

    Set dbs = CurrentDb()
    On Error Resume Next
    stPath = dbs.TableDefs(strTable).Connect
    If stPath = "" Then
        fGetLinkPath = vbNullString
        'can change this to currentdb.name
    Else
        lSide = InStrRev(stPath, "DSN") + 4
        rSide = InStrRev(stPath, "LINK") - lSide - 1
        fGetLinkPath = MID(stPath, lSide, rSide)
    End If
    Set dbs = Nothing
End Function

 

[PHV]

An one-liner way:
fGetLinkPath = Mid(Left(stPath, InStr(stPath, " ") - 1), InStr(stPath, "DSN=") + 4)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244