Access VBA: How to select particular columns in ado using getstring?

Question

Hi,

When we use getstring to get data from a recordset(ADO) then it returns all the columns, if only particular columns are required then how do we modify the getstring statement?#

Thanks tksy

Answer 1

You can't. GetString returns all columns of all or a specified number of rows. You'll need to loop through the recordset, getting the columns you want explicitly.

It's all in the documentation.

Answer 2

You can take a step back and build the recordset with only the fields (columns) that you want, for example:

strSQL="SELECT ID, FName, SName FROM Members"
rs.Open strSQL, cn

a=rs.GetString

Answer 3

You can also use a combination of join and getrows

myString = join(rs.getrows( , , myColumn),";")

• rsGetrows returns an array containing only the myColumn's values
• Join will transfer the array in a string like "value1;value2; ..."

Check the exact syntax as this was written on the fly

EDIT: unfortunately, it cannot be that straight as .getrows will return a 2 dimensions array. Are there any functions that can extract a one dimension array from a 2 dimensions one? It can be written easily, can't it?