ms-access save query result in a string

Question

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?

Answer 1

Ok.. taking a complete shot in the dark here...

The query you are running is literally a query... think of it as its OWN table... it can be referenced as you would any other table, and can be queried against.

If you are trying to return a single string item based on a single criteria your best bet is a Dlookup:

Lookup = Nz(DLookup(string Field, string Table, string Criteria), "")

If your looking for a group of records:

dim tsSQL as string
stSQL = "SELECT * FROM table WHERE field=criteria"
dim toRecordset as new ADODB.Recordset
toRecordset.open stSQL, CurrentProject.AccessConnection, int Keyset, int Lock

Then you can directly access the fields by:

If toRecordset.RecordCount > 0 then
  String = toRecordset!FieldName
End If

W/o more information... that about it... Also it works in the other direction as well..

You can do:

toRecordset.AddNew
toRecordset!Field = Value
toRecordset.Update

I hope somewhere in there is an answer for you.

To get the entire query you could change up the select statement from example one to "SELECT * FROM query name" and that should pull the whole thing in.

Answer 2

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

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;

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

Update: I think you may need quoted values in your string, so I revised the function so it adds quotes if you specify True as the (optional) second parameter. The first parameter is the name of your saved query.

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