Microsoft KB Archive/181832

= PRB: Migrating SQL Statements with Quotes from DAO to ADO =

Article ID: 181832

Article Last Modified on 3/14/2005

-

APPLIES TO


 * Microsoft ActiveX Data Objects 1.0
 * Microsoft ActiveX Data Objects 1.5
 * Microsoft ActiveX Data Objects 2.0
 * Microsoft ActiveX Data Objects 2.1 Service Pack 2
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft ActiveX Data Objects 2.6
 * Microsoft ActiveX Data Objects 2.7

-



This article was previously published under Q181832



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.



STATUS
This behavior is by design.



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.

