Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2002
    Location
    Kansas City
    Posts
    103

    Unanswered: Changing the File Path on Linked Tables

    Is it possible to specify the location/file path when linking a table/text file into a 2003 SP2 Access database? When using 'file'......'get external data'......'link tables', the location of the browsed table reflects my individual drive mapping. For example, J\folder\folder\folder. A user with their 'J' drive mapped differently obviously cann't open the linked DBO/text file.

    Although I found the file path for the linked table in the 'Database' column of MSysObjects, I was unable to change J\folder\folder\folder to \\fileserver\folder\folder. The error message said, 'Control can’t be edited; it’s bound to replication system column ‘Database’.

    Any suggestions for replacing the local drive letter mapping of a linked table's location would be greatly appreciated.

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Here some code I use

    Code:
    Sub RelinkTables(path,db)
        Dim dbs As Database
        Dim tdf As TableDef
        ' Loop through all tables in the database.
        Set dbs = CurrentDb
        For Each tdf In dbs.TableDefs
            ' If the table has a connect string, it's a linked table.
            If Len(tdf.Connect) > 0 Then
                tdf.Connect = ";DATABASE=" & Path & "\" & db
                Err = 0
                On Error Resume Next
                tdf.RefreshLink         ' Relink the table.
                If Err <> 0 Then
                End If
           End If
        Next tdf
      End Sub
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Jun 2002
    Location
    Kansas City
    Posts
    103
    Myle, thank you for the suggestion and code. Not being real adept at VBA, would I insert this as a general declaration when the database is launched? I assume I'd insert my file path in the line tdf.Connect = ";DATABASE=" & Path & "\" & db following the second ampersand? Am I at all close?

  4. #4
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    turn the sub's in a function

    replace the sub with function

    so
    Sub RelinkTables(
    to
    Function RelinkTables(

    and the

    End Sub
    to
    End Function

    Save it into a Module

    Know we can create a Macro

    select the Action runcode

    in the function name
    type RelinkTables ("\\servername\folder","data.mdb")

    replace the "\\servername\folder" with your folder

    and "data.mdb" with the datbasename

    then create this action for each table you want to fix

    give the Macro name a Good name

    then you can call this macro @ anything

    does this make cents



    select the Action to be
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  5. #5
    Join Date
    Jun 2002
    Location
    Kansas City
    Posts
    103
    Hi Myle, I created a new module and a macro to run the code but I don't know where to reference the file path of the linked table/file in the code itself? I also don't quite know how to specify/relink a specific table to a specific file? I have two tables linked to two files so I was trying to figure out how to specify which table should be linked to which file. Following the conditional logic in the code, I believe it will find any linked table in the MSysObjects table and relink all to the same text file on my file server. Please bear with me as I'm not a VBA programmer! That said, I think I'm pretty close to making this work - with your help.

    Here's the module code:

    Function RelinkTables()
    Dim dbs As Database
    Dim tdf As TableDef
    ' Loop through all tables in the database.
    Set dbs = CurrentDb
    For Each tdf In dbs.TableDefs
    ' If the table has a connect string, it's a linked table.
    If Len(tdf.Connect) > 0 Then
    tdf.Connect = ";DATABASE=" & path & "\" & db
    Err = 0
    On Error Resume Next
    tdf.RefreshLink ' Relink the table.
    If Err <> 0 Then
    End If
    End If
    Next tdf
    End Function

    And, here's the RunCode line of my macro:

    RelinkTables ("\\elvis\hr\ghrstaffing\inbound","GHR Staffing 2.0")

  6. #6
    Join Date
    Jun 2002
    Location
    Kansas City
    Posts
    103

    Smile

    Although I'm certain the above VBA would have worked had I been able to work out the nuances in the code, I actually discovered a much easier solution for my situation.......

    By using the linked table manager (right click on the table/linked file containing the mapped disk drive letter in the linkage and select linked table manager), I re-linked the dbo browsing to the file on the server via network neighborhood. You must select 'Always prompt for new location' along with the dbo/file(s) to be re-linked. This action eliminated the drive letter in the linkage and the accompanying hassles for my users.

  7. #7
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    here
    change the

    also the Function in the module should have the

    Function RelinkTables(path,db)



    RelinkTables ("\\elvis\hr\ghrstaffing\inbound","GHR Staffing 2.0")

    to

    RelinkTables ("\\elvis\hr\ghrstaffing\inbound","GHR Staffing 2.0.MDB")

    I would take the 2.0 out
    to
    RelinkTables ("\\elvis\hr\ghrstaffing\inbound","GHRStaffing20.M DB")

    Spaces "." (more than) in Filename do not mix well in my Book
    Last edited by myle; 06-08-07 at 15:45.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Posting Permissions

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