+ Reply to Thread
Results 1 to 7 of 7

Send results of SQL query to an array

Hybrid View

  1. #1
    Registered User
    Join Date
    12-09-2008
    Location
    Hertfordshire, England
    Posts
    12

    Send results of SQL query to an array

    I have successfully connected to a database and extracted the data I want to a worksheet using the 'Activesheet.QueryTables.Add' approach.

    However, I don't want the data on a worksheet at all. Ideally I want the data to be put into an array which I can then extract data from to enter on a user form or in a comment box.

    I can do this by downloading the data to a worksheet and then creating an array from that data but this seems long-winded. Does anybody know of an easier way to get my query results into an array?

  2. #2
    Valued Forum Contributor
    Join Date
    08-26-2006
    Location
    -
    MS-Off Ver
    2010
    Posts
    388
    Use ADO (ActiveX Data Objects) and SQL to return the results of the query to a recordset.
    Then loop through the records in the recordset doing whatever you need to do. An array isn't required for this.

  3. #3
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    22,064
    You didn't say what database, so here's an ADO sample to get data from a workbook into an array (requires a reference to the Microsoft ActiveX Data Objects 2.n library):
    Sub GetData()
       ' Sample demonstrating how to return a recordset from a workbook
       Dim cn As ADODB.Connection, strQuery As String, rst As ADODB.Recordset, strConn As String
       Dim varData As Variant
       Set cn = New ADODB.Connection
    '   strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ActiveWorkbook.FullName & ";" & _
    '         "Extended Properties=""Excel 8.0;HDR=Yes;"""
       With cn
          .Provider = "Microsoft.Jet.OLEDB.4.0"
          .ConnectionString = "Data Source=" & ActiveWorkbook.FullName & ";" & _
             "Extended Properties=""Excel 8.0;HDR=Yes;"""
          .Open
       End With
       strQuery = "SELECT * FROM [Sheet1$];"
       Set rst = New ADODB.Recordset
       rst.Open strQuery, strConn, adOpenStatic, adLockReadOnly, adCmdText
       ' dump array of data into variable
       varData = rst.GetRows
       rst.Close
       Set rst = Nothing
    '   cn.Close
       Set cn = Nothing
    End Sub

  4. #4
    Registered User
    Join Date
    12-09-2008
    Location
    Hertfordshire, England
    Posts
    12
    Thanks, I seem to be on the right track now but I have run into difficulties with the provider and connection string.

    The database is an IBM iSeries AS/400 server. I have a driver for this configured in Data Sources(ODBC) area of the Windows control panel. The driver is called 'iSeries Access ODBC Driver' and the data source name is As400a. When I use the 'QueryTables.Add' method I set the connection property to "ODBC;DSN=As400a;" and it works fine, however, I have no idea what provider or connection string to use with ADO as everything I have tried has failed.

  5. #5
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    22,064
    Try this site for all things related to connections strings (link is to the page for AS400)

  6. #6
    Valued Forum Contributor
    Join Date
    08-26-2006
    Location
    -
    MS-Off Ver
    2010
    Posts
    388
    I've tested the following code with a User DSN called AS400a connected to a MS Access database containing a 'Computers' table.

    Just change the SQL string to query the table(s) in your database as required.
    Sub QueryAS400()
        Dim rsData As ADODB.Recordset
        Dim sConnect As String
        Dim sSQL As String
        Dim iField As Integer
        
        sConnect = "DSN=AS400a;"     'User DSN - AS400a
        sSQL = "SELECT * FROM Computers"
        Set rsData = New ADODB.Recordset
        rsData.Open sSQL, sConnect, adOpenForwardOnly, adLockReadOnly, adCmdText
        
        'print records to immediate window
        If Not rsData.EOF Then
            Do While Not rsData.EOF
                For iField = 0 To rsData.Fields.Count - 1
                    Debug.Print rsData.Fields(iField).Value; " ";
                Next iField
                Debug.Print 'new line
                rsData.MoveNext
            Loop
        Else
            MsgBox "No records"
        End If
                
        rsData.Close
        Set rsData = Nothing
    
    End Sub

  7. #7
    Registered User
    Join Date
    12-09-2008
    Location
    Hertfordshire, England
    Posts
    12
    That's worked perfectly! And the website link was very useful too! Thank you guys.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1