Options for Networked situations

[Kelemit]

I have been having issues dealing with "moving servers" addresses, file relocations, and folder name changes.

this makes 'linking' quite difficult.

I came up with this option, and am wondering if this would work.

'Delete old table connection
DoCmd.DeleteObject acTable, "ExampleTableLink"

'remake them based on current location.
DoCmd.TransferDatabase acLink, "Microsoft Access", Application.CurrentProject.Path & "\DatabaseName.mdb", acTable, "ExampleTable", "ExampleTable"

Basically, this code deletes your current link, then recreates it based upon the path of the current database location. As long as the two databases are relativily equal in there locations, one could move the databases / be in completely different locations, and the links would always work fine.

IE:

One computer has the location as w:\database folder

Another has q:\somewhere\somehow\I\don't\get\it\database folder

3rd example \\serverWierd\someplacehot\cool\cheese.blue.org\database folder

and the links would still be established correctly.

In fact, old broken links would be erased and re-established correctly.

I KNOW there are limitations to this.

For example... If this is run from A FRONT END, then the front end's current location is give as the current location.

Can anyone think of a way around this? Without user interaction?

The only option I can think of for this example would be to allow a user to manually run this "relinking" process by re-copying out a new user interface from the backend setup.

IE:
If the current front end fails, tell user to go to backend location, open the "backup" user interface, on doing so, the links are re-established and a copy of the front end is put on user Computer with desktop link.

Don't really like that option though... too.. clumsy and dependent on user knowledge / skill.

Any other ideas guys?

Kelemit

 

[WayneRyan]

Kelemit,

You can just use the TableDef object. You don't have to delete the table, just
do something like:

For Each tdf In dbs.TableDefs
   If Len(tdf.Connect) > 0 Then
      tdf.Connect = ";DATABASE=" & Me.txtFilterDatabase
      tdf.RefreshLink
   End If
   Next tdf

But how do you programmatically find the database? With no user intervention,
you have to traverse the entire file system/network looking for your db. Is that what
you need to do?

Need more info,
Wayne

 

[Robert88]

Hi Kelemit

I have been having issues dealing with "moving servers" addresses, file relocations, and folder name changes.

Is this neccessary?

If you map a network drive to a share (say Z:\) and you have to move servers then disable the shared folder and renable the same share name on the new server.

Using scripting for individual users or maybe even a group you can re-map thier new server and share as the new Z:\ drive.

I do not see why you have users with all different types of connections?

Robert88

 

[Kelemit]

Apologies..

But the moving servers etc is not a current situation.

I'm setting up for possible changes / etc.

The reason, because they already did this, and since everything was statically applied, changing servers and site upgrades (especially when changing tech support companies) can cause lots of needless, but far reaching changes.

I want my database and its links to be independent of any such happenings.

If they move to another company, they simply can move their databases without worry that links will go dead, and their system stops working.

But, this only works if they don't use a front end on the local machine.

I just want to know if there is a way to do this, even with a front end.

As i think about it... I honestly don't see any way around it with a front end.

Which is why i am curious.

Kelemit

 

[Robert88]

Hi Kelemit,

Interesting problem one currently I am not able to find a solution.

I suppose you would also have to consider if the same name file is on the new destination server as well, not likely to happen but is possible.

Robert88

 

[Kelemit]

Honestly, at the moment, the only option i can think of is to keep a copy of all the FEs in the directory with the databases. When a server change, support company change, ZZZ change because of XXX, WHATEVER...

The company can simply regenerate the FE's by going to the FE's in the directory housing all the databases.

Currently, ALL databases are set to work within a relative distance from each other. IE, as long as the basic folder structure where the databases are at stays the same relationally, then each database can automatically find each other regardless of where they are placed, as long as you move THE WHOLE set of databases and maintain the directory structure they are currently in.

server\directory\(Databases, and directories to other databases)

As long as the LAST portion (Databases adn directories) are moved as a whole, the links will be able to stay in tact, regardless of where you move the whole chunk. On each open, and databases with linked tables will automatically RELINK the data using the current directory / path that the database is in, and then link from there to the rest of the databases, because of the KNOWN directory structure that I created.

But using a FE will cause problems. FEs will report the CURRENT DIRECTORY of the FE. Therefore, FE's on local machine will report something like c:\documents and settings\user\desktop\fe.mdb.

So, as I said, I guess the only option would be to leave a copy of each FE in with the BE of its corresponding BE. When a move is needed, TEACH.. (sigh.. dangerous) someone how to open the FE in the directory that the BE is in, then have it relink approbriately, then copy and pass that puppy out, overwriting old FE's...

But I'm not sure... Don't know if I really like this setup....

 

[KeithG]

Can you make a form for the user to enter the directory of the links that need to be refreshed and then refresh the links with the directory provided?

 

[Robert88]

Hi Kellmet,

Not sure if you can get your hands on a magazine I have subscription?

The article is written by Doug Steele, titled: "Connect Your Split Application", document #17454 from the magazine "Advisor Guide to Microsoft Access" on page 36.

If you had subscription it is located at http://My.Advisor.com/Doc/17454

Plenty of functions discussing reconnection.

I hope you can get a hold of it and hope it helps?

Robert88