Results 1 to 10 of 10
  1. #1
    Lounger
    Join Date
    Jan 2002
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Getting the application's path at network (MS Access 97)

    Hi All,
    I have a MS Access application and the users are supposed to export and Import files using this application. This application would be located on network. The path where the files will be exported will always be the same as of the application for e.g
    L:9098909802REDSREDSUSERDonor MotivationRecptControltest
    Now I can put this path in the code and it runs just fine.
    The problem is different users have used different drives to map to this directory.
    I tried using genralised path like:
    RK8VOL8029098909802REDSREDSUSERDonor MotivationRecptControltest
    it does'nt work....
    Please suggest some options that I could use.....

  2. #2
    Lounger
    Join Date
    Jan 2002
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting the application's path at network (MS Access 97)

    I realised all I want to solve this problem is the application path.
    Here is the code that I used to get the path of the mdb:

    Public Function GetAPath()
    Dim FullPath$, AppPath$
    Dim DB As Database
    Dim iloc As Integer
    Dim X As Integer

    Set DB = CurrentDb


    FullPath = DB.Name

    For X = Len(FullPath) To 1 Step -1
    iloc = InStr(X, FullPath, "")
    If iloc <> 0 Then
    Exit For
    End If
    Next X

    GetAPath = Mid$(FullPath, 1, iloc)

    End Function

    And then I am calling this function from the following :

    Private Sub exportFile(qryName As String)

    DoCmd.TransferSpreadsheet acExport, 8, qryName, GetAPath + qryName, True, ""
    End Sub

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Getting the application's path at network (MS Access 97)

    Here is slightly simpler version of your function:
    Public Function fngetpath() As String
    Dim mypath As String
    Dim myfilename As String
    mypath = CurrentDb().Name
    myfilename = Dir(mypath)
    mypath = Left(mypath, Len(mypath) - Len(myfilename))
    End Function

    One proviso
    If you have a db split into be/fe, with the fe on the local machine, this function and yours will both return the location of the fe not the be. Instead in that case you need the location of the back end.
    This function will do that:
    <pre>public function fnGetnetworkPath() as string
    Dim db As Database
    Set db = CurrentDb

    Dim strpath As String
    Dim lnglength As Long
    Dim tabledef As TableDef
    Dim strconnect As String
    Set tabledef = db.TableDefs("tblPatients")

    strconnect = mytabledef.Connect
    ' msgbox(myconnect)
    lnglength = Len(strconnect)
    strpath = Right(strconnect, lnglength - 10)
    lnglength = Len(strpath)
    strpath = Left(strpath, lnglength - 16)
    fnGetnetworkPath = strpath
    end function
    </pre>

    You would need to change tblPatients to one of your tables.
    The connection string includes the path but precedes it by a database type description, and finishes with the full file name. These are removed to leave the path. The 10 and 16 in my code are just hardcoded values for the length of these.
    Regards
    John



  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting the application's path at network (MS

    > Here is slightly simpler version of your function:

    I'm going to steal this version, unless you tell me not to within the next three milliseconds.

    Nice solution!

    (three milliseconds later) Thanks!

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Getting the application's path at network (MS

    I can't claim credit for this.

    I forget just where I got it - I think it was from some ealier post on this forum.
    Regards
    John



  6. #6
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Getting the application's path at network (MS

    I don't know if this is an improvement or not, but it should not be necessary to "hard code" the lengths used in function if you are dealing with a linked .MDB table, which has a "standard" connection string. Sample function to return path of linked .MDB table (assumes linked file is an .MDB file):

    Public Function GetLinkedTablePath(ByVal strTbl As String) As String

    Dim db As DAO.Database
    Dim tbl As DAO.TableDef
    Dim strConn As String
    Dim strMsg As String
    Dim intStart As Integer

    ' This function intended for linked .MDB tables only!
    ' TableDef Attributes property:
    ' &H40000000 = Linked ISAM (.mdb, .xls, .txt, etc)
    ' &H20000000 = Linked ODBC
    ' Connection string example:
    ' ;DATABASE=H:09AACCESSFILESNorthwind.mdb

    Set db = CurrentDb
    Set tbl = db.TableDefs(strTbl)
    strConn = tbl.Connect

    ' Note: This excludes "" at end of path; modify if necessary:
    If tbl.Attributes = &H40000000 And Right(strConn, 4) = ".mdb" Then
    intStart = 11 ' Based on standard conn string for linked .MDB table
    GetLinkedTablePath = Mid(strConn, intStart, InStrRev(strConn, "", -1, 0) - intStart)
    Else
    strMsg = "Specified Table (" & strTbl & ") is not a linked .MDB table."
    MsgBox strMsg, vbExclamation, "NOT LINKED TABLE"
    GetLinkedTablePath = ""
    End If

    Exit_Function:
    Set db = Nothing
    Set tbl = Nothing
    Exit Function
    Err_Handler:
    strMsg = "Error No " & Err.Number & ": " & Err.Description
    Beep
    MsgBox strMsg, vbExclamation, "ERROR MESSAGE"
    Resume Exit_Function
    End Function

    Example of use (local hard drive, works same if on network); returns path for standard installation of Northwind.mdb in Access XP/2002:

    ? GetLinkedTablePath("Products1")
    C:Program FilesMicrosoft OfficeOffice10Samples

    Function can be modified for use with other linked table types, which have varying connection string formats....

  7. #7
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Getting linked table path (A2K)

    <P ID="edit" class=small>(Edited by MarkD on 08-Mar-03 09:32. Minor clarifications.)</P>As alternative to DAO TableDef properties, you can use ADOX properties to get path for non-ODBC linked tables. Sample function returns full path of linked table, including or excluding file name of source db, depending on option specified in 2nd argument:

    Public Function GetLinkedTablePathADO(strTbl As String, intOpt As Integer) As String
    On Error GoTo Err_Handler

    ' intOpt = 1: returns source database full path (including filename)
    ' intOpt = 2: returns source database full path (excluding filename)
    ' Linked .TXT file: Use intOpt 1, returns path only
    ' Linked .XLS file: same as .MDB
    ' Does not work with linked ODBC tables
    ' ODBC tables: parse string returned by "JET OLEDB:Link Provider String" property

    Dim cat As New ADOX.Catalog
    Dim tbl As ADOX.Table
    Dim strProp As String
    Dim strPath As String
    Dim strMsg As String

    cat.ActiveConnection = CurrentProject.Connection
    Set tbl = cat.Tables(strTbl)
    strProp = "Jet OLEDB:Link Datasource"
    strPath = tbl.Properties(strProp).Value

    If Len(strPath) > 0 Then
    If intOpt = 2 Then
    strPath = Left(strPath, InStrRev(strPath, "", -1, 0) - 1)
    End If
    GetLinkedTablePathADO = strPath
    Else
    strMsg = strTbl & " is not a valid linked table."
    MsgBox strMsg, vbExclamation, "INVALID TABLE"
    GetLinkedTablePathADO = ""
    End If

    Exit_Function:
    Set cat = Nothing
    Set tbl = Nothing
    Exit Function
    Err_Handler:
    strMsg = "Error No " & Err.Number & ": " & Err.Description
    MsgBox strMsg, vbExclamation, "ERROR MESSAGE"
    Resume Exit_Function

    End Function

    Example of use:

    ? GetLinkedTablePathADO("Products1",1)
    C:Program FilesMicrosoft OfficeOffice10SamplesNorthwind.mdb

    ? GetLinkedTablePathADO("Products1",2)
    C:Program FilesMicrosoft OfficeOffice10Samples

    This may be simpler than DAO appoach in previous example? Only drawback, I don't see simple way to distinguish linked ISAM from linked ODBC tables the way you can with DAO TableDef Attributes property, other than to test for Link Datasource property returning an empty string. Also, when testing both methods, the DAO method was VERY slow, taking several seconds to return connection string (even on local drive), whereas the ADOX function returned string with no delay. You'd have to test both to see if this applies on your system. To use this example, set a reference to Microsoft ADO Ext. 2.x for DDL and Security (ADOX) type library (MSADOX.DLL).

    HTH

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Getting linked table path (A2K)

    The worst thing about working with ADO for this is that the OLEDB properties have such clunky names. There are no simple property names like "Connect" or "SourceTable". Instead, you have to get the OLEDB property name exactly right for the correct provider and they can be pretty much whatever the provider decides to call them. That provides tremendous flexibility in developing OLEDB drivers but it can be awfully frustrating for the developer using them. <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23> Once you've build the code, it runs nicely, of course. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Charlotte

  9. #9
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Getting linked table path (A2K)

    You're right about the clunky property names. I had to loop thru table properties to get right syntax using sub like this:

    Public Sub GetADOTableProperties(strTbl As String)
    On Error Resume Next

    Dim cat As New ADOX.Catalog
    Dim tbl As ADOX.Table
    Dim i As Integer

    cat.ActiveConnection = CurrentProject.Connection
    Set tbl = cat.Tables(strTbl)

    For i = 1 To tbl.Properties.Count - 1
    Debug.Print tbl.Properties(i).Name & ": " & tbl.Properties(i).Value
    Next i

    Set cat = Nothing
    Set tbl = Nothing

    End Sub

    Example (linked .MDB table):

    GetADOTableProperties("Products1")

    Jet OLEDB:Table Validation Text:
    Jet OLEDB:Table Validation Rule:
    Jet OLEDB:Cache Link Name/Password: False
    Jet OLEDB:Remote Table Name: Products
    Jet OLEDB:Link Provider String:
    Jet OLEDB:Link Datasource: C:Program FilesMicrosoft OfficeOffice10SamplesNorthwind.mdb
    Jet OLEDB:Exclusive Link: False
    Jet OLEDB:Create Link: True
    Jet OLEDB:Table Hidden In Access: False

    Like that last property listed; I think a simple "Hidden" may have sufficed as property name....

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Getting linked table path (A2K)

    Yes, everyone working with ADO needs a routine like this in their toolkit because every provider's properties are different. So is you change the provider to SQL Server, you've got a whole new set of properties to work with. <img src=/S/groan.gif border=0 alt=groan width=16 height=15> That means, you have to enum the property names for each provider before you start working with them. <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>
    Charlotte

Posting Permissions

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