Use relative address for database query

[Horatio J. Bilge, Jr.]

I used a query in Excel to link to an Access query.
The problem is that when I moved the files to a new folder, I could no
longer refresh the external data (excel was still looking for the *.mdb file
in the old folder). Is it possible to use a relative location for the db
file, instead of an absolute location? It will always be in the same folder
as the excel file.

Thanks,
~ Horatio

 

[FSt1]

hi
the file path to the access query is hard coded into the sql of the excel
query.
you must change the file path to the new location.
but from my experience and from a number of posts to this forum, changing
the file path in the sql doesn't seem to work as expected. the excel query
still points at the old location.
so i always just delete the old query in excel and create a new query for
the new location.
yeah, i know. sucks. so my best advise is to have someone spank your hands
everytime you get into the mood of moving something.

Regards
FSt1

 

[Horatio J. Bilge, Jr.]

Thanks for the advice.

hi
the file path to the access query is hard coded into the sql of the excel
query.
you must change the file path to the new location.
but from my experience and from a number of posts to this forum, changing
the file path in the sql doesn't seem to work as expected. the excel query
still points at the old location.
so i always just delete the old query in excel and create a new query for
the new location.
yeah, i know. sucks. so my best advise is to have someone spank your hands
everytime you get into the mood of moving something.

Regards
FSt1

 

[Horatio J. Bilge, Jr.]

I came up with a solution.
I wrote the query as a macro (using macro recorder for help). Then I used
the workbook path where needed in the query, since the workbook path and the
database path will always be the same. It looks like this:

Sub Query_1()
Dim sPath As String
sPath = ThisWorkbook.Path

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MS Access Database;" & _
"DBQ=" & sPath & "\MyDB.mdb;" & _
' etc... I went through the query code, and
' inserted sPath into the code wherever needed,
' just like the "DBQ=..." line above.
'
End With
End Sub