SQL Query results directly to Array

[SeanDamnit]

Hello internet,

I'd like to take a SQL query and have it extract directly in to an array, so I can both manipulate the data and store it to avoid unnecessarily pulling fresh data every time this UDF is used.

I googled this, and it seems like the most common solution is to use a recordset, however it's my understanding that anyone using the UDF will need to set a reference to the Microsoft ActiveX Data Object Library in order for this to work. I'm trying to create a very user friendly all-in-one type of solution for my co-workers (who all seem to fear excel and it's magiks) and I'm afraid that requiring additional steps to get this working won't go over well.

I thought I'd be clever, and have my function dump the query in to a temp worksheet, turn the resulting table in to an array, and delete the worksheet, but it dawned on me all too late that a UDF called from the worksheet can't create new worksheets.

Any recommendations?

 

[Norie]

You don't need a reference to the ADO library if you use late-binding.

 

[SeanDamnit]

You don't need a reference to the ADO library if you use late-binding.

late-binding is just not declaring the variables right? how would I modify this cookie cutter to achieve that?

Sub GetDataFromADO()
    'Declare variables'
        Set objMyConn = New ADODB.Connection
        Set objMyRecordset = New ADODB.Recordset
        Dim strSQL As String


    'Open Connection'
        objMyConn.ConnectionString = "Provider=SQLOLEDB;Data Source=localhost;Initial Catalog=MyDatabase;User ID=abc;Password=abc;"
        objMyConn.Open


    'Set and Excecute SQL Command'
        strSQL = "select * from myTable"


    'Open Recordset'
        Set objMyRecordset.ActiveConnection = objMyConn
        objMyRecordset.Open strSQL            


    'Copy Data to Excel'
        ActiveSheet.Range("A1").CopyFromRecordset (objMyRecordset)


End Sub

 

[Norie]

Eh, definitely not.

You still declare the variables, as you always should, you just don't declare them as specific types.

Also, you use CreateObject instead of New to create the objects instead.

So instead of this,

  Set objMyConn = New ADODB.Connection
  Set objMyRecordset = New ADODB.Recordset

you would have this.

Dim objMyConn As Object ' ADODB.Connection
Dim objMyRecordset As Object ' ADODB.Recordset

  Set objMyConn = CreateObject("ADODB.Connection")
  Set objMyRecordset = CreateObject("ADODB.Recordset")

That's the basic idea behind late-binding.

Another thing to consider are named constants.

You need to either replace them with their actual values or declare them in your code.

 

[SeanDamnit]

Eh, definitely not.

You still declare the variables, as you always should, you just don't declare them as specific types.

Also, you use CreateObject instead of New to create the objects instead.

So instead of this,

  Set objMyConn = New ADODB.Connection
  Set objMyRecordset = New ADODB.Recordset

you would have this.

Dim objMyConn As Object ' ADODB.Connection
Dim objMyRecordset As Object ' ADODB.Recordset

  Set objMyConn = CreateObject("ADODB.Connection")
  Set objMyRecordset = CreateObject("ADODB.Recordset")

That's the basic idea behind late-binding.

Another thing to consider are named constants.

You need to either replace them with their actual values or declare them in your code.

Thanks Norie, that makes sense, and I've modified the code as you suggested. But there's an error at the end now: "Run-time error '430': Class does not support Automation or does not support expected interface"

Here is my code:

Sub heisenberg()
    'Declare variables'
        Dim vSelect As String
        Dim vFrom As String
        Dim vOrder As String
        Dim vSQL As String
        
    'Toggle for Early-Binding'
        'Set objMyConn = New ADODB.Connection
        'Set objMyRecordset = New ADODB.Recordset
        
    'Toggle for Late-Binding'
        Dim objMyCmd As Object
        Dim objMyConn As Object
        Dim objMyRecordset As Object
        Set objMyConn = CreateObject("ADODB.Connection")
        Set objMyRecordset = CreateObject("ADODB.Recordset")


    'Open Connection'
        objMyConn.ConnectionString = "Provider=SQLOLEDB;DRIVER=SQL Server;SERVER=xxx;UID=yyy;PWD=zzz;APP=2007 Microsoft Office system;WSID=fff;DATABASE=ddd"
        objMyConn.Open


    'Create Command Variables'
        vSelect = _
            "SELECT " & _
            "LanguageTranslations_3.FieldText AS 'District', " & _
            "iQclerk_Stores.Abbreviation, " & _
            "LanguageTranslations_2.FieldText AS 'Location', " & _
            "LanguageTranslations_1.FieldText AS 'StoreType', " & _
            "LanguageTranslations.FieldText AS 'Address', " & _
            "iQclerk_Stores.City, iQclerk_Stores.Province, " & _
            "iQclerk_Stores.PostalCode, " & _
            "iQclerk_Stores.PhoneNumber AS 'LocationPhone', " & _
            "iQmetrix_Employees.Employee_Name AS 'RegionalManager', " & _
            "iQmetrix_Employees.Email AS 'RegionalEmail', " & _
            "iQmetrix_Employees.Cellular_Number AS 'RegionalCell', " & _
            "iQmetrix_Employees_1.Employee_Name AS 'LocationManager', " & _
            "iQmetrix_Employees_1.Email AS 'ManagerEmail', " & _
            "iQmetrix_Employees_1.Cellular_Number AS 'ManagerCell'"
            
        vFrom = _
            "FROM " & _
            "iQclerk_Districts iQclerk_Districts, " & _
            "iQclerk_Stores iQclerk_Stores, " & _
            "iQclerk_StoreTypes iQclerk_StoreTypes, " & _
            "iQmetrix_Employees iQmetrix_Employees, " & _
            "iQmetrix_Employees iQmetrix_Employees_1, " & _
            "LanguageTranslations LanguageTranslations, " & _
            "LanguageTranslations LanguageTranslations_1, " & _
            "LanguageTranslations LanguageTranslations_2, " & _
            "LanguageTranslations LanguageTranslations_3 "
            
        vWHERE = _
            "WHERE " & _
            "iQclerk_Districts.DistrictID = iQclerk_Stores.DistrictID AND " & _
            "iQclerk_Stores.StoreTypeID = iQclerk_StoreTypes.StoreTypeID AND " & _
            "iQclerk_Stores.AddressID = LanguageTranslations.ReferenceID AND " & _
            "iQclerk_StoreTypes.StoreTypeNameID = LanguageTranslations_1.ReferenceID AND " & _
            "iQclerk_Districts.PrimaryContactID = iQmetrix_Employees.Id_Number AND " & _
            "iQclerk_Stores.PrimaryContactID = iQmetrix_Employees_1.Id_Number AND " & _
            "iQclerk_Stores.StoreNameID = LanguageTranslations_2.ReferenceID AND " & _
            "iQclerk_Districts.DistrictNameID = LanguageTranslations_3.ReferenceID AND " & _
            "iQclerk_Stores.Enabled=1"
            
        vOrder = _
            "ORDER BY " & _
            "LanguageTranslations_3.FieldText"
            
        vSQL = vSelect & vFrom & vWHERE & vOrder


    'Open Recordset'
        Set objMyRecordset.ActiveConnection = objMyConn
        objMyRecordset.Open vSQL


    'Copy Data to Excel'
        ActiveSheet.Range("A1").CopyFromRecordset (objMyRecordset)


End Sub

And I'm getting the error on the last line..."ActiveSheet.Range("A1").CopyFromRecordset (objMyRecordset)"

I googled this, and all the fixes I've found were to switch to the Early Binding setup ><

 

[Norie]

Try removing the () around MyRecordSet.

By the way, if you want an array take a look at GetRows.

 

[SeanDamnit]

Try removing the () around MyRecordSet.

By the way, if you want an array take a look at GetRows.

That worked. I like you Norie.

And yes thanks for the GetRows suggestion. I've never worked with a recordset before, and was just trying to get the **** thing to work before I start figuring out the array part of it.

Thanks again!