ACC2000: ODBC Error with Pass-Through Update Query (208481)

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.
On the property sheet, set the Returns Records property to No.

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";

Run the query. Note that you receive the error message mentioned in the "Symptoms" section of this article.

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

REFERENCES

For more information about creating ODBC connections for pass-through queries, click Microsoft Access Help on the Help menu, type create the odbc connection string for an sql pass-through query in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

For more information about controlling SQL Server through pass-through queries, click Microsoft Access Help on the Help menu, type send commands to an sql database using a pass-through query in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.