locked
Is there a quick way to set non-scalar values from a query results? RRS feed

  • Question

  • Lets say I want all the results from one field. Like, Ids from a Customers table where ZipCode = 12345.

    Do I have to create and loop over a Recordset object and read each value into an array? I can do this no problem, please don't explain how. But is there anything in VBA that makes it easier to take a non-scalar return from a query on a single field and do something with those values? Something similar to the Domain functions, DLookup, DCount etc, but for non-scalar values. Or must we create a Recordset and loop over it?

    Thursday, January 19, 2017 9:43 PM

Answers

  • Do I have to create and loop over a Recordset object and read each value into an array? I can do this no problem, please don't explain how. But is there anything in VBA that makes it easier to take a non-scalar return from a query on a single field and do something with those values? Something similar to the Domain functions, DLookup, DCount etc, but for non-scalar values. Or must we create a Recordset and loop over it?

    Hi HTHP,

    I do not know what more recent versions then A2003 offer, but as far as I have experienced in A2003 I would take a recordset and loop through it.

    My approach would be to make a general routine that accepts any sql-string and the fieldname in question, and returns an array. After that you can use this routine as a kind of method of a recordset.

    This generalization is very widely applicable. On this moment I run over 90 completely different applications, all using the same generalized functionality.

    Imb.

    • Marked as answer by HTHP Friday, January 20, 2017 3:08 PM
    Thursday, January 19, 2017 11:44 PM
  • I suppose the issue will depend on what you want to do with those values? I mean as you note you can pull some values from a table into a recordset. And there is a command to pull those values from a recrodset into a array without a loop (GetRows method of a recrodset). However when you have data sitting in an array(), then such data is really not much use and if you modify such data, then you would have to send the data back to the table – and that’s work.

    If you wanted to update those values, then consider using a UPDATE query (SQL) and that can update all rows based on your criteria – and no need to mess around with an array, or even a loop.

    Given that the recordset behaves much like an array, but allows updating of data (in a loop), then the case for moving the data into an array is much reduced – especially if you looking to update that data.

    I suppose it really depends on what you want to do with the data, but if you are looking to update such data, then I would recommend that you avoid moving such data into an array. If you know the data you want to select, then you know the data to update based on some criteria.

    Regards,
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada

    • Marked as answer by HTHP Friday, January 20, 2017 3:08 PM
    Friday, January 20, 2017 12:44 AM

All replies

  • Hi,

    I believe even domain aggregate functions process the values by looping through a recordset.

    Just my 2 cents...

    Thursday, January 19, 2017 11:19 PM
  • Do I have to create and loop over a Recordset object and read each value into an array? I can do this no problem, please don't explain how. But is there anything in VBA that makes it easier to take a non-scalar return from a query on a single field and do something with those values? Something similar to the Domain functions, DLookup, DCount etc, but for non-scalar values. Or must we create a Recordset and loop over it?

    Hi HTHP,

    I do not know what more recent versions then A2003 offer, but as far as I have experienced in A2003 I would take a recordset and loop through it.

    My approach would be to make a general routine that accepts any sql-string and the fieldname in question, and returns an array. After that you can use this routine as a kind of method of a recordset.

    This generalization is very widely applicable. On this moment I run over 90 completely different applications, all using the same generalized functionality.

    Imb.

    • Marked as answer by HTHP Friday, January 20, 2017 3:08 PM
    Thursday, January 19, 2017 11:44 PM
  • I suppose the issue will depend on what you want to do with those values? I mean as you note you can pull some values from a table into a recordset. And there is a command to pull those values from a recrodset into a array without a loop (GetRows method of a recrodset). However when you have data sitting in an array(), then such data is really not much use and if you modify such data, then you would have to send the data back to the table – and that’s work.

    If you wanted to update those values, then consider using a UPDATE query (SQL) and that can update all rows based on your criteria – and no need to mess around with an array, or even a loop.

    Given that the recordset behaves much like an array, but allows updating of data (in a loop), then the case for moving the data into an array is much reduced – especially if you looking to update that data.

    I suppose it really depends on what you want to do with the data, but if you are looking to update such data, then I would recommend that you avoid moving such data into an array. If you know the data you want to select, then you know the data to update based on some criteria.

    Regards,
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada

    • Marked as answer by HTHP Friday, January 20, 2017 3:08 PM
    Friday, January 20, 2017 12:44 AM
  • I'm only worried about getting the values from the database and into memory in an array so they can be passed around and used independent of the persistence layer. When persisting back to the datasource, when and if that is needed, will happen much later in the life of the values I am pulling out.

    I sort of like the DAO.Recordset.GetRows() method. It returns a two-dimensional Variant array. Its really very similar to just iterating over the Recordset though isn't it? From the feedback so far, I don't think its going to get much easier than that.

    Thanks!
    Friday, January 20, 2017 3:08 PM