ACC: "ODBC Call Failed" When Exporting Table to SQL Server (109341)

SYMPTOMS

Advanced: Requires expert coding, interoperability, and multiuser skills.

You receive the error message "ODBC - Call failed" when you export a Microsoft Access table to a SQL Server database using ODBC if the table field names are SQL reserved words. This error message is followed by another which reads:

[Microsoft][ODBC SQL Server Driver][SQL Server] Incorrect syntax near the keyword '<keyword>'. (#156)

CAUSE

This problem will occur if the table you are exporting contains a field name that is also an SQL keyword. The Microsoft ODBC driver for SQL Server translates the export command into an SQL statement that can be interpreted by the SQL Server. If a field name in the Microsoft Access table is an SQL keyword, the SQL Server will interpret the field name as an SQL command keyword.

RESOLUTION

Rename the fields in the Microsoft Access table to non-SQL reserved words. Field names such as Order, In, From, or Where should not be used when you create Microsoft Access tables.

STATUS

Microsoft has confirmed this to be a problem in Microsoft Access version 2.0. This problem no longer occurs in Microsoft Access version 7.0.

MORE INFORMATION

Steps to Reproduce Problem

Create a new table with a field named Order.
Save the table with the name Test Table.
From the File menu, choose Export.
In the Data Destination box select <SQL Database>, and then choose OK.
In the Select Microsoft Access Object box, choose Test Table. Choose OK.
In the Export dialog box, choose OK.
In the SQL Data Sources dialog box, select a SQL Server. Choose OK.
If necessary, log on to the SQL Server using the SQL Server Login dialog box.

The error messages stated above will be displayed.