CODEWITHSUNDEEP
sqlms-accessvba
Alex Gordon
Alex Gordon

Reputation: 60741

ms-access save query result in a string

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?

Upvotes: 2

Views: 4232

Answers (2)

HansUp
HansUp

Reputation: 97101

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

Upvotes: 4

Patrick
Patrick

Reputation: 7712

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.

Upvotes: 3

Related Questions