none
SQL Statement giving Syntax error RRS feed

  • Question

  • Hi all,

    I want to retrieve data from a remote database using the SELECT statement. The statement code as follows:

       Set rs = cnn.Execute("SELECT * " _
        & "FROM CusMdnMaster+;")

    The table name I want to access is "CusMdnMaster+". The select statement does not like the "+". It generates a "....syntax error near '+'"

    I tried the following:

       Set rs = cnn.Execute("SELECT * " _
        & "FROM CusMdnMaster" & Chr(43) & ";")

    No joy.

    Then I also tried:

       Set rs = cnn.Execute("SELECT * " _
        & "FROM CusMdnMaster" & "'" & Chr(43) & "'" & ";")

    Still no joy.

    And also:

       Set rs = cnn.Execute("SELECT * " _
        & "FROM CusMdnMaster" & "'" + "'" & ";")

    Also no joy.

    Any suggestions how I can access this table?

    Thanks

    Deon

    Thursday, June 30, 2016 1:40 PM

Answers

  • Table names, query names and field names that contain spaces or "unusual" characters such as "+" must be enclosed in square brackets [ ] in SQL:

    Set rs = cnn.Execute("SELECT * " _
        & "FROM [CusMdnMaster+];")

    (For many database developers this is a reason to avoid spaces, punctuation etc. in table names, query names and field names; the same goes for database objects such as forms and reports and for controls on forms and reports)


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Deon SA Thursday, June 30, 2016 2:16 PM
    Thursday, June 30, 2016 2:11 PM
  •    Set rs = cnn.Execute("SELECT * " _
        & "FROM CusMdnMaster+;")

    Hi Deon,

    The lesson to learn is NOT to use the + (or other extravagant characters) in objectnames.

    Did you try to surround the name with square brackets:  [CusMdnMaster+]

    Imb.


    • Edited by Imb-hb Thursday, June 30, 2016 2:16 PM
    • Marked as answer by Deon SA Thursday, June 30, 2016 2:16 PM
    Thursday, June 30, 2016 2:15 PM

All replies

  • Table names, query names and field names that contain spaces or "unusual" characters such as "+" must be enclosed in square brackets [ ] in SQL:

    Set rs = cnn.Execute("SELECT * " _
        & "FROM [CusMdnMaster+];")

    (For many database developers this is a reason to avoid spaces, punctuation etc. in table names, query names and field names; the same goes for database objects such as forms and reports and for controls on forms and reports)


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Deon SA Thursday, June 30, 2016 2:16 PM
    Thursday, June 30, 2016 2:11 PM
  •    Set rs = cnn.Execute("SELECT * " _
        & "FROM CusMdnMaster+;")

    Hi Deon,

    The lesson to learn is NOT to use the + (or other extravagant characters) in objectnames.

    Did you try to surround the name with square brackets:  [CusMdnMaster+]

    Imb.


    • Edited by Imb-hb Thursday, June 30, 2016 2:16 PM
    • Marked as answer by Deon SA Thursday, June 30, 2016 2:16 PM
    Thursday, June 30, 2016 2:15 PM
  • Thanks Hans. This resolved it.
    Thursday, June 30, 2016 2:16 PM