Getting all field contents

[jalldridge]

Hi guys

I want to be able to run a query via VBA to pull out all records according to a criteria ie

select product where email=xyz.com

Can anybody tell what the function is called that allows me to do this. Been hunting for some time and have only come across the aggregate functions ie dlookup - obviously these only pull back one record...

Thanks

 

[jalldridge]

OK I've also tried to use the following code:

    Dim query As String
    query = "Select type_of_product from tbl_download where email_address='xyz@hotmail.com'"
    Dim curdb As Database
    Set curdb = CurrentDb
    Dim rst As Recordset
    Set rst = curdb.OpenRecordset(query, dbOpenDynaset)
    vDBDetails = rst.GetRows

I know for a fact that the email address supplied has 2 entries in the db, however only 1 row is pulled from the db when I use the above code. If I look at the recordset in the watch window I'm told that there is only one entry. The fields count is 1 (as expected) , and the recordcount is 1 (should be 2)

(I've run the sql query directly in access so know that the query is ok as I get 2 rows returned)

Why?

Hope somebody can shed some light for me.

Going mad here

 

[jalldridge]

OK If I use the following I get 2 rows pulled back

Dim curconn As New ADODB.connection
    Dim rst As ADODB.Recordset
    Dim curdb As Database

    Set curdb = CurrentDb

    With curconn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = "data source = " & curdb.name
        .Open

    End With
    Dim vDBDetails As Variant

    Set rst = New ADODB.Recordset
    rst.Open "select type_of_product from tbl_download where email_address='xyz@hotmail.com';", curconn, , , adCmdText

    If rst.EOF Then
        'no previous entry
        GoTo closeConnection
    End If
    vDBDetails = rst.GetRows

I tried using an dbOpenDynamic in the openRecordset method and this gives me an invalid argument error message.

What else do I need to alter???

 

[jalldridge]

For those that may need to know I believe that I have solved this

The openRecordset method only pulls back a SINGLE row from the db at a time. As far as I know using this method there is no way to put the whole resultset into a variant array using the getRows method (as you can using adodb).

So you have to do your processing on each record and then put this into an array as you move through the resultset. The number of rows pulled back can be found by the recordCount, and this can in turn be used to define the array size.

Any better way then I would appreciate knowing!!!