Change Drive Letter and Path for Linked Tables

[RBPM]

Good afternoon to the Forum!

Have an issue that I am hoping someone can help with.

Information:

1) Have two access databases one labeled PROG and another one labeled DATA. Basically front end and back end. All tables are in Data and are linked to from PROG (has all forms-queries-etc.)

2) The network drive is letter J and my local drive is C. The path where the PROG and the DATA mdbs' are stored is different.

3) Example of the path names is
a) c:\path1\path2\path3\DATA.mdb
b) J:\path4\path5\path6\path7\DATA.mdb

4) I keep a local copy of the PROG mdb on the C drive.

5) Since I am still developing the code on the local copy of PROG I want to switch the drive letter and path to get to the DATA mdb.

I know that I can do this by relinking all of the tables involved that are within in the DATA mdb.

Am hoping that there is a much better way of doing this.

Any help would be greatly appreciated.

Thanks

 

[smedvid]

One approach I've used in the past is to create DNSless Connection and Relink Tables... Also never assign drive letters, use UNC instead. UNC is \\NetworkServer\Shared\Directries\ where fixed drive letter is J:\Directories\. Can you be 100% sure J: Drive wil exist for all users and never change? Here are some code snippets that may be useful.. I relink all tables and unlink tables each time a user opens my front end. In my case I have SQL Server for a backend. htwh,


Public DBCONNECT As String
Public DBNAME As String
Public Const APPLICNAME = "CONTROL"

Function SetConnectionString()

Dim DBConn As New ADODB.Connection

AutoExec.DBNAME = "CONTROL_DB"
DBConn.ConnectionString = "driver={SQL Server};server=NewServerName;database=" & DBNAME & ";Trusted Connection=Yes"

...
End Function


Function RefreshLinkedTables()

Dim ThisDB As DAO.Database
Dim tdf As DAO.TableDef
Dim counter As Integer

On Error Resume Next

DoCmd.Hourglass True
Set ThisDB = CurrentDb

For Each tdf In ThisDB.TableDefs
counter = counter + 1
SysCmd acSysCmdSetStatus, "Loading...(" & Format((counter / ThisDB.TableDefs.Count) * 100, "Standard") & "%) "
If tdf.Attributes = dbAttachedODBC Then
tdf.Connect = "ODBC;" & DBCONNECT & ";TABLE=" & tdf.SourceTableName
tdf.RefreshLink
End If
Next

DoCmd.Hourglass False
SysCmd acSysCmdClearStatus

End Function



Steve Medvid
"IT Consultant & Web Master"

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[gusbrunston]

Hi.

This is probably too simple, but do you just want to speed up the process?

I don't know why, but it took years to learn that I could go to:

Tools>
Database Utilities>
Linked Table Manager>

Then click on "Always prompt for location",
then on "Select All", and "OK". Zippity do da, automatic.

Before that, I was relinking one table at a time.

If this helps, I'm glad.

[tt]
Gus Brunston - Access2000/2002(DAO)[/tt]

 

[earthandfire]

I have exactly the same problem which I solve with a very simple Batch file:

Assuming the server path is:

J:\path4\path5\path6\path7\

create a folder on you C: drive -

C:\MyJDrive

in MyJDrive create the appropriate structure:

\path4\path5\path6\path7\

Once this has been done create a Batch file on you Desktop which just contains the one line:

Subst J: C:\MyJDrive

whenever you need to use the copy of the database on your C: drive just run the Batch file.

This way the path for the backend is always:

J:\path4\path5\path6\path7\DATA.mdb


Hope this helps.