Link to data on another Network?

[Big Pat]

Sorry if the following is a bit long-winded!!

I have a database split FE/BE where the front-end sits on my laptop's desktop screen and connects to a back-end at my employer's head office, when I'm there 1-2 days a week (I don't need it at my usual office.)

Several colleagues at other branches would like to do something similar, but connecting to back-ends on their OWN networks, not head office. Their data will be different but the data STRUCTURES are identical. So this should work. All they need to do is link the tables to the new path on their network.

But my boss wants to know if I can set up these paths for them in advance - remotely - "so they don't break it". Is this possible?

One colleague has sent me the full path on her network where she has filed her copy of the back-end. I thought I could make a copy of my front end and change the path to the back end using Linked Table Manager. I knew that the copy would not then work for ME, but I thought I could save the path so it would work for HER, if I zipped it and emailed it.

So of course I can't SELECT the path using Linked Table Manager, because I'm not on her network, but is there any other way to tell Access that that's where I want it to point? Somewhere I can paste that path in and have it saved?

Of course she *could* do it herself if I typed up some instructions and screenshots, but I'd have to tell her about holding down SHIFT to disable startup macros etc. And we'd rather avoid telling people about that option. They're not IT people and the boss's fear is somewhat justified. There would be approx 10 copies, all on different networks. All using MS Access 2010.

So making ten copies with the ten different paths would make for an easy rollout. Otherwise I may end up having to actually go to those offices.

I'd be grateful for any advice.

 

[CJ_London]

I would investing using VBA to refresh the tabledef.

You can can ship your front end still linked to your backend and in your initial startup form the first task is to check if the links are still valid and if not, refresh them with the new path.

There are a number of ways you can check if the link is valid - for example by inspecting the tabledef and finding the path, then seeing if the path exists or by trying to open the table and trapping the error.

 

[Big Pat]

Thanks for that, but I might be getting out of my depth here. The bit I should have added was "I'm not really IT people either, more of a hack who likes to have a go".

I just googled it and didn't follow much of what I read, but are you saying I could run some code like below, assuming the following?

Path: as shown; database name: PM_Database; table name: Projects

Dim dbs As DAO.Database
 
Set dbs = CurrentDb
 
dbs.TableDefs(Projects).Connect = "\\uhb\userdata\R & D\CLRN\Common\RM&G Info\Metrics\" & PM_Database
dbs.TableDefs(Projects).RefreshLink         ' Relink the table.

And would I need to repeat those last two lines for tables called "Activity", "Staff" and some others?

Am I even in the ballpark?

 

[CJ_London]

pretty much there, missing the file extension and some quotation marks

dbs.TableDefs("Projects").Connect = "\\uhb\userdata\R & D\CLRN\Common\RM&G Info\Metrics\" & PM_Database.accdb

 

[CJ_London]

you can test it on your own system by using the path/file to your BE

 

[Big Pat]

CJ_London: Thank you so much. Brackets, dots, quotes, etc. are where I always go wrong, so I appreciate the edit. It may be the middle of next week before I get a chance to test that, so I just wanted to come back before that and say thanks. I’ll let you know how I get on.

AccessBlaster: No I can’t. In fact “boss”, “head office” and “branch” are misnomers. We’re more of a network of people working in research across several different hospitals, all subject to our own IT policies. It can be challenging!!