ACC: Pass-Through Update Query - ODBC Error (#207) to SQL 6.0

• Microsoft Access 2.0, 7.0, 97

SYMPTOMS

Moderate: Requires basic macro, coding, and interoperability skills.

When you use double quotation marks (") around the values in a pass-through update query or you send a string containing a single quote as in (like ''O Brien') to Microsoft SQL 6.0, you may receive an ODBC call Failed error message with the error "(#207) 'the value' is not a valid column name." This error does not occur when you use the ISQL/w tool with the Microsoft SQL 6.0 client utilities.

CAUSE

Microsoft SQL Server version 6.0 introduced support for ANSI SQL quoted identifiers. Clients can set a connection option asking the server to enforce the ANSI quoted identifier rules for SQL commands sent to it over that connection. ANSI SQL expects double quotation marks (") to enclose identifier and single quotation marks (') to enclose character string data values. Double quotation marks are the identifier delimiter in ANSI SQL, not the string delimiter. In order for this to be a valid pass-through query, you need to use single quotation marks.

The reason for the different behavior between ISQL/W and the pass-through query is that ISQL/W uses DB-LIB, which has a different default behavior than the Microsoft SQL 6.0 ODBC driver which is used by the pass-through query. The ODBC driver sets QUOTED_IDENTIFIERS ON when it runs against a Microsoft SQL Server version 6.0 server so that the driver's behavior more closely matches the ANSI and ODBC standards. ODBC applications that use double quotation marks for parameter values may see this behavior after you upgrade to Microsoft SQL Server version 6.0 and the ODBC 2.50.0121 driver. DB-Library clients such as ISQL/W can also exhibit this behavior if they issue a SET QUOTED_IDENTIFIER ON command.

RESOLUTION

Use single quotation marks for your values instead of double quotation marks. And, if the string you are sending contains a single quote, use two single quotes instead of one, as in the example:

   UPDATE authors SET authors.au_lname = '''O Brien', authors.au_fname =
   'John', authors.phone = '999-999-0000'
   WHERE (((authors.au_id)='527-72-3246'));

MORE INFORMATION

Steps to Reproduce Behavior

1. Create a data source to a Microsoft SQL 6.0 server using the 32-bit ODBC utility in Control Panel for Windows 95 or Windows NT. For Windows 3.x, run the ODBC utility in Control Panel and click the Add button on the Data Sources screen.

2. Select the Microsoft SQL server driver from the Add Data Source dialog box and click OK.

3. Pick your data source name and server name, click Options, and then specify Pubs as the database name.

4. Click OK and quit the ODBC utility.

5. Start Microsoft Access 2.0, 7.0 or 97 and create a new query in Design view. Do not select any tables.

6. On the Query menu, point to SQL Specific, and then click Pass-Through on the menu that appears. You should see a blank window with the title "Query1-SQL Pass-Through Query."

7. On the View menu, click Properties.

8. In the Query Properties dialog box, click the ODBC Connect Str Property, click the Build button, select your data source, and then log onto SQL 6.0.

9. Set the Returns Records Property to No.

10. Close the property windows and type the following in the Query1-SQL Pass-Through Query window:

    UPDATE authors SET authors.au_lname = "Doe", authors.au_fname = "John", authors.phone = "999-999-0000" WHERE (((authors.au_id)="527-72-3246"));

11. Run the query. Note that you receive the error mentioned in the "Symptoms" section.

REFERENCES