Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    London, Gtr London
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Linking tables change (2003)

    Our office is moving location in April this year and as a result our entire network setup is going to change. When I designed my access databases I had no idea this was going to happen so as a result all table links etc. were designed with the current network set-up.

    What I was thinking is, is there a way I can store the locations of the tables in a "master file" which can be changed at anytime and would pick up the correct location of a specific table no matter which database is being used? Or is there an easier way to solve this?

    Many thanks as always
    Hayden

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

    Re: Linking tables change (2003)

    The code in <post#=405161>post 405161</post#> should give you an idea, and perhaps <post#=249646>post 249646</post#>. Post back if you need additional help.

  3. #3
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    London, Gtr London
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking tables change (2003)

    Hello Hans

    I'm sorry but you've lost me. I've been trying the code from the posts you suggested but I'm not entirely sure how it all fits together.

    I have attached an excel spreadsheet showing the databases I use and where the table links are. When the office relocates the U: directory will change so I need to point all the links to the new location.

    Regards
    Hayden

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

    Re: Linking tables change (2003)

    All databases listed in your spreadsheet are in the same folder: U:Finance_Databases_Links. Will all databases still be in the same folder (a different one than the current one) after the change? Or could they end up in multiple folders?

  5. #5
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    London, Gtr London
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking tables change (2003)

    I will try to get them all into the same folder although I'm not sure at this stage what the directory will be, there are two tables currently linked from the p: directory but again, I'm not sure at this stage where this will be moved to.

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

    Re: Linking tables change (2003)

    Let's assume that there may be different folders then. Create a table tblPaths with two text fields: OldPath and NewPath. The size of both fields must be large enough to hold the complete path and file name of the linked databases.

    <table border=1><td align=center>OldPath</td><td align=center>NewPath</td><td>U:Finance_Databases_Links01-Nldownloads.mdb</td><td>J:SomewhereElse01-Nldownloads.mdb</td><td>...</td><td>...</td></table>
    You could use code like that posted below. It needs a reference to the Microsoft DAO 3.6 Object Library (check this in Tools | References... in the Visual Basic Editor).

    Sub UpdateTableLinks
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim strOldPath As String
    Dim strNewPath As String

    On Error GoTo ErrHandler

    Set dbs = CurrentDb ' or set it to another database.
    For Each tdf In dbs.TableDefs
    If Len(tdf.Connect) > 0 Then
    ' table is linked.
    strOldPath = Mid(tdf.Connect, 11)
    strNewPath = Nz(DLookup("NewPath", "tblPaths", _
    "OldPath=" & Chr(34) & strOldPath & Chr(34)))
    If Not strNewPath = "" Then
    tdf.Connect = ";DATABASE=" & strNewPath
    tdf.RefreshLink
    End If
    End If
    Next tdf

    ExitHandler:
    Set tdf = Nothing
    Set dbs = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

  7. #7
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    London, Gtr London
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking tables change (2003)

    Wow Hans you're a genius!!!!

    I copied the databases onto my C: directory and created the table and code as you suggested and the linked tables are now pointing to the ones in the C: drive and not the U:drive anymore.

    In order to try and learn what has happened here can I assume that the code looks through all the tables in the database, checks to see if the connection details >0 (i.e.: it must be a linked table) then replaces the old path with the new path I gave it in my new table? If my assumption is correct then how does it distinguish between an ODBC linked table and and Access linked table?

    Incidentally would this also work if say I was linking to an excel data source for example?

    Many thanks again Hans, I am extremely grateful, Hayden

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

    Re: Linking tables change (2003)

    My code assumes that you only have linked Access tables, it'll fail if you have other types of linked tables. As you indicate, it loops through the tables, checks if the Connect property is non-blank, and replaces it with ";DATABASE=" followed by the new database name looked up in the table. To make it work for other types of linked tables, you'd have to do more parsing to determine the contents of the Connect string. I don't have any ODBC linked tables at my disposal at the moment, so I don't know what the Connect string looks like.

  9. #9
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    London, Gtr London
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking tables change (2003)

    From what I'm able to see, it seems that the ODBC links have remained unaffected. When I hover the mouse over the table I get this: ODBC;DSN=liveams;;TABLE=root.SALES_ITEM if this helps in any way?

    Thanks again for your time
    Hayden

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

    Re: Linking tables change (2003)

    The idea would be as follows (this is pseudo code):

    If the Connect string is not blank then
    If the connect string begins with ODBC then
    Skip this table
    ElseIf the connect string contains DATABASE= then
    Extract the old file name
    Look up the corresponding new name
    Replace the old name with the new one in the connect string.
    Refresh the link
    End If
    End If

  11. #11
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    London, Gtr London
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking tables change (2003)

    Hello Hans

    I have just realised that some of my access databases have functions which transfer tables to excel, I have hard coded the locations like this for example:

    DoCmd.TransferSpreadsheet acExport, 8, "FCS1", "U:Finance_Databases_ExtractsCash(CH)FCS1", True, ""

    When the server moves the U: drive will change is there a way to globally change the output files similar to the way you helped me change the lookup tables before?

    Kind Regards, Hayden

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

    Re: Linking tables change (2003)

    You can do a global Find and Replace in the Visual Basic editor. If you click the "Current Project" radio button in the Replace dialog, you can replace a path in all modules in one fell swoop.

    An alternative would be to put the paths in a table, and use DLookup in the code, something like
    <code>
    DoCmd.TransferSpreadsheet acExport, 8, "FCS1", _
    DLookup("ExportPath", "tblSettings", "Target = 'FCS1'") & "FCS1", True
    </code>
    You wouldn't have to change the code modules, only the values in the settings table.

  13. #13
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    London, Gtr London
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking tables change (2003)

    Great thanks Hans, I'll give it a try

    Regards
    Hayden

Posting Permissions

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