Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL array using VBA (VBA\Excel 2003)

    How can take the results of a SQL query and place the values in an array using VBA?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: SQL array using VBA (VBA\Excel 2003)

    Here is some code using ADO. You must have set a reference (in Tools | References...) to the Microsoft ActiveX Data Objects 2.n Library.
    <code>
    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim vArray As Variant

    ' Open recordset
    cnn.Open ...
    rst.Open "SELECT strLastName, strFirstName FROM tblEmployees", cnn

    ' Get records into array
    vArray = rst.GetRows

    ' Clean up
    rst.Close
    Set rst = Nothing
    Set cnn = Nothing

    ' Example of using the array
    Dim i As Long
    For i = LBound(vArray, 2) To UBound(vArray, 2)
    Debug.Print vArray(0, i) & ", " & vArray(1, i)
    Next i

    ' Clean up
    Erase vArray
    </code>

    The DAO Recordset object also has a GetRows method, so if you prefer using DAO instead of ADO, you can. You must have set a reference to the Microsoft DAO 3.6 Object Library.

  3. #3
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL array using VBA (VBA\Excel 2003)

    Thanks. It works great. Is there a way to compare the vArray to another array created from data in an Excel spreadsheet and extract only what values are different? Thanks.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: SQL array using VBA (VBA\Excel 2003)

    The only way I can think of is to loop through the elements of one array and compare each to the corresponding element in the other array.

Posting Permissions

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