By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,652 Members | 1,842 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,652 IT Pros & Developers. It's quick & easy.

Automate table re-linking

P: 44
Hi,

How do I automate table re-linking? Presently I have to do this through link table manager. Thanks
Oct 29 '06 #1
Share this Question
Share on Google+
8 Replies


NeoPa
Expert Mod 15k+
P: 31,487
Try :-
Expand|Select|Wrap|Line Numbers
  1. CurrentDb.TableDefs("tblMyTable").RefreshLink
or, for all of them :-
Expand|Select|Wrap|Line Numbers
  1. Dim tblTable as TableDef
  2. For Each tblTable In CurrentDb.TableDefs
  3.     If tblTable.Connection > "" Then Call tblTable.RefreshLink
  4. Next tblTable
Oct 29 '06 #2

P: 44
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
Oct 30 '06 #3

NeoPa
Expert Mod 15k+
P: 31,487
Well that all depends on what you are trying to achieve.
If you can explain clearly what you want to do...
Oct 30 '06 #4

P: 44
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
Oct 30 '06 #5

Expert 5K+
P: 8,434
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.
Oct 30 '06 #6

P: 44
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
Oct 30 '06 #7

Expert 5K+
P: 8,434
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.
Oct 30 '06 #8

NeoPa
Expert Mod 15k+
P: 31,487
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.
Oct 30 '06 #9

Post your reply

Sign in to post your reply or Sign up for a free account.