CODEWITHSUNDEEP
excelvba
Pilar Giannine Valera Fernndez
Pilar Giannine Valera Fernndez

Reputation: 55

Create an array in excel from an access table field

I need to create an array in excel vba from an access table field. The table has 4 fields, but what I need is to create an array that contains only data from field 2. I did the connection to access, then I use this code to fill the array:

Array1 = Array(Rs.Fields(1).Value)

But Array1 only loads 1 item from field 2 and I don't know how can I make the loop to load all the data.

Upvotes: 1

Views: 103

Answers (1)

Dy.Lee
Dy.Lee

Reputation: 7567

We need to use dynamic arrays, and move the recordset in a loop.

Dim array1() As Variant
Do Until Rs.EOF
    n = n + 1
    ReDim Preserve array1(1 To n)
    array1(n) = Rs.Fields(1).Value
    Rs.MoveNext
Loop

The method of getting the entire recordset as an array is as follows, but the rows and columns are transposed.

array1 = Rs.GetRows

Upvotes: 1

Related Questions