Absolute v Relative Path Help...

[KLZA]

Hi. I have an Access DB that resides on a network drive. Not
everyone in my company maps the network drive the same. Anyone can
map it with any letter (ie - drive H: M: etc). The database looks
for
a text file in the same root directory where the DB resides to email
it. Since anyone can map there drive letter, i need a relative path
to the text file. Is it possible to have the access DB look for the
text file using a relative, rather than absolute path?

It currently works for anyone mapped as H. See below..


BodyFile$ = ("H:\Customer Service\CiRT\CIRCUIT REQUESTS ETC
\emailbody.txt")


I need it something like this:
BodyFile$ = (".\emailbody.txt") or BodyFile$ = ("emailbody.txt")


I tried CurDir$ and App.Path but I'm not getting anywhere...

 

[akphidelt]

I am not sure exactly how to search directories like that but I have run in
to that exact same problem. The way I dealt with it was if the Main_Path came
up with an error I opened up the dialog box and gave them a chance to choose
the correct file. It looked something like this

Dim fDialog As Office.FileDialog
Dim varFile As Variant
Dim Main_Path as String
Main_Path = "YourPath.mdb"
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)

With fDialog
.Title = "Select File"
.Filters.Clear
.Filters.Add "All Files", "*.*"

On Error GoTo Err_Problem
If .Show = True Then
For Each varFile In .SelectedItems
Main_Path = varFile
Next
Else
MsgBox "You clicked Cancel in the file dialog box. No Path was
selected!", vbCritical
DoCmd.Close
Exit Sub
End If
End With

If you use this you have to select the office object library in the references

 

[Dale Fye]

If the text file is in the same folder as the application (mdb or mde file),
then you could use:

BodyFile$ = CurrentProject.Path & "\emailbody.txt"

HTH
Dale

 

[Tom Wickerath]

Hi KLZA,

Yes, this is possible. CurrentProject.Path should reveal the correct
directory. However, your database *should* be split into what is commonly
termed a "front-end" (FE) application file, and a "back-end" (BE) data file.
Each user should have their own copy of the FE application file running from
their local hard drive. Please see this article for more information on this
topic:

Implementing a Successful Multiuser Access/JET Application
http://www.accessmvp.com/TWickerath/articles/multiuser.htm

In this scenerio, it is still possible to determine the correct path to the
BE database, so that you can have a single copy of the emailbody.txt file.
You just need to programmatically determine the .Connect property for a
linked table. An example is shown in my ImageDemo sample:

http://www.accessmvp.com/TWickerath/downloads/ImageDemo.zip


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Tom Wickerath]

You just need to programmatically determine the .Connect property for a

linked table. An example is shown in my ImageDemo sample:

http://www.accessmvp.com/TWickerath/downloads/ImageDemo.zip

I went ahead and looked it up. Here is a shortened form of the code, from
the form named "frmEmployees":

Private Sub Form_Load()
On Error GoTo ProcError

'Determine if tblEmployees is a local or linked table. This is
'used to set the initial folder for the FileDialogPicker.

Dim db As DAO.Database
Dim tdf As DAO.TableDef

Set db = CurrentDb()
Set tdf = db.TableDefs("tblEmployees")

If Len(tdf.Connect) > 0 Then
mstrInitialDir = tdf.Connect
mstrInitialDir = Mid$(mstrInitialDir, 11, InStrRev(mstrInitialDir,
"\") - 11)

Else
mstrInitialDir = CurrentProject.path
End If

'Debug.Print mstrInitialDir

ExitProc:
'Cleanup
Set tdf = Nothing: Set db = Nothing
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in Form_Load event procedure..."
Resume ExitProc
End Sub


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________