Results 1 to 7 of 7
  1. #1
    snoopy2003 is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Feb 2011
    Posts
    72

    Speed up linking tables when front end is opened

    Hello,



    I have a split DB - backend and front end.
    When the front end is opened I check the links to the back end with this
    module :

    Code:
    Set tbldefs = CurrentDb.TableDefs
    For Each tbldef In tbldefs
      If Len(CurrentDb.TableDefs(tbldef.Name).Connect) > 0 Then
      CurrentDb.TableDefs(tbldef.Name).RefreshLink
      end if
    Next tbldef
    It takes a long time to run this code.
    How can I make the refresh link process faster ?

    Thank you for your help

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    The answer to both of your posts is the same. There is no reason to do what you are doing.

  3. #3
    snoopy2003 is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Feb 2011
    Posts
    72
    Quote Originally Posted by RuralGuy View Post
    The answer to both of your posts is the same. There is no reason to do what you are doing.
    Hello,

    Thank you for your answer.

    I had 2 questions:
    1. Should I refresh links on startup (design) ?
    2. How can I speedup refresh link module (programming) ?

    As you advices, I will remove the refresh link on startup and the only check will be the existence of the backend file.

    If the user wish to update link afterward, what is the best way to
    implement this (by refreshing links or by dropping them and create new links) ?

    Thank you for your help

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    I would have to ask why would the user want to refresh the links unles the backEnd has been moved in which case you will need more code than you have to rectify that situation.

  5. #5
    snoopy2003 is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Feb 2011
    Posts
    72
    Hello,

    Thank you for your answer.

    I thought that I should check if all the tables links are valid each time the frontend is opened. Anyway, I think I can assume that the backend location is constant and will not change. Every time the system is opened I will check if the backend file exists and assume that it was not changed (structure).

    The admin can change the location of the backend system so he need the functionality of updating links in the frontend file. What is the proffered way to do it ?

    Thank you for your help

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    You can use the Linked Table Manager to achieve that or create your own version of it with this link: http://www.mvps.org/access/api/api0001.htm and code you already have.

  7. #7
    snoopy2003 is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Feb 2011
    Posts
    72
    Hello,

    Thank you very much for your help and for attached link

    Thank you

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Linking tables
    By newtoAccess in forum Forms
    Replies: 7
    Last Post: 05-16-2011, 09:26 AM
  2. Replies: 0
    Last Post: 03-04-2011, 10:28 AM
  3. Replies: 7
    Last Post: 05-15-2010, 10:12 AM
  4. Linking two tables
    By nitsua0491 in forum Forms
    Replies: 2
    Last Post: 10-02-2009, 07:48 AM
  5. Linking Tables?
    By briancb2004 in forum Access
    Replies: 0
    Last Post: 09-29-2008, 01:14 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums