Make linked table have a relative path

[ghadley_00]

Hi,

I have an access database with a table linked to an external text file.
Occasionally the location of the database and the text file change (but
are always in the same directory). Is there a way to have the location
of the external text file be a relative, rather than absolute path (to
be .\textfile.txt rather than C:\dir\textfile.txt)?

Any advice that can be provided would be provided would be greatly
appreciated/

Best wishes,

George Hadley
gh********@yahoo.com

[Ron2006]

Here is how I solved that type of problem for our site.(This example is
doing more than you need, but gives you the idea of how it might be
handled.)..................

I have created a control file within the app defined as:

TimeAnalysisControlTable
ControlID text
ControlInfo text

I place in that table such things as Report Directory, BaseDirectory,
etc anything that is static but is subject to change on occasion. This
way no path is hard coded anywhere in the application.

For instance
ControlID ReportDirectory
ControlInfo C:\MYApp Directory\

I have the following query defined

SELECT TimeAnalysisControlTable.ControlInfo,
TimeAnalysisControlTable.ControlID
FROM TimeAnalysisControlTable
WHERE (((TimeAnalysisControlTable.ControlID)="ReportDire ctory"));

and it is called "Query - Get Report Directory"

========================================

In my vba module I have a createanalysis function: it deletes the
output report if it happens to have already been run so that it can be
replaced by a more recent version. I then copy a master that has some
special formating etc. and then export into that copy.

function CreateAnalysis()

MasterDir = DLookup("[ControlInfo]", "Query - Get Masters
Directory")
ReportDir = DLookup("[ControlInfo]", "Query - Get Report
Directory")

ReportFileName = "Time Analysis Report " & Year(EndDate) &
Format(Month(EndDate), "00") & Format(Day(EndDate), "00") & ".xls"

With Application.FileSearch
.NewSearch
.LookIn = ReportDir
.SearchSubFolders = False
.Filename = ReportFileName

If .Execute() = 1 Then
Let match = ""
VBA.FileSystem.Kill ReportDir & ReportFileName
End If
End With

VBA.FileCopy MasterDir & "Time Analysis Ding Master.xls", ReportDir
& ReportFileName

ReportFileName = ReportDir & ReportFileName

DoCmd.TransferSpreadsheet _
TransferType:=acExport, _
SpreadsheetType:=acSpreadsheetTypeExcel9, _
TableName:="Query - All Employee Ding Only", _
Filename:=ReportFileName, _
HasFieldNames:=True, _
Range:="ExportData"

function end
===========================================
That is the general gist of the function. I does other things but that
is how I use the dlookup and the control file.

I started out a long time ago having a single record file with many
fields, but whenever I had to add another field it was a bear. Then I
saw a reference to this type of structure in in one of the groups and
have converted over to using it.

Ron

[Tanis]

gh********@yahoo.com wrote:

Hi,

I have an access database with a table linked to an external text file.
Occasionally the location of the database and the text file change (but
are always in the same directory). Is there a way to have the location
of the external text file be a relative, rather than absolute path (to
be .\textfile.txt rather than C:\dir\textfile.txt)?

Any advice that can be provided would be provided would be greatly
appreciated/

Best wishes,

George Hadley
gh********@yahoo.com

If you link to a file on a local area network, make sure to use a
universal naming convention (UNC) path, instead of relying on the drive
letter of a mapped network drive in Windows Explorer. A drive letter
can vary on a computer or may not always be defined, whereas a UNC path
is a reliable and consistent way for Microsoft Access to locate the
data source that contains the linked table.

[ghadley_00]

Is there any way to do this non-programmatically? (just alter the
settings for an existing linked table)?

Thanks,

George

[Ron2006]

Yes. It is just a question of changing the way you get the mappiong.

For instance

Letter mapping:
V:\Team folders\Team\Databases\

UNC mapping:
\\gsgw.CompName.com\share\Team folders\Team\Databases\
=======================

Underlined part is same as V part

The front part is basically the network mapping. Your network contact
can tell what that front part should be that you would replace V :\
with. or you can somtimes see it when you open my computer for the
network drive mapping.

In fact if they tell you that you can actually use that in IE to start
looking at and you will see the same thing that you would see if you
had put in the letter format.

[Rick Wannall]

I think you're going to have to write a little bit of code.

I can't find a way to make the browse dialog (in Get Data | Link Tables) to
store the path as relative. I can use expressions like
"..\datadirectory\data.mdb", but when the link is created it is resolved to
an explicit path.

The only way I know that you can do this is with code along this line:

Public Function xxx()

Dim strConnect As String
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Set dbs = CurrentDb
strConnect = ";DATABASE=..\..\Projects\CodeTest\datatest.md b"
Set tdf = dbs.TableDefs("Mailing List1")
tdf.Connect = strConnect
tdf.RefreshLink
Debug.Print tdf.Connect
Set tdf = Nothing
Set dbs = Nothing
Debug.Print "OK"

End Function

Here's the setup: both codetest.mdb and datatest.mdb are in
c:\projects\codetest.

Using the code above I was able to get the connection stored using relative
notation. Here's teh debug.print output from my 3 runs, each time changing
the assignment to strConnect:
;DATABASE=C:\Projects\CodeTest\datatest.mdb
OK
;DATABASE=..\CodeTest\datatest.mdb
OK
;DATABASE=..\..\Projects\CodeTest\datatest.mdb
OK

The printed strings are right out of the Connection property of the
tabledef. So far, so good. The thing to investigate is this: To WHAT
directory does a relative string such as this refer? You have to make sure
that you know what setting is being used as the starting point so that you
know that the ".." or "." or whatever is going up FROM the right place. If
you don't know that, you don't know that going back DOWN you wind up where
you intend.

Someone more knowledgeable about Access internal settings can probably tell
you the one to check on/set.

[Rick Wannall]

By the way, by "relative path" I assume that you mean "starting here, go up
one level and then down to directory xxxx".

The other post I see in this thread is showing you URL, which is not a
relative notation. Using URL you are not dependent on drive mappings, so
they are a good thing and really the best way to fly most of the time. But
they are not relative. They still point to a particular path on a
particular server. Nothing relative about it.

If you want URL notation, no sweat. Just browse that way when the link
tables dialog opens. If you browse to \\myserver\directory1\directory2...,
the link will be recorded with that path.

Which one are you after?

[ghadley_00]

Is there any simple way to make the database look in the same directory
that the database is in (i.e. ".") for the linked table?

Thanks,

George

[Rick Wannall]

In a form that opens before any other form, you can use the tabledefs
collection to loop through all your tables and set any connection string you
want:

dim db as dao.database
dim tdf as dao.tabledef
dim strConnect as string

strConnect = currentproject.path
strConnect = ";DATABASE=" & strConnect & "\mydata.mdb"

set db = currentdb
for each tdf in db.tabledefs
tdf.connect = strConnect
tdf.refreshlink
next tdf

set tdf = nothing
set dbs = nothing