Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

How to fetch the data from SQLSERVER in an array in VBA EXCEL instead to display it in worksheet

Posted on 2014-03-26
Last Modified: 2014-03-27

My question is simple for EE used to EXCEL VBA
I want to get the data from SQLSERVER in an array instead to display
it in the worksheet
My code works to select the data and display it in the EXCEL worksheet
it is done via this command

 ThisWorkbook.Sheets("my_sheet").Range("D7").CopyFromRecordset rst

Open in new window

Can Someone tell me how to fetch the rst in an array inside the macro VBA please?

Thanks Dave
 Sub load_users()

    Dim cnn As ADODB.Connection

    Dim connectionString As String   Dim rst1 As ADODB.Recordset
    Dim rst2 As ADODB.Recordset
   Dim strSQL As String
    Dim User_ID As String
    Dim User_PWD As String
    Dim Initial_Catalog As String 'Nom de la base de données
    Dim Workstation_ID As String
    Dim Data_Source As String
    Application.ScreenUpdating = False
    On Error GoTo Verif_Connexion
    User_ID = "my login " '"SYGES"   
    User_PWD = "my password" ' "SYGES2014"  
    Initial_Catalog = "DATABASE"  
    Data_Source = "url"  
    Workstation_ID = "url"  '
    Set cnn = New ADODB.Connection
    connectionString = "Provider=SQLOLEDB.1;Persist Security Info=True;User ID=" & User_ID & ";PWD=" & User_PWD & ";Initial Catalog=" & Initial_Catalog & ";Data Source=" & Data_Source & ";Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=" & Workstation_ID & ";Use Encryption for Data=False;Tag with column collation when possible=False;"
    ThisWorkbook.Sheets("my_sheet").Range("A2:AZ65536").Value = ""
    strSQL = "select login, password from MYTABLE  "
    cnn.Open connectionString

    Set rst = cnn.Execute(strSQL)


    'This put the result in the worksheet
    'I would like to have it in an array inside the code instead to displa it in the worksheet

    ThisWorkbook.Sheets("my_sheet").Range("D7").CopyFromRecordset rst

    Set rst = Nothing
    Set cnn = Nothing
end sub

Open in new window

Question by:DavidInLove
  • 3
  • 2
LVL 50

Accepted Solution

Rgonzo1971 earned 300 total points
ID: 39955521

pls try

arrRecordArray = rst.GetRows

Open in new window


Author Comment

ID: 39955608

I've tried to code something but it doesn't work.

' I declare the array as Variant
  Dim arrRecordArray As Variant

    Set rst = cnn.Execute(strSQL)
 arrRecordArray = rst.GetRows

MsgBox (arrRecordArray(0))

Open in new window

I just would like to do the same as the following working in PHP

    $res_query  = mssql_query ($req);			

Open in new window

So if SO could give me the code to fetch

Thanks Dave
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 200 total points
ID: 39955628
The returned array will be two dimensional with fields as 'rows' and records as 'columns' so you have to supply both indices.
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.


Author Comment

ID: 39955640
Still doesn't work:

Since I am not good at VBA maybe the way to fetch the data in array I did is wrong:

 Dim arrRecordArray As Variant
    Set rst = cnn.Execute(strSQL)
 arrRecordArray = rst.GetRows

MsgBox (arrRecordArray(1)(1))

Open in new window

LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 200 total points
ID: 39955646
MsgBox arrRecordArray(1, 1)

Open in new window

is how you specify both indices.

Author Closing Comment

ID: 39958344
Ok Thanks to you.

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

808 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