Copy MS Access Table into a VBA Array

Posted on 2006-04-27
Last Modified: 2007-12-19
I am trying to read an entire MS Access table in to a VBA array.  What is the best way to do this?
Question by:mvs10000
    LVL 34

    Accepted Solution

       Dim v() As Variant
        Dim rs As ADODB.Recordset
        Set rs = New ADODB.Recordset
        rs.Open "SELECT * FROM tblData", CurrentProject.Connection, adOpenDynamic, adLockReadOnly
        v = rs.GetRows
    LVL 65

    Expert Comment

    by:Jim Horn
    Just out of curiosity, why do you wish to do this?

    Either way (using ADO)

    Dim cn as ADODB.Connection
    Set cn = CurrentProject.Connection

    Dim rs as ADODB.Recordset
    Set rs = New ADODB.Recordset

    Dim arr() as Variant

    rs.Open "SELECT * FROM SomeTableName"

    arr = rs.GetRows
    LVL 26

    Expert Comment

    Seriously...!? Why?

    LVL 5

    Author Comment

    I am trying to read an Access table to Area.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

    730 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now