query external database

[joemeshuggah]

i have a macro that automatically runs a query in an external database
(teradata) and puts the results in excel...the query is very simple...e.g.

query = "select * from mytbl"

i would like to use the same idea for more complex queries (multiple joins
and conditions, etc), however when i paste the query into the macro, the
formatting causes issues (e.g. the query text is highlighted in red). how do
i incorporate the new query? i tried using _ to connect, but at one point i
received a message that too many were used.

 

[FSt1]

hi
if high lighted in red then you have a syntax problem
post your macro.

regards
FSt1

 

[joemeshuggah]

the problem is when i copy and paste the query into the macro...for example

query = "select * from mytbl inner join mytbl2 on mytbl.a=mytbl2.a" would
work fine...

but when copying and pasting the query as written into the macro, the red
text appears (a quote appears automatically after select *, inner join mytbl2
on and mytbl.a=mytbl2.a appear in red and a quote automatically appears after
mytbl.a=mytbl2.a).

query = "select * "
from mytbl
inner join mytbl2 on
mytbl.a=mytbl2.a"


is it possible to paste the query without reformatting it...and if not, how
do you reformat to ensure there are no syntax errors? here is an example of
how the query is formatted:

select *

from

mytbl

inner join mytbl2 on
mytbl.a=mytbl2.a

 

[FSt1]

hi
i don't think you understand. you are just posting the SQL. Post the entire
query. with red text, that is invalid syntax. we need to see more than just
the red text.
there is a lot more to it that just "query =.....". we need to see all of
"the other stuff". help us help you. post all of your macro.
it's 3am in atlanta. i am about to crash. i will search for you reply
tomorrow. god bless.
regards
FSt1

 

[Patrick Molloy]

your query may be split over sevaral lines...its impossible to tell unless
you post the exact code.

however you have two options here:

1) use one line
query = "select * from mytbl inner join mytbl2 on mytbl.a=mytbl2.a"

or
(2) concatenate
query = "select * from mytbl inner" _
& "join mytbl2" _
& "on mytbl.a=mytbl2.a"

note here the underscore "_" which tells the VBA compiler that the line
follows on

 

[norie]

If you are getting an error regarding too many _ try building the SQL
like this.

strSQL = "SELECT * "
strSQL = strSQL & "FROM mytbl "
strSQL = strSQL & "INNER JOIN mytbl2 ON mytbl.a=mytbl2.a;"

You can also use this syntax to make the code more readable, but be
careful to add spaces where needed for the SQL.