SQL Statement giving Syntax error

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

Answer 1

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)

Answer 2

   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.

Answer 3

Thanks Hans. This resolved it.