Microsoft Access 2007 tutorial: DAO Recordset retrieve data

DAO Recordset retrieve data

IV.1. Retrieve Data from the Table by Using Recordset Object

To Retrieve the data from the table through recordset object, you need to know the properties and methods of recordset object:

 - RecordsetObject(FieldIndex) or

 - RecordsetObject(“ObjectName”) or

 - RecordsetObject!FieldName

 - RecordsetObject.MoveFirst

 - RecordsetObject.MoveNext

 - RecordsetObject.MovePrevious

 - RecordsetObject.MoveLast

 - RecordsetObject.EOF

 - RecordsetObject.RecordCount

 - RecordsetObject.BOF

 - RecordsetObject.AbsolutePosition

IV.1. 1. RecordsetObject(FieldIndex) or RecordsetObject(“ObjectName”)or RecordsetObject!FieldName

Syntax1: Reading data from Recordset object

                   Variable= RecordsetObject(Index)


                   Variable= RecordsetObject(“FieldName”)


                    Variable= RecordsetObject!FieldName

Index: Represents the index of each field of Recordset Object. In general, index is counted from zero(0); it mean that index zero represent field one, index one represent field two, index two represent field three and so forth.

FieldName: The field name of Recordset

Syntax2: Assign data to Recordset object

                   RecordsetObject(Index)= Value


                    RecordsetObject(“FieldName”)= Value


                    RecordsetObject!FieldName= Value

          For instance, you have the form as figure below:

Microsoft Access 2007 Read Data Form 

When you click the Fetch Data command button, the data is retrieved from Recordset(one record) to display in the text boxes(CategoryID, CategoryName). Look at the demonstrated figure below:

Microsoft Access 2007 Read Data Result Form 

To read data from Recordset object to display in the two text boxes above, type the following code:

Dim Db As DAO.Database

Dim Rs As DAO.Recordset

Private Sub Form_Load()

 Set Db = CurrentDb

End Sub


Private Sub cmdRead_Click()

 Dim SqlText As String

 SqlText = "Select * From TblCategory"

 Set Rs = Db.OpenRecordset(SqlText, dbOpenDynaset)

'Assign the first field to txtCategortID text box

 txtCategoryID.Value = Rs(0) 

' Assign the second field to txtCategortName text box

  txtCategoryName.Value = Rs(1) 

End Sub



This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.