Saturday, October 16, 2010

MS Access Recordset Handling

I showed how to create an instance of a 32 bit Microsoft Access database in a 64 bit Revit name space but I did not mention how to handle opening and updating recordset objects.

The dao.Recordset object provides the ability to open and update a recordset returned by a SQL select command.

''' <summary>
    ''' Returns a Recordset object from an Access Database
    ''' </summary>
    ''' <param name="tblName">Name of the table containing the record</param>
    ''' <param name="keyID">Field name</param>
    ''' <param name="keyValue">Field value to search for</param>
    ''' <returns>Recordset matching search criteria</returns>
    ''' <remarks></remarks>
    Public Function OpenRecordset(ByVal tblName As String, _
                                  ByVal keyID As String, _
                                  ByVal keyValue As String) As Access.Dao.Recordset
        Dim m_rs As Access.Dao.Recordset
        Dim SQL As String = "SELECT * FROM [" & tblName & "] WHERE ([" & keyID & "]='" & keyValue & "')"
        m_rs = m_DaoDB.OpenRecordset(SQL)
        Return m_rs
    End Function

Now with the m_rs object, the recordset can be modified using:

m_rs.Fields("FieldName").Value = MyValue

You can get a value using:

MyValue = m_rs.Fields("FieldName").Value