Select query returns a different result in Access and Access-VBA

[Konrad Hammerer]

Hi!

I have the following problem:

I have a query (a) using another query (b) to get the amount of records
of this other query (b), means:

select count(MNR) as Number from Jahrbuch_Einzelversand_Komplett (while
Jahrbuch_Einzelversand_Komplett is a query itself)

The result of Number is 2446.

Running the same query within Access-VBA, means:

Dim oRec2 as New Recordset

oRec2.Open "select count(MNR) as Number from
Jahrbuch_Einzelversand_Komplett", CurrentProject.Connection,
adOpenForwardOnly, adLockReadOnly

returns 2425 as Number.

What is the difference?

Thanks,
Konrad

 

[Jeff Boyce]

Konrad

I don't know if this will make any difference, but you need to use something
other than "Number" -- I believe that is a reserved word in Access, so
Access may be using a different definition that what you intend.

Also, and again only possibilities, it is not explicit whether the Recordset
you create is an ADO recordset or a DAO recordset.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Konrad Hammerer]

Konrad

I don't know if this will make any difference, but you need to use something
other than "Number" -- I believe that is a reserved word in Access, so
Access may be using a different definition that what you intend.

I use something different but it is a German word so for the newsgroup I
chose "Number" ;-).

My Recordset ist ADO (at least, I think). If I use DAO and Set rs1 =
CurrentDb.OpenRecordset("select MNR from
Jahrbuch_Einzelversand_Komplett"), the result is 2446, just as the
access query itself. But the DAO recorset is so slow that it is not
usable in my case (I don't know why).

Thanks,
Konrad

 

[Konrad Hammerer]

Question:

With Reference should I use in my VBA Project? ADO in which version? How
should I declare the recordset and with what statement should I open the
recordset? Maybe there is some problem because as I already said: It
works with DAO (just the performance is so bad with DAO).

Thanks,
Konrad

 

[RoyVidar]

Hi!

I have the following problem:

I have a query (a) using another query (b) to get the amount of
records of this other query (b), means:

select count(MNR) as Number from Jahrbuch_Einzelversand_Komplett
(while Jahrbuch_Einzelversand_Komplett is a query itself)

The result of Number is 2446.

Running the same query within Access-VBA, means:

Dim oRec2 as New Recordset

oRec2.Open "select count(MNR) as Number from
Jahrbuch_Einzelversand_Komplett", CurrentProject.Connection,
adOpenForwardOnly, adLockReadOnly

returns 2425 as Number.

What is the difference?

Thanks,
Konrad

Are you by any chance using the LIKE operator and wildcard?

If so, remember that ADO uses % in stead of * and _ (underscore) in
stead of ?.

 

[Jeff Boyce]

Konrad

I think you would get more knowledgeable folks looking at your question if
you post it to the .modulesdaovba.ado or .modulesdaovba newsgroups.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Konrad Hammerer]

Are you by any chance using the LIKE operator and wildcard?

If so, remember that ADO uses % in stead of * and _ (underscore) in
stead of ?.

That's it! Thank you very much for this hint. Would have never thougt of
this ;-). The query "Jahrbuch_Einzelversand_Komplett" is a UNION query
which opens 5 other queries and one of them uses the LIKE operator and
the wildcard "*". When I remove this query from
"Jahrbuch_Einzelversand_Komplett", it returns 2425 entries, just as my
VBA code with ADO.

Question: How can I solve this? Can I somehow use ADO and open my
queries in VBA with the same result as access itself or not? As
mentioned, I tried DAO within my VBA code and it is so slow that I
cannot use it!

Thanks,
Konrad

 

[RoyVidar]

That's it! Thank you very much for this hint. Would have never thougt
of this ;-). The query "Jahrbuch_Einzelversand_Komplett" is a UNION
query which opens 5 other queries and one of them uses the LIKE
operator and the wildcard "*". When I remove this query from
"Jahrbuch_Einzelversand_Komplett", it returns 2425 entries, just as
my VBA code with ADO.

Question: How can I solve this? Can I somehow use ADO and open my
queries in VBA with the same result as access itself or not? As
mentioned, I tried DAO within my VBA code and it is so slow that I
cannot use it!

Thanks,
Konrad

Usually it is the other way around. Since DAO is native to Jet, it will
often perform slightly faster than ADO.

I haven't had this challenge in production, so you'll need to test and
verify, but I think you should be able to do the following

WHERE MyField Like "blah*" Or MyField Like "blah%"