You are currently offline, waiting for your internet to reconnect

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

This article was previously published under Q109727
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.
SUMMARY
Advanced: Requires expert coding, interoperability, and multiuser skills.

This article shows you two Visual Basic for Applications methods to fill anarray. 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 forApplications and with creating Microsoft Access applications using theprogramming tools provided with Microsoft Access. For more informationabout 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 MicrosoftAccess versions 1.x and 2.0. For more information about Access Basic,please refer to the "Introduction to Programming" manual in MicrosoftAccess version 1.x or the "Building Applications" manual in MicrosoftAccess version 2.0.
MORE INFORMATION
This example demonstrates how to fill a one-dimensional array when you knowthe 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 thenumber of elements. It uses the Preserve argument of the ReDim statement toadjust 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 numberof 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: 01/18/2007 23:42:42 - Revision: 2.3

  • 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
  • kbhowto kbprogramming KB109727
Feedback