SQL query works in access but when called from Excel

[john robinson]

I am trying to send an SQL query string from Excel VBA to an access database. I have a standard module for this and it works fine except when I am trying to get this query to run.
sSQL = SELECT SV_CgRefset.Name FROM SV_CgRefset WHERE ((SV_CgRefset.Name) = "CKD_15");

In short this gets to the line once connections are set up and executes fine but returns empty.
Set Rs = .Execute(sSQL)

IF I send this string to the same database it works fine
sSQL = "SELECT sct.cui, sct.term " & _
"FROM sct " & _
"WHERE (((sct.cui)=105000));"

I have excluded all the other fields I want to return to narrow down the issues and it seems to me it may be due to the "_" in the tablename. It doesn't work for the string "CKD" with no "_" either.
Both "CKD" and CKD_15" are valid names in the table and both string work in access.
I actually want to send a list of names. At one point it did return one result from a list of names but I can't get anything to return now. No errors are generated.
Most grateful for any help.

 

[welshgasman]

Use single quotes around text string, unless that string can have a single quote. Then it is triple double quotes I think. You would have to test.
If CKD etc are names of fields, then you would concatenate the value of that field, in the same way you would a form control/variable.