SQL Query in Visual Basic Problem!

[Newbie19]

I'm trying to break up a sql query from being on long line, so it is easier to maintain for future users.

currently I have this:

Dim QrtTest As String
QrtTest = "SELECT dbo.Issues.ID, dbo.Types.Name AS Type, dbo.Issues.Syno psis, dbo.States.Name AS State, dbo.Users.Name AS [User], dbo.Projects.Na me AS Projects FROM dbo.Issues INNER JOIN (dbo.Projects ON dbo.Issues.Proj ectID = dbo.Projects.ID INNER JOIN dbo.States ON dbo.Issues.Stat eID = dbo.States.ID INNER JOIN dbo.Types ON dbo.Issues.Type ID = dbo.Types.ID INNER JOIN dbo.Users ON dbo.Issues.Assi gnedUserID = dbo.Users.ID"
With ActiveSheet.Que ryTables.Add(Co nnection:=Array (Array( _
"ODBC;DRIVER=SQ L Server;SERVER=d evsql2.corp.adv ertising.com;UI D=<YOURNAME>;AP P=Microsoft Office 2003;WSID=VSOME SWARAN;DATABASE =IntegrityManag e" _
), Array("r;Truste d_Connection=Ye s")), Destination:=Ra nge("A1"), Sql:=QrtTest)
.SourceConnecti onFile = _
"C:\Documen ts and Settings\vsomes waran\Applicati on Data\Microsoft\ Queries\adserve closed defect count.dqy"
.Refresh BackgroundQuery :=False
End With

End Sub

As you see the sql statement is one long line, the basic query is as follows:

SELECT dbo.Issues.ID, dbo.Types.Name AS Type, dbo.Issues.Syno psis, dbo.States.Name AS State, dbo.Users.Name AS [User],
dbo.Projects.Na me AS Projects
FROM dbo.Issues INNER JOIN
dbo.Projects ON dbo.Issues.Proj ectID = dbo.Projects.ID INNER JOIN
dbo.States ON dbo.Issues.Stat eID = dbo.States.ID INNER JOIN
dbo.Types ON dbo.Issues.Type ID = dbo.Types.ID INNER JOIN
dbo.Users ON dbo.Issues.Assi gnedUserID = dbo.Users.ID

How do I break the SQL query up in a VB code?

Thanks,

[danp129]

How do I break the SQL query up in a VB code?

Some people do it by hand and some people are lazy and make a little app to do it for them :) Either way... it looks similar to this in the end.

[CODE=vb]strSQL = "SELECT dbo.Issues.ID, dbo.Types.Name AS Type, dbo.Issues.Syno psis, dbo.States.Name AS State, dbo.Users.Name AS [User],"
strSQL = strSQL & " dbo.Projects.Na me AS Projects"
strSQL = strSQL & " FROM dbo.Issues INNER JOIN"
strSQL = strSQL & " dbo.Projects ON dbo.Issues.Proj ectID = dbo.Projects.ID INNER JOIN"
strSQL = strSQL & " dbo.States ON dbo.Issues.Stat eID = dbo.States.ID INNER JOIN"
strSQL = strSQL & " dbo.Types ON dbo.Issues.Type ID = dbo.Types.ID INNER JOIN"
strSQL = strSQL & " dbo.Users ON dbo.Issues.Assi gnedUserID = dbo.Users.ID"
[/CODE]

[Newbie19]

Thanks for the help,it worked.

[Newbie19]

take that back, now I'm getting an error stating that there is incorrect syntax and is high lighting the following:

Private Sub CommandButton1_ Click()

Dim QrtTest As String
QrtTest = strsql = "SELECT dbo.Issues.ID, dbo.Types.Name AS Type, dbo.Issues.Syno psis, dbo.States.Name AS State, dbo.Users.Name AS [User],"
strsql = strsql & " dbo.Projects.Na me AS Projects"
strsql = strsql & " FROM dbo.Issues INNER JOIN"
strsql = strsql & " dbo.Projects ON dbo.Issues.Proj ectID = dbo.Projects.ID INNER JOIN"
strsql = strsql & " dbo.States ON dbo.Issues.Stat eID = dbo.States.ID INNER JOIN"
strsql = strsql & " dbo.Types ON dbo.Issues.Type ID = dbo.Types.ID INNER JOIN"
strsql = strsql & " dbo.Users ON dbo.Issues.Assi gnedUserID = dbo.Users.ID"
With ActiveSheet.Que ryTables.Add(Co nnection:=Array (Array( _
"ODBC;DRIVER=SQ L Server;SERVER=d evsql2.corp.adv ertising.com;UI D=jmantheiy;APP =Microsoft Office 2003;WSID=JMANT HEIY;DATABASE=I ntegrityManage" _
), Array("r;Truste d_Connection=Ye s")), Destination:=Ra nge("A1"), Sql:=QrtTest)
.SourceConnecti onFile = _
"C:\Documen ts and Settings\JMANTH EIY\Application Data\Microsoft\ Queries\adserve closed defect count.dqy"
.Refresh BackgroundQuery :=False
End With

End Sub

What should I do?

[danp129]

With ActiveSheet.Que ryTables.Add(Co nnection:=Array (Arra y( _
"ODBC;DRIVER=SQ L Server;SERVER=d evsql2.corp.adv ertising.com;UI D=jma ntheiy;APP=Micr osoft Office 2003;WSID=JMANT HEIY;DATABASE=I ntegrityManage" _
), Array("r;Truste d_Connection=Ye s")), Destination:=Ra nge("A1"), Sql:=QrtTest)

replace that with strSQL, or replace all the strSQL's with QrtTest

[Newbie19]

Thanks, I just replaced everything with what I originally had and it worked.