ms-access保存查询结果为字符串 [英] ms-access save query result in a string

问题描述

我在查询部分保存了一个查询.我正在从VBA运行查询.可以将查询结果保存为字符串吗?

I have a query saved in the queries section. I am running the query from VBA. Is it possible to save the results of this query to a string?

推荐答案

ADO记录集具有GetString方法，可能对您有用.

An ADO Recordset has a GetString method which might be useful to you.

我有一个名为 qryListTables 的查询，如下所示:

I have a query named qryListTables which looks like this:

SELECT m.Name AS tbl_name
FROM MSysObjects AS m
WHERE
        (((m.Name) Not Like "msys%"
    And (m.Name) Not Like "~%")
    AND ((m.Type)=1))
ORDER BY m.Name;

请注意，查询使用而不是*作为通配符.选择该选项的原因是ADO需要ANSI通配符(%和_而不是*和?).

Notice that query uses % instead of * as the wildcard character. The reason for that choice is that ADO requires ANSI wild card characters (% and _ instead of * and ?).

我可以使用以下函数通过如下调用来吐出一个字符串，该字符串包含数据库中常规表的带引号的名称，并用分号分隔:

I can use the following function to spit out a string containing the quoted names of regular tables in my database, separated by semicolons, by calling it like this:

? DemoGetString("qryListTables", True)

Public Function DemoGetString(ByVal pQueryName As String, _
    Optional ByVal AddQuotes As Boolean = False) As Variant
    '* early binding requires a reference to Microsoft ActiveX
    '* Data Objects Library
    'Dim rs As ADODB.Recordset
    'Set rs = New ADODB.Recordset

    '* use late binding; no referenced needed
    Dim rs As Object
    Set rs = CreateObject("ADODB.Recordset")

    Dim varOut As Variant
    rs.Open pQueryName, CurrentProject.Connection
    If AddQuotes Then
        varOut = """" & rs.GetString(2, , , """;""") '2 = adClipString
        ' strip off last quote
        If Len(varOut & vbNullString) > 0 Then
            varOut = Left(varOut, Len(varOut) - 1)
        End If
    Else
        varOut = rs.GetString(2, , , ";") '2 = adClipString
    End If
    rs.Close
    Set rs = Nothing
    DemoGetString = varOut
End Function

这篇关于ms-access保存查询结果为字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！