Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: SQL syntax error in from clause...

  1. #1
    Board Regular
    Join Date
    Jul 2008
    Location
    New Jersey
    Posts
    422
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default SQL syntax error in from clause...

    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:

    Code:
    '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

  2. #2
    Board Regular
    Join Date
    Jan 2009
    Posts
    1,159
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SQL syntax error in from clause...

    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

  3. #3
    Board Regular
    Join Date
    Jul 2008
    Location
    New Jersey
    Posts
    422
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SQL syntax error in from clause...

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

  4. #4
    Board Regular
    Join Date
    Jan 2009
    Posts
    1,159
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SQL syntax error in from clause...

    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

  5. #5
    Board Regular
    Join Date
    Jul 2008
    Location
    New Jersey
    Posts
    422
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SQL syntax error in from clause...

    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.

  6. #6
    Board Regular
    Join Date
    Jan 2009
    Posts
    1,159
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SQL syntax error in from clause...

    sounds right to me

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

  7. #7
    Board Regular
    Join Date
    Jul 2008
    Location
    New Jersey
    Posts
    422
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SQL syntax error in from clause...

    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...

  8. #8
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,264
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)

    Default Re: SQL syntax error in from clause...

    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%'

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

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  9. #9
    Board Regular
    Join Date
    Jul 2008
    Location
    New Jersey
    Posts
    422
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SQL syntax error in from clause...

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

  10. #10
    Board Regular
    Join Date
    Jan 2009
    Posts
    1,159
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SQL syntax error in from clause...

    ha !

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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •