PRB: Migrating SQL Statements with Quotes from DAO to ADO (181832)

SYMPTOMS

ActiveX Data Objects (ADO) imposes no restrictions on the syntax used to generate a recordset or execute a statement. This comes from the underlying native provider, such as the OLE DB provider for Index Server, or the OLE DB provider for ODBC drivers. Valid syntax required for using ADO successfully is defined by the underlying OLE DB provider. When migrating SQL syntax to ADO, you will need to consider this for SQL statements such as the following, which works when using DAO to go to the Microsoft Access Jet engine:

   INSERT INTO Authors ( Au_ID, Author ) VALUES (  54, "Record # 54" )

However, with ADO (to the OLE DB provider for ODBC drivers through the Microsoft Access ODBC driver) this syntax generates an error and fails to execute.

With ADO, this syntax generates an error, 0x80040E10 or -2147217904, or the following error message:

Too few parameters. Expected 1.

Instead, you have to use the following syntax:

   INSERT INTO Authors ( Au_ID, Author ) VALUES (  54, 'Record # 54' )

This is only one example and specific to DAO/Jet.

CAUSE

You should examine the Online help for the underlying provider and for the datastore it exposes in order to determine the correct syntax to provide your ADO code. As in the preceding case, even though DAO and the Microsoft Access ODBC Driver both use Jet, the syntax differs slightly in order for the Microsoft Access ODBC Driver to meet the ODBC specification.

MORE INFORMATION

Errors raised by the provider (or the datastore it exposes) are stored in the ADO Errors collection. Whereas errors raised by ADO are exposed by the native error handling of the language you are using. For Visual Basic for Applications (VBA) and/or VBScript this is the Err object. For C++ or Java this is typically some kind of exception, although with C++ you may just have to check for a failed HRESULT if manipulating ADO directly through COM.

REFERENCES

For additional information, please see the following article in the Microsoft Knowledge Base:

178070 HOWTO: Handle Quotes and Pipes in Concatenated SQL Literals

This article, while written for DAO, has handy routines for processing a string to be used in a SQL statement that contains special characters. It can easily be adapted for non-DAO syntax requirements.

For additional information on error handling, please see the following article in the Microsoft Knowledge Base: