Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    auto refresh linked tables (2000 all updates)

    I have a Front End database, "Trinity Data.mdb" which has linked tables on 3 back ends, "Trinity Data_be.mdb" and "Trinity Miscellaneous.mdb" and "Trinity Archive.mdb"

    Is there code that could be run from a command button, on the Main Menu of "Trinity Data.mdb," which would then refresh the links with the various back end tables?

    Thanks.

    Tom

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

    Re: auto refresh linked tables (2000 all updates)

    You can use DAO and set the Connect property of a TableDef object, then call its RefreshLink method. See for example Automatic Table Relinking.

    Note: to use DAO, you must set a reference to the Microsoft DAO 3.6 Object Library in Tools | References...

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: auto refresh linked tables (2000 all updates)

    I have used this coding to relink all linked tables that can be in different backends. This code was supplied by Hans. The only question is if CurrentProject.Path can be used in Access 2000.

    <pre>Public Function Set_Links() As Boolean
    Dim tdf As DAO.TableDef
    Dim strBE As String

    On Error Resume Next

    ' Loop through tables.
    For Each tdf In CurrentDb.TableDefs
    ' If Connect property is non-empty, table is linked.
    If Len(tdf.Connect) > 0 Then
    ' Set Connect property
    Dim sN As String, iSt As Integer, iEnd As Integer
    strBE = tdf.Connect
    iSt = 1
    Do
    iEnd = InStr(iSt, strBE, "", vbTextCompare)
    If iEnd = 0 Then Exit Do
    iSt = iEnd + 1
    Loop
    sN = tdf.Name
    tdf.Connect = ";DATABASE=" & CurrentProject.Path & "" & Mid(strBE, iSt)
    Err.Clear
    tdf.RefreshLink ' Try to re-link the table.
    If Err Then
    ' Something went wrong
    Set_Links = False
    GoTo ExitHandler
    End If
    End If
    Next tdf
    Set_Links = True ' Linking succeeded.

    ExitHandler:
    Set tdf = Nothing
    Exit Function
    End Function
    </pre>


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

    Re: auto refresh linked tables (2000 all updates)

    > The only question is if CurrentProject.Path can be used in Access 2000.

    Yes, CurrentProject was introduced in Access 2000. In Access 97, you'd have to parse CurrentDb.Name.

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: auto refresh linked tables (2000 all updates)

    Thanks Hans

  6. #6
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: auto refresh linked tables (2000 all updates)

    Pat and Hans
    Thanks so much. That code works perfectly.

    Tom

  7. #7
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Kitchener, Ontario, Canada
    Posts
    228
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: auto refresh linked tables (2000 all updates)

    This is a littler over my head.
    how would I set this
    c:whateverfrontend.mdb
    c:whateverdatabackend.mdb

    the first time the frontend is run it uses this code to find the table links (full network) sorta like an autoexec and makes the changes to the links

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

    Re: auto refresh linked tables (2000 all updates)

    Here is a modified version of the function from the article I posted a link to:

    Function LinkTableDefs()
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim strPath As String

    On Error GoTo ErrHandler

    ' Path of frontend
    strPath = CurrentProject.Path
    ' Add backslash if necessary
    If Not Right(strPath, 1) = "" Then
    strPath = strPath & ""
    End If
    ' Construct backend path
    strPath = strPath & "DataBackend.mdb"

    ' Loop through TableDefs collection, only processing
    ' the table if it already has a non-blank Connect property.

    Set dbs = CurrentDb
    For Each tdf In dbs.TableDefs
    If Not tdf.Connect = "" Then
    tdf.Connect = ";DATABASE=" & strPath
    tdf.RefreshLink
    End If
    Next

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

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

    You can call this function from a macro named AutoExec (using the RunCode action) or from the On Open event of the startup form of the frontend.

  9. #9
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Kitchener, Ontario, Canada
    Posts
    228
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: auto refresh linked tables (2000 all updates)

    so how would I change this to after

    strPath = strPath & "DataBackend.mdb"

    write the contents of strPath to a table in frontend called [backend] with a field called [backend_location] also this is only to be run on startup if this record is empty (their should only ever be 1 record; the current strPath.

    what I want to happen is the program gets installed wherever the user wants on the network both the front end and the back. The frontend will have its links severed because different people will place it in different location (this is going to be used on different networks at different plants) I want the first time the front end is run at the installation location it picks up the directory of the backend fixes the links and allows the fron to be copied to as many users they need to with the links attached.

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

    Re: auto refresh linked tables (2000 all updates)

    This code will update the table backend

    dim SQL as string
    SQL = "Update backend set backend.backend_location = " & Chr(34) & strPath & Chr(34)
    CurrentDb.Execute SQL, dbFailOnError

    You don't say why you want to store the backend location in a table.

    I often store the path to the backend, rather than the full name/path of the backend file, because I use it a lot for merges and displaying images and it seems quicker to retrieve it from a table, than work it out. But because things might change, I update the stored value whenever the database is loaded.
    Regards
    John



  11. #11
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Kitchener, Ontario, Canada
    Posts
    228
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: auto refresh linked tables (2000 all updates)

    I have tried adding the code from both John and Hans with no success. I have attached a stripped version of my database for you to look at What I am trying to do is:
    (This database is going to 3 of our sister companies, at each company the front end will be copied to a bunch computer which needs it. When installed the frontend will be in a directory (this directory will be different for each sister company) the backend will be in a sub directory called /Data. The problem is the links between front and back will only work if their directory tree is exactly the same as mine. (which it will not be) so what I wanted to do was the first time the frontend is run (it will always be installed in the directory above the backend, and I will have the run it from their first) it knows the backend (and the missing links) are in the subdirectory /Data. So I want it to automatically re-link to the backend. The problem is that I don't want it to try automatically re-linking as they will copy the frontend to different computers and if it looks again after the move it will not fond a subdirectory /Data in its new home.... the /Data backend will be on a server for everybody to access. I hope I have described this good enough.

  12. #12
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: auto refresh linked tables (2000 all updates)

    What i do on all my front end and backend(s) is to copy the FE to the backend folder first and it re-links to any number of BEs after popping a dialog box to ask if you want to relink. I can then copy that FE anywhere on the system and it knows where the backends are.

    Are the backends on the same folder in all companies?

    If i have misunderstood your problem would you explain it further.

    The code that does this is in <post#=578984>post 578984</post#>.

  13. #13
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Kitchener, Ontario, Canada
    Posts
    228
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: auto refresh linked tables (2000 all updates)

    Ok but how would I get this to only run on the first time I start the front end. If I place it in an autoexec it will run every time which will not be good after the front end is copied to the users computers. But it must run the first time or the front end will never know the location to start with...Is their a way to have it in an autoexec which runs the first time the frontend is run (which will be in the directory /data location. Then set a value in a table [backend] field [backend_location] to say a value of 1. That the next time the frontend is ran the value of the 1st record in the table [backend] is looked at with an if statment and skipped until the vaule is changed with a forn to reset the links system.

    The problem I am trying to get around is the fact that I will not have any control over where the database will be placed. The only think I will know is that the backend will be in a sub directory. The people using this will have no idea how to use access so I am trying to make this as easy as I can for them. If I can automate the linking process that would be great. The problem is that when you move the frontend and it is auto looking it will loose the backend.

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

    Re: auto refresh linked tables (2000 all updates)

    The Solutions9.mdb sample database (available from Microsoft here) demonstrates an approach you could adapt for your purposes. Each time the database is opened, it checks whether the links to the backend still work; if not, it first tries to re-link the tables automatically, and if that fails, it asks the user to locate the backend.
    So although the code will run each time the database is opened, it won't do anything as long as the path to the backend remains unchanged. It'll only act if the backend has been moved.

  15. #15
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Kitchener, Ontario, Canada
    Posts
    228
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: auto refresh linked tables (2000 all updates)

    Hans
    As always You are a genius....

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

Page 1 of 2 12 LastLast

Posting Permissions

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