By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,810 Members | 818 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,810 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Share this Question
Share on Google+
4 Replies


P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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.