asked on

Excel VBA - Using DAO - How to loop throguh recordset

Experts, I am using the following code to pull the results of a SQL query into excel, and add the record set into a combo box. The code below is working, however it only adds the very first result- how can I make it loop through every result (row) returned by the query and add it to the combo box?

Much appreciated!

Sub Database_connect()
Dim dbEng As dbengine
Dim oRs As Recordset
Dim oRow As Range
Dim oWspc As Workspace
Dim oConn As Connection
Dim oQuery As QueryDef
 
strConnection = "ODBC;DATABASE=databasename;UID=userid;PWD=password;DSN=DSNname"
strSQL = "SELECT * FROM accounts where term like " & """VS%"""
Debug.Print strSQL
Set dbEng = CreateObject("DAO.DBEngine.35")
Set oWspc = dbEng.CreateWorkspace("ODBCWspc", "", "", dbUseODBC)
Set oConn = oWspc.OpenConnection("DSNname", , True, strConnection)
Set oQuery = oConn.CreateQueryDef("tmpFindAccount")
    
oQuery.Sql = strSQL
Set oRs = oQuery.OpenRecordset
If oRs.EOF = True Then
    MsgBox "No More Records"
Else
    Worksheets("Sheet2").ComboBox1.AddItem oRs.Fields("fieldname")
End If
 
oRs.Close
oQuery.Close
oConn.Close
oWspc.Close
Set oRs = Nothing
Set dbEng = Nothing
 
 
 
 
End Sub

Open in new window

TimCottee

Try this loop that iterates through until there are no remaining records.

Set oRs = oQuery.OpenRecordset
If oRs.EOF = True Then
    MsgBox "No More Records"
Else
 With oRs
   Do While
    Worksheets("Sheet2").ComboBox1.AddItem .Fields("fieldname")
    .MoveNext
   Loop Until .Eof
End If

Open in new window

0tacon

ASKER

Thanks for the quick reply- it seems the do while loop needs a qualifier? (i.e. until there are no more records)

0tacon

ASKER

This seems to be working....though not sure if it's the correct implementation:

With oRs
   Do
    Worksheets("Sheet2").ComboBox1.AddItem .Fields("fieldname")
    .MoveNext
   Loop Until .EOF
End With

Open in new window

ASKER CERTIFIED SOLUTION

TimCottee

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

0tacon

ASKER

Fantastic- you're a legend, much appreciated!