Hi everyone,
I'm trying to write some SQL embedded into a VBA macro in an Excel
sheet that pulls data from a DB. Easy enough. I've got several other
sheets with the mechanism working well.
This one sheet, however, has a rather lengthy SQL statement and I
think I'm up against an inherent VBA limitation. I cannot create a
single line in the
VB Editor over 1024 characters -- when I hit 1024,
I'm stuck -- no line wrap. SO, I did some research and found a couple
of possible solutions (one, declare a string variable and chunk up the
SQL string into bits and add them one-by-one into the variable) and
also the use of '& _' to 'span' lines together.
When I try either of these methods, I get a nice MS VBA popup box with
NO text info, an OK button and a Cancel button. I've chopped the
query in half (so it all fits on one line) and THAT works just fine,
so I'm 99% sure the problem is with the '.Open. line and the following
line (see code example below)
I'm NOT a programmer by trade (network guy who got roped into this by
the boss), and I'd appreciate ANY and ALL suggestions you might have
for me.
And yes, I have verified that the SQL query works -- I can execute it
from the SQL Mgmt. Studio and get the expected results.
*************************** Code Example *****************************
Sub AQA()
' Create a connection object and loop variables.
Dim cnSubs As ADODB.Connection
Set cnSubs = New ADODB.Connection
Dim x As Integer
' Provide the connection string.
Dim strConn As String
strConn = "PROVIDER=SQLOLEDB;"
'Connect to the database on the remote server
strConn = strConn & "DATA SOURCE=x.x.x.x; INITIAL CATALOG=xxxx;
uid=xxxx; pwd=xxxx;"
'Now open the connection.
cnSubs.Open strConn
' Create a recordset object.
Dim rsSubs As ADODB.Recordset
Set rsSubs = New ADODB.Recordset
With rsSubs
' Assign the Connection object.
.ActiveConnection = cnSubs
.Open "SELECT assessmentid, MAX(CASE WHEN questionid = 1 THEN
answer END) AS Q1, MAX(CASE WHEN questionid = 2 THEN answer END) AS
Q2, MAX(CASE WHEN questionid = 3 THEN answer END) AS Q3, MAX(CASE WHEN
questionid = 4 THEN answer END) AS Q4, MAX(CASE WHEN questionid = 5
THEN answer END) AS Q5, MAX(CASE WHEN questionid = 6 THEN answer END)
AS Q6, MAX(CASE WHEN questionid = 7 THEN answer END) AS Q7, MAX(CASE
WHEN questionid = 8 THEN answer END) AS Q8, MAX(CASE WHEN questionid =
9 THEN answer END) AS Q9, MAX(CASE WHEN questionid = 10 THEN answer
END) AS Q10, MAX(CASE WHEN questionid = 11 THEN answer END) AS Q11,
MAX(CASE WHEN questionid = 12 THEN answer END) AS Q12, MAX(CASE WHEN
questionid = 13 THEN answer END) AS Q13, MAX(CASE WHEN questionid = 14
THEN answer END) AS Q14, MAX(CASE WHEN questionid = 15 THEN answer
END) AS Q15, MAX(CASE WHEN questionid = 16 THEN answer END) AS Q16,
MAX(CASE WHEN questionid = 17 THEN answer END) AS Q17 INTO
#tmp_results_tech FROM results GROUP BY assessmentid " & _
"SELECT a.id ,a.consultant as Consultant ,a.cliententity + ' / ' +
a.clientcontact as Client ,a.clientemail as
ClientContact ,a.description as Project ,a.reportperiod as
ReportingPeriod ,a.createdate as DateSent ,a.completedate as
DateReceived ,r.Q1 ,r.Q2 ,r.Q3 ,r.Q4 ,r.Q5 ,r.Q6 ,r.Q7 ,r.Q8 ,r.Q9 ,r.Q10 ,r.Q11 ,r.Q12 ,r.Q13 ,r.Q14 ,r.Q15 ,r.Q16 ,r.Q17 ,c.text
as Comments FROM assessments a LEFT OUTER JOIN #tmp_results_tech r ON
a.id = r.assessmentid LEFT OUTER JOIN comments c ON a.id =
c.assessmentid WHERE a.department = 'Technology Consultant' OR
a.department = 'Technology - subcontractors' OR (a.department =
'Admin' AND a.consultant = 'Wagner, Jack') DROP TABLE
#tmp_results_tech"
Long SQL Query (>1024) in VBA???
