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

[Tom van Stiphout]

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

[Konrad Hammerer]

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

[Tom van Stiphout]

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

[Konrad Hammerer]

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