Loop thru a recordset in Access

 Keywords:  loop recordset OLE Access

---

Sample 1:

This sample code which queries the table and loops through the result set.

file  		= "C:\Temp\Temp.mdb" ;<<<<
tablename 		= "Test" ;<<<<
query = StrCat("select * from ",tablename);<<<<

; create the access application object...
oApp = objectopen("Access.Application")
; set the mdb name you want to work with...
dbname = "C:\Temp\Temp.mdb"
; open the database...
oApp.opencurrentdatabase(dbname)
; set the db object to the current db...
db = oApp.currentdb
; open the recordset...
rs = db.openrecordset(query)
; set the field...
rfields = rs.fields
; loop thru the recordset...
while rs.eof == @false
   fieldcount = rFields.count
	For x = 0 to fieldcount-1
	   field = rFields.Item(x)
      message(field.name, field.value)
	Next
	rs.movenext
endwhile
rs.close
oApp.closecurrentdatabase
oApp.quit
; close up the objects...
objectclose(field)
objectclose(rfields)
objectclose(rs)
objectclose(db)
objectclose(oApp)
; notify the user...
message("Debug", "All Done")
exit

---

Sample 2:

This sample code loops through the entire table grabbing every record.

file  		= "C:\Temp\Temp.mdb" ;<<<<
table 		= "Email" ;<<<<

;Open recordset with data from table.
strCnn     	= StrCat("Provider=MicroSoft.Jet.OLEDB.4.0; Data Source=",file,";")
adCmdTable 	= 2 

rstRecset = ObjectOpen("ADODB.Recordset")
rstRecset.Open(Table, strCnn, , , adCmdTable)

title = StrCat("Field values in ",table)
BoxOpen(title,"")
rstRecset.MoveFirst

;Enumerate the Fields collection of the table.
rstFields = rstRecset.Fields
While @True
	fieldcount = rstFields.count
	For x = 0 to fieldcount-1
	  ;Because Value is the default property of a 
	  ;Field object, the use of the actual keyword
	  ;here is optional.
	  field = rstFields.Item(x)
	  BoxText(StrCat(field.Name," = ",field.Value))
	  Timedelay(0.5)
	Next 
	;Move to next record
	rstRecset.MoveNext
	;Trap for EOF.
   If rstRecset.EOF Then break
EndWhile
TimeDelay(1)
rstRecset.Close
ObjectClose(rstFields)
ObjectClose(rstRecset)

---

Article ID:   W14672

Filename:   loop thru a recordset in Access.txt