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

G

Guest

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
 
V

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.
 
B

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.
 
D

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.
Click to expand...

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.
 
J

Jamie Collins

David said:
That makes no sense. The ADO version for Jet 4 is backward
Click to expand...

Yours makes no sense: there is no 'ADO version for Jet 4'. The OP is
referring to the MS OLE DB provider for Jet 4.0.
compatible and can read and write older Jet formats (unless they are
replicated).
Click to expand...

Even if you mean the MS OLE DB provider for Jet 4.0, you would have to
assume it is bug free (I have evidence to suggest it is not) for your
statement to be correct. It is entirely possible the OP has discovered
a bug.
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
Click to expand...

You seem to be saying that using ADO in an Access MDB backend solution
(as distinct from a solution that uses a Jet MDB but not Access) is
'nonsense'. I think most people would disagree with that.
I assumed that the host app is
*not* Access.
Click to expand...

So what? There are no 'Jet' newsgroups, so it is entirely appropriate
to ask questions about Jet, including OLE DB providers, in the Access
groups even when Access is not being used. Most people use 'Access when
they more correctly mean 'Jet'.

Jamie.

--
 
Top