PRB: "Syntax Error in FROM clause" Opening a Jet Table (181489)

MORE INFORMATION

When using the Microsoft Access ODBC driver from ADO, even if you specify adCmdTable as the command type, the ODBC driver still passes it back to Jet as a SQL command.

NOTE: The user needs to create a DSN called "NWIND" in the ODBC Data Source Administrator for the following code to work.

Steps to Reproduce Behavior

1. Create a Visual Basic for Applications (VBA) project and add a reference to Microsoft ActiveX Data Objects.
2. Add a form and a command button (Command1) that has the following code:

      Private Sub Command1_Click()
      Dim cn As ADODB.Connection, rs As ADODB.Recordset
        Set cn = New ADODB.Connection
        cn.Open "DSN=NWIND"
        Set rs = New ADODB.Recordset
        rs.Open "Order", cn, adOpenKeyset, adLockOptimistic, adCmdTable
        rs.Close
        cn.Close
      End Sub
   					

3. Run the project, click the command button, and note that you see the error.
4. Change the rs.Open line to:

       rs.Open "[Order]", cn, adOpenKeyset, adLockOptimistic, adCmdTable
   					

5. Re-run and note that the error does not occur.

NOTE: A similar error also occurs using Remote Data Objects (RDO), which also uses the ODBC driver, but not with Data Access Objects (DAO) which does not use the ODBC driver. The workaround of using square brackets "[ ]" around the table name also works in RDO.