problem with WHERE clause when using Microsoft.Jet.OLEDB.4.0

[JackChiou]

Hi everyone,

I am using ADO with Microsoft.Jet.OLEDB.4.0 to connect to the Access97
database. The problem is every time when I use WHERE clause with duplicated
numerical value, it will fail....

For Example:
RecordSet.Open("SELECT * FROM xxxxx WHERE yyyyy = 1",
m_pConnection,
adOpenStatic,
adLockOptimistic,
adCmdUnknown);
(if field yyyyy is Index Yes with Duplicates OK, it will catch error.)

The error message shows:
ADORecordset error, error code =80004005, code meaning = Unspecified error

It's only happen on Microsoft.Jet.OLEDB.4.0, If I use other provider like
MSDASQL or Microsoft.Jet.OLEDB.3.51 then it's working fine. I also had tried
to convert the database from Access97 to Access2000 and upgrade the ADO and
Microsoft.Jet.OLEDB.4.0 to the latest version but still the same. It feels
like Jet.OLEDB.4.0 doesn' like any duplicated numerical value???!!

I have been working on this for 2 weeks already but still no luck, getting
so frustrated now so please help me!! Thanks!!!

-Jack

 

[Van T. Dinh]

Access97 uses JET 3.5 engine so the correct version to use is
Microsoft.Jet.OLEDB.3.51, not the 4.0 version.

 

[Brendan Reynolds]

I'm not sure whether this has any direct bearing on your current problem or
not, but why use adCmdUnknown? The command is a SQL statement, so the
optimal choice for command type would adCmdText.

If that doesn't help, it might help if you told us the actual values of
'xxxxx' and 'yyyyy' - possibly one of them might be a reserved word?

Is 'yyyyy' a field name or an expression? Possibly there might be a data
type mis-match happening there - though admittedly I would expect a
different error message if that were the problem.

 

[David W. Fenton]

Access97 uses JET 3.5 engine so the correct version to use is
Microsoft.Jet.OLEDB.3.51, not the 4.0 version.

That makes no sense. The ADO version for Jet 4 is backward
compatible and can read and write older Jet formats (unless they are
replicated).

Now, if you're working in A97 and you want to use ADO, I don't know
what the situation is, but that's a nonsensical way to get to Jet
data in any version of Access, so I assumed that the host app is
*not* Access.