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 describes two methods that you can use to fill an array using
Visual Basic for Applications. 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:
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. Using
Preserve can cause your code to run significantly slower; it is better to
create an array with a known number of elements prior to filling it.
- 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
Keywords : kbprg PgmHowTo PgmOthr
Version : 1.0 1.1 2.0 7.0 97
Platform : WINDOWS
Hardware : x86
Issue type : kbhowto