Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL command help
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 -- cmpcwil2 ------------------------------------------------------------------------ cmpcwil2's Profile: http://www.excelforum.com/member.php...o&userid=33411 View this thread: http://www.excelforum.com/showthread...hreadid=548912 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL command help
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 -- colofnature ------------------------------------------------------------------------ colofnature's Profile: http://www.excelforum.com/member.php...o&userid=34356 View this thread: http://www.excelforum.com/showthread...hreadid=548912 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL command help
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 -- tony h ------------------------------------------------------------------------ tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074 View this thread: http://www.excelforum.com/showthread...hreadid=548912 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL command help
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! -- cmpcwil2 ------------------------------------------------------------------------ cmpcwil2's Profile: http://www.excelforum.com/member.php...o&userid=33411 View this thread: http://www.excelforum.com/showthread...hreadid=548912 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
One command in one cell initiating another command in another cel. | Excel Worksheet Functions | |||
command code ( GOTO command) in formula | New Users to Excel | |||
command button add another command | Excel Discussion (Misc queries) | |||
Pivot Table Error Message - "Command Text not set for command obje | Excel Discussion (Misc queries) | |||
I want to combine a "match" command with a copy and paste command. | Excel Programming |