Results 1 to 5 of 5

Thread: Linked Tables

  1. #1
    Join Date
    Apr 2002
    Posts
    5

    Red face Unanswered: Linked Tables

    I have databases with Linked tables. Everytime that I have to move the folder containing all databases, I have to reset the links. Does anyone know of a way to not hard code the path, but to allow for the databases to access each other as long as they are in the same folder?

  2. #2
    Join Date
    Jun 2003
    Location
    USA
    Posts
    1,032
    I'm pretty sure the links are stored absolute instead of relative.
    J. Paul Schmidt, Freelance Web and Database Developer
    www.Bullschmidt.com
    Access Database Sample, Web Database Sample, ASP Design Tips

  3. #3
    Join Date
    Jan 2003
    Location
    Dordrecht, The Netherlands
    Posts
    95

    Re: Linked Tables

    The Linked Table Manager doesn't offer this function I think, but you can do it in code.

    Sub Link()
    'Make sure you have a reference to Microsoft DAO 3.6 Object library
    Dim dbsThisOne As DAO.Database, dbsRemote As DAO.Database
    Dim tdfRemote As TableDef, tdfL As DAO.TableDef
    Dim strRemoteFileSpec As String

    Set dbsThisOne = CurrentDb
    'db2 is the database of which the tables have to be linked to this db
    'db2 must be in the same folder as this database for the code to work
    strRemoteFileSpec = PathFromFileSpec(dbsThisOne.Name) & "\db2.mdb"

    'Remove existing linked tables
    For Each tdfL In dbsThisOne.TableDefs
    If tdfL.Attributes = dbAttachedTable Then dbsThisOne.TableDefs.Delete tdfL.Name
    Next

    'Link to tblTest in db2
    Set tdfL = dbsThisOne.CreateTableDef("tblTest", 0, "tblTest", strRemoteFileSpec)
    dbsThisOne.TableDefs.Append tdfL

    Set dbsRemote = Nothing
    Set tdfRemote = Nothing
    End Sub

    Public Function Reverse(s As String) As String
    'This function returns the input string backwards,
    'i.e. last character becomes first and vice versa
    Dim i As Integer

    If Len(s) = 0 Then Reverse = "": Exit Function

    Reverse = ""
    For i = Len(s) To 1 Step -1
    Reverse = Reverse & Mid(s, i, 1)
    Next

    End Function

    Public Function PathFromFileSpec(strFileSpec As String) As String
    'This function returns the path from the filespec, i.e. full path and filename
    Dim r As String, intPos As Integer

    If Len(strFileSpec) = 0 Then PathFromFileSpec = "": Exit Function

    r = Reverse(strFileSpec)
    intPos = InStr(1, r, "\")
    r = Right(r, Len(r) - intPos)
    PathFromFileSpec = Reverse(r)

    End Function

    Public Function FileNameFromFileSpec(strFileSpec As String) As String
    Dim r As String, intPos As Integer

    r = Reverse(strFileSpec)
    intPos = InStr(1, r, "\")
    If intPos > 0 Then r = Left(r, intPos - 1)
    FileNameFromFileSpec = Reverse(r)

    End Function


    Originally posted by bird44ca
    I have databases with Linked tables. Everytime that I have to move the folder containing all databases, I have to reset the links. Does anyone know of a way to not hard code the path, but to allow for the databases to access each other as long as they are in the same folder?

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    Ad,

    gosh, i would never have thought of that!

    have you done any timing tests comparing your reverse approach with the more conventional approach of successive (? recursive ?) instr looking for the last "/"

    izy

  5. #5
    Join Date
    Jan 2003
    Location
    Dordrecht, The Netherlands
    Posts
    95
    No, I haven't done any tests. My guess is that it wouldn't make much difference in practice, at least I haven't noticed it. To test on performance you would have to make a loop that does a large number of operations of this kind to notice any difference. In practice the database operations determine the efficiency of my programs, so I don't bother too much about this.

    Originally posted by izyrider
    Ad,

    gosh, i would never have thought of that!

    have you done any timing tests comparing your reverse approach with the more conventional approach of successive (? recursive ?) instr looking for the last "/"

    izy

Posting Permissions

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