469,267 Members | 1,150 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,267 developers. It's quick & easy.

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

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
Jun 7 '07 #1
4 3997
On Thu, 07 Jun 2007 21:47:26 +0200, Konrad Hammerer
<ko*************@tesis.dewrote:

I'd say it's time to repair & compact this database.

Create a second query using this sql statement. Open the query. Open
the query from VBA. Should have the same number.

-Tom.
>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
Jun 8 '07 #2
On Thu, 07 Jun 2007 21:47:26 +0200, Konrad Hammerer
<ko*************@tesis.dewrote:

I'd say it's time to repair & compact this database.
Did this at least 10 times!
Create a second query using this sql statement. Open the query. Open
the query from VBA. Should have the same number.
Already did this! Same problem. Open the query in Access itself returns
2446, open it in VBA with "oRec2.Open "Abfrage1",
CurrentProject.Connection" returns 2425!

Using a DAO recordset return 2446, but the performance is so bad that I
cannot use this!

What else could be the problem?
-Tom.
>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
Jun 8 '07 #3
On Fri, 08 Jun 2007 21:30:20 +0200, Konrad Hammerer
<ko*************@tesis.dewrote:

Email me a zipped copy of your database, and some instructions.
-Tom.
>On Thu, 07 Jun 2007 21:47:26 +0200, Konrad Hammerer
<ko*************@tesis.dewrote:

I'd say it's time to repair & compact this database.

Did this at least 10 times!
>Create a second query using this sql statement. Open the query. Open
the query from VBA. Should have the same number.

Already did this! Same problem. Open the query in Access itself returns
2446, open it in VBA with "oRec2.Open "Abfrage1",
CurrentProject.Connection" returns 2425!

Using a DAO recordset return 2446, but the performance is so bad that I
cannot use this!

What else could be the problem?
>-Tom.
>>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
Jun 9 '07 #4
Hi Tom,
thanks for your help but I found the problem!
Jahrbuch_Einzelversand_Komplett is a UNION query wich opens 5 other
queries and one of them uses LIKE and "*" as wildcard. For ADO (in my
VBA code) the wildcard should by "%" and that is the problem ;-).

Thanks,
Konrad

Tom van Stiphout schrieb:
On Fri, 08 Jun 2007 21:30:20 +0200, Konrad Hammerer
<ko*************@tesis.dewrote:

Email me a zipped copy of your database, and some instructions.
-Tom.
>>On Thu, 07 Jun 2007 21:47:26 +0200, Konrad Hammerer
<ko*************@tesis.dewrote:

I'd say it's time to repair & compact this database.
Did this at least 10 times!
>>Create a second query using this sql statement. Open the query. Open
the query from VBA. Should have the same number.
Already did this! Same problem. Open the query in Access itself returns
2446, open it in VBA with "oRec2.Open "Abfrage1",
CurrentProject.Connection" returns 2425!

Using a DAO recordset return 2446, but the performance is so bad that I
cannot use this!

What else could be the problem?
>>-Tom.

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
Jun 10 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Bob Bedford | last post: by
3 posts views Thread by Hendry Taylor | last post: by
2 posts views Thread by shumaker | last post: by
3 posts views Thread by Tcs | last post: by
reply views Thread by Florian | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.