Access 2010 VBA query a table and iterate through results

DAO is native to Access and by far the best for general use. ADO has its place, but it is unlikely that this is it.

 Dim rs As DAO.Recordset Dim db As Database Dim strSQL as String Set db=CurrentDB strSQL = "select * from table where some condition" Set rs = db.OpenRecordset(strSQL) Do While Not rs.EOF    rs.Edit    rs!SomeField = "Abc"    rs!OtherField = 2    rs!ADate = Date()    rs.Update    rs.MoveNextLoop

Ahh. Because I missed the point of you initial post, here is an example which also ITERATES. The first example did not. In this case, I retreive an ADODB recordset, then load the data into a collection, which is returned by the function to client code:

EDIT: Not sure what I screwed up in pasting the code, but the formatting is a little screwball. Sorry!

Public Function StatesCollection() As CollectionDim cn As ADODB.ConnectionDim cmd As ADODB.CommandDim rs As ADODB.RecordsetDim colReturn As New CollectionSet colReturn = New CollectionDim SQL As StringSQL = _    "SELECT tblState.State, tblState.StateName " & _    "FROM tblState"Set cn = New ADODB.ConnectionSet cmd = New ADODB.CommandWith cn    .Provider = DataConnection.MyADOProvider    .ConnectionString = DataConnection.MyADOConnectionString    .OpenEnd WithWith cmd    .CommandText = SQL    .ActiveConnection = cnEnd WithSet rs = cmd.ExecuteWith rs    If Not .EOF Then    Do Until .EOF        colReturn.Add Nz(!State, "")        .MoveNext    Loop    End If    .CloseEnd Withcn.CloseSet rs = NothingSet cn = NothingSet StatesCollection = colReturn

End Function

I know some things have changed in AC 2010. However, the old-fashioned ADODB is, as far as I know, the best way to go in VBA. An Example:

Dim cn As ADODB.ConnectionDim cmd As ADODB.CommandDim prm As ADODB.ParameterDim rs As ADODB.RecordsetDim colReturn As New CollectionDim SQL As StringSQL = _    "SELECT c.ClientID, c.LastName, c.FirstName, c.MI, c.DOB, c.SSN, " & _    "c.RaceID, c.EthnicityID, c.GenderID, c.Deleted, c.RecordDate " & _    "FROM tblClient AS c " & _    "WHERE c.ClientID = @ClientID"Set cn = New ADODB.ConnectionSet cmd = New ADODB.CommandWith cn    .Provider = DataConnection.MyADOProvider    .ConnectionString = DataConnection.MyADOConnectionString    .OpenEnd WithWith cmd    .CommandText = SQL    .ActiveConnection = cn    Set prm = .CreateParameter("@ClientID", adInteger, adParamInput, , mlngClientID)    .Parameters.Append prmEnd WithSet rs = cmd.ExecuteWith rs    If Not .EOF Then        Do Until .EOF            mstrLastName = Nz(!LastName, "")            mstrFirstName = Nz(!FirstName, "")            mstrMI = Nz(!MI, "")            mdDOB = !DOB            mstrSSN = Nz(!SSN, "")            mlngRaceID = Nz(!RaceID, -1)            mlngEthnicityID = Nz(!EthnicityID, -1)            mlngGenderID = Nz(!GenderID, -1)            mbooDeleted = Deleted            mdRecordDate = Nz(!RecordDate, "")            .MoveNext        Loop    End If    .CloseEnd Withcn.CloseSet rs = NothingSet cn = Nothing