SQL.REQUEST

Stochastic,

Thanks for getting back - I don't understand what you mean to mark up - could you give me an idiots guide please.

If this is the formula how to I enter it so that I see both columns of data - also I don't know how many records will be returned so can I "mark" two full columns?


SQL.REQUEST("DSN=EURSUN101.CAMSUN;UID=Viewer;PWD=Rodeo;Database=CAMSUN",,2,"SELECT SSRFACC.SUN_DB,SSRFACC.ACCNT_CODE FROM SSRFACC ",TRUE)

Thanks,

Andrew

Also, if I want to put a cell ref in to replace the connection-string how could I do that?

Then I go do something elsewhere to make sure that that cell was populated according to the users choice in a list box elsewhere on the spreadsheet.

> I don't understand what you mean to mark up
Sorry, bad choice of word. By "mark" I meant select or paint the range.

(this might sound dumb, to correctly characterize me, but let me give an example:
if you want to mark the range B2:C20, click the mouse in B2, keep the mouse button
pressed down, and move to C20 and leave the mouse button. Know what I mean?)

BTW, there is an easier way to select a range if you know the cell addresses of the corners.
Just click in the Name Box (top left corner, below the menu bars) and just type B2:C20 <Enter>


Let's get back on track:

The point is that if you type the formula in a single cell, you get only the first element of the answer.
So you need to type the formula in a whole range which is the target of the formula's output.
So you need to select the whole range - two columns and whatever number of rows.

Should you mark two full columns?
Perhaps not. I think you should mark only as many rows (and two columns) as you expect in the
output. If you select fewer than required columns, you will see a truncated output. If you select too many,
you might get blanks or zeroes or #VALUEs in the excess rows. Try out a few combinations.

And yes, don't forget to type Ctrl-Shift-Enter after writing the formula. Not just Enter.

- sto

Andrew,

you might also look at http://www.bygsoftware.com/examples/sqlrequest.html

It will take a while and some effort to get it working (paths etc), but it will be worth it.

- stochastic

Stochastic,

This is realy maddening!!!

I've entered the output range and doen the Ctrl-SHift_Enter thing giving me curly brackets around the whole formula but still,  alone, the resultset is SUN_DB and that appears in the cell where the formula was typed.

If I take off the true part in the formula then I get the ist value in the SUN_DB column - but that's all.

What in the name of Fridat Afternoon BST can be going on??

Grateful for your continuing help with this,

U couldn't send me an example of a formula that actually works could you ?  ( just querying two columns like mine and I'll amend the names)

Andrew

 

You can always try using ADO. It's faster/more robust than SQLRequest.

You can use code similar to what you see below, but customize it for your needs. You could pass it the arguments (such as the connection string, where to dump the results, etc)... and you can set it up to run based on different events, such as whenever you click a button or whenever you change the value in a certain cell or whatever.

Let's say you have three criteria... the connection string, the cell that you want to drop the data into (you just have to specify the top left cell, it wil populate the full range starting there) and the SQL statement.

Type the con string into A1, the cell into A2 and the SQL into A3 and then run this.

Sub SQLReq()

    On Error GoTo ErrHndlr
   
    strCon = Range("A1").Value
    strRng = Range("A2").Value
    strSQL = Range("A3").Value    
    con.Open strCon
    Set rs = con.Execute(strSQL)
   
    Range(strRng).CopyFromRecordset rs
   
ExitHndlr:
    con.Close
    Set con = Nothing
    Set rs = Nothing
    Exit Sub
   
ErrHndlr:
    MsgBox Err.Description
    GoTo ExitHndlr
   
End Sub



WATYF

Andrew,

Sorry, I was away on the weekend. I will try to send you a sample that works
if possible, but the byg software link I provided does have a working example.
Did you get a chance to try that out?

- sto

Changing tack for a moment - I recorded the Macro below which does what I need for one server/database/instance.  In the example below they are EURSUN /CAMSUN/UK1

Now if i can make that general and pass in those arguments then I have the solution I need.

So, I go

Dim strServer As String
Dim strdatabase As String
Dim strinstance As String
 
 
strServer = "DATA!$B$116"
strdatabase = "DATA!$B$117"
strinstance = "DATA!$B$118"


but how do I refer to these in the macro code - UR ADVICE GREATLY APPRECIATED

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 20/09/2004 by Andrew McLaughlin

Dim strServer As String
Dim strdatabase As String
Dim strinstance As String
 
 
strServer=WORKSHEET.(DATA!).$B$116
strdatabase=WORKSHEET.(DATA!).$B$117
strinstance= WORKSHEET.(DATA!).$B$118

'

'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "ODBC;DRIVER=SQL Server;SERVER=EURSUN101;UID=Viewer;PWD=Rodeo;APP=Microsoft® Query;WSID=CAMAMC-D5302K;DATABASE=CAMSUN" _
        , Destination:=Range("A1"))
        .CommandText = Array( _
        "SELECT SSRFACC.SUN_DB, SSRFACC.ACCNT_CODE, SSRFACC.ACCNT_NAME, SSRFACC.SUSPEND" & Chr(13)
        & "" & Chr(10) & "FROM CAMSUN.dbo.SSRFACC SSRFACC" & Chr(13) & "" & Chr(10) & "WHERE (SSRFACC.SUN_DB='UK1')
        AND (SSRFACC.ACCNT_CODE Not Like '66%') AND (SSRFACC.SUSPE" _
        , "ND<>'C')" & Chr(13) & "" & Chr(10) & "ORDER BY SSRFACC.SUN_DB, SSRFACC.ACCNT_CODE")
        .Name = "Query from Test"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
End Sub


   

strServer = Sheets("Data").Range("$B$116").Value
strdatabase = Sheets("Data").Range("$B$117").Value
strinstance = Sheets("Data").Range("$B$118").Value



WATYF

You could also place that code in the WorkSheet_Change event of your data sheet, so that whenever you change the values in any of those cells, it will reload the data.

Place this code in the module of the worksheet that contains your selection cells.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$B$116" Or Target.Address = "$B$117" Or Target.Address = "$B$118" Then
       
        'Put all your code here.
       
    End If
   
End Sub



WATYF

WATYF

I'm having some problems with the syntax here:

FOR INSTANCE - if I want to replace CAMSUN with strdatabase everywhere it appears in the code below, what's the correct syntax?

Thanks,

Andrew

"ODBC;DRIVER=SQL Server;SERVER=EURSUN101;UID=Viewer;PWD=Rodeo;APP=Microsoft® Query;WSID=CAMAMC-D5302K;DATABASE=CAMSUN" _
        , Destination:=Range("A1"))
        .CommandText = Array( _
        "SELECT SSRFACC.SUN_DB, SSRFACC.ACCNT_CODE, SSRFACC.ACCNT_NAME, SSRFACC.SUSPEND" & Chr(13)
        & "" & Chr(10) & "FROM CAMSUN.dbo.SSRFACC SSRFACC" & Chr(13) & "" & Chr(10) & "WHERE (SSRFACC.SUN_DB='UK1')
        AND (SSRFACC.ACCNT_CODE Not Like '66%') AND (SSRFACC.SUSPE" _
        , "ND<>'C')" & Chr(13) & "" & Chr(10) & "ORDER BY SSRFACC.SUN_DB, SSRFACC.ACCNT_CODE")

Please ignore previous question.

I now have the coding below inserted into the data sheet module.  The query was originally set up on Sheet1.
Now I've just passed in the strinstance to the code to test it.

Now when I change cell B4 on the data sheet - where strinstnace the string instance value is taken from - the  coding below doesn't overwrite the original query results, it just dumps the column headings in cell A1 . Do you know what I need to do to fix this?

Thanks,

Andrew



Sub SUPERSOLUTION()

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$2" Or Target.Address = "$B$3" Or Target.Address = "$B$4" Then

Dim strserver As String
Dim strdatabase As String
Dim strinstance As String

strserver = Sheets("Data").Range("$B$2").Value
strdatabase = Sheets("Data").Range("$B$3").Value
strinstance = Sheets("Data").Range("$B$4").Value



    With Sheet1.QueryTables.Add(Connection:= _
        "ODBC;DSN=EURSUN101.CAMSUN;Description=EURSUN101.CAMSUN;UID=Viewer;PWD=Rodeo;APP=Microsoft® Query;WSID=CAMAMC-D5302K;DATABASE=CAMSUN" _
        , Destination:=Sheets("Sheet1").Range("A1"))
        .CommandText = Array( _
        "SELECT SSRFACC.SUN_DB, SSRFACC.ACCNT_CODE, SSRFACC.ACCNT_NAME, SSRFACC.SUSPEND" & Chr(13) & "" & Chr(10) & "FROM CAMSUN.dbo.SSRFACC SSRFACC" & Chr(13) & "" & Chr(10) & "WHERE (SSRFACC.SUN_DB='strinstance') AND (SSRFACC.ACCNT_CODE Not Like '6%') AND (SSRFACC.SUSPEN" _
        , "D<>'C')")
        .Name = "Query from EURSUN101"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
End If
End Sub

WATYF - I have created a new question for this and called it "Automating an ODBC query in Excel".  Please can you look at that new question ( I'll give you credit for the help you giev above under that question heading"

Want to pursue the SQL Request as well.

Thanks,

Andrew

Sorry I didn't get back to this sooner... I just noticed that your new question is already closed. Do you still need help on this?


WATYF

Yes please - i would like to get the SQL.Request to work.

As I had mentioned, you're probably better off using ADO for this because it is more flexible (and faster, if that matters).

Is there a specific reason why you'd like to use SQL.Request, or what that just your first inclination?

If you'd like to try this with ADO, give me a sample of the SQL statement along with the cells on your sheet that would be used to populate it (and the event that you would like to have trigger an update) and I can show you a sample script using ADO.



WATYF

I would like an update to be triggered by a change in column D.

Here's an example of my SQL and variables:

Dim strserver As String
Dim strdatabase As String
Dim strinstance As String


strserver = Sheets("AccountCodes").Range("$I$117").Value
    strdatabase = Sheets("AccountCodes").Range("$I$118").Value
    strinstance = Sheets("AccountCodes").Range("$I$116").Value
   


"SELECT SSRFACC.SUN_DB, SSRFACC.ACCNT_CODE, SSRFACC.ACCNT_NAME, " & _
            "SSRFACC.SUSPEND FROM " & strdatabase & ".dbo.SSRFACC SSRFACC " & _
            "WHERE (SSRFACC.SUN_DB='" & strinstance & "') AND " & _
            "(SSRFACC.ACCNT_CODE = D13 ) AND (SSRFACC.SUSPEND<>'C')")


So any time that column D changes - ie the account code changes I'd like cell E to be populated with the name of the account code as per the SQL statement.  I only reallly want it to return one value - the account code name.

Thanks,

Andrew