Moving through the Recordset in Access VBA

Question

I have a simple function using Excel VBA for calculating volatility. It takes as inputs a column of numbers (Zeros) and two dates. The code is:

Function EWMA(Zeros As Range, Lambda As Double, MarkDate As Date, MaturityDate As Date) As Double

    Dim vZeros() As Variant
    Dim Price1 As Double, Price2 As Double
    Dim SumWtdRtn As Double
    Dim I As Long
    Dim m As Double

    Dim LogRtn As Double, RtnSQ As Double, WT As Double, WtdRtn As Double

vZeros = Zeros

m = Month(MaturityDate) - Month(MarkDate)

For I = 2 To UBound(vZeros, 1)

    Price1 = Exp(-vZeros(I - 1, 1) * (m / 12))

    Price2 = Exp(-vZeros(I, 1) * (m / 12))

    LogRtn = Log(Price1 / Price2)

    RtnSQ = LogRtn ^ 2

    WT = (1 - Lambda) * Lambda ^ (I - 2)

    WtdRtn = WT * RtnSQ

    SumWtdRtn = SumWtdRtn + WtdRtn

Next I

EWMA = SumWtdRtn ^ (1 / 2)

End Function

The main feature enabling the function to work is the For loop. I want to re-create this in Access VBA using recordset objects. The recordset has the same fields as the Excel spreadsheet. I'm not exactly sure how to convert the code over, though. Here is what I have so far:

Function EWMA(rsCurve As Recordset, InterpRate As Double, Lambda As Double) As Double

    Dim vZeros() As Variant
    Dim Price1 As Double, Price2 As Double
    Dim SumWtdRtn As Double
    Dim I As Long
    Dim mat As Date
    Dim mark As Date

    Dim LogRtn As Double, RtnSQ As Double, WT As Double, WtdRtn As Double


    CurveInterpolateRecordset = Rnd()

    If rsCurve.RecordCount <> 0 Then

    vZeros = CVar(rsCurve.Fields("CurveInterpolateRecordset"))

    mat = CDate(rsCurve.Fields("MaturityDate"))
    mark = CDate(rsCurve.Fields("MarkDate"))

    m = Month(mat) - Month(mark)

For I = 2 To UBound(vZeros, 1)

    Price1 = Exp(-vZeros(I - 1, 1) * (m / 12))

    Price2 = Exp(-vZeros(I, 1) * (m / 12))

    LogRtn = Log(Price1 / Price2)

    RtnSQ = LogRtn ^ 2

    WT = (1 - Lambda) * Lambda ^ (I - 2)

    WtdRtn = WT * RtnSQ

    SumWtdRtn = SumWtdRtn + WtdRtn

Next I

EWMA = SumWtdRtn ^ (1 / 2)

End If

        Debug.Print EWMA

End Function

The function is called in an earlier subroutine in Access. What am I missing in order to move through the recordset in Access, similar to looping through the spreadsheet in Excel VBA?

Answer 1

The easiest method would be to use GetRows to pull an array from your recordset:

Recordset.GetRows Method

Then the new code would be nearly a copy-n-paste of your proven code starting with basically this:

vZeros = rsCurve.GetRows(rsCurve.RecordCount)

As a side note you wouldn't need CDate here:

mat = rsCurve.Fields("MaturityDate").Value

Answer 2

Here are some basics about using a recordset.

Dim rs As New ADODB.Recordset

'Add fields to your recordset for storing data.
With rs
    .Fields.Append "Row", adInteger
    .Fields.Append "ColumnName2", adChar, 30
    .Fields.Append "ColumnName3", adInteger
    .Open
End With

Add records to it manually

rs.AddNew
rs.Fields("Row").Value = 1
rs.Fields("ColumnName2").Value = "Put some value in"   
rs.Update

rs.AddNew
rs.Fields("Row").Value = 2
rs.Fields("ColumnName2").Value = "Put another value in"   
rs.Update

You can also populate it with a query of a table.

Move to the begining of the recordset

If rs.EOF = False Then
    rs.MoveFirst
End If

Loop through the recordset

Do While rs.EOF = False

        msgbox(rs.Fields("ColumnName2").Value)

rs.MoveNext
Loop