How to Use Recordset in MS Access

This is viagra australia price done in fluid medium in the laboratory. You can visit this generic levitra easily take this medicine; it won’t disturb the taste of your mouth.Kamagra oral jelly has been presented by its maker Ajanta Pharma so as to bond the achievement brought by their non specific item Kamagra pills. Ever since that discovery cialis buy on line was used to treat ED. They feel unloved, frustrated and stressed due to cialis sale uk some or the other problem.


How to Use Recordset

A recordset is a data structure that consists of a group of database records, and can either come from a table, query or SQL.

Syntax

expression .OpenRecordset(Name, Type(Optional), Options(Optional), LockEdit(Optional))

expression A variable that represents a Database object.

#1 Set your references

versions of Access that are earlier than Access 2000.
  Dim rs As DAO.Recordset

new version of Access doesn’t need DAO reference
Dim rs As Recordset

#2 Type of Recordset

Dynaset:- A Dynaset is temporary set of data from one or more tables. A Dynaset may be a query. Like a table, a                               Dynaset is updatable if file is not locked or open for read only. Data in Dynaset is live that is updatable.

Set rs = db.OpenRecordset(“TableName”, dbOpenDynaset)

Snapshot:- Snapshot Record Set, like a dynaset, may be taken from one or more tables or query. The difference is that snapshot is like a screenshot that not updatable.

Set rs = db.OpenRecordset(“TableName”, dbOpenSnapShot)

#3 Set Up Recordset Object

To set a recordset object, we use the OpenRecordset method of a database object. There are a few ways to do this:

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset(“DataSource”)

Or

Dim rs As DAO.Recordset
Dim db As Database
Set db = CurrentDb
Set rs = db.OpenRecordset(“DataSource”)

Note: Data Sources with a Recordset

The “DataSource” can be one of three things, passed as a literal string or a variable:

  • a Table Name
  • a Query Name
  • an SQL string that returns records
 #4 Moving the Cursor on Recordset

 To move the cursor, use one of the following moving methods of the Recordset object:

rs.MoveFirst
 rs.MoveLast
 rs.MoveNext
 rs.MovePrevious
 rs.Move

 

#5 BOF (Beginning of File) and EOF (End of File)

Using any of the Move methods above (MoveFirst, MoveLast, MoveNext, or MovePrevious) will cause an error if the recordset has no records.

To avoid the error we need to test if there is a record before using any of the Move methods by using these methods:

If Not (rs.BOF And rs.EOF) Then
     'There are no records if Beginning-Of-File and End-Of-File are both true.
End if
If rs.RecordCount <> 0 Then
     ‘there are some records   
End if
If (rs.EOF = False) OR (rs.BOF = False) Then
     'there are recordset
End If




#6 RecordCount with MoveLast

The function below will get a number of records from a data source of Recordset. The cursor will move to the last record and count the record.

Function FindRecordCount(strSQL As String) As Long
Dim db As Database
Dim rstRecords As Recordset
   Set db = CurrentDb
   Set rstRecords = db.OpenRecordset(strSQL)
   If rstRecords.EOF Then
      FindRecordCount = 0
   Else
      RstRecords.MoveLast
      FindRecordCount = rstRecords.RecordCount
   End If
   rstRecords.Close
   db.Close
   Set rstRecords = Nothing
   Set db = Nothing
End Function

Note: Don’t MoveLast unless you really need to: this will be slow with a large recordset or a recordset drawn across a network. RecordCount will always be at least 1 if records exist.

#7  MoveNext Using EOF and Loop

A MoveNext may take you to the end of the recordset (EOF) or a MovePrevious to the beginning of the recordset (BOF). Failure to test for these conditions means your code works for most cases, but generates an error one day when the last/first record is accessed.

Code:

Use this construct for looping through Access recordsets:

    Do while Not rst.EOF
        If rst![MyField] <> Something Then 
            Exit Do   'The real loop exit condition.
        End If
            ' Rest of your code here.
        rst.MoveNext
    Loop

#8 FindFirst/Seek Using NoMatch

Always test for NoMatch after using Seek or a Find method(FindFirst, FindLast, FindNext, FindPrevious). If  you do not test for NoMatch, your code will appear to work until you strike a case where the find fails.

Code:

    rs.FindFirst "City = ""New York"""
    If Not rs.NoMatch Then
        'It's okay to keep processing.
    End If
#9 Referencing a Field on Recordset

The fields available to us in the recordset is based on the table or query that is used as a datasource, and are ordered in the recordset the same as they are ordered in the table or query.

We can reference the field to read or write a value to it by calling the Fields collection of the Recordset object. There are a number of ways to do this:

 rs.Fields("FieldName")
 rs.Fields(VariableName)
 rs.Fields(#)
 rs(#)
 #10 AddNew without moving to LastModified

When inserting a new record into a recordset, the new record does not automatically become the current record. To access the new record, move to the bookmark LastModified.

Code:

    rst.AddNew
         .Fields("FieldName1") = Value1
         .Fields("FieldName2") = Value2
    rst.Update
    rst.Bookmark = rst.LastModified
    ' Work with the new record here.

Example of Adding New Record with Recordset here.

 #11 Edit a Record

To edit the existing record.

Set rs = CurrentDb.OpenRecordset(“DataSource”)

With rs
  .Edit
    .Fields("Field1") = Value1
    .Fields("Field2") = Value2
  .Update
 End With

Or

Set rs = CurrentDb.OpenRecordset("DataSource")
 rs .Edit
         rs!Fields(“Fields1”) = Value1
         rs!Fields("Field2") = Value2
 rs .Update
Example of updating record with Recordset here.  
# 12 Close a recordset

Always close recordsets and set objects to Nothing in the error recovery of your procedure. It is poor programming to open anything without explicitly closing it. This problem is particularly serious in Access 97. Short of pressing Ctrl+Alt+Del, you may find that Access will not quit if recordsets or other objects are not closed and dereferenced.

Code:

    Sub SomeName()
    On Error Goto Err_Name
        Dim db as Database
        Dim rst As Recordset
        Set db = CurrentDb()
        Set rst = db.OpenRecordset("MyTable")
           'Useful code here.
        rst.Close            'Close what you opened
    Exit_Name:
        Set rst = Nothing    'De-assign all objects
        Set db = Nothing
        Exit Sub
    Err_MyProc:
        'Error handler here.
        Resume Exit_Name
    End Sub
#13 Complete Recordset
Sub SomeName()
On Error Goto Err_Name
Dim db as Database
Dim rst As Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordset("MyTable")
Do while Not rst.EOF
    If rst![MyField] <> Something Then 
           Exit Do   'The real loop exit condition
    End If
    rs .Edit
           rs!Fields(“Field1”) = Value1
           rs!Fields("Field2") = Value2
    rs .Update
    rst.MoveNext
Loop
rst.Close            'Close what you opened
Exit_Name:
Set rst = Nothing    'De-assign all objects
Set db = Nothing
Exit Sub
Err_Name:
    'Error handler here.
    Resume Exit_Name
End Sub




Related posts