Possible Jet 4.0 / ADO Bug

[lachie]

I'm not sure if this is the correct place to be posting this - apologies if it is.

I've recently been given the task of porting our Access 97 database so that it uses SQL Server. I'm in the process of getting all of our data out of databases (in 97 format) and replicating this data in SQL Server 7.0 / 2000 using ADO 2.6 and ADOX.

Because I need to relink the tables in Access to point to the SQL Server and have to be able to recognise identity columns in the database I've decided to use the Jet 4 provider.

The problem I face is that everything works peachy until I attempt to upload a table called "Names". I attempt to open a recordset (SELECT * FROM Names) to retrieve the table schema and I get returned an error saying method open of recordset failed. (The ADO connection (To the Jet database) error log states a SQLState of 3000 - thanks alot, helpcontext of 5003251 and a native error of -533136361) - none of which I've been able to find help for.

If I rename the table - works like a charm.

If I use the jet 3.51 provider - works like a charm.

Any thoughts guys and girls?

 

[tlbroadbent]

The clue here is that the query runs if you change the name of the table. "Name" is an ANSI-92 reserved word. I found the following in Microsoft's Knowledgebase.[ul]Problem
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. The Microsoft Jet 4.0 OLEDB Provider relies on this setting and it cannot be disabled.[/ul]Source:

http://support.microsoft.com/support/kb/articles/Q238/2/43.ASP

Terry

http://members.home.net/tlbroadbent/prog.htm

The reason why worry kills more people than work is that more people worry than work. - Robert Frost

 

[lachie]

Thanks heaps..

I looked through my SQL Server books and my access books and found no mention of "Names" as a reserved word.

Thanks heaps.

L