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

• ActiveX Data Objects (ADO), versions 1.0, 1.5

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 97 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: "[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: User needs to create a DSN called "NWIND" in the ODBC Data Source Administrator for the code below to work.

WARNING: ANY USE BY YOU OF THE CODE PROVIDED IN THIS ARTICLE IS AT YOUR OWN RISK. Microsoft provides this code "as is" without warranty of any kind, either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose.

Steps to Reproduce Behavior

1. Create a Visual Basic for Applications (VBA) project and add a reference to Microsoft OLE DB ActiveX Data Objects.

2. Add a form and a command button (Command1) and 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 and click the command button. You will see the error.

4. Change the rs.Open line to:

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

5. Re-run and the error will go away.

(c) Microsoft Corporation 1998, All Rights Reserved. Contributions by Malcolm Stewart, Microsoft Corporation

Keywords          : adoaccess
Version           : WINDOWS:1.0,1.5
Platform          : WINDOWS
Issue type        : kbprb