Microsoft KB Archive/181489

= PRB: "Syntax Error in FROM clause" Opening a Jet Table =

Article ID: 181489

Article Last Modified on 3/14/2005

-

APPLIES TO


 * Microsoft ActiveX Data Objects 1.0
 * Microsoft ActiveX Data Objects 1.5
 * Microsoft ActiveX Data Objects 2.0
 * Microsoft ActiveX Data Objects 2.1 Service Pack 2
 * Microsoft ActiveX Data Objects 2.5

-



This article was previously published under Q181489



SYMPTOMS
When opening a Microsoft Jet table using ActiveX Data Objects (ADO), the program displays the following error:

Run-time error '-2147217900 (80040e14)':

[Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause.



CAUSE
The table name is a SQL reserved word, such as "Order."



RESOLUTION
Change the table name to a non-reserved word, such as "Orders" or place square brackets around the name as follows:

"[Order]"



STATUS
Microsoft is researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.



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
 Create a Visual Basic for Applications (VBA) project and add a reference to Microsoft ActiveX Data Objects.  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  Run the project, click the command button, and note that you see the error.  Change the rs.Open line to: rs.Open "[Order]", cn, adOpenKeyset, adLockOptimistic, adCmdTable  Re-run and note that the error does not occur.</li></ol>

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.

Keywords: kbdatabase kbjet kbprb kbmdacnosweep KB181489

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.