Relative File path for ODBC connection

[dmcgimpsey]

Hi folks

I need to know how to specify relative file paths for ODBC connection, instead of using hard coded file paths. This is so I can launch my application from a shared drive or from a local drive (C:\)

Here is the example for reading a file:

Private Sub ReadFile(FileNum)
' Open "c:\Model Vision\Query Parts\part" & FileNum & ".txt" For Input As #1
Open ".\Query Parts\part" & FileNum & ".txt" For Input As #1
Do While Not EOF(1)
Line Input #1, LineofText
Print #2, LineofText
Loop
Close #1
End Sub


Also I need to specify the path in the ODBC connection too ...

Range("T4").Select
Range("T4").Select
With Selection.QueryTable
.Connection = Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=\Database\model_vision.mdb;DefaultDir=\Database;DriverId=25;FIL=MS Acc" _
), Array("ess;MaxBufferSize=2048;PageTimeout=5;"))
.CommandText = Array( _
"SELECT Secondarytest.model_id, Secondarytest.Column_NM, Secondarytest.Frequency, Secondarytest.MDL_Owner, Secondarytest.Model_Description, Secondarytest.MDL_Type, Secondarytest.Bus_Group, Secondarytes" _
, _
"t.Pop_Sel, Secondarytest.Target_system" & Chr(13) & "" & Chr(10) & _
"FROM `\Database\model_vision`.Secondarytest Secondarytest" & Chr(13) & "" & Chr(10) & _
"WHERE (Secondarytest.model_id<>' ') AND " & Qstr1 _
, _
Qstr2 & _
Qstr3 & _
Qstr4 & _
Qstr5 & _
Qstr6)
.Refresh BackgroundQuery:=False
End With


Thanks in advance

Don

 

[BrianB]

The base drive becomes relative by removing it from the code. The rest of the path must match though. eg.

    MyFile = "test.xls"
    MyPath = "\Reporting\"
    ChDrive "H"  ' not necessary if already set
    Workbooks.Open FileName:=MyPath & MyFile