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

Article translations Article translations
Article ID: 109727 - View products that this article applies to.
This article was previously published under Q109727
Expand all | Collapse all

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:
  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
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.
  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

Properties

Article ID: 109727 - Last Review: January 18, 2007 - Revision: 2.3
APPLIES TO
  • Microsoft Access 1.0 Standard Edition
  • Microsoft Access 1.1 Standard Edition
  • Microsoft Access 2.0 Standard Edition
  • Microsoft Access 95 Standard Edition
  • Microsoft Access 97 Standard Edition
Keywords: 
kbhowto kbprogramming KB109727
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com