How to update path to linked tables?

[crillux]

Finally my first little project is ready to go live, but I am stuck in the handling of linked tables as I have split the database.

My problem is that the links to the table in the FE part points to the location where I did the development. And this means that the links doesn't work when I deploy the app using Dev Kit install package.

I have found example databases that have code that handles this issues, but these examples have been so large and complicated that I just don't understand it.

I would appreciate som help with:

1) check if the path is correct - are the tables (or the BE part) in the specied location? I don't even know which properties to look for...

2) set the path - is it possible to set a path that is relative to the location of the FE part? If not, how can I create a box where the user can choose the location?

 

[ghudson]

Can you relink the tables yourself to the production backend database file before you send the users their front end copy to install on their computer? That would be the easy way.

 

[crillux]

I guess I could, but I would still like to be able to handle it using code for future apps.

I am especially interested in if I could set a relative path since that would make life easier.

Is there a way to extract the path for the FE part? If so, I could make the install package put the BE part in the path "<FE part>\data", add "\data" to the extracted FE path and then I could set the link property to point to that location.

But I would still need some help on how to extract the path, and what property to change to get the links correct...

 

[DCrake]

You can split the FE and BE but having the two mdbs on the same drive will not work in a multi user setup. The BE must be on a shared network location.

For now move your back end to the shared location and reconnect using the LTM. Repackage and deploy. Then work on automating it later.

 

[crillux]

You can split the FE and BE but having the two mdbs on the same drive will not work in a multi user setup. The BE must be on a shared network location.

For now move your back end to the shared location and reconnect using the LTM. Repackage and deploy. Then work on automating it later.

Thanks, will do.

Do you have any info at all in response to my questions above to get me started?

 

[Peter Bellamy]

How about this a way to proceed?
At the time of installation open a form and ask the user where the backend is has been installed.
Use that path to Re-link the BE db tables, maybe using DoCmd.DoMenuItem lines.

pnb

 

[crillux]

I think I found a process to handle it, but I would appreciate some coding assistance.

This is what I have in mind:
1) As I put <FE path>/data as default path for the BE in the Installation Wizard, I will start by checking wheter the BE part is there or not. This I guess I could do with dir().. The tables will then be linked with the code below (I found it on the net and have not tested it yet. Comments on it would be appreciated).

2) If it is not there, I prompt the user for the path (how do I do this?).

Now, the code below (if it is working) links one table. Can you advice me on how to use an array and a for...each loop to get all tables re-linked?

 

[DCrake]

This is not exctly what you are looing for but has code in there for liinking tables with automation.http://www.access-programmers.co.uk/forums/showthread.php?t=170607

 

[crillux]

@DCrake

Thanks a million! The code with your comments is great. Suddenly I understand a lot more.

On other question... Is there a simple way to get a file browsing dialog where the user can select a path/folder, and then put this in a variable?

 

[ghudson]

@DCrake

On other question... Is there a simple way to get a file browsing dialog where the user can select a path/folder, and then put this in a variable?

You can check out my Browse [Find a directory or file] sample.

 

[BrokenBiker]

Attached is what I use. It will map a network drive and/or open a browse window to select the BE file for the tables to link to.

The DB will create a command file (based on CurrentProject.Path) to map the network drive, and then delete the command file. The VBA is in a module and called into use in the form.

The VBA for the browser is in a module. The rest of the code to re-link the tables is in the form.

The attached DB is not set up for operational use. It only holds the objects and VBA to better show how it should be set up.

Typically, I have these functions operate in the opening/blank form. When everything checks good, the opening form will close and open the main form for the database.

Hopefully this helps.