Too long SQL in Excel VBA

Question

Hi,

I wrote query that is of about 56,514 characters in which I have to union 9 different temp tables into the tenth table which gives me final results after some calculations. I want to pull it in MS Excel using VBA because I have to use some inputs from sheet cells. Since Excel wouldn't allow me paste all the 16 pages in one Cell, I put the query in 16 different cells using Excel's ""&"" concatenation to keep the text strings under 255 chars. There are about 4,000 characters in each cell now concatenated @ intervals of less than 255 chars (or Excel wont let me have it).

Now, when I use the below pasted macro to get the data, it throws me an Application error 1044 ~ which I persume is because of the string length.

Macro code is:

Sub Details()
Dim sql
sql = Sheets("Sheet1").Cells(1, 10) + Sheets("Sheet1").Cells(1, 11) + Sheets("Sheet1").Cells(1, 12) + Sheets("Sheet1").Cells(1, 13) + Sheets("Sheet1").Cells(1, 14) + Sheets("Sheet1").Cells(1, 15) + Sheets("Sheet1").Cells(1, 16) + Sheets("Sheet1").Cells(1, 17) + Sheets("Sheet1").Cells(1, 18) + Sheets("Sheet1").Cells(1, 19) + Sheets("Sheet1").Cells(1, 20) + Sheets("Sheet1").Cells(1, 21) + Sheets("Sheet1").Cells(1, 22) + Sheets("Sheet1").Cells(1, 23) + Sheets("Sheet1").Cells(1, 24) + Sheets("Sheet1").Cells(1, 25)
Range("a10:e100") = ""
Dim connString
connString = "ODBC;DSN=SNLDB2;Description=DB2;UID=FarhanN;Trusted_Connection=Yes"
Dim thisQT As QueryTable
Set thisQT = QueryTables.Add(Connection:=connString, Destination:=Range("a10"))
thisQT.BackgroundQuery = False
thisQT.sql = sql
thisQT.Refresh
End Sub

Please help me with fixing up this error!! I have tried the results of these 16 cells text formula in MS Query and it does provide me results; therefore, the query is good to go but I dont know what's wrong with the VBA thing as it cannot do thisQT.Refresh. Alternatively, is there a way out to run SQL query in MS Query and take values for variables from sheet cells? I wont be able to build up a 16 pages long query using MS Query's query wizard, though.

Thanks in advance for your help.

Answer 1

0

Can't you just create a stored procedure and execute that?

answered Jan 01 '12 at 10:20 AM

GPO
1.7k ● 26 ● 30 ● 33

+1 : I wish I could give more.......

Jan 01 '12 at 02:40 PM Kev Riley ♦♦

I dont think I have enough rights on the server to create an SP. Also, in my next version of this query, I'll be taking almost all the input values (roughly 50+) from Sheet cells making it user friendly for some users who are even newbies to Excel. Can any other method be used? or Shall I share the query here?

Jan 01 '12 at 10:33 PM FarhanN

(comments are locked)

Answer 2

0

I agree that a Stored Procedure would be the best approach, but even if you do not have the rights to make a stored procedure, you could ask your sysadmin people to make an SP for you. This way you could have limit the SQL Injection attacks. Now, you need to validate all the input variables at application level.

But if still you cannot have the Stored Procedure, you could break down the execution of your query into multiple steps, since, it involves temp tables already. If you are not able to do, then post your query.

answered Jan 01 '12 at 11:59 PM

Sacred Jewel
1.6k ● 2 ● 4 ● 5

(comments are locked)

Answer 3

0

I agree with GPO - your query seems a little long to be executed as dynamic SQL. But I get it; that's not what you're asking for.

So, to your problem instead: The limitation for the CommandText property is 32767 characters. So if you have 4000 characters in each of 16 cells you have some serious work to do in order to reduce redundant space from your SQL string :)

answered Jan 01 '12 at 11:29 PM

Magnus Ahlkvist
13.7k ● 13 ● 17 ● 30

Okay - I'll do the SP thing and send it to sysadmin. Thanks all for the help. I will buzz again if I couldn't get the SP thing approved or breaking down the query to multiple steps.

Jan 02 '12 at 06:06 AM FarhanN

Thanks a lot GPO and Magnus. I wanted to select both as my "accepted answers" but could select only one. Though, according to Magnus reply - I came to know about the max limit of commandText property and added three additional temp tables to cut down the query. It has started working in VBA now but I would still try getting my SP request approved to make the process easier for others. I'm not attaching my query here to avoid spamming but thanks again !!!

Jan 16 '12 at 03:49 AM FarhanN

(comments are locked)

Too long SQL in Excel VBA

Follow this question

Related Questions