linked tables in same directory 1

[idd]

Hi,

Is there a way to link tables using a relative path so that my backend and frontend database could be moved but the linked tables will stay linked.

i.e. if they are both in c:\mydocs
the backend with the tables called back.mdb and the frontend with everything called front.mdb

can i link the tables by showing a relative path, letting access know that the backend with the tables is in the same directory and the filename is back.mdb

does the question make sense

 

[mikey69]

I think this is not possible, because Access reemmebers details of the path not the relative positions of the two folders. Why do you want to do it?

One related issue - if you are working across a network with multiple front ends, be sure to point via network neghbourhood even if the front end is on the same machine. That way, yyou can copy a master front end to all machines, without having to re=establish links.

 

[Steve101]

If you provide me an email address, I'll send over some application independent code to do the trick. Its encapsulated within a form, so a bit hard to publish here,
Cheers,
Steve

 

[mikey69]

Hey Steve - I'd like to see that code too. tks in advance

youmike@mweb.co.za

 

[Steve101]

Mikey69,
Its on its way,
Steve Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au

 

[scriverb]

The process for this that I use is this. Four files. Front-end, Back-End, System.mdw resident in same folder as the back-end, and an .ini file that is updated with the path to the System.mdw file. This .ini file is initiate through the application shortcut( /INI "c:\windows\app.ini&quot along with the system.mdw ( /WRKGRP "g:\databases\backendfiles\inventory\system.mdw&quot path.
INI file info:.
[Options]
SystemDB=g:\databases\backendfiles\inventory\system.mdw

The MainMenu strips off the path(in red) and checks to see if the .ini path to the System.mdw is the same as the links from the front-end to the back-end. If they are different then code loops through the linked tables and re-attachs according to the new pathway which came from the .ini file.

This way when you move your database you only have to go into the .INI file and change the pathway to the system.mdw file. Everthing else then happens automatically.

Just another way to do it.

Bob Scriver

 

[Steve101]

Good one Bob; I like that. Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au

 

[Steve101]

A variation of Bob Scriver's excellent posting above; something that I sometimes do, which has no dependence on the mdw or ini files. Its useful when you KNOW that the frontend and the backend will ALWAYS be located in the same folder. I sometimes design systems where I make this the case.

What happens here, is when the Client is initiated, its path is tested against the path of its first attached table.

If these paths are equal, then no further action is taken re linking the client's tables to the backend.

If these paths are NOT equal, then the client initiation process re-links ALL of its linked tables using the path of the client program, before continuing.

This technique can be totally transparent, as it requires no action on the part of the installer/user.

I've found it very useful for copying a system and its data to a second 'test' or 'development' folder. Its nice and safe, because you dont run the risk of forgetting to relink the 'test' client, which otherwise inadvertently may remain linked to the real production data during your 'development' / 'testing' (poor mans development/testing environment).

Works well for me, and whilst it lacks total flexibility (re totally independent locations for client and backend), in practice I've found that many of my apps happily and logically co-exist in the same folder (or based on a simple relative offset folder).

Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au

 

[idd]

Hi Steve,

My e-mail address is

iadmani@lec.ac.uk

thanks in advance

iddris

 

[scriverb]

Steve101: I like you idea especially the part of "forgetting to relink the 'test' client, which otherwise inadvertently may remain linked to the real production data ." Been there, and done that before!!!

I had to develope this relinking process due to mass distribution to our local offices where we had multiple servers and had to store the data(back-end) on the primary domain server in that office. As each user may be hard linked to a different server in the office for their "home files" say their "g:\" drive we couldn't just send it out already linked to their G drive. So, we developed this process of installing on the PC with the .ini being updated with the location of the system.mdw being updated with the serverID path, (i.e. \\AS9801-1\databases\backendfiles\inventory\system.mdw ).

In the OnLoad procedure I would try to read a recordset record from a linked table and trap for the error as the path couldn't be found. That way I knew that this front-end had to be re-linked. The relinking subroutine would then kickin using the .ini file info to relink all files.

One of the down sides to relinking a production application this way especially if you have large data files on the back-end, is that the queries perform a re-optimization the first time they are executed at each PC site. That can be a problem with end-users when they have to sit their while the ACCESS Jet performs its processes.

We have recently gone to one Windows 2000 server in each office where everyone is mapped to this server through a hard mapping so this situation has been solved. All applications go out already linked to the G drive mapping and no relinking or re-optimization has to take place. I am relieved because it is so much more efficient.

Works pretty well for this type of distribution process.

Bob Scriver

 

[Steve101]

iddris,
It will be on its way shortly. Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au