Clever Geek Handbook

Ms-access store query result in string

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

+2


source to share


2 answers


The ADO Recordset has a method GetString

that you might find useful.

I have a query named qryListTables that 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;


Note that the wildcard is used %

instead of *

. The reason for this choice is that ADO requires ANSI wildcard characters ( %

and _

instead of *

and ?

).

I can use the following function to highlight a row 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
+4


source


Ok .. shoot full tempo here ...

The query you are making is literally a query ... think of it as your OWN table ... it can reference any other table and can be queried.

If you are trying to return a single inline element based on a single criterion, Dlookup would be your best bet:

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

If you are 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 access the fields directly:



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

More information ... about this ... It also works in the other direction.

You can do:

toRecordset.AddNew
toRecordset!Field = Value
toRecordset.Update

I hope there is an answer for you somewhere.

To get the whole query, you can change the select statement from example one to "SELECT * FROM query name" and that should lead to all of this.

+3


source






More articles:


All Articles