SQL syntax error in from clause...

[krice1974]

Hi all. I'm trying to query my access database from outlook vba. I wrote a very basic query in access that works, and copied in the sql to access. I get a "syntax error in from clause" on the last line below. Here's what I've got:

'Get the symbol from the database.
    str = "SELECT tblSymbolsMain.Symbol FROM tblSymbolsMain WHERE (((tblSymbolsMain.Company) " & _
        "Like '*" & Company & "*'));"
    
    'Open a recordset.
    Set rsData2 = New ADODB.Recordset
    
    rsData2.Open str, conn, adOpenKeyset, adLockOptimistic, adCmdTable

Any ideas? As always thanks in advance...

Kevin

 

[james_lankford]

does Company have a single quote in it ?

Smith and Son's Groceries
would be
Like '*Smith and Son's Groceries*'

the single quote in the company name would conflict with the single quotes surrounding the name

 

[krice1974]

Hi James, unfortunately no, the value I'm stuck on that's throwing the error is "Cypress"

 

[james_lankford]

do

debug.print str

'Open a recordset.
Set rsData2 = New ADODB.Recordset

and set a breakpoint on the set statement and run the code.

then when the code breaks there, look at the immediate window, copy the printed sql and paste it directly into the the access query builder and try to run it
see if it gives you a better error message or something

 

[krice1974]

Hi James,

Earlier before I posted I did just about the same thing (I think, tell me if I'm off) by doing "? str" in the immediate window right after it was assigned its value, and I pasted that into a sql window in access and it ran just fine.

 

[james_lankford]

sounds right to me

is this all Access or are the tables MySQL, SQL Server or Oracle ?

 

[krice1974]

Straight up access. Could it be something in the translation between outlook vba and access vba? The ado? I guess it has to be actually since the query works in access...

 

[xenou]

Straight up access. Could it be something in the translation between outlook vba and access vba? The ado? I guess it has to be actually since the query works in access...

Might be worth a try using another syntax for the wildcard. In fact, I vaguely recall the asterisks not working for me either once.

Try:
Like '%Cypress%'

str = "SELECT tblSymbolsMain.Symbol FROM tblSymbolsMain " _
    & "WHERE tblSymbolsMain.Company " _
    & "LIKE " & Chr(39) & Chr(37) & company & Chr(37) & Chr(39) _
    & ";"

 

[krice1974]

Unfortunately that didn't do it. This is a head scratcher at this point.

 

[james_lankford]

ha !

rsData2.Open str, conn, adOpenKeyset, adLockOptimistic, adCmdTable