VB6 Assigning data to variables from a database

Question

I've been asked to make a change to a VB6 project. The issue I'm having is that I'm trying to get some data from an Access database and assign the data to some variables.

I've got the code:

Dta_Period.DatabaseName = DB_Accounts_Name$
Dta_Period.RecordSet = "SELECT * FROM [Period]"
Dta_Period.Refresh

The table Period contains 2 fields. sMonth and Period The sMonth field contains the months January - December. The Period field stores a number 0 to 11, to represent what number has been assigned to which month in the customers financial year. January may be 0, or may be 11, essentially.

I need to know which month goes with which period, which is why I have selected this data from the database. However, I'm stuck with what to do next.

How can I loop over the RecordSet (If this is even possible?) and find out what number has been assigned to each month?

I don't think there is a way I can use a Do Until loop. Is it easier to just use 12 separate queries, and then create an array of strings and an array of integers and then loop over the array of strings until I find the correct month, the use the same index for the array on integers?

EDIT 1

To make things simpler to follow for both myself and anyone attempting to provide an answer, I have modified the code.

Dim rstPeriod As DAO.RecordSet
Dim accDB As DAO.Database

' DB_Session is a Workspace, whilst DB_Accounts_Name$ is the name of the DB I am using
Set accDB = DB_Session.OpenDatabase(DB_Accounts_Name$)

SQL = "SELECT * FROM [Period] ORDER BY [Period]"

Set rstPeriod = accDB.OpenRecordset(SQL, dbOpenDynaset)

If rstPeriod.BOF = False Then
   rstPeriod.MoveFirst
End If

Dim strMonth(11) As String
Dim pNumber(11) As Integer

Pseudocode idea:

Do Until rstPeriod.EOF
   Select Case currentRow.Field("Month")
     Case "January"
       strMonth(0) = "January"
       pNumber(0) = currentRow.Field("Number")
     Case "February"
       strMonth(1) = "February"
       pNumber(1) = currentRow.Field("Number")
    End Select
Loop

Answer 1

Loop through recordset and fill the arrays with the month name and month number.

This assumes the recordset returns no more than 12 records.

Public Sub LoopThroughtRecordset()
    On Error GoTo ErrorTrap

    Dim rs As DAO.Recordset
    Set rs = CurrentDb().OpenRecordset("SELECT * FROM [Period] ORDER BY [Period]", dbOpenSnapShot)
    With rs
        If .EOF Then GoTo Leave
        .MoveLast
        .MoveFirst
    End With

    Dim strMonth(11) As String
    Dim pNumber(11) As Integer

    Dim idx As Long
    For idx = 0 To rs.RecordCount -1
        strMonth(idx) = rs![Month]
        pNumber(idx) = rs![Number]
        rs.MoveNext
    Next idx

Leave:
    On Error Resume Next
        rs.Close
    Set rs = Nothing
    On Error GoTo 0
    Exit Sub

ErrorTrap:
    MsgBox Err.Description, vbCritical, CurrentDb.Properties("AppTitle")
    Resume Leave
End Sub

'strMonth(0) = January
'strMonth(1) = February
'...
'pNumber(0) = 1
'pNumber(1) = 2
'...