Change the link path

R

Ray

I have a database with front end and back end using at other office. The
front end is on the pc of each user and the back end is on the server. I
need to bring the database to my office for modification and then send the
database back to that office for trial/use by email. When I modify the
database at my office, I need to change the link path. However, I found no
way to change it back to original path. Can someone advise me how to modify
the link path on the database.

Thanks,

Ray
 
A

Allen Browne

There is a linked table manager to help with this task:
Tools | Database Utilities

Programmatically, you can set the Connect property of the TableDef.

Another alternative is to simulate the drive letter on your local computer,
using the SUBST command in a batch file, e.g.:
subst T: C:\Data\SomeFolder
 
R

Ray

Allen,

The first one cannot be used on site. Regarding the second and third one,
could you please give me more detail how to construct it into my current
database. Your guidance is greatly appreciated.

Thanks,

Ray
 
A

Allen Browne

Programmatically, the basic idea is this:
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim sConnect As String
Dim sName As String
sConnect = ";DATABASE=C:\SomePath\SomeFile.mdb"
Set db = CurrentDb()
For Each tdf In db.TableDefs
With tdf
sName = .Name
If .Connect <> vbNullString Then
If Not (Left$(sName, 4) = "MSys" Or Left$(sName, 1) = "~") Then
.Connect = sConnect
.RefreshLink
End If
End If
End With
Next
Set tdf = Nothing
Set db = Nothing

There is an example in Microsoft's sample database named solutions.mdb of
how to pop up the Windows File Open dialog for the user to locate where the
back end file is is located, and reconnect. Download from:
http://msdn.microsoft.com/library/officedev/bapp2000/mdbdownload.htm


As an example for the batch file, if you needed to simulate a T: drive on
your local PC the batch file would contain something like this:
subst T: C:\SomePath

Then in the AutoExec macro of your database, you would RunCode the Init()
procedure to execute the batch if it has not already been run this session:
Public Function Init()
Dim strFile As String
strFile = "C:\SomePath\AssignTDrive.bat"

If IsBadDrive Then
Application.FollowHyperlink strFile
End If
End Function
Private Function IsBadDrive()
Dim Dummy As Variant
On Error Resume Next
Dummy = Dir("T:\")
IsBadDrive = (Err.Number <> 0&)
End Function
 
G

Guest

I put both frontend and backend on the server and when I
want to change the frontend I open the frontend from the
server and make the changes. On all the users desktop I
use DOS to Xcopy the frontend to their computer so no one
is actually using the frontend off of the server. Each
time they click on the Access icon on their desktop it
brings a copy over.
 
R

Ray

Thanks for your sharing idea. It is good for maintenance. However, it
exposes where the databases are located. Currently, I send a mde file of
front end database that linked with the back end database on a hidden folder
of the server. Most of users cannot track down where is the back end
database located. It will minimize any interference to your databases.

Ray
 
R

Ray

Allen,

Many thanks for your useful information.

I tried to create a virtual drive to simulate the client and server
environment but unsuccessful according to your suggestion. The path on the
server is \\Server1\bedb$. I did subst \\Server1\bedb$ E:\backend_database.
It seems the first part is an invalid parameter according to subst command
or I incorrectly applied it.

Ray
 
A

Allen Browne

Fair enough, Ray.

Personally, I've only used the SUBST where my client actually uses a drive
letter such as T: in the Connect property, and I actually reassign the
Connect property programmatically if they have used a UNC.
 
Top