Function to Open Recordset

To open a collection of recordsets, we use letRsOpen function. The letRsOpen function open recordsets collection of a query or table. The opened recordsets collection is viewed in a computer screen. We can set whether the recordsets can be edited or read only.


There are two parameters used in the letRsOpen function, they are strSql and boolForEdit. The strSql parameter is SQL statement with string data type, mandatory in nature. The boolForEdit is a Boolean expression that is optional. The default value for boolForEdit is False, means that the record will be viewed for read only. If the record is viewed and editable, then the value for boolForEdit is True.

The letRsOpen function is as follows:

Function letRsOpen(strSql As String, Optional boolForEdit As Boolean) As DAO.Recordset 'OK
' Parameters:
' strSql      = any SELECT query used to open recordset
' boolForEdit = Optional boolean expression whether the
'               related SELECT query is open for viewing or
'               editing/storing its recordset.
'
' If boolForEdit = True then the recordset can be edited/stored.
  Dim rst As DAO.Recordset
On Error GoTo Err_Msg
  If boolForEdit Then
    Set rst = daoDbs.OpenRecordset(strSql, dbOpenDynaset)
  Else
    Set rst = daoDbs.OpenRecordset(strSql, dbOpenSnapshot)
  End If
  Set letRsOpen = rst
Exit_Function:
  Exit Function
Err_Msg:
  MsgBox "Function letRsOpen, Error # " & str(Err.Number) & ", source: " & Err.Source & _
  Chr(13) & Err.description
  Resume Exit_Function
End Function

The letRsOpen function must be executed after a database has been opened. Therefore, make sure that letDbsOpen has been carried out before executing letRsOpen function. Thus, the programming sequence is as follows:

1. letDbsOpen
2. letRsOpen

Inside letRsOpen, there is object named daoDbs that must be set using letDbsOpen. If daoDbs object is not set, a run-time error #91 occurred, saying that object variable not set.

Implementation:

We can apply this function in a front-end database without any table or query. The following example use letRsOpen to display the first ten records of the primaryAccountCode field name in tblPrimaryAccounts table.

Function letRsOpenExample()
  Dim strSql As String
  Dim rst As DAO.Recordset
  Dim fld As DAO.Fields
  
  strSql = "SELECT * FROM tblPrimaryAccounts"
  
  letDbsOpen
  
  Set rst = letRsOpen(strSql)
  Do While Not rst.EOF
    Debug.Print "Record Number: " & rst.AbsolutePosition + 1 & ", " & rst.Fields(0).Name & ": " & rst.Fields(0).Value
    If rst.AbsolutePosition = 9 Then Exit Function
    rst.MoveNext
  Loop
rst.Close
  Set rst = Nothing
  
  letDbsClose
End Function

When running in Immediate Window, the result is as follows:

Remember that we should first connect to a database using letDbsOpen function before running the letRsOpen.