Jet 3.51 to Jet 4.0

[graham]

Hi guys,

Anyone have any ideas why this statement would execute on an old Access97
database using Jet 3.51 but not when I use Jet 4.0?? - I have to interface
with a 3rd-party db which for whatever reason runs in the old format, and I
WAS using the 3.51 driver but realized that deployment may be a problem in
that 3.51 hasn;t been available since MDAC 2.0.

SELECT [section].[year], [section].farm_id, [section].field_id,
[section].section_id, [section].section_name, [section].section_description,
[section].section_location, [field].field_name, ([field].field_name + ' - '
+ [section].section_name) AS field_section_name FROM section, field WHERE
[section].farm_id = [field].farm_id AND [section].[year] = [field].[year]
AND [section].field_id = [field].field_id AND ([section].[year] = ?)

I added the [] to ensure I wasn't treading on any reserved words.

The original commandtext was in the form of INNER JOIN - I changed this,
thinking it may be the cause - but no joy.

If anyone can shed some light I'd appreciate it.
Even if I could interrogate the error a little might give me some clues...

The error I receive is "An unhandled exception of type
'System.Data.OleDb.OleDbException' occurred in system.data.dll"
The exception message is;
IErrorInfo.GetDescription failed with E_FAIL(0x80004005)
Thanks,
Graham

[a]

FROM section, field WHERE

maybe the use of the word 'field' in the FROM. Try escaping with [] here
also. I would try the query in bits and pieces to see when the error comes
in.

SELECT [field].field_name FROM [field]

then

SELECT [field].field_name, ([field].field_name + ' - ' +
[section].section_name) AS field_section_name FROM [field]

Kevin

"graham" <gr*************@rogers.com> wrote in message
news:Iu********************@twister01.bloor.is.net .cable.rogers.com...

Hi guys,

Anyone have any ideas why this statement would execute on an old Access97
database using Jet 3.51 but not when I use Jet 4.0?? - I have to interface
with a 3rd-party db which for whatever reason runs in the old format, and I WAS using the 3.51 driver but realized that deployment may be a problem in that 3.51 hasn;t been available since MDAC 2.0.

SELECT [section].[year], [section].farm_id, [section].field_id,
[section].section_id, [section].section_name, [section].section_description, [section].section_location, [field].field_name, ([field].field_name + ' - ' + [section].section_name) AS field_section_name FROM section, field WHERE
[section].farm_id = [field].farm_id AND [section].[year] = [field].[year]
AND [section].field_id = [field].field_id AND ([section].[year] = ?)

I added the [] to ensure I wasn't treading on any reserved words.

The original commandtext was in the form of INNER JOIN - I changed this,
thinking it may be the cause - but no joy.

If anyone can shed some light I'd appreciate it.
Even if I could interrogate the error a little might give me some clues...

The error I receive is "An unhandled exception of type
'System.Data.OleDb.OleDbException' occurred in system.data.dll"
The exception message is;
IErrorInfo.GetDescription failed with E_FAIL(0x80004005)
Thanks,
Graham

[graham]

Thank you Kevin! Yep, looks like either (or both) 'section' or 'field' are
reserved words in 4.0 but not in 3.51.

Thanks again.
Graham
"a" <[e-mail]> wrote in message
news:%2******************@TK2MSFTNGP09.phx.gbl...

FROM section, field WHERE

maybe the use of the word 'field' in the FROM. Try escaping with [] here
also. I would try the query in bits and pieces to see when the error comes in.

SELECT [field].field_name FROM [field]

then

SELECT [field].field_name, ([field].field_name + ' - ' +
[section].section_name) AS field_section_name FROM [field]

Kevin

"graham" <gr*************@rogers.com> wrote in message
news:Iu********************@twister01.bloor.is.net .cable.rogers.com...

Hi guys,

Anyone have any ideas why this statement would execute on an old Access97 database using Jet 3.51 but not when I use Jet 4.0?? - I have to interface with a 3rd-party db which for whatever reason runs in the old format, and

I

WAS using the 3.51 driver but realized that deployment may be a problem in

that 3.51 hasn;t been available since MDAC 2.0.

SELECT [section].[year], [section].farm_id, [section].field_id,
[section].section_id, [section].section_name,

[section].section_description,

[section].section_location, [field].field_name, ([field].field_name +

' - '

+ [section].section_name) AS field_section_name FROM section, field

WHERE [section].farm_id = [field].farm_id AND [section].[year] = [field].[year] AND [section].field_id = [field].field_id AND ([section].[year] = ?)

I added the [] to ensure I wasn't treading on any reserved words.

The original commandtext was in the form of INNER JOIN - I changed this,
thinking it may be the cause - but no joy.

If anyone can shed some light I'd appreciate it.
Even if I could interrogate the error a little might give me some clues...
The error I receive is "An unhandled exception of type
'System.Data.OleDb.OleDbException' occurred in system.data.dll"
The exception message is;
IErrorInfo.GetDescription failed with E_FAIL(0x80004005)
Thanks,
Graham