Microsoft KB Archive/109727

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

Article ID: 109727

Article Last Modified on 1/18/2007

-

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

-



This article was previously published under Q109727



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 </li> 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

</li></ol>

Keywords: kbhowto kbprogramming KB109727

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.