Pehaps not the most elegant approach, but this will give you the desired
outcome

Private Sub cmdUpdateSeq_Click()
   
   Dim db As DAO.Database
   Dim rs As DAO.Recordset
   Dim strSQL As String
   Dim intI As Integer
   
   Set db = CurrentDb()
   
   strSQL = "SELECT columnA, columnB " & _
            "FROM table1 " & _
            "ORDER BY columnA;"
           
   Set rs = db.OpenRecordset(strSQL)
   
   With rs
       If .RecordCount > 0 Then
           .MoveFirst
           Do While Not .EOF
               intI = 1
               strColACurrent = !ColumnA
               strColANext = !ColumnA
               Do While strColACurrent = strColANext And Not .EOF
                   .Edit
                   !ColumnB = intI
                   .Update
                   intI = intI + 1
                   .MoveNext
                   If Not .EOF Then
                       strColANext = !ColumnA
                   End If
               Loop
           Loop
       End If
   End With

End Sub

Using a VBA array would be a roundabout, and slow way to do
this.

If your table already has the sequence column correctly
filled in, then the problem is how to generate the next
sequence number for a new record.  In this case you can use
the data entry form's BeforeUpdate event to get the maximum
sequence number, add 1 to it and put that into the
corresponding field:

If Me.NewRecord Then
 Me.colB = Nz(DMax("colB", "thetable", _
                                                    "colA=" & Me.ColA),0)+1
End If

If you are asking how to put acceptable sequence numbers in
the entire colB for the first time, then I suggest using a
recordset:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim lngSeqNo As Long
Dim strColA As String

strSQL = "SELECT colA,colB FROM thetable " _
                    & "ORDER BY colA, colC"
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
Do Until rs.EOF
    If rs!colA <> strColA Then
        lngSeqNo = 0
        strColA = rs!colA
    End If
    lngSeqNo = lngSeqNo +1
    rs.Edit
        rs!colB = lngSeqNo
    rs.Update
    rs.MoveNext
Loop

Note that the colC above is important to determine the order
of the sequence numbers for an item.  If it takes more than
one field to determine the orider, then add those columns to
the ORDER BY clause.  If you don't care what order an item's
records are sequenced, then you only need to specify colA.