How many ORs can you have in ADODB

11 Oct 2005 9:50 PM

Jerry Spence1

I have an ADODB connection to an Access Database. I have the following
command:

SQL = "SELECT * from <table> WHERE ((myfield1 like '%red&') or (myfield2
like '%red&') or (myfield3 like '%red&') or (myfield4 like '%red&') or
(myfield5 like '%red&') or (myfield6 like '%red&') or etc... etc..

set rs1 = cn.open (SQL)

I have 50 fields ands am getting the error "Method 'Open' of '_Recordset'
failed and I think it is because I have too many OR statements. It works OK
on MySQL Database. Is there a maximum number you can have?

Is there a better way of doing a search throughout all fields?

-Jerry

11 Oct 2005 10:06 PM

Bob Butler

"Jerry Spence1" <jerry.spe***@somewhere.com> wrote in message
news:434c338d$0$73612$ed2619ec@ptn-nntp-reader03.plus.net
> I have an ADODB connection to an Access Database. I have the following
> command:
>
> SQL = "SELECT * from <table> WHERE ((myfield1 like '%red&') or
> (myfield2 like '%red&') or (myfield3 like '%red&') or (myfield4 like
> '%red&') or (myfield5 like '%red&') or (myfield6 like '%red&') or
> etc... etc..
>
> set rs1 = cn.open (SQL)

do you mean .Execute?

> I have 50 fields ands am getting the error "Method 'Open' of
> '_Recordset' failed and I think it is because I have too many OR
> statements. It works OK on MySQL Database. Is there a maximum number
> you can have?

Should be easy enough to test; stop it at that line and use the immediate
window to remove some of them and try it. Either that or copy the query
into Access and run it from there. You should be able to determine the max,
if there is one, pretty quickly. My guess is that you have some other
problem.

--
Reply to the group so all can participate
VB.Net: "Fool me once..."

11 Oct 2005 10:54 PM

Jerry Spence1

Show quote Hide quote

"Bob Butler" <tiredofit@nospam.com> wrote in message
news:%23o7JHArzFHA.2072@TK2MSFTNGP14.phx.gbl...
> "Jerry Spence1" <jerry.spe***@somewhere.com> wrote in message
> news:434c338d$0$73612$ed2619ec@ptn-nntp-reader03.plus.net
>> I have an ADODB connection to an Access Database. I have the following
>> command:
>>
>> SQL = "SELECT * from <table> WHERE ((myfield1 like '%red&') or
>> (myfield2 like '%red&') or (myfield3 like '%red&') or (myfield4 like
>> '%red&') or (myfield5 like '%red&') or (myfield6 like '%red&') or
>> etc... etc..
>>
>> set rs1 = cn.open (SQL)
>
> do you mean .Execute?
>
>> I have 50 fields ands am getting the error "Method 'Open' of
>> '_Recordset' failed and I think it is because I have too many OR
>> statements. It works OK on MySQL Database. Is there a maximum number
>> you can have?
>
> Should be easy enough to test; stop it at that line and use the immediate
> window to remove some of them and try it. Either that or copy the query
> into Access and run it from there. You should be able to determine the
> max,
> if there is one, pretty quickly. My guess is that you have some other
> problem.
>
> --
> Reply to the group so all can participate
> VB.Net: "Fool me once..."
>

Yep - done that. Strangly (or not) it seems to be 15, I just wanted
confirmation of that.

-Jerry

11 Oct 2005 11:15 PM

Bob Butler

"Jerry Spence1" <jerry.spe***@somewhere.com> wrote in message
news:434c42ca$0$29112$ed2619ec@ptn-nntp-reader01.plus.net
> Yep - done that. Strangly (or not) it seems to be 15, I just wanted
> confirmation of that.

I don't think so; are you sure you don't have field # 16 in your query
spelled wrong or have a missing quote or something like that? I was able to
run the following as a test without error using Access 2000 and both ADO 2.5
and 2.7:

Dim db As ADODB.Connection
Dim rs As ADODB.Recordset
Dim s As String
s = "select * from test1 where (f1 like '%red&') " & _
"or (f2 like '%red&') or (f3 like '%red&') " & _
"or (f4 like '%red&') or (f5 like '%red&') " & _
"or (f6 like '%red&') or (f7 like '%red&') " & _
"or (f8 like '%red&') or (f9 like '%red&') " & _
"or (f10 like '%red&') or (f11 like '%red&') " & _
"or (f12 like '%red&') or (f13 like '%red&') " & _
"or (f14 like '%red&') or (f15 like '%red&') " & _
"or (f16 like '%red&') or (f17 like '%red&') " & _
"or (f18 like '%red&') or (f19 like '%red&') " & _
"or (f20 like '%red&')"
Set db = New Connection
db.Open "<connect string to access database>"
Set rs = db.Execute(s)
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

--
Reply to the group so all can participate
VB.Net: "Fool me once..."

12 Oct 2005 8:31 AM

Jan Hyde

"Jerry Spence1" <jerry.spe***@somewhere.com>'s wild thoughts

were released on Tue, 11 Oct 2005 22:50:02 +0100 bearing the
following fruit:

Show quoteHide quote

>I have an ADODB connection to an Access Database. I have the following
>command:
>
>SQL = "SELECT * from <table> WHERE ((myfield1 like '%red&') or (myfield2
>like '%red&') or (myfield3 like '%red&') or (myfield4 like '%red&') or
>(myfield5 like '%red&') or (myfield6 like '%red&') or etc... etc..
>
>set rs1 = cn.open (SQL)
>
>I have 50 fields ands am getting the error "Method 'Open' of '_Recordset'
>failed and I think it is because I have too many OR statements. It works OK
>on MySQL Database. Is there a maximum number you can have?
>
>Is there a better way of doing a search throughout all fields?
>

Do you still get the error if you open the recordset this
way

Set rs1 = new adodb.recordset
rs1.open SQL,cn

I'm certain it has nothing to do with the number of OR
statements, I've fired off some pretty hefty SQL's with VB
and ADO without issue.

Also check you the connection is correctly set.

Jan Hyde (VB MVP)

--
The unexamined life may not be worth living,
but the life too closely examined may not be lived at all.

[Abolish the TV Licence - http://www.tvlicensing.biz/]