locked
Link Table Manager prompting for each table RRS feed

  • Question

  • Something odd is happening. I've always selected all and checked the "Always prompt for new location" box then Access would update every table. Now when I do this sequence Access prompts on every link for a new location.

    I tried the Linked Table Manager on an older copy of my front end and it worked fine.

    Any ideas what's happening? Corruption?

    Thanks!

    Saturday, August 14, 2010 1:17 PM

Answers

  • Grab a pen drive and create directories on it that match the exact path to the backend file on your company's network. When you plug in the pen drive on your home machine, change the driver letter to the drive letter that matches the drive at work. Viola! when you put the front end on your machine at home and plug in the pen driver, you won't have to refresh. Access doesn't give a ____, or even recognize that you're on the company network. It only cares that it can see the tables using the path to the links.

     

    You'll never have to refresh your development copy of your front end again. Oh what's that you say, you're working with SQL Server using ODBC? Name the ODBC System DSN connection exactly like the one at work.


    David H
    • Marked as answer by JohnLute Tuesday, August 17, 2010 10:08 AM
    Tuesday, August 17, 2010 12:19 AM

All replies

  • Something odd is happening. I've always selected all and checked the "Always prompt for new location" box then Access would update every table. Now when I do this sequence Access prompts on every link for a new location.

    I tried the Linked Table Manager on an older copy of my front end and it worked fine.

    Any ideas what's happening? Corruption?

    Thanks!

    What happens if you uncheck it? Do the tables refresh? You can refresh without selecting a new location. When you refresh without selecting a new location, Access will look at the original source and refresh the table. If the original source has been moved or the table deleted, Access will then throw a warning that it couldn't refresh the table. If the original source will never move, there's no need to check it.

    David H
    Saturday, August 14, 2010 3:07 PM
  • Something odd is happening. I've always selected all and checked the "Always prompt for new location" box then Access would update every table. Now when I do this sequence Access prompts on every link for a new location.

    I tried the Linked Table Manager on an older copy of my front end and it worked fine.

    Any ideas what's happening? Corruption?

    Thanks!

    What happens if you uncheck it? Do the tables refresh? You can refresh without selecting a new location. When you refresh without selecting a new location, Access will look at the original source and refresh the table. If the original source has been moved or the table deleted, Access will then throw a warning that it couldn't refresh the table. If the original source will never move, there's no need to check it.

    David H


    Hi, David. Thanks for the reply.

    Same thing when I uncheck it. Very strange. I deleted all of the links and simply re-linked. I then tried the manager again and it refreshed all of the links without problem. I don't get it. This has never happened before that I can remember.

    Saturday, August 14, 2010 4:30 PM
  • Something odd is happening. I've always selected all and checked the "Always prompt for new location" box then Access would update every table. Now when I do this sequence Access prompts on every link for a new location.

    I tried the Linked Table Manager on an older copy of my front end and it worked fine.

    Any ideas what's happening? Corruption?

    Thanks!

    What happens if you uncheck it? Do the tables refresh? You can refresh without selecting a new location. When you refresh without selecting a new location, Access will look at the original source and refresh the table. If the original source has been moved or the table deleted, Access will then throw a warning that it couldn't refresh the table. If the original source will never move, there's no need to check it.

    David H


    Hi, David. Thanks for the reply.

    Same thing when I uncheck it. Very strange. I deleted all of the links and simply re-linked. I then tried the manager again and it refreshed all of the links without problem. I don't get it. This has never happened before that I can remember.

    As I seem to recall, it should prompt you if something has occurred where a table in the back end isn't found. For example, if you change the name in the back end and then refresh, you should get the prompt. There are samples out there that demonstrate how to refresh via code which allows you to trap which tables couldn't be refreshed thus telling you which ones are causing the problem.

    David H
    Saturday, August 14, 2010 5:21 PM
  • This is the expected behavior when there is at least one difference between one the tables and the others for the connection strings.  The LTM don't prompt you for every link only when they are all identical.  The best thing you can do is to delete them all and recreate them; as this will be much faster to do.

    Another strange behavior from this Wizard that we cannot control.


    Sylvain Lafontaine, ing.
    MVP - Access
    Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
    Independent consultant and remote programming for Access and SQL-Server (French)

    Saturday, August 14, 2010 6:35 PM
  • This is the expected behavior when there is at least one difference between one the tables and the others for the connection strings.  The LTM don't prompt you for every link only when they are all identical.  The best thing you can do is to delete them all and recreate them; as this will be much faster to do.

    Another strange behavior from this Wizard that we cannot control.


    Sylvain Lafontaine, ing.
    MVP - Access
    Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
    Independent consultant and remote programming for Access and SQL-Server (French)


    Thanks, David and Sylvain.

    More info: I use the db at work and it links to a server there. The problem with refreshing the links is when I have it on my home PC and try to link it to my local drive. I've never had a problem until recently and the only thing I've changed are the addition of two timer events on my splash page.

    The first event checks for a value in a BE table that denotes whether or not the database BE is closed for maint. I had no problems with that event. I recently added a second event that checks for a table value in the BE against a value in a textbox on the splash page. This either triggers a MsgBox telling the user that their FE is out of date or permits entry into the database.

    What's happening is that the splash page gets hung up on the second event. I simply right-click on the splash page to go into design mode. From there I try to refresh the links. This is when the problem occurs. I've tried bypassing the splash page but no good. It seems like once I go through these steps and "crash out" from the splah page that the Link Manager is affected somehow...?

    Tuesday, August 17, 2010 12:10 AM
  • Grab a pen drive and create directories on it that match the exact path to the backend file on your company's network. When you plug in the pen drive on your home machine, change the driver letter to the drive letter that matches the drive at work. Viola! when you put the front end on your machine at home and plug in the pen driver, you won't have to refresh. Access doesn't give a ____, or even recognize that you're on the company network. It only cares that it can see the tables using the path to the links.

     

    You'll never have to refresh your development copy of your front end again. Oh what's that you say, you're working with SQL Server using ODBC? Name the ODBC System DSN connection exactly like the one at work.


    David H
    • Marked as answer by JohnLute Tuesday, August 17, 2010 10:08 AM
    Tuesday, August 17, 2010 12:19 AM
  • I've had this happen when there are table links to two different backends anywhere in the list of tables.

    An additional (nastier) problem is that if you have made any design change to the structure of one of the backend tables, the Linked Table Manager will probably not catch the change; cached information about the table can cause the link and the actual table to disagree, and give some really wierd bugs. I'll fairly often just delete all the links and relink using File... Get External Data... Link (you can select all the backend tables at once), or use VBA code to effectively do the same (drop the links and create new ones).

     


    John W. Vinson/MVP
    Tuesday, August 17, 2010 1:03 AM
  • Grab a pen drive and create directories on it that match the exact path to the backend file on your company's network. When you plug in the pen drive on your home machine, change the driver letter to the drive letter that matches the drive at work. Viola! when you put the front end on your machine at home and plug in the pen driver, you won't have to refresh. Access doesn't give a ____, or even recognize that you're on the company network. It only cares that it can see the tables using the path to the links.

     

    You'll never have to refresh your development copy of your front end again. Oh what's that you say, you're working with SQL Server using ODBC? Name the ODBC System DSN connection exactly like the one at work.


    David H

    LOL! You have a certain tone of sneakiness in your post! Nothing like getting one up on Access! I never thought of this - thanks for sharing your seemingly devious mind :)
    Tuesday, August 17, 2010 10:10 AM
  • OLD thread, but still relevant.

    We are having the SAME issue - cannot get a "mass-refresh" of all links to a bank-end db.

    All throughout this article, people mention "delete all the links are re-create," well, how is that done?

    In the LTM, there is a "Select All," but I don't see ANY option for "Delete" ???

    I am on Windows XP professional workstation, with THOUSANDS of links pointing to a SQL back-end, if any of that matters.

    Same scenario - it USED to be auto-magical - if you did "Select All" it would refresh, but now, it does not, just returns back to the original screen or, if we choose "prompt," then it promts FOR EACH AND EVERY LINK (seemingly).

    So, yes, someone mentions VBA code - well, do you care to share that code? I mean, it's great to say, "Hey, I use THIS," but it's like a "teaser," if you don't actually post the code for us to try. I did find some other such code, and I am checking it is as well - not sure if we can post "links" on this forum?

    1) Anyway, so how do I perform a "delete all" on all my links and then get them re-created? I don't want to go through and manually re-create 4,000 links.

    2) AND, what if I just delete this particular System DSN and then re-create it? Will that force a refresh of all the related links?

    This seems to be, to this day, a VERY common issue.

    Thanks in advance, as always.

     

    Friday, April 15, 2011 6:15 PM
  • Access is useless, how can this not be included and worse, it used to work?  WTF Microsoft.
    Tuesday, June 26, 2012 6:40 PM
  • I just stumbled upon this thread while looking for an answer to the same problem.  After gleaning some clues from this list, I noticed that you only get prompted for each table if any of the selected tables have different paths associated with them.  In order to do a bulk re-link, only select tables with identical paths (right or wrong) and then, after selecting "Always Prompt", click OK - you will only have to choose the new target file once.

    My problem stemmed from an interrupted link process that was only hafway through.  When I selected the tables with the wrong link, I was able to do it all with the single selection.

    I know it's not the easiest work around, but it's a little faster than linking all the tables individually, and a heck of a lot less scary than deleting all of the tables and re-importing/linking them from the back end.


    Will Knapp 20+ Year Access VBA Developer

    Wednesday, July 10, 2013 3:13 PM