能否在SQL查询中添加自定义函数？MS Access中子查询结果如何拼接

方案可行性结论 #

你定义的VBA字符串拼接函数本身逻辑是可用的，调用无响应是因为SQL写法存在多处语法错误，修正后可正常实现行转列拼接的需求。

存在的核心问题 #

• 调用Coalsce函数时传参格式错误：该函数第一个参数要求是字符串类型的SQL语句，你直接将裸的SELECT子查询作为参数传入，Access SQL引擎无法将子查询识别为字符串参数传给VBA函数，直接触发语法错误。
• 子查询中使用了SQL Server专属语法[text()]，这是配合FOR XML PATH做拼接的写法，Access SQL完全不支持该语法，放在此处无任何意义还会引发报错。
• 缺少必要的资源释放逻辑：VBA函数中打开的Recordset和Database对象使用后没有关闭释放，长时间运行容易触发Access内存泄漏甚至进程卡死。

修正方案 #

1. 先优化VBA函数，增加资源释放和错误处理 #

Function Coalsce(strSQL As String, strDelim As String, ParamArray NameList() As Variant) As String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strList As String
    
    On Error GoTo ErrHandler
    
    Set db = CurrentDb
    If strSQL <> "" Then
        Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
        Do While Not rs.EOF
            strList = strList & strDelim & rs.Fields(0).Value
            rs.MoveNext
        Loop
        ' 去掉开头多余的分隔符
        If Len(strList) > 0 Then
            strList = Mid(strList, Len(strDelim) + 1)
        End If
        rs.Close
    Else
        strList = Join(NameList, strDelim)
    End If
    
    Coalsce = strList
    
ExitHandler:
    If Not rs Is Nothing Then Set rs = Nothing
    If Not db Is Nothing Then Set db = Nothing
    Exit Function
    
ErrHandler:
    MsgBox "拼接错误：" & Err.Description, vbCritical
    Resume ExitHandler
End Function

2. 修正SQL调用写法 #

需要把完整的查询语句用双引号包裹为字符串，作为第一个参数传入Coalsce，分隔符作为第二个参数传入，示例如下：

SELECT Switch(
    Coalsce(
        "SELECT DISTINCT obj_phase2.name 
         FROM ((((t_object obj_ds2
             INNER JOIN t_connector co2 ON obj_ds2.object_id = co2.start_object_id)
             INNER JOIN t_object obj_adu2 ON co2.end_object_id = obj_adu2.object_id AND obj_adu2.Stereotype='EUM_ADU')
             LEFT JOIN t_object obj_prx2 ON obj_prx2.Classifier_guid = co2.ea_guid)
             LEFT JOIN t_connector pc2 ON obj_prx2.Object_ID = pc2.Start_Object_ID AND pc2.Stereotype = 'trace')
             LEFT JOIN t_object obj_phase2 ON pc2.End_Object_ID = obj_phase2.object_id
         WHERE obj_ds2.Stereotype = 'EUM_Data-Stream' AND obj_ds2.Object_ID = 13919",
        ", "
    ) = "", "",
    True, "teste"
) AS [test]
FROM t_object

额外注意事项 #

• 如果需要批量查询t_object表中每一行对应的拼接结果，不要把obj_ds2.Object_ID写死为13919，需要动态拼接参数值，数值类型直接拼接、字符串类型需要额外加单引号包裹。
• 当前SQL没有加筛选条件会返回和t_object行数一致的重复结果，如果只需要返回一行计算结果，可增加TOP 1限定，或者使用Access专属的伪表语法避免冗余扫描。

内容的提问来源于stack exchange，提问作者vascobnunes