Using SQL with VBA

[eoinymc]

Hi,

I was wondering if anyone out there can help me!

Currently, when I want to pull data from my Oracle database, I use Microsoft Query.

I click on Data -> From Other sources -> From Microsoft Query. I choose my data source (Microsoft ODBC for Oracle Connect) and then enter they SQL query using the Microsoft Query editor. This pulls the required data into my spreadsheet.

Can anyone help me recreate this using VBA? I know it's possible, but just can't seem to find how to do it by googling!

My connections string is: DSN=CATL1;UID=CATL1_user;PWD=abc123;SERVER=LIVE01;

A simple sample query that I would use is:

select fname, lname, sum(total_spend) from user_spend
where spend_month <= 3
and account_num in ('TL234', 'TL245', 'TL567')

Any help would be greatly appreciated.

If you need anymore information, please let me know.

Thanks,

Eoin

 

[eoinymc]

Gary,

I can't use IN and parameters using MS Query I don't believe...


rorya, thanks for your help...where would the function go? Inside my Sub?

Cheers,

Eoin

 

[RoryA]

No - the functions go outside the sub ( you can never define a sub/function within another sub/function).

 

[eoinymc]

Thanks for that...

I am now getting an error saying Type Mismatch and when I debug, it is pointing to the .CommandText line.

Any ideas what this error may be?

Cheers,

E

 

[RoryA]

What is the complete code for that line now?

 

[eoinymc]

This is basically what I have:

Function GetStringInList(rngCriteria As Range) As String
   Dim rngCell As Range
   Dim strTemp As String
   For Each rngCell In rngCriteria.Cells
      If Len(rngCell.Value) > 0 Then strTemp = strTemp & ",'" & DoubleQuotes(rngCell.Value) & "'"
   Next rngCell
   ' now strip off leading comma
   GetStringInList = Mid$(strTemp, 2)
End Function
Function DoubleQuotes(strIn As String) As String
   DoubleQuotes = Replace(strIn, "'", "''")
End Function

Sub NewQuery()
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "ODBC;DSN=CATL1;UID=CATL1_user;;SERVER=LIVE01;", Destination:=Range( _
        "$A$1")).QueryTable
 .CommandText = "SELECT n.fname, n.fname, sum(total_spend) FROM user_spend n WHERE (n.spend_month <= 3) AND (n.account_num In ((" & GetStringInList(Sheets("Sheet2").Range("A1:A3") & "))"
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_Query_from_CATL1_1"
        .Refresh BackgroundQuery:=False
    End With
End Sub

Only difference from what I originally said is that the account numbers are just numeric. They are not alphanumeric.

Cheers,

Eoin

 

[RoryA]

2 typos:

    CommandText = "SELECT n.fname, n.fname, sum(total_spend) FROM user_spend n WHERE (n.spend_month <= 3) AND (n.account_num In (" & GetStringInList(Sheets("Sheet2").Range("A1:A3")) & "))"

 

[eoinymc]

Thanks Rory...works great!!