How to use ADO RecordSet in VB

 
Recordset in VB is used to keep the data retrived from database through the connection.
 
Recordset is defined as follows.
Public rs As ADODB.Recordset
 
Recordset is set as follows.
Set rs = New ADODB.Recordset
 
Recordset is executed (opened) as follows.
rs.open "insert into table1 values('field1','field2')", cnn             '
rs.open "select field1, field2 from table1", cnn, adOpenStatic
 
where cnn is the connection name
 
Recordset is closed as follows.
rs.close
 
The CursorLocation of the recordset is usually set to adUseClient,
to retrive data faster without requiring to contact server every time.
rs.CursorLocation = adUseClient
 
To check the state of Recordset
rs.state
 
if rs.state = 1 then
msgbox "The recordset is open"
elseif rs.state = 0 then
msgbox "The recordset is closed"
end if
 
To find whether end of recordset has reached or not use EOF.
while rs.eof = false
  msgbox rs(0)
  rs.movenext
wend
 
To find if cursor at recordset has reached at begining by back traversing, use BOF
while rs.bof = false
  msgbox rs(0)
  rs.MovePrevious
wend
 
To retrive items of recordset, use it as follows
1.  rs(ordinal_number) 
2.  rs!fieldName
3.  rs("fieldName")
 
Movements of cursor in recordset
rs.movenext to move to next record
rs.moveprevious to move to previous record
rs.movefirst to move to first record
rs.movelast to move to last record
 
 
To add new record in recordset, use it as follows
    rs.AddNew
    rs(0) = field1_value
    rs(2) = field2_value
    rs.Update
   
To get the no of records in recordset, use it as follows
rs.RecordCount