Relink tables from backend

[Gasman]

Hi all,

What have I done wrong when Access continually prompts me for the location of a moved BE, table by table, rather than accepting once, and relinking all the tables.?

TIA

 

[Deleted member 147267]

You're referring to using the Linked Table Manager? Or code?
I never figured out why, but sometimes LTM would prompt me for every table and I'd have to go through all the motions each time. Others, it would do it en masse. I finally gave up and copied the path from Windows Explorer dialog so that I could just paste in at every prompt.

 

[Minty]

Have you "upgraded"?
The LTM in 2016 > O365 versions do this as the default behaviour. It's a right royal annoyance!

Obviously a feature upgrade designed by someone who has never used access.

 

[Deleted member 147267]

Minty: interesting. My comments were based on using version 2007, so I'd say the "problem" has been around for a while.

 

[Minty]

I'd agree with your observation - It occasionally did it in 2010, but now always does it, every time.

It's so annoying that I now use code and a table of tables and sources to relink them from VBA. Effectively I've created my own legacy version of the LTM without realising...

 

[isladogs]

I agree about the new LTM. It does have some good features but some serious weaknesses.
As I use DSN-less connection strings to both SS and Access in my commercial apps, I also have my own tables with connection / link info and my own routines for relinking.

However, I've never seen that behaviour in the old LTM Which is odd as I often manage to find issues that others are lucky enough to escape!

 

[Gasman]

Thank you all for the replies.

I am on 2007.
I have always used the LTM (until now), but it has happened to me in the past as well.
I got around it by using Pat's RelinkTable forms that she uploaded here.

 

[Pat Hartman]

Glad you found the relink tool useful. I built it because the linked tables manager was just soooo annoying. However, using the linked tables manager, this problem is usually caused because you selected multiple tables that are not all from the same BE or that have different DSN's. The latter reason is harder to see.

 

[AccessBlaster]

I can't remember my SLQ backend ever coming unlinked, unless I was tinkering with something. I have 5-6 databases running daily for years.

 

[Pat Hartman]

We are not talking about a BE becoming spontaneously unlinked. We are talking about consciously switching from one BE to another which you should be doing quite regularly unless you like living on the edge and make your FE updates while attached to the production BE.

 

[Deleted member 147267]

Someone uploaded a be/fe file in a post and I recently downloaded so I had a chance to check the 2016 version of LTM. When I navigated to the file, it automatically successfully relinked all tables in a flash (obviously I had chosen 'all'). The troubles we sometimes have with it might forever be a mystery.

 

[zeroaccess]

Hi all,

What have I done wrong when Access continually prompts me for the location of a moved BE, table by table, rather than accepting once, and relinking all the tables.?

TIA

I have gotten around this by selecting all the linked tables in the navigation pane, hitting delete, then reselecting my access back-end with the External Data tab, and hitting "Select All". Didn't get prompted again.

 

[gemma-the-husky]

its really easy linking tables in code, much friendlier, and avoids the pain of the linked table manager.

 

[Gasman]

TBH, my setups are very simple, all tables in one DB in the BE if the DB is split.
So it takes very little time to press the Select All, then link.

Just now again, it prompts for each in turn.? I do remember some problem with linked Excel workbooks, so I generally deselect those, but I will keep Pat's manager handy and use that if it happens again.

 

[The_Doc_Man]

The "ask for location" behavior occurred for me in one of my Navy databases, and I was never able to figure out why. I tried both drive-letter mapping and UNC mapping, but nothing helped.

However, the workaround was trivial. I built a query that was the equivalent of SELECT * FROM table; (i.e. a single-table, all-of-the-fields query with no filters or ordering) and used THAT for all forms and reports that needed the table's contents. Once I did that, I never again was questioned on location. And the tip I got for that fix, perhaps 10 years ago, came from this forum.