Automate table re-linking

[Zeeshan7]

Hi,

How do I automate table re-linking? Presently I have to do this through link table manager. Thanks

[NeoPa]

Try :-

CurrentDb.TableDefs("tblMyTable").RefreshLink

or, for all of them :-

Dim tblTable as TableDef
For Each tblTable In CurrentDb.TableDefs
    If tblTable.Connection > "" Then Call tblTable.RefreshLink
Next tblTable

[Zeeshan7]

Hi,

Where to put this code? To explain you further, I have a front end database and I want to link it with a back-end database which resides in other computer. Presently I can manage this manually through a link table manager where I can select the tables manually and browse for back-end location to refresh links for installation. Thanks

[NeoPa]

Well that all depends on what you are trying to achieve.
If you can explain clearly what you want to do...

[Zeeshan7]

Hi,

1- I have created an ms access application named as CDM.mdb. It is my front application.
2- Through database splitter, I have created a back-end file (contain all tables) named as CDM_be.mdb which resides in my sharing drive.
3- I have distributed my front end to various users.
4- Sometimes it is required to distribute and re-install the application and I have to establish the links manually through a link table manager.
5- Users can not open my application database window / link table manager as I have disabled this option, also bypass key.
6- I want to automate this re-linking process through a code or some other option so that users could run the command to establish links without my support.

Thanks

[Killer42]

Hi,
1- I have created an ms access application named as CDM.mdb. It is my front application.
2- Through database splitter, I have created a back-end file (contain all tables) named as CDM_be.mdb which resides in my sharing drive.
3- I have distributed my front end to various users.
4- Sometimes it is required to distribute and re-install the application and I have to establish the links manually through a link table manager.
5- Users can not open my application database window / link table manager as I have disabled this option, also bypass key.
6- I want to automate this re-linking process through a code or some other option so that users could run the command to establish links without my support.

Thanks

Are you saying that the back-end database is in a different location each time you reinstall? Or do you just mean that you have to change the links to point to the real back-end instead of your development one, or what?

I worked with an Access database years ago which had an automated function (developed by someone else) which would change all the links to point to a selected location. Unfortunately it is in a password-protected archive file and I forgot the password years ago, so can't get it out.

If I manage to get it unzipped, I'll let you know.

[Zeeshan7]

Hi, back-end resides in my shared folder in local disk C.
I have distributed front-end to other users remotely who are connected with my network drive where back-end shared database folder resides. Now, whenever when I need to install the application, I have to open my front end database window and link table manager to select and browse for back-end database location manually to refresh the links. I want to automate this procedure if it is possible. Thanks

[Killer42]

Hi, back-end resides in my shared folder in local disk C.
I have distributed front-end to other users remotely who are connected with my network drive where back-end shared database folder resides. Now, whenever when I need to install the application, I have to open my front end database window and link table manager to select and browse for back-end database location manually to refresh the links. I want to automate this procedure if it is possible. Thanks

Why can't you just change the links in your front-end to point to the back-end tables via the network address, and work that way yourself? (Keeping backups in case of problems, of course). Surely that would mean you no longer need to cvhange it each time you distribute.

[NeoPa]

Why can't you just change the links in your front-end to point to the back-end tables via the network address, and work that way yourself? (Keeping backups in case of problems, of course). Surely that would mean you no longer need to change it each time you distribute.

This is your best option.
Use a UNC link to your back end tables.
Assuming your PC is called 'ZeeshanPC' and the share you have defined for others to access via is called 'BackEnd' (with the database in the root of that share), then make a connection in Access to the back end database as \\ZeeshanPC\BackEnd\CDM_be.Mdb and connect to the tables from there.

If, for any reason this will not work then you can add some code to the OnOpen event of your first form (the one defined in StartUp options).
So, if you had a linked table called tblMain, then you can test if it works (using On Error Resume Next or whatever) and, if it doesn't, assume it's because it's accessing remotely and change the Connection property.
At this point I did some testing and found that, although it doesn't complain when you set the .Connect property, nor does it actually change it :(
So, unless someone else knows how set this, you'll have to go with the first option.

BTW - the .Connect property for a table (EG tblMain) would be CurrentDB.TableDefs!tblMain.Connect.
If it's blank then it's not a linked table.