View original (French)

Retrieve the result of a SQL query using VBA.

Juliaz -  
f894009 Posted messages 17417 Registration date   Status Member Last intervention   -
Bonjour,
I have been going in circles for a good hour.
I want to retrieve the result of an Access query and put it into a variable that I will use later.

I used the following code: 

Function recherche() Dim rst As DAO.Recordset Dim sSQL As String ' Open the database Dim v As Variant sSQL = "Select * From table2 where [np]='vo jacques'" 'open Set rst = CurrentDb.OpenRecordset(sSQL, dbOpenForwardOnly, dbReadOnly) 'processing v = rst![np] MsgBox v 'close rst.Close End Function


This code works well but I only retrieve the first element of the query.
How could I retrieve all the results of the query?

Thank you in advance,

3 answers

  1.
    yg_be Posted messages 23437 Registration date   Status Contributor Last intervention   Ambassadeur 1 588
     
    Good evening, for example: 
    'processing Do Until rst.EOF v = rst![np] MsgBox v rst.MoveNext Loop
    1
  2. Juliaz
     
    Hello,

    Thank you yg_be, it works very well.

    Thanks again.
    0
  3. f894009 Posted messages 17417 Registration date   Status Member Last intervention   1 717
     
    Hello,
    Find here:
    https://www.generation-nt.com/reponses/transferer-un-recordset-dans-un-tableau-en-vba-entraide-418091.html

    MichDenis
    On 09/03/2005 at 16:54 #2053631
    Hello Thierry,

    Dim Tblo as variant

    Tblo = Rst.getrows

    And to limit the number of rows copied into the array

    Copy only the first 25 records
    tblo = rst.getrows(25)

    Regards!
    It's up to you to adapt your code
    However, if you don't change anything in your function, your array won't be useful "later"
    0