How to automate re-linking tables from FE to BE, if the location or name of the destination folder changes? (1 Viewer)

Sampoline

Member
Local time
Tomorrow, 07:23
Joined
Oct 19, 2020
Messages
161
I've had a doubt for a while now, not sure if this has already been answered in a different context or thread.

If in the event the location of a BE changes or the folder structure that contains the BE and/or FE changes, how could I avoid having to manually re-link the tables again? As in is there a way for Access to recognise this change and update the linked tables automatically or is there some VBA involved perhaps?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 15:23
Joined
Feb 28, 2001
Messages
27,001
You will almost always have to tell Access where to look. Whether you do this by an input box, a file picker/folder navigation control, or some really complicated FileSystemObject recursive folder search, Access has to know where to look. But it gets more complex.

Your FE has to be initially coded to ALWAYS assume it will be relinking because if you don't, you will start getting errors related to "table not found." Then you will have issues in error handling AND relinking.

Here are a couple of links you might want to read.



 

Sampoline

Member
Local time
Tomorrow, 07:23
Joined
Oct 19, 2020
Messages
161
You will almost always have to tell Access where to look. Whether you do this by an input box, a file picker/folder navigation control, or some really complicated FileSystemObject recursive folder search, Access has to know where to look. But it gets more complex.

Your FE has to be initially coded to ALWAYS assume it will be relinking because if you don't, you will start getting errors related to "table not found." Then you will have issues in error handling AND relinking.

Here are a couple of links you might want to read.



Thanks for the information Doc.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:23
Joined
Oct 29, 2018
Messages
21,358
Hi. The only way Access can automatically relink a BE moved to a different folder is if the BE is placed in the same folder as the FE.
 

Sampoline

Member
Local time
Tomorrow, 07:23
Joined
Oct 19, 2020
Messages
161
Hi. The only way Access can automatically relink a BE moved to a different folder is if the BE is placed in the same folder as the FE.
Oh I never knew that! So my current FE/BE are in different folders. It just occurred to me that in the event of a folder change or BE moved, that I would have to re-link manually. Hence why my curiosity led me here. I could put them in the same folder. That would certainly be the quickest solution. But I kinda don't want them together for the sake of confidentiality in tables. I guess I'll have to keep searching huh?! Thanks DB.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:23
Joined
Oct 29, 2018
Messages
21,358
Oh I never knew that! So my current FE/BE are in different folders. It just occurred to me that in the event of a folder change or BE moved, that I would have to re-link manually. Hence why my curiosity led me here. I could put them in the same folder. That would certainly be the quickest solution. But I kinda don't want them together for the sake of confidentiality in tables. I guess I'll have to keep searching huh?! Thanks DB.
If you don't want the FE and the BE in the same folder, the next best thing is to put the BE in a sub folder under the FE folder, but the sub folder name has to be fixed.
 

Sampoline

Member
Local time
Tomorrow, 07:23
Joined
Oct 19, 2020
Messages
161
If you don't want the FE and the BE in the same folder, the next best thing is to put the BE in a sub folder under the FE folder, but the sub folder name has to be fixed.
So if the folder was changed for example, all I would need to do is change the folder name back so I wouldn't need to re-link tables?
 

namliam

The Mailman - AWF VIP
Local time
Today, 21:23
Joined
Aug 11, 2003
Messages
11,696
You can also make things relative.... i.e.

Your db exists:
C:\Program\DB\FrontEnd\YourDB.accdb

Your BE exists
C:\Program\Tables\Backend\YourBE.accdb

assuming the relative path stays the same ..\..\Tables\Backend\YourBE.accdb you can relink them as wel.

Assuming you have the BE on a server that is mapped to your H drive and for some reason the H drive changes to G drive...
This you can fix / prevent by using the full UNC path,
Your backend now lives on
H:\SomeFolder\SubFolder\YourBE.accdb

Your backend should be living here:
\\YourServer\YourShare\SomeFolder\SubFolder\YourBE.accdb

This makes things less likely to change... unless the server changes offcourse...

A lot of servers can have "generic" names if you ask IT to take care of it.
I.e. a server called "Server21" can have a nick name "DBServer" this DBServer then reroutes to Server21.
If Server21 ever changes to Cloud21 or Server423, all they do is make sure the nickname knows this and also reroutes to this new server and your DB is/stays good to go. Such a nick can even "land" on a much deeper level, you could have
\\DBServer\YourShare\SomeFolder\SubFolder\YourBE.accdb
\\DBServer\SomeFolder\SubFolder\YourBE.accdb
and even
\\DBServer\YourBE.accdb

spleh siht epoh I
 

CJ_London

Super Moderator
Staff member
Local time
Today, 20:23
Joined
Feb 19, 2013
Messages
16,553
Hi. The only way Access can automatically relink a BE moved to a different folder is if the BE is placed in the same folder as the FE.

That's really only relevant when the app is single user. If multi user then the BE has to be on a server somewhere and the FE on a local machine. Putting the FE on the server as well means users will be using the same front end which will ultimately lead to corruption.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:23
Joined
Oct 29, 2018
Messages
21,358
That's really only relevant when the app is single user. If multi user then the BE has to be on a server somewhere and the FE on a local machine. Putting the FE on the server as well means users will be using the same front end which will ultimately lead to corruption.
No disagreement there. That quoted post was just in response to this statement by the OP.
...is there a way for Access to recognise this change and update the linked tables automatically...
It wasn't intended as a suggestion or a proposal for a solution.

So, for a multi-user environment, you can't really avoid prompting the user to identify the new location of the BE, if it cannot be known ahead of time or derived by any other means.

Just my 2 cents...
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:23
Joined
Feb 19, 2002
Messages
42,974
If you are distributing your FE correctly, only YOU will ever have to relink the BE. The users should NEVER, EVER have to do this in a multi-user environment. If the BE needs to move, relink the master copy and redistribute.
 

Sampoline

Member
Local time
Tomorrow, 07:23
Joined
Oct 19, 2020
Messages
161
Hi everyone. Thankyou all for the suggestions. Just to clarify further, yes I have my BE on the server. The users are distributed the FE to their local machines.

So it's not the end of the world to simply re-link tables. And then redistribute the FE. But was just curious if there was a simple solution to automating that process instead. Something I just blatantly didn't think of. That's all.
 

Sampoline

Member
Local time
Tomorrow, 07:23
Joined
Oct 19, 2020
Messages
161
Thanks DB. I may implement this in the near-future and report back my progress.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:23
Joined
Feb 19, 2002
Messages
42,974
Access is not omniscient. If you want it to link to a different BE, you need to tell it the name and location of the BE. WHY would the users ever, on their own, have to relink a BE? This is just not something an individual should do. What happens if 5 people relink and the sixth doesn't get the memo and keeps using the old BE???? Really? This is a recipe for disaster.
 

Sampoline

Member
Local time
Tomorrow, 07:23
Joined
Oct 19, 2020
Messages
161
Access is not omniscient. If you want it to link to a different BE, you need to tell it the name and location of the BE. WHY would the users ever, on their own, have to relink a BE? This is just not something an individual should do. What happens if 5 people relink and the sixth doesn't get the memo and keeps using the old BE???? Really? This is a recipe for disaster.
Hi Pat,

I think you misunderstood my query. The BE isn't necessarily different. But only the admin (me) would re-link BE tables to the master FE and then redistribute FE copy to users. I was just wondering if in the scenario a folder name or location changed for a particular reason, how could I avoid having to manually re-link the tables. So just wanted to gauge what type of solutions there may possibly be. Thanks.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:23
Joined
Feb 19, 2002
Messages
42,974
I guess you're right. I have no idea why opening the linked tables manager to relink the tables would be a burden to you. If you have multiple- BE's, the linked tables manager is a PITA but at least the current version supports multiple BE's. If you need an alternative, I can post one.
 

Solo712

Registered User.
Local time
Today, 16:23
Joined
Oct 19, 2012
Messages
828
Hi Pat,

I think you misunderstood my query. The BE isn't necessarily different. But only the admin (me) would re-link BE tables to the master FE and then redistribute FE copy to users. I was just wondering if in the scenario a folder name or location changed for a particular reason, how could I avoid having to manually re-link the tables. So just wanted to gauge what type of solutions there may possibly be. Thanks.
Assuming that the network mapping is the same for the developer and the users, then after the BE has been relocated to a new location, the new FE at the individual stations will not have to be re-linked. Most larger organizations have a network share dedicated to corporate data, and use a common drive letter for it. If you don't have that sort arrangement it is still possible to automate the relinking, assuming that the filename is the same except for the "_BE" suffix. Before you first open the Db, check if the existing BE is available. If it is, open the database. If not, perform a recursive search through pertinent shares and subfolders until you find the new path for the BE filename. Then, relink to the new path.

Best,
Jiri
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:23
Joined
Feb 19, 2002
Messages
42,974
Now we get to the problem.

Instead of using mapped drives to link the tables, use the UNC. You will have to type the path in manually when YOU use the linked tables manager:

//servername/folder/yourdb.accdb

That takes care of the BE. If the users have to reference or export documents on the shared drive, you can keep the UNC name in a table and provide that for them when they do inport/export operations.
 

Users who are viewing this thread

Top Bottom