Create an array in excel from an access table field

Question

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.

Dy.Lee · Accepted Answer · 2020-06-20 15:23:18Z

1

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

answered Jun 20, 2020 at 15:23

Dy.Lee

7,5471 gold badge14 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Create an array in excel from an access table field

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related