MakeAbsolute() function in Select statement

  • Thread starter Thread starter Jsb111
  • Start date Start date
J

Jsb111

Access Queries seem require Absolute file address such as.

SELECT * FROM [Sheet1$] IN 'd:/project/test1/WorkSample.xls'[EXCEL 5.0;]

But I really do not like hardcoded absolute paths --they are limiting.

I would rather call something like this public function from my Select
statement
**************************************
'Makes a Relative path an Absolute path (Access2000)
Public Function MakeAbsolute(str) As String
Makeabsolute = CurrentProject.Path & str
End Function
**************************************
So I rather write the Select using the function:

SELECT * FROM [Sheet1$] IN MakeAbsolute("test1/WorkSample.xls")[EXCEL 5.0;]

Any Ideas on how to make this work? Am I missing some punctuation?
Thanks for any help
 
Hello.

The only problem is just that Access does accept functions as field value expressions only, and not in other parts of the query.

You need to write some VBA code to read the SQL string out of its location, parse and modify it to contain the name, and then write it back.
 
Back
Top