ADODB.Connection strange output!!

[Boon]

Hello,

I would appreciate if you would explain me what is the reason, or cause of
this....

I am using recordset in my vba code. I use ADODB.connection......

I am able to use this successfully, and I understand how it loop through the
records.

The problem I have is when I use this with one of my query. basically, I use
a code like < table.open "Query1" >

My output doesn't make sense based on the data in my Query1. It was like I
have 100 records in my Query1, but my output has 200 records. I was spending
hours to determine what's wrong and here is what I found out:

Fact: My Query1 is a select query based on several tables, and query. It is
not the most complicated one, but it is not a one step query from one table.

in my Query1 I have one criteria like this in field "NAME" --> not like
"B*". basically the Query1 selects everything with the name not starting
with B. when I open the query in Access, I see 100 records. my ADODB output
shows 200 records.

My solution: I adjusted the Query1 by not using "not like "B*". I use

left([NAME],1)

<>"B"

And this time the ADODB output shows 100 records which is correct.

Note that both queries how the same output in Access.

thanks,
Boon

 

[Paul Shapiro]

Hello,

I would appreciate if you would explain me what is the reason, or cause of
this....

I am using recordset in my vba code. I use ADODB.connection......

I am able to use this successfully, and I understand how it loop through
the records.

The problem I have is when I use this with one of my query. basically, I
use a code like < table.open "Query1" >

My output doesn't make sense based on the data in my Query1. It was like I
have 100 records in my Query1, but my output has 200 records. I was
spending hours to determine what's wrong and here is what I found out:

Fact: My Query1 is a select query based on several tables, and query. It
is not the most complicated one, but it is not a one step query from one
table.

in my Query1 I have one criteria like this in field "NAME" --> not like
"B*". basically the Query1 selects everything with the name not starting
with B. when I open the query in Access, I see 100 records. my ADODB
output shows 200 records.

My solution: I adjusted the Query1 by not using "not like "B*". I use

left([NAME],1)

<>"B"

And this time the ADODB output shows 100 records which is correct.

Note that both queries how the same output in Access.

Without verifying, I can think of two possibilities you might look into:
1. Null values might be included in the Not Like "B*" condition, but
excluded from the Left([Name], 1) <> "B". Treatment of nulls might be a
little different in ADODB vs. DAO which Access uses when you open the query
directly in Access rather than VBA.
2. I seem to remember ADODB uses the standard SQL wildcard match character,
so the condition should be Not Like "B%". If that's the case, then Not Like
"B*" would return all rows except those with a literal 'B*' at the beginning
of the name, which is probably all rows. You could use a DAO recordset
instead of ADODB if you wanted to keep the * wildcard.

 

[Boon]

Interesting issue on the wildcard as Paul suggested...

These are 2 versions of the Query1. Both display the exact same result in
Access. But the first one will work correctly in ADODB, but the second one
won't work correctly in ADODB.

1)
SELECT USA_Dealers_By_Nitch_Carrier.DLRCD,
USA_Dealers_By_Nitch_Carrier.NAME, USA_Dealers_By_Nitch_Carrier.CITY,
USA_Dealers_By_Nitch_Carrier.ST, USA_Dealers_By_Nitch_Carrier.ZIP,
USA_Dealers_By_Nitch_Carrier.Carrier, Carrier_Symbol.Symbol
FROM Carrier_Symbol INNER JOIN USA_Dealers_By_Nitch_Carrier ON
Carrier_Symbol.Carrier = USA_Dealers_By_Nitch_Carrier.Carrier
WHERE (((Left([DLRCD],1))<>"C"));

2)
SELECT USA_Dealers_By_Nitch_Carrier.DLRCD,
USA_Dealers_By_Nitch_Carrier.NAME, USA_Dealers_By_Nitch_Carrier.CITY,
USA_Dealers_By_Nitch_Carrier.ST, USA_Dealers_By_Nitch_Carrier.ZIP,
USA_Dealers_By_Nitch_Carrier.Carrier, Carrier_Symbol.Symbol
FROM Carrier_Symbol INNER JOIN USA_Dealers_By_Nitch_Carrier ON
Carrier_Symbol.Carrier = USA_Dealers_By_Nitch_Carrier.Carrier
WHERE (((USA_Dealers_By_Nitch_Carrier.DLRCD) Not Like "C*"));

What's wrong using "DoCmd.OpenQuery"?

You say that "Query1" are using table joins. That may be the case of
duplicate as the joints may may incorrect.

As for the wild card "B*", it should work even with DAO or ADODB. But "B%"
will only work with Oracle Database.

Why not show/post your SQL string here. Hopefully someone can spot the
error.

Hello,

I would appreciate if you would explain me what is the reason, or cause of
this....

I am using recordset in my vba code. I use ADODB.connection......

I am able to use this successfully, and I understand how it loop through
the
records.

The problem I have is when I use this with one of my query. basically, I
use
a code like < table.open "Query1" >

My output doesn't make sense based on the data in my Query1. It was like I
have 100 records in my Query1, but my output has 200 records. I was
spending
hours to determine what's wrong and here is what I found out:

Fact: My Query1 is a select query based on several tables, and query. It
is
not the most complicated one, but it is not a one step query from one
table.

in my Query1 I have one criteria like this in field "NAME" --> not like
"B*". basically the Query1 selects everything with the name not starting
with B. when I open the query in Access, I see 100 records. my ADODB
output
shows 200 records.

My solution: I adjusted the Query1 by not using "not like "B*". I use

left([NAME],1)

<>"B"

And this time the ADODB output shows 100 records which is correct.

Note that both queries how the same output in Access.

thanks,
Boon

 

[Paul Shapiro]

Further searching indicates that indeed ADODB supports only the % matching
character, and not *, while Access' query processor is the reverse. I did
find mention of an undocumented ALike operator which lets you use the % sign
in both queries and ADODB. So you could change
FROM: WHERE (USA_Dealers_By_Nitch_Carrier.DLRCD Not Like "C*")
TO: WHERE (Not USA_Dealers_By_Nitch_Carrier.DLRCD ALike "C%")
Or maybe this would work too:
WHERE (USA_Dealers_By_Nitch_Carrier.DLRCD Not ALike "C%")

I verified ALike works with the % match character in an Access 2007 query,
but did not test an ADODB recordset. The source I found suggested it would
work there as well, so it might be worth trying.

Interesting issue on the wildcard as Paul suggested...

These are 2 versions of the Query1. Both display the exact same result in
Access. But the first one will work correctly in ADODB, but the second one
won't work correctly in ADODB.

1)
SELECT USA_Dealers_By_Nitch_Carrier.DLRCD,
USA_Dealers_By_Nitch_Carrier.NAME, USA_Dealers_By_Nitch_Carrier.CITY,
USA_Dealers_By_Nitch_Carrier.ST, USA_Dealers_By_Nitch_Carrier.ZIP,
USA_Dealers_By_Nitch_Carrier.Carrier, Carrier_Symbol.Symbol
FROM Carrier_Symbol INNER JOIN USA_Dealers_By_Nitch_Carrier ON
Carrier_Symbol.Carrier = USA_Dealers_By_Nitch_Carrier.Carrier
WHERE (((Left([DLRCD],1))<>"C"));

2)
SELECT USA_Dealers_By_Nitch_Carrier.DLRCD,
USA_Dealers_By_Nitch_Carrier.NAME, USA_Dealers_By_Nitch_Carrier.CITY,
USA_Dealers_By_Nitch_Carrier.ST, USA_Dealers_By_Nitch_Carrier.ZIP,
USA_Dealers_By_Nitch_Carrier.Carrier, Carrier_Symbol.Symbol
FROM Carrier_Symbol INNER JOIN USA_Dealers_By_Nitch_Carrier ON
Carrier_Symbol.Carrier = USA_Dealers_By_Nitch_Carrier.Carrier
WHERE (((USA_Dealers_By_Nitch_Carrier.DLRCD) Not Like "C*"));

What's wrong using "DoCmd.OpenQuery"?

You say that "Query1" are using table joins. That may be the case of
duplicate as the joints may may incorrect.

As for the wild card "B*", it should work even with DAO or ADODB. But
"B%"
will only work with Oracle Database.

Why not show/post your SQL string here. Hopefully someone can spot the
error.

Hello,

I would appreciate if you would explain me what is the reason, or cause
of
this....

I am using recordset in my vba code. I use ADODB.connection......

I am able to use this successfully, and I understand how it loop through
the
records.

The problem I have is when I use this with one of my query. basically, I
use
a code like < table.open "Query1" >

My output doesn't make sense based on the data in my Query1. It was like
I
have 100 records in my Query1, but my output has 200 records. I was
spending
hours to determine what's wrong and here is what I found out:

Fact: My Query1 is a select query based on several tables, and query. It
is
not the most complicated one, but it is not a one step query from one
table.

in my Query1 I have one criteria like this in field "NAME" --> not like
"B*". basically the Query1 selects everything with the name not starting
with B. when I open the query in Access, I see 100 records. my ADODB
output
shows 200 records.

My solution: I adjusted the Query1 by not using "not like "B*". I use

left([NAME],1)

<>"B"

And this time the ADODB output shows 100 records which is correct.

Note that both queries how the same output in Access.

 

[Boon]

thanks so much!

Further searching indicates that indeed ADODB supports only the % matching
character, and not *, while Access' query processor is the reverse. I did
find mention of an undocumented ALike operator which lets you use the %
sign in both queries and ADODB. So you could change
FROM: WHERE (USA_Dealers_By_Nitch_Carrier.DLRCD Not Like "C*")
TO: WHERE (Not USA_Dealers_By_Nitch_Carrier.DLRCD ALike "C%")
Or maybe this would work too:
WHERE (USA_Dealers_By_Nitch_Carrier.DLRCD Not ALike "C%")

I verified ALike works with the % match character in an Access 2007 query,
but did not test an ADODB recordset. The source I found suggested it would
work there as well, so it might be worth trying.

Interesting issue on the wildcard as Paul suggested...

These are 2 versions of the Query1. Both display the exact same result in
Access. But the first one will work correctly in ADODB, but the second
one won't work correctly in ADODB.

1)
SELECT USA_Dealers_By_Nitch_Carrier.DLRCD,
USA_Dealers_By_Nitch_Carrier.NAME, USA_Dealers_By_Nitch_Carrier.CITY,
USA_Dealers_By_Nitch_Carrier.ST, USA_Dealers_By_Nitch_Carrier.ZIP,
USA_Dealers_By_Nitch_Carrier.Carrier, Carrier_Symbol.Symbol
FROM Carrier_Symbol INNER JOIN USA_Dealers_By_Nitch_Carrier ON
Carrier_Symbol.Carrier = USA_Dealers_By_Nitch_Carrier.Carrier
WHERE (((Left([DLRCD],1))<>"C"));

2)
SELECT USA_Dealers_By_Nitch_Carrier.DLRCD,
USA_Dealers_By_Nitch_Carrier.NAME, USA_Dealers_By_Nitch_Carrier.CITY,
USA_Dealers_By_Nitch_Carrier.ST, USA_Dealers_By_Nitch_Carrier.ZIP,
USA_Dealers_By_Nitch_Carrier.Carrier, Carrier_Symbol.Symbol
FROM Carrier_Symbol INNER JOIN USA_Dealers_By_Nitch_Carrier ON
Carrier_Symbol.Carrier = USA_Dealers_By_Nitch_Carrier.Carrier
WHERE (((USA_Dealers_By_Nitch_Carrier.DLRCD) Not Like "C*"));

What's wrong using "DoCmd.OpenQuery"?

You say that "Query1" are using table joins. That may be the case of
duplicate as the joints may may incorrect.

As for the wild card "B*", it should work even with DAO or ADODB. But
"B%"
will only work with Oracle Database.

Why not show/post your SQL string here. Hopefully someone can spot the
error.

Boon wrote:
Hello,

I would appreciate if you would explain me what is the reason, or cause
of
this....

I am using recordset in my vba code. I use ADODB.connection......

I am able to use this successfully, and I understand how it loop through
the
records.

The problem I have is when I use this with one of my query. basically, I
use
a code like < table.open "Query1" >

My output doesn't make sense based on the data in my Query1. It was like
I
have 100 records in my Query1, but my output has 200 records. I was
spending
hours to determine what's wrong and here is what I found out:

Fact: My Query1 is a select query based on several tables, and query. It
is
not the most complicated one, but it is not a one step query from one
table.

in my Query1 I have one criteria like this in field "NAME" --> not like
"B*". basically the Query1 selects everything with the name not starting
with B. when I open the query in Access, I see 100 records. my ADODB
output
shows 200 records.

My solution: I adjusted the Query1 by not using "not like "B*". I use

left([NAME],1)

<>"B"

And this time the ADODB output shows 100 records which is correct.

Note that both queries how the same output in Access.