ACC: How to Fill an Array Using Visual or Access Basic

MORE INFORMATION

1. Open the sample database Northwind.mdb (or NWIND.MDB in versions 1.x and 2.0).
2. Create a module and type the following line in the Declarations section if it is not already there:

   Option Explicit

3. Type the following procedure.
   
   In Microsoft Access 2.0, 7.0 and 97:

         Function FillOneDimArray ()
            Dim i As Long
            Dim DB As Database, RS As Recordset
            Dim RecordCount As Long
            On Error GoTo ErrorHandler
            Set DB = CurrentDB()
            Set RS = DB.OpenRecordset("Employees")
            ' Get number of records.
            RS.MoveLast
            RecordCount = RS.RecordCount
            ' Create the (zero-based) array.
            ' Address elements starting from row 0 rather than 1.
            ReDim AnArray(RecordCount - 1)
            ' Fill the array.
            ' NOTE: In version 2.0, type a space in [Last Name].
            RS.MoveFirst
            For i = 0 To RecordCount - 1
               AnArray(i) = RS![LastName]
               RS.MoveNext
            Next i
            ' View the array contents.
            For i = 0 To RecordCount - 1
               Debug.Print AnArray(i)
            Next i
            RS.Close
            DB.Close
            Exit Function
         ErrorHandler:
            MsgBox Error
            Exit Function
         End Function
   						

   In Microsoft Access 1.x:

         Function FillOneDimArray ()
            Dim i As Long
            Dim DB As Database, SS As Snapshot
            Dim RecordCount As Long
            On Error GoTo ErrorHandler
            Set DB = CurrentDB()
            Set SS = DB.CreateSnapshot("Employees")
            ' Get number of records.
            SS.MoveLast
            RecordCount = SS.RecordCount
            ' Create the (zero-based) array.
            ' Address elements starting from row 0 rather than 1.
            ReDim AnArray(RecordCount - 1)
            ' Fill the array.
            SS.MoveFirst
            For i = 0 To RecordCount - 1
               AnArray(i) = SS![Last Name]
               SS.MoveNext
            Next i
            ' View the array contents.
            For i = 0 To RecordCount - 1
               Debug.Print AnArray(i)
            Next i
            SS.Close
            DB.Close
            Exit Function
         ErrorHandler:
            MsgBox Error
            Exit Function
         End Function
   						

4. To test this function, type the following line in the Debug window (or Immediate window in versions 1.x and 2.0), and then press ENTER.

   ? FillOneDimArray()

   Note that you receive the following results:

   Davolio
   Fuller
   Leverling
   Peacock
   Buchanan
   Suyama
   King
   Callahan
   Dodsworth

1. Open the sample database Northwind.mdb (or NWIND.MDB in versions 1.x and 2.0).
2. Create a new module with the following sample code.
   
   In Microsoft Access 2.0, 7.0 and 97:

         Function FillIndefArray ()
            Dim DB As Database, RS As Recordset, Count As Integer
            Dim AnArray()
            Dim i As Long
            Set DB = CurrentDB()
            Set RS = DB.OpenRecordset("Employees")
            Count = 0
            ReDim Preserve AnArray(0)
            ' Fill the array.
            RS.MoveFirst
            Do Until RS.EOF
               ' Fill the array row with the last name.
               ' NOTE: In version 2.0, type a space in [Last Name].
               AnArray(Count) = RS![LastName]
               ' Increase the number of elements in the array
               ' by one to accommodate the next record.
               ReDim Preserve AnArray(UBound(AnArray) + 1)
               Count = Count + 1
               RS.MoveNext
            Loop
            ' Remove the remaining empty array row.
            ReDim Preserve AnArray(UBound(AnArray) - 1)
            RS.Close
            ' View the array contents.
            For i = 0 To Count - 1
               Debug.Print AnArray(i)
            Next i
         End Function
   						

   In Microsoft Access 1.x:

         Function FillIndefArray ()
            Dim DB As Database, SS As Snapshot, Count As Integer
            Dim AnArray()
            Dim i As Long
            Set DB = CurrentDB()
            Set SS = DB.CreateSnapshot("Employees")
            Count = 0
            ReDim Preserve AnArray(0)
            ' Fill the array.
            SS.MoveFirst
            Do Until SS.EOF
               ' Fill the array row with the last name.
               AnArray(Count) = SS![Last Name]
               ' Increase the number of elements in the array
               ' by one to accommodate the next record.
               ReDim Preserve AnArray(UBound(AnArray) + 1)
               Count = Count + 1
               SS.MoveNext
            Loop
            ' Remove the remaining empty array row.
            ReDim Preserve AnArray(UBound(AnArray) - 1)
            SS.Close
            ' View the array contents.
            For i = 0 To Count - 1
               Debug.Print AnArray(i)
            Next i
         End Function
   						

3. To test this function, type the following line in the Debug window (or Immediate window in versions 1.x and 2.0), and then press ENTER.

   ? FillIndefArray()

   Note that you receive the following results:

   Davolio
   Fuller
   Leverling
   Peacock
   Buchanan
   Suyama
   King
   Callahan
   Dodsworth