Returning SQL to Excel

I've got a lengthy piece of SQL which I need to be able to run and return to excel. With this sort of thing I normally cheat and use the record function to create a background query which I can then add my variables into etc but because this SQL statement is so long it's just not playing ball :(

I think this is due to the sql statement being formed in an array in VB which is limited to certain parameters(?)

Can anyone offer any advice on how this can be done?

Any advice much appreciated...

Excel aside for a moment. Once your query is done running from VB, do you get a successful recordset created?

What DB are you using SQL Server, Access, MySQL, etc...

Right Ok - So this is an Oracle Database (PVEC) which I can happily run my script in SQL Navigator and alike....

When I go down the path of creating a background query, ie Data > Import external Data > New Database query - I can create the query through MS Query and successfully return the data to Excel.

My problem is that once I've recorded a macro of my actions, the SQL shows up as an errored array (in red) and the SQL gets chopped short, presumably because it uses an array to hold the SQL statement and this must have some form of character limitation? (I'm just guessing here)...

So, I can create a background query that I can refresh, however I need a variable in this SQL statement and so need a way of amending the SQL prior to execution......

it sounds like the sql query string is to long, hence showing up in red on the following line.

you could try building the query string in bits (also watch out for quote marks)

thus

VB Code:

mysql = "extreamely long string "withquotemarks" in it in annoying places"

becomes

VB Code:

mysql = "extreamely long string 'withquotemarks'" 
mysql = mysql & " in it in annoying places"

to build it in smaller chunks...

Ok so I tried this method to get to here;

VB Code:

l
 
mysql = "select ...........  lots of stuff         "
mysql = mysql & " from someplace"
mysql = mysql & "where some things equal others..."
mysql = mysql & "and myvariable = 'whatitellitto'.... "
 
With ActiveSheet.QueryTables.Add(Connection:= _
        "ODBC;DSN=PVEC;UID=username;PWD=password;SERVER=PVEC;", Destination:=Range("A1" _
        ))
        .CommandText = Array( _
        "" & mysql)
        .Name = "Query from PVEC_1"
        .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

But I just get a runtime error 13 when the code reaches the & mysql point... :confused:

in the above, mysql ends up 1794 characters long if this helps!!??

Any other Ideas?

Did you dim mysql as a string variable?

Does the .commandText as to be an array?
Can't you just say .commandText= mysql ?

I don't know, I'm just guessing.