VBA Database connection

[skooch]

Hi, I wonder if any one could help with the following SQL statement.

SELECT ConsultantSkills.[Consultant Name]FROM ConsultantSkills INNER JOIN AdditionalSkills ON ConsultantSkills.[Consultant Name]=AdditionalSkills.[Consultant Name]WHERE ConsultantSkills.LoadRunner>=1<=5 And AdditionalSkills.PVCS>=1<=5;

If I run the above code in the SQL editor in Access 2007 it produces the correct number of results (5 items). However when I run the same SQL from within a VBA function (below) it only returns one value even though it should have 5 listed as it does when run in Access alone. I would appreciate any help.

Dim dbase As DAO.Database
Dim RecSet As DAO.Recordset
Set dbase = CurrentDb

Set RecSet = dbase.OpenRecordset("SELECT ConsultantSkills.[Consultant Name]FROM ConsultantSkills INNER JOIN AdditionalSkills ON ConsultantSkills.[Consultant Name]=AdditionalSkills.[Consultant Name]WHERE ConsultantSkills.LoadRunner>=1<=5 And AdditionalSkills.PVCS>=1<=5;")

For b = 0 To RecSet.Fields.Count

MsgBox RecSet.Fields(b).Name

Next

dbase.Close
Set dbase = Nothing
Set RecSet = Nothing



End Function

 

[ByteMyzer]

You're only looping through the fields of the current record; you are not looping through the actual records themselves. Try changing the following code:

[COLOR=navy]For[/COLOR] b = 0 [COLOR=navy]To[/COLOR] RecSet.Fields.Count
    MsgBox RecSet.Fields(b).Name
[COLOR=navy]Next[/COLOR]

...to:

[COLOR=navy]Do While Not[/COLOR] RecSet.EOF
    MsgBox RecSet.Fields(0).Name
    RecSet.MoveNext
[COLOR=navy]Loop[/COLOR]

 

[skooch]

Hi,
Thanks for that, it seems to have done the trick. Would you be able to answer another question for me, is there a limit to the number of columns that can be added to a recordset ?

When I run a query to get all the records it says that too many fields are defined.

Thanks.

 

[ByteMyzer]

Once again, you are confusing fields with records.

A recordset may be limited to 255 fields, depending on your recordsource, but may contain millions of records.

In the following output example from a query:

 tID | tDate    | tAmount
-------------------------
 102 | 1/1/2009 |  123.45
 203 | 2/1/2009 |  678.90
 305 | 3/1/2009 |  234.56
 407 | 4/1/2009 |  789.01
 511 | 5/1/2009 |  345.67

...the recordset has 3 fields (labelled tID, tDate, and tAmount) and 5 records.