Learn how to a build a cloud-first strategyRegister Now


Copy MS Access Table into a VBA Array

Posted on 2006-04-27
Medium Priority
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

flavo earned 1000 total points
ID: 16553942
   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 66

Expert Comment

by:Jim Horn
ID: 16553945
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

ID: 16553948
Seriously...!? Why?


Author Comment

ID: 16553991
I am trying to read an Access table to Area.

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

810 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