Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Unanswered: Getting the db pathname for a db assigned under a DSN

    Hi all.

    I have to admit that I've used DSNs for over 7 years and have never had to ask this question: Anyone have some code or a link that can show how to pull the db path from a DSN (file, User, System, or otherwise) if at all possible?

    Thanks.

    - Mike
    Back to Access ... ADO is not the way to go for speed ...

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1

    DSN File Data Source

    Hi Mike

    I am not sure in what cirumsdtances/context you are trying to find the DSN file data source, but if you have a File DSN path/name or are wanting to find it from an active connection (File DSN or System DSN) then this seems to do it on my machine/network!

    Code:
    Sub TestDSN()
        Dim cn As New ADODB.Connection
        
        'Using DSN File on disc
        MsgBox "DSN File Data file = " & GetDSNFileSource("M:\CES Data Sources\TrendSysLink.dsn", True)
        
        'Using File DSN connection
        cn.Open "FileDSN=TrendSysLink.dsn"
        MsgBox "DSN File Data file = " & GetDSNFileSource(cn.ConnectionString)
        cn.Close
        
        'Using System DSN connection
        cn.Open "TrendSysLink"
        MsgBox "DSN File Data file = " & GetDSNFileSource(cn.ConnectionString)
        cn.Close
    
        
    End Sub
    
    Function GetDSNFileSource(ByVal strDSNPathAndFile As String, Optional ByVal IsDSNFile As Boolean = False) As String
        Dim FileNumber As Long
        Dim LineText As String
        Dim DBQPos As Long
            
        If IsDSNFile Then
            FileNumber = FreeFile   ' Get unused file number.
            If Dir(strDSNPathAndFile) = "" Then
                GetDSNFileSource = "DSN file Not found !"
            Else
                Open strDSNPathAndFile For Input As #FileNumber ' Create filename.
                LineText = ""
                Do While Not EOF(FileNumber)
                    Line Input #FileNumber, LineText
                    DBQPos = InStr(LineText, "DBQ=")
                    If DBQPos <> 0 Then
                        GetDSNFileSource = Mid(LineText, DBQPos + 4)
                    End If
                Loop
                Close #FileNumber   ' Close file.
            End If
        Else
            DBQPos = InStr(strDSNPathAndFile, "DBQ=")
            If DBQPos <> 0 Then
                Dim ColonPos As Long
                ColonPos = InStr(DBQPos, strDSNPathAndFile, ";")
                If ColonPos = 0 Then ColonPos = Len(strDSNPathAndFile)
                GetDSNFileSource = Mid(strDSNPathAndFile, DBQPos + 4, ColonPos - (DBQPos + 4))
            End If
        End If
    End Function
    This may not be fully tested/debugged but it relies on the Path/File being specified after the 'DBQ=' in the File/Connection String.

    May be I have missed the point !

    MTB

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Naw ... You didn't miss the point. My point is to use the DSN as the path marker for additional file use. Since the DSN locates where my DB is on the network, if I want to have associated pics be stored on the same drive designator (in a different directory) I need a foolproof way to get that drive ... I can't hardcode the path in my program because some people see the DB path as G: and some see it as H: ...
    Back to Access ... ADO is not the way to go for speed ...

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Maybe it would be easier to approach this by creating a connection object, hooking it up to your DSN and then pulling relevant values from the connection object instead of the DSN itself?

    MTB already hit on the problem with parsing info out of the file itself... you're not ABSOLUTELY GAURANTEED that it will have the format you're expecting.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    UPDATE:

    That does the trick. You're correct that DBQ= point to the path and db name ... InStr here I come ...

    Hey, even Grand Poobahs can't see what's right in front of them sometimes ...

    Thanks Mike!

    - Mike
    Back to Access ... ADO is not the way to go for speed ...

Posting Permissions

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