Results 1 to 13 of 13
  1. #1
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Varying server locations problem (Access 2K Win 2K)

    Dear All

    Hello again. I'm working on a very simple little database for my boss, I've got the bulk of it sorted and have now come across a little problem. The finished item will be a front end / back end system, used by up to 10 people at one time, my problem is that some of the 10 people are using different drive letters for the server location I've been allocated for this database.

    The initial solution, change their drive letters, has been rejected for various reasons.

    I can easily enough link to the Access back end via the 'my network places' option, the problem comes from supporting files that I need to manipulate with VBA (all Excel files). They will be in the same folder as the back end system, but the only way I seem to be capable of getting the support file locations is an activeX list box (the example from programming for Dummies) backed up with a combo box for the user to select the drive letter. This requires more user understanding (and creates some potential errors) than I'm happy with.

    How can I put the server name into code? My attempts to date have failed miserably.

    Thanks

    Ian

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Varying server locations problem (Access 2K Win 2K)

    You should be able to use paths like "serversharefolder" in code. You can also get at the path to the database in code using CurrentDb.Name. For example,

    Left(CurrentDb.Name, InStrRev(CurrentDb.Name, ""))

    returns the path of the database with trailing backslash. This avoids having to hard-code the path.

  3. #3
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Varying server locations problem (Access 2K Win 2K)

    Hans

    Thank You, the currentDb thing is the perfect solution to my headache.

    Thanks

    Ian

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

    Re: Varying server locations problem (Access 2K Wi

    I believe that Left(CurrentDb.Name, InStrRev(CurrentDb.Name, "")) will return the path to the front not the path to the back end.

    To get the path to the back end, I always look at the connect property of one of the tables, but I have often wondered if there is an easier way.
    Regards
    John



  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Varying server locations problem (Access 2K Wi

    You are correct. I didn't read the question carefully enough.

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

    Re: Varying server locations problem (Access 2K Wi

    Here is the function I use to find the path to the back end:
    <pre>Public Function fngetlinkedpath() As String
    Dim strmypath As String
    Dim db As DAO.Database
    Set db = CurrentDb
    Dim strTableName As String
    Dim mytabledef As DAO.TableDef
    Dim strmyconnect As String
    strTableName = "tbldoctors"
    Set mytabledef = db.TableDefs(strTableName)
    strmyconnect = mytabledef.Connect
    strmypath = Right(strmyconnect, Len(strmyconnect) - 10)
    strmypath = Left(strmypath, InStrRev(strmypath, ""))
    fngetlinkedpath = strmypath

    End Function
    </pre>


    The connect property returns " ; DATABASE=" followed by the name and path of the back end.
    so I chop of the first 10 characters to get the name and path, then cut it back to just the path.

    edited by john to remove some unnecessary code
    Regards
    John



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

    Re: Varying server locations problem (Access 2K Wi

    Try ADO. In A2002 this works without an ADO reference, but I don't recall whether it does in 2000.

    <pre>Public Function GetCurrentDataPath() As String
    'created by Charlotte Foust 2/14/2001
    'returns the current backend path
    'from the base connection string
    On Error Resume Next
    Dim strSource As String
    Dim strFile As String
    'get the connection string for the backend
    strSource = CurrentProject.BaseConnectionString
    If strSource <> "" Then
    'get everthing to the right of "DATA SOURCE="
    strSource = Mid(strSource, InStr(strSource, "DATA SOURCE=") + 12)
    'trim off everything beyond the path
    strSource = Left(strSource, InStr(strSource, ";") - 1)
    End If
    GetCurrentDataPath = strSource
    End Function 'GetCurrentDataPath() As String</pre>

    Charlotte

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

    Re: Varying server locations problem (Access 2K Wi

    When I tried this function getCurrentdatapath() in 2000, it returned the path to the front end.
    <pre>? getCurrentdatapath()
    C:My DocumentsTitan.mdb
    ? fngetlinkedpath()
    E:JOHNACCESSGenPrac
    </pre>

    Regards
    John



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

    Re: Varying server locations problem (Access 2K Wi

    I'll have to check it in A2k at home. It returns the appropriate value in AXP on this machine, and the first table in the db window is not a linked table. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    AHA!! You need an ADO connection established to the back end before this gives you the right value.
    Charlotte

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

    Re: Varying server locations problem (Access 2K Wi

    If you're too lazy to declare variables, etc, you can use one-line functions like these examples:

    Public Function GetLinkedTablePath(ByRef strTbl As String) As String

    GetLinkedTablePath = Mid$(Left$(CurrentDb.TableDefs(strTbl).Connect, _
    InStrRev(CurrentDb.TableDefs(strTbl).Connect, "", , 0) - 1), 11)

    End Function

    Public Function GetLinkedTableDBName(ByRef strTbl As String) As String

    GetLinkedTableDBName = Mid$(CurrentDb.TableDefs(strTbl).Connect, _
    InStrRev(CurrentDb.TableDefs(strTbl).Connect, "", , 0) + 1)

    End Function

    Sample results (A2K) with linked table located in Northwind.mdb:

    ? GetLinkedTablePath("Orders")
    C:Program FilesMicrosoft OfficeOfficeSamples

    ? GetLinkedTableDBName("Orders")
    Northwind.mdb

    Functions returned correct results with other linked tables in other back end db's. These are intended for linked Access tables where the connection string looks like this:

    ;DATABASE=C:Program FilesMicrosoft OfficeOfficeSamplesNorthwind.mdb

    HTH

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

    Re: Varying server locations problem (Access 2K Wi

    If not lazy, you can use more than single-line function to get path for linked Excel or Text files in addition to Access .mdb files:

    Public Function GetLinkedTablePathEx(ByRef strTbl As String) As String
    On Error GoTo Err_Handler

    ' Use for linked Access, XL, or Text files:
    Dim strConnect As String
    Dim strMsg As String

    strConnect = CurrentDb.TableDefs(strTbl).Connect

    Select Case Left$(strConnect, 5)
    Case ";DATA", "EXCEL"
    ' Access .MDB or Excel .XLS - connect string returns full path & file name:
    GetLinkedTablePathEx = Mid$(Left$(strConnect, InStrRev(strConnect, "", , 0) - 1), _
    InStr(1, strConnect, "DATABASE=", vbTextCompare) + 9)
    ' Test if file in root directory:
    If InStr(1, strConnect, "", 0) = InStrRev(strConnect, "", , 0) Then
    GetLinkedTablePathEx = GetLinkedTablePathEx & ""
    End If

    Case "TEXT;"
    ' Text file - Connection string returns path only:
    GetLinkedTablePathEx = Mid$(strConnect, InStr(1, strConnect, "DATABASE=", vbTextCompare) + 9)
    Case Else
    GetLinkedTablePathEx = vbNullString
    End Select

    Exit_Sub:
    Exit Function
    Err_Handler:
    Select Case Err.Number
    Case 3265 ' Item not found in collection
    strMsg = "Table not found!"
    MsgBox strMsg, vbExclamation, strMsg
    Case Else
    strMsg = "Error No " & Err.Number & ": " & Err.Description
    MsgBox strMsg, vbExclamation, "Unexpected Error"
    End Select
    Resume Exit_Sub
    End Function

    Test results (with example of connection string formats):

    ? CurrentDB.TableDefs("Orders").Connect
    ;DATABASE=C:Program FilesMicrosoft OfficeOffice10SamplesNorthwind.mdb
    ? CurrentDB.TableDefs("XLFile").Connect
    Excel 5.0;HDR=NO;IMEX=2;DATABASE=Cocuments and SettingsMARK DMy DocumentsWork StuffXLFile.xls
    ? CurrentDB.TableDefs("TextFile").Connect
    Text;DSN=TextFile Link Specification;FMT=Delimited;HDR=NO;IMEX=2;Characte rSet=1252;DATABASE=C:Access

    ? GetLinkedTablePathEx("Orders")
    C:Program FilesMicrosoft OfficeOffice10Samples
    ? GetLinkedTablePathEx("XLFile")
    Cocuments and SettingsMARK DMy DocumentsWork Stuff
    ? GetLinkedTablePathEx("TextFile")
    C:Access

    Note that the connection string for linked Text file provides path only. Also added test in case file in root directory (eg, path = "C:"). Modified function to get file name only:

    Public Function GetLinkedTableDBNameEx(ByRef strTbl As String) As String
    On Error GoTo Err_Handler

    Dim strConnect As String
    strConnect = CurrentDb.TableDefs(strTbl).Connect

    Select Case Left$(strConnect, 5)
    Case ";DATA", "EXCEL"
    GetLinkedTableDBNameEx = Mid$(strConnect, InStrRev(strConnect, "", , 0) + 1)
    Case "TEXT;"
    ' Text file - Connection string returns path only, use SourceTableName property:
    GetLinkedTableDBNameEx = CurrentDb.TableDefs(strTbl).SourceTableName
    Case Else
    GetLinkedTableDBNameEx = vbNullString
    End Select
    ' Error handling, etc
    End Function

    Test results:

    ? GetLinkedTableDBNameEx("Orders")
    Northwind.mdb
    ? GetLinkedTableDBNameEx("XLFile")
    XLFile.xls
    ? GetLinkedTableDBNameEx("TextFile")
    TextFile.txt

    The 2nd function worked correctly whether or not file located in root directory. If working with other types of linked tables, then functions can be modified accordingly based on format of connection string returned by table's Connect property.

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

    Re: Varying server locations problem (Access 2K Wi

    One last note, you can use ADOX instead of DAO to get linked table properties, but it is perceptibly slower than DAO. Example, applicable to linked Access, Excel, or Text files using Jet 4.0 provider:

    Public Sub GetLinkedTablePathADO(ByRef strTbl As String, _
    ByRef strPath As String, _
    ByRef strFile As String)
    On Error GoTo Err_Handler

    Dim cat As New ADOX.Catalog
    Dim strLinkProvider As String
    Dim strConnect As String
    Dim strMsg As String

    ' Example (Access):
    ' Jet OLEDB:Link Datasource: C:Program FilesMicrosoft OfficeOffice10SamplesNorthwind.mdb

    cat.ActiveConnection = CurrentProject.Connection
    ' Determine type of linked table:
    strLinkProvider = Left$(cat.Tables(strTbl).Properties("Jet OLEDB:Link Provider String"), 5)

    If Len(strLinkProvider) = 0 Then
    ' Linked Access table
    strConnect = cat.Tables(strTbl).Properties("Jet OLEDB:Link Datasource").Value
    strPath = Left$(strConnect, InStrRev(strConnect, "", , 0) - 1)
    strFile = Mid$(strConnect, InStrRev(strConnect, "", , 0) + 1)
    Else
    Select Case strLinkProvider
    Case "Excel"
    strConnect = cat.Tables(strTbl).Properties("Jet OLEDB:Link Datasource").Value
    strPath = Left$(strConnect, InStrRev(strConnect, "", , 0) - 1)
    strFile = Mid$(strConnect, InStrRev(strConnect, "", , 0) + 1)
    Case "Text;"
    strPath = cat.Tables(strTbl).Properties("Jet OLEDB:Link Datasource").Value
    strFile = Replace(cat.Tables(strTbl).Properties("Jet OLEDB:Remote Table Name"), "#", ".", , , 0)
    Case Else
    MsgBox "Unknown Link Provider.", vbExclamation, "UNKNOWN"
    End Select
    End If

    Debug.Print "Linked table path: " & strPath
    Debug.Print "Linked table file: " & strFile

    Exit_Sub:
    Set cat = Nothing
    Exit Sub
    Err_Handler:
    Select Case Err.Number
    Case 3265 ' Item not found in collection
    strMsg = "Table not found."
    MsgBox strMsg, vbExclamation, strMsg
    Resume Exit_Sub
    Case Else
    strMsg = "Error No " & Err.Number & ": " & Err.Description
    MsgBox strMsg, vbExclamation, "Unexpected Error"
    Resume Exit_Sub
    End Select

    End Sub

    Test results:

    GetLinkedTablePathADO "Orders",vbNullString ,vbNullString
    Linked table path: C:Program FilesMicrosoft OfficeOffice10Samples
    Linked table file: Northwind.mdb

    GetLinkedTablePathADO "XLFile",vbNullString ,vbNullString
    Linked table path: Cocuments and SettingsMARK DMy DocumentsWork Stuff
    Linked table file: XLFile.xls

    GetLinkedTablePathADO "TextFile",vbNullString ,vbNullString
    Linked table path: C:Access
    Linked table file: TextFile.txt

    Note use of sub vice function to avoid duplication; pass null strings by reference to sub for strPath and strFile arguments. If using linked tables other than shown here (such as ODBC), or a different provider, modify sub as necessary. To determine what provider-specific properties are available for linked table, and their values, use sub like this to list table properties:

    Public Sub GetTablePropertiesADO(ByRef 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 = 0 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

    As noted above, if using Jet linked tables only, DAO will return quicker results.

  13. #13
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: Varying server locations problem (Access 2K Win 2K)

    Just for what it's worth picking up an old thread but...
    suppose you have supporting front-end table(s) with source table information including the (eventually presumed) relative/full source file path: could this be of some help?
    For example, suppose that the users just have a different drive letter but the drives point at exact the same location. Then, if you're sure the user HAS such a drive, you could get that drive letter in one way or another, put that in front of the relative path you get from the supporting table and off you go...
    But maybe I'm overlooking something... looking to all this code in the previous posts (which I, to be honest, didn't fully consume <img src=/S/blush.gif border=0 alt=blush width=15 height=15>)

Posting Permissions

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