如何将 VBA 变量用于 MS-ACCESS 查询的 IN 'SourceDB' 子句 [英] How to use VBA variable for IN 'SourceDB' clause of MS-ACCESS query

问题描述

我正在尝试将 vba 字符串变量传递给查询构建器视图中 SQL 语句的 IN 子句.

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.

谢谢.

推荐答案

通常，您可以随心所欲——使用函数来提供参数字符串.

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()

但是在某些部分/情况下，您不能使用变量.IN 子句都在其中.

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

这些不起作用:

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

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

并且您的用例也不可能:

and your use-case is not possible either:

GetDbPath = "C:\path\myDb.accdb"

SELECT * FROM bar IN GetDbPath()

您必须动态构建整个 SQL:

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

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

这篇关于如何将 VBA 变量用于 MS-ACCESS 查询的 IN 'SourceDB' 子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！