sql - How to use VBA variable for IN 'SourceDB' clause of MS-ACCESS query

Question

I am trying to pass a vba string variable to an IN clause of a SQL statement in the query builder view.

the string is created by the following function:

Public Function GetBackEnd()
    If Len(GetBackEnd) = 0 Then GetBackEnd = BackEnd
End Function

backend itself is derived from a dropdown box in userform, there are two entries in a table with two different addresses, one each for the live and developement DB's. The dropdown box sets the "environment" variable upon selection.

Property Get BackEnd() As String
    Select Case Environment
        Case Is = "Development"
            BackEnd = DLookup("VariableValue", "Globals", "Variable= 'TestEnvironment'")
        Case Else
            BackEnd = DLookup("VariableValue", "Globals", "Variable= 'Backend'")
    End Select
End Property

I have tried a couple of variations on the following but get an error each time.

SELECT *
FROM TableName IN 'GetBackEnd()';

I imagine its something simple but after staring at this for so long Ijust can't see it.

thank you.

Answer 1

Generally, you can do what you want - use a function to provide parameter strings.

Public Function GetName() As String
    GetName = "foo"
End Function

SELECT * FROM bar WHERE floo = GetName()

But in some parts / cases, you can't use variables. Both IN clauses are among them.

These won't work:

GetInList = "'x', 'y', 'z'"

SELECT * FROM bar WHERE floo IN (GetInList())

and your use-case is not possible either:

GetDbPath = "C:pathmyDb.accdb"

SELECT * FROM bar IN GetDbPath()

You will have to construct the whole SQL on the fly:

Db.QueryDefs("myQuery").SQL = "SELECT * FROM TableName IN '" & GetBackEnd() & "'"