PRB: Method 'Open' of Object '_Recordset' Fails with Jet OLEDB Provider

• Microsoft OLE DB Provider for Jet, version 4.0

SYMPTOMS

When using the Jet OLEDB provider version 4.0, an application might fail with the following error message:

Run-Time Error -2147467259 (80004005) Method 'Open' of Object '_Recordset' Failed.

CAUSE

In Jet 4.0 there is a new property called ExtendedAnsiSQL. The Jet OLEDB Provider version 4.0 turns on this new ANSI flag to enable new SQL syntax. Because the ExtendedAnsiSQL flag is turned on, Jet 4.0 uses the reserved words list defined by the SQL-92 standard. If the user tries to use a SQL-92 reserved word as an unquoted object name, an error is returned.

RESOLUTION

To work around this problem, try one of the following:

• Change the name of the field in the database so that it does not use one of the reserved words define by the SQL-92 standard.



• Place square brackets ([]) around the reserved word in the query. This allows the query to be executed even though the reserved word is in use. For example, "SELECT Usage From Table1" would become "SELECT [Usage] From Table1."

STATUS

This behavior is by design.

MORE INFORMATION

Below is a list of all the reserved words that Jet now enforces when this ANSI flag is turned on:

ABSOLUTE | ACTION | ADD | ALL | ALLOCATE | ALTER | AND | ANY | ARE | AS | ASC | ASSERTION | AT | AUTHORIZATION | AVG | BEGIN | BETWEEN | BIT | BIT_LENGTH | BOTH | BY | CASCADE | CASCADED | CASE | CAST | CATALOG | CHAR | CHARACTER | CHAR_ LENGTH | CHARACTER_LENGTH | CHECK | CLOSE | COALESCE | COLLATE | COLLATION | COLUMN | COMMIT | CONNECT | CONNECTION | CONSTRAINT | CONSTRAINTS | CONTINUE | CONVERT | CORRESPONDING | COUNT | CREATE | CROSS | | CURRENT || CURRENT_DATE | CURRENT_TIME | CURRENT_TIMESTAMP | CURRENT_ | USER | CURSOR | DATE | DAY | DEALLOCATE | DEC | DECIMAL | DECLARE | DEFAULT | DEFERRABLE | DEFERRED | DELETE | DESC | DESCRIBE | DESCRIPTOR | DIAGNOSTICS | DISCONNECT | DISTINCT | DOMAIN | DOUBLE | DROP | ELSE | END | END-EXEC | ESCAPE | EXCEPT | EXCEPTION | EXEC | EXECUTE | EXISTS | EXTERNAL | EXTRACT | FALSE | FETCH | FIRST | FLOAT | FOR | FOREIGN | FOUND | FROM | FULL | GET | GLOBAL | GO | GOTO | GRANT | GROUP | HAVING | HOUR | IDENTITY | IMMEDIATE | IN | INDICATOR | INITIALLY | INNER | INPUT | INSENSITIVE | INSERT | INT | INTEGER | INTERSECT | INTERVAL | INTO | IS | ISOLATION | JOIN | KEY | LANGUAGE | LAST | LEADING | LEFT | LEVEL | LIKE | LOCAL | LOWER | MATCH | MAX | MIN | MINUTE | MODULE | MONTH | NAMES | NATIONAL | NATURAL | NCHAR | NEXT | NO | NOT | NULL | NULLIF | NUMERIC | OCTET_LENGTH | OF | ON | ONLY | OPEN | OPTION | OR | ORDER | OUTER | OUTPUT | OVERLAPS | PARTIAL | POSITION | PRECISION | PREPARE | PRESERVE | PRIMARY | PRIOR | PRIVILEGES | PROCEDURE | PUBLIC | READ | REAL | REFERENCES | RELATIVE | RESTRICT | REVOKE | RIGHT | ROLLBACK | ROWS | SCHEMA | SCROLL | SECOND | SECTION | SELECT | SESSION | SESSION_ USER | SET | SIZE | SMALLINT | SOME | SQL | SQLCODE | SQLERROR | SQLSTATE | SUBSTRING | SUM | SYSTEM_USER | TABLE | TEMPORARY | THEN | TIME | TIMESTAMP | TIMEZONE_ HOUR | TIMEZONE_MINUTE | TO | TRAILING | TRANSACTION | TRANSLATE | TRANSLATION | TRIM | TRUE | UNION | UNIQUE | UNKNOWN | UPDATE | UPPER | USAGE | USER | USING | VALUE | VALUES | VARCHAR | VARYING | VIEW | WHEN | WHENEVER | WHERE | WITH | WORK | WRITE | YEAR | ZONE

Additional query words:

Keywords : kbADO kbDatabase kbOLEDB kbGrpVBDB kbGrpMDAC kbDSupport
Version : WINDOWS:4.0
Platform : WINDOWS
Issue type : kbprb