SQL command help

[cmpcwil2]

Hi,
I have an ADODB connection to a sql server DB and have been using MS
query to generate my SQL commands, however they don't seem to work. I
have looked similar threads and tried the suggestions but they don't
work either. TBH I am not all that sure about the format of such sql
commands but have used the following, which work:

cmd.CommandText = "SELECT ApplicationName " & "FROM tbl1"
cmd.CommandType = adCmdText
Set rs = cmd.Execute

if you were to replicate this query in ms Query you would get a
different string that does not work, why is this?

I am now having difficulty with the following sql command; I would like
to select all ApplicationName from tbl1 where ApplicationName contains
java. MS query commands do not work, so I have the following:

cmd.CommandText = "SELECT ApplicationName " & "FROM tbl1" & "WHERE
ApplicationName Like 'java'"

This returns an "incorrect syntax near 'ApplicationName'"
There maybe an obvious mistake I’m making here, but I just don't see it
my sql knowledge is very limited.

If anyone can point me in the direction of a *good* source of correct
syntax for sql commands for vba I would be very grateful.

Thanks in advance for any advice

 

[colofnature]

I don't use MSQuery so I can't answer your first question, but the
incorrect syntax is probably due to the concatenation "FROM tbl1" &
"WHERE ApplicationName Like 'java'" - this results in "FROM tbl1WHERE
ApplicationName Like 'java'". You could replace this:
cmd.CommandText = "SELECT ApplicationName " & "FROM tbl1" & "WHERE
ApplicationName Like 'java'"

with this:
cmd.CommandText = "SELECT ApplicationName FROM tbl1 WHERE
ApplicationName Like '*java*'

For more info on SQL check out www.sql.org


Col

 

[tony h]

Tend to agree with Col. The Like needs wildcards to be useful lie "aa"
is the same as ="aa".

Again I am not a MSQuery user - though I keep meaning to give it a go -
but I would expect the SQL to be terminated by a semi-colon and to use
doublequotes for the strings eg

cmd.CommandText = "SELECT ApplicationName FROM tbl1 WHERE
ApplicationName Like ""*java*"";"


Note that the double double-quotes puts a single double quote in the
string eg myVar="a""b"
msgbox myVar will give a"b

Hope this helps, let us know if it doesn't

 

[cmpcwil2]

Thank you both for your suggestions, I replaced the wild card char *
with % and this works fine. Thank you also for the sql link, just what
I was after!

Can I just say that the people on this forum are so helpful thank you!