to use module in sql ms-Access-2010

Question

I have defined a procedure in my module, returning the path of the database and I want to use that path in my sql query.

Procedure in module :

Public Function pathOfBillingSoftware() As String

    pathOfBillingSoftware = """path"""

End Function

I am using the function above in my sql query :

SELECT *
FROM tableName IN  pathOfBillingSoftware();

It is giving me the error: "error in from clause"

but when I am using it in vba code , it is working fine

a = pathOfBillingSoftware()

  sql = "INSERT INTO tableName  " & _
     "IN " & a & _
    " SELECT * FROM tableName"

    currentdb.Execute sql

Any Solution ?

I believe @Erik gave you the answer (method). Don't know why he deleted it. — Gustav, Jan 08 '18 at 08:07
Yes. As @Erik noted, you cannot use a function this way in SQL. So use VBA to build the SQL, then run/call it (similar to your second code block) in VBA to open a recordset. — Gustav, Jan 08 '18 at 08:24
@Gustav I wanted to verify that the string delimiters weren't the issue (if this were possible, they would likely cause an error). Verified now that they weren't — Erik A, Jan 08 '18 at 08:29

Erik A · Accepted Answer · 2018-01-08T08:32:37.997

2

This is dynamic SQL. You seem to already have found the solution: use VBA code. You can only use functions to return values in static SQL.

If you want to return operators or other things that aren't standard values, you need to use dynamic SQL, and thus need to use VBA.

You can create queries through VBA if you want, but note that once they are created, they are static.

Sample code to create a query with your function

CurrentDb.CreateQueryDef "MyQuery", "SELECT * FROM tableName IN " & pathOfBillingSoftware();

edited Jan 08 '18 at 08:32

answered Jan 08 '18 at 07:57

Erik A

SELECT * ,pathOfBillingSoftware() as path FROM tableName . why this is working fine . – Harshit kyal Jan 08 '18 at 08:32
Yes, obviously, because in that context, path is a variable. In the `IN` context, you need to use a static string, not a variable, and thus it can never be a function. – Erik A Jan 08 '18 at 08:33
1

Yes, exactly (well, if it doesn't have to be a query, you could use a form in datasheet view with a dynamic record source to emulate a query, like in [this question](https://stackoverflow.com/q/21271497/7296893)) – Erik A Jan 08 '18 at 08:48

1 Answers1