Microsoft KB Archive/208481

= ACC2000: ODBC Error with Pass-Through Update Query =

Article ID: 208481

Article Last Modified on 6/30/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q208481



This article applies only to a Microsoft Access database (.mdb).

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



SYMPTOMS
When you use quotation marks ("") around the values in a pass-through update query, or you send a string that contains an apostrophe (') to Microsoft SQL Server, you may receive the following ODBC error:

ODBC--call failed.

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name ' '. (#207)



CAUSE
Beginning with SQL Server 6.0, Microsoft 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 quotation marks (") to enclose identifiers, and apostrophes (') to enclose character string data values. 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 must use apostrophes.



RESOLUTION
Use apostrophes around your values instead of quotation marks. Also, if the string that you are sending already contains an apostrophe, use two apostrophes instead of just one. For example, if you were passing the last name of O'Brien, you would precede the apostrophe with two additional apostrophes (that is, 'O''Brien'). UPDATE authors SET au_lname = 'O''Brien', au_fname = 'John', phone = '999-999-0000' WHERE au_id = '527-72-3246';



MORE INFORMATION
When you use quotation marks ("") around the values in a pass-through update query, or you send a string that contains an apostrophe (') to Microsoft SQL Server, you receive an ODBC call failed error.

This error does not occur when you use the ISQL/W tool with the Microsoft SQL Server client utilities. 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 Server ODBC driver that is used by the pass-through query. The ODBC driver sets QUOTED_IDENTIFIERS ON when it runs against a Microsoft SQL Server, so that the driver's behavior more closely matches the ANSI and ODBC standards. DB-Library clients, such as ISQL/W, can exhibit this failed behavior if they issue a SET QUOTED_IDENTIFIER ON command.

Steps to Reproduce Behavior
 Create a new Data Source Name (DSN) that points to a Microsoft SQL Server and specify the Pubs database. After creating the DSN, start Microsoft Access, open any database, and then create a new query in Design View. Do not select any tables. On the Query menu, point to SQL Specific, and then click Pass-Through. You should see a blank window with the title Query1:SQL Pass-Through Query. On the View menu, click Properties. In the Query Properties dialog box, click the ODBC Connect Str property, click the Build button, select your DSN, and then log onto the SQL Server.</li> On the property sheet, set the Returns Records property to No.</li>  Close the property sheet, and then enter the following in the Query1:SQL Pass-Through Query window: UPDATE authors SET au_lname = "Doe", au_fname = "John", phone = "999-999-0000" WHERE au_id = "527-72-3246"; </li> Run the query. Note that you receive the error message mentioned in the "Symptoms" section of this article.</li></ol>

NOTE: Microsoft Access 2000 only supports connectivity to SQL Server 6.5 or higher.

<div class="references_section">