Read through the results of a query one record at a time.

[strangeryet]

I need to pull some information from an Access table or
via a query. But I need to read one record at a time, perform some action and then read the next record.
This will continue until eof.
Does anyone have code that will do this?

Thank you

ps. This sounds like a simple procedure. Can it be done or do I need to somehow load the result of the query into an Array and then loop through the array?
Thanks

 

[RuralGuy]

Do a search on RecordSet. You can use either DAO or ADO.

HTH RuralGuy (RG for short) acXP winXP Pro
Please respond to this forum so all may benefit

 

[strangeryet]

Thank you

 

[strangeryet]

Here is some basic code to do this:
Besure to include reference for DAO

Dim strTableName As String
Dim rs As DAO.Recordset
'Load your table name
strTableName = "Table Name"
Set rs = CurrentDb.OpenRecordset(strTableName, dbOpenDynaset)
Do While Not rs.EOF
MsgBox "See Result= " & rs.Fields("FieldName1") ' from table
MsgBox "See Result= " & rs.Fields("FieldName2") ' from table
rs.MoveNext
Loop

 

[Hans8823]

databasepath = "c:\test.mdb"
sSQL = "SELECT ID, name FROM test"

Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & databasepath
Set rsTest = Server.CreateObject("ADODB.Recordset")
With rsTest
'to use adLockReadOnly and adOpenForwardOnly do not forget to include adovbs.inc, otherwise use the values 1 and 0
.Open sSQL, conn, adLockReadOnly, adOpenForwardOnly
While Not .EOF
'an example how to get the data
ID = .Fields("ID")
name = .Fields("name")
'Do the things you want to do
.MoveNext
Wend
End With
Set rsTest = Nothing
conn.Close
Set conn = Nothing