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

ID: Q109727

The information in this article applies to:

Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article shows you two Visual Basic for Applications methods to fill an array. The first method fills an array when you know the number of elements in the array. The second method fills an array when you do not know the number of elements in the array.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access versions 1.x and 2.0. For more information about Access Basic, please refer to the "Introduction to Programming" manual in Microsoft Access version 1.x or the "Building Applications" manual in Microsoft Access version 2.0.

MORE INFORMATION

This example demonstrates how to fill a one-dimensional array when you know the number of elements:

Open the sample database Northwind.mdb (or NWIND.MDB in versions 1.x and 2.0).

Create a module and type the following line in the Declarations section if it is not already there:
Option Explicit

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

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

The next example demonstrates how to fill an array when you do not know the number of elements. It uses the Preserve argument of the ReDim statement to adjust the size of the array without destroying the array's contents. Realize that using Preserve can cause your code to run more slowly; therefore, if possible, it is better to create an array with a known number of elements.

Open the sample database Northwind.mdb (or NWIND.MDB in versions 1.x and 2.0).

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

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

Additional query words:

Keywords : kbprg
Version : WINDOWS:1.0,1.1,2.0,7.0,97
Platform : WINDOWS
Issue type : kbhowto