DAO Sql statement doesnt work in an ADO environment 
Author Message
 DAO Sql statement doesnt work in an ADO environment

The following SQL statement worked in a DAO environment with no problems.
It has no been ported to ADO and fails...Any ideas on what is causing it to
fail????

 sqlStringBom = "SELECT * " _
        & " FROM [tblDrawings]" _
        & " WHERE (size      LIKE '" & "%" & strFanSize & "%" & "' OR " _
        & "        size         = 'a') AND " _
        & "       type      LIKE '" & "%" & sWheelType & "%" & "' AND " _
        & "    Fantype      LIKE '" & "%" & sFanType & "%" & "' AND " _
        & " [single/double] LIKE '" & "%" & strWidth & "%" & "' AND " _
        & " arrangement     LIKE '" & "%" & strArr & "%" & "' AND " _
        & " (rotation       LIKE '" & strRotation & "%" & "' OR " _
        & " rotation        = 'a') AND " _
        & " (BrgLife        Is Null OR BrgLife LIKE '" & "%" & strBearing &
"%" & "') AND " _
        & " (MotorSize      Is Null OR MotorSize LIKE '" & "%" & strMotor &
"%" & "') AND " _
        & " (option2        Is Null OR option2 LIKE '" & "%" & strOption2 &
"%" & "') AND " _
        & " (option         Is Null OR option LIKE '" & "%" & strOption1 &
"%" & "') AND " _
        & " (discharge      LIKE '" & "%" & strDischarge & "%" & "' OR " _
        & " discharge       = 'a') AND " _
        & " DrawingObsolete = False AND " _
        & " [class/des]     LIKE '" & "%" & strClass & "%" & "' " _
        & " ORDER BY family"

Any ideas or help would be greatly appreciated.

Chris



Sun, 18 Apr 2004 02:35:21 GMT
 DAO Sql statement doesnt work in an ADO environment

Hi,

What kind of error do you get?

Val

Quote:
>-----Original Message-----
>The following SQL statement worked in a DAO environment
with no problems.
>It has no been ported to ADO and fails...Any ideas on

what is causing it to
Quote:
>fail????

> sqlStringBom = "SELECT * " _
>        & " FROM [tblDrawings]" _
>        & " WHERE (size      LIKE '" & "%" & strFanSize
& "%" & "' OR " _
>        & "        size         = 'a') AND " _
>        & "       type      LIKE '" & "%" & sWheelType
& "%" & "' AND " _
>        & "    Fantype      LIKE '" & "%" & sFanType
& "%" & "' AND " _
>        & " [single/double] LIKE '" & "%" & strWidth
& "%" & "' AND " _
>        & " arrangement     LIKE '" & "%" & strArr & "%"
& "' AND " _
>        & " (rotation       LIKE '" & strRotation & "%"
& "' OR " _
>        & " rotation        = 'a') AND " _
>        & " (BrgLife        Is Null OR BrgLife LIKE '"

& "%" & strBearing &
Quote:
>"%" & "') AND " _
>        & " (MotorSize      Is Null OR MotorSize LIKE '"
& "%" & strMotor &
>"%" & "') AND " _
>        & " (option2        Is Null OR option2 LIKE '"

& "%" & strOption2 &
Quote:
>"%" & "') AND " _
>        & " (option         Is Null OR option LIKE '"

& "%" & strOption1 &

- Show quoted text -

Quote:
>"%" & "') AND " _
>        & " (discharge      LIKE '" & "%" & strDischarge
& "%" & "' OR " _
>        & " discharge       = 'a') AND " _
>        & " DrawingObsolete = False AND " _
>        & " [class/des]     LIKE '" & "%" & strClass
& "%" & "' " _
>        & " ORDER BY family"

>Any ideas or help would be greatly appreciated.

>Chris

>.



Sun, 18 Apr 2004 02:52:44 GMT
 DAO Sql statement doesnt work in an ADO environment
the problem is that the DAO wildcard character is "%",
while for ADO, it is "*".  Change ALL of the "%"
characters in the SQL to "*", and your problems SHOULD
evaporate(notice I said "SHOULD") - remember, consistency
is the hobgoblin of feeble minds.

Arthur Wood

Quote:
>-----Original Message-----
>The following SQL statement worked in a DAO environment
with no problems.
>It has no been ported to ADO and fails...Any ideas on

what is causing it to
Quote:
>fail????

> sqlStringBom = "SELECT * " _
>        & " FROM [tblDrawings]" _
>        & " WHERE (size      LIKE '" & "%" & strFanSize
& "%" & "' OR " _
>        & "        size         = 'a') AND " _
>        & "       type      LIKE '" & "%" & sWheelType
& "%" & "' AND " _
>        & "    Fantype      LIKE '" & "%" & sFanType
& "%" & "' AND " _
>        & " [single/double] LIKE '" & "%" & strWidth
& "%" & "' AND " _
>        & " arrangement     LIKE '" & "%" & strArr & "%"
& "' AND " _
>        & " (rotation       LIKE '" & strRotation & "%"
& "' OR " _
>        & " rotation        = 'a') AND " _
>        & " (BrgLife        Is Null OR BrgLife LIKE '"

& "%" & strBearing &
Quote:
>"%" & "') AND " _
>        & " (MotorSize      Is Null OR MotorSize LIKE '"
& "%" & strMotor &
>"%" & "') AND " _
>        & " (option2        Is Null OR option2 LIKE '"

& "%" & strOption2 &
Quote:
>"%" & "') AND " _
>        & " (option         Is Null OR option LIKE '"

& "%" & strOption1 &

- Show quoted text -

Quote:
>"%" & "') AND " _
>        & " (discharge      LIKE '" & "%" & strDischarge
& "%" & "' OR " _
>        & " discharge       = 'a') AND " _
>        & " DrawingObsolete = False AND " _
>        & " [class/des]     LIKE '" & "%" & strClass
& "%" & "' " _
>        & " ORDER BY family"

>Any ideas or help would be greatly appreciated.

>Chris

>.



Sun, 18 Apr 2004 02:52:47 GMT
 DAO Sql statement doesnt work in an ADO environment
Its opposite ADO requires % and DAO requires *....


Quote:
> the problem is that the DAO wildcard character is "%",
> while for ADO, it is "*".  Change ALL of the "%"
> characters in the SQL to "*", and your problems SHOULD
> evaporate(notice I said "SHOULD") - remember, consistency
> is the hobgoblin of feeble minds.

> Arthur Wood

> >-----Original Message-----
> >The following SQL statement worked in a DAO environment
> with no problems.
> >It has no been ported to ADO and fails...Any ideas on
> what is causing it to
> >fail????

> > sqlStringBom = "SELECT * " _
> >        & " FROM [tblDrawings]" _
> >        & " WHERE (size      LIKE '" & "%" & strFanSize
> & "%" & "' OR " _
> >        & "        size         = 'a') AND " _
> >        & "       type      LIKE '" & "%" & sWheelType
> & "%" & "' AND " _
> >        & "    Fantype      LIKE '" & "%" & sFanType
> & "%" & "' AND " _
> >        & " [single/double] LIKE '" & "%" & strWidth
> & "%" & "' AND " _
> >        & " arrangement     LIKE '" & "%" & strArr & "%"
> & "' AND " _
> >        & " (rotation       LIKE '" & strRotation & "%"
> & "' OR " _
> >        & " rotation        = 'a') AND " _
> >        & " (BrgLife        Is Null OR BrgLife LIKE '"
> & "%" & strBearing &
> >"%" & "') AND " _
> >        & " (MotorSize      Is Null OR MotorSize LIKE '"
> & "%" & strMotor &
> >"%" & "') AND " _
> >        & " (option2        Is Null OR option2 LIKE '"
> & "%" & strOption2 &
> >"%" & "') AND " _
> >        & " (option         Is Null OR option LIKE '"
> & "%" & strOption1 &
> >"%" & "') AND " _
> >        & " (discharge      LIKE '" & "%" & strDischarge
> & "%" & "' OR " _
> >        & " discharge       = 'a') AND " _
> >        & " DrawingObsolete = False AND " _
> >        & " [class/des]     LIKE '" & "%" & strClass
> & "%" & "' " _
> >        & " ORDER BY family"

> >Any ideas or help would be greatly appreciated.

> >Chris

> >.



Sun, 18 Apr 2004 03:06:13 GMT
 DAO Sql statement doesnt work in an ADO environment
Method of Open recordset object failed..

Fails on the SQL statement

Chris

Quote:
> Hi,

> What kind of error do you get?

> Val

> >-----Original Message-----
> >The following SQL statement worked in a DAO environment
> with no problems.
> >It has no been ported to ADO and fails...Any ideas on
> what is causing it to
> >fail????

> > sqlStringBom = "SELECT * " _
> >        & " FROM [tblDrawings]" _
> >        & " WHERE (size      LIKE '" & "%" & strFanSize
> & "%" & "' OR " _
> >        & "        size         = 'a') AND " _
> >        & "       type      LIKE '" & "%" & sWheelType
> & "%" & "' AND " _
> >        & "    Fantype      LIKE '" & "%" & sFanType
> & "%" & "' AND " _
> >        & " [single/double] LIKE '" & "%" & strWidth
> & "%" & "' AND " _
> >        & " arrangement     LIKE '" & "%" & strArr & "%"
> & "' AND " _
> >        & " (rotation       LIKE '" & strRotation & "%"
> & "' OR " _
> >        & " rotation        = 'a') AND " _
> >        & " (BrgLife        Is Null OR BrgLife LIKE '"
> & "%" & strBearing &
> >"%" & "') AND " _
> >        & " (MotorSize      Is Null OR MotorSize LIKE '"
> & "%" & strMotor &
> >"%" & "') AND " _
> >        & " (option2        Is Null OR option2 LIKE '"
> & "%" & strOption2 &
> >"%" & "') AND " _
> >        & " (option         Is Null OR option LIKE '"
> & "%" & strOption1 &
> >"%" & "') AND " _
> >        & " (discharge      LIKE '" & "%" & strDischarge
> & "%" & "' OR " _
> >        & " discharge       = 'a') AND " _
> >        & " DrawingObsolete = False AND " _
> >        & " [class/des]     LIKE '" & "%" & strClass
> & "%" & "' " _
> >        & " ORDER BY family"

> >Any ideas or help would be greatly appreciated.

> >Chris

> >.



Sun, 18 Apr 2004 03:05:46 GMT
 DAO Sql statement doesnt work in an ADO environment
When I saw this post, I immediately thought of wildcards.  DAO
will take * in a LIKE expression to mean zero or more
characters.   ADO, on the other hand, uses % for that function.
(It doesn't seem to matter what ADO is running against, it will
translate to that target.  This makes sense, but it's a little
weird that you would specify % for an access database, when on
some level it's probably translating it back to *.)

Looking at your SQL though, it seems you should have the problem
going the other way--from ADO to DAO, not from DAO to ADO as
your purport doing?


Quote:
> The following SQL statement worked in a DAO environment with
no problems.
> It has no been ported to ADO and fails...Any ideas on what is
causing it to
> fail????

>  sqlStringBom = "SELECT * " _
>         & " FROM [tblDrawings]" _
>         & " WHERE (size      LIKE '" & "%" & strFanSize & "%"
& "' OR " _
>         & "        size         = 'a') AND " _
>         & "       type      LIKE '" & "%" & sWheelType & "%" &
"' AND " _
>         & "    Fantype      LIKE '" & "%" & sFanType & "%" &
"' AND " _
>         & " [single/double] LIKE '" & "%" & strWidth & "%" &
"' AND " _
>         & " arrangement     LIKE '" & "%" & strArr & "%" & "'
AND " _
>         & " (rotation       LIKE '" & strRotation & "%" & "'
OR " _
>         & " rotation        = 'a') AND " _
>         & " (BrgLife        Is Null OR BrgLife LIKE '" & "%" &
strBearing &
> "%" & "') AND " _
>         & " (MotorSize      Is Null OR MotorSize LIKE '" & "%"
& strMotor &
> "%" & "') AND " _
>         & " (option2        Is Null OR option2 LIKE '" & "%" &
strOption2 &
> "%" & "') AND " _
>         & " (option         Is Null OR option LIKE '" & "%" &
strOption1 &
> "%" & "') AND " _
>         & " (discharge      LIKE '" & "%" & strDischarge & "%"
& "' OR " _
>         & " discharge       = 'a') AND " _
>         & " DrawingObsolete = False AND " _
>         & " [class/des]     LIKE '" & "%" & strClass & "%" &
"' " _
>         & " ORDER BY family"

> Any ideas or help would be greatly appreciated.

> Chris



Sun, 18 Apr 2004 03:12:49 GMT
 DAO Sql statement doesnt work in an ADO environment
Hi

I mean what particular error message do you get?

Thanks,

Val

Quote:
>-----Original Message-----
>Method of Open recordset object failed..

>Fails on the SQL statement

>Chris


>> Hi,

>> What kind of error do you get?

>> Val

>> >-----Original Message-----
>> >The following SQL statement worked in a DAO environment
>> with no problems.
>> >It has no been ported to ADO and fails...Any ideas on
>> what is causing it to
>> >fail????

>> > sqlStringBom = "SELECT * " _
>> >        & " FROM [tblDrawings]" _
>> >        & " WHERE (size      LIKE '" & "%" & strFanSize
>> & "%" & "' OR " _
>> >        & "        size         = 'a') AND " _
>> >        & "       type      LIKE '" & "%" & sWheelType
>> & "%" & "' AND " _
>> >        & "    Fantype      LIKE '" & "%" & sFanType
>> & "%" & "' AND " _
>> >        & " [single/double] LIKE '" & "%" & strWidth
>> & "%" & "' AND " _
>> >        & " arrangement     LIKE '" & "%" & strArr
& "%"
>> & "' AND " _
>> >        & " (rotation       LIKE '" & strRotation & "%"
>> & "' OR " _
>> >        & " rotation        = 'a') AND " _
>> >        & " (BrgLife        Is Null OR BrgLife LIKE '"
>> & "%" & strBearing &
>> >"%" & "') AND " _
>> >        & " (MotorSize      Is Null OR MotorSize
LIKE '"
>> & "%" & strMotor &
>> >"%" & "') AND " _
>> >        & " (option2        Is Null OR option2 LIKE '"
>> & "%" & strOption2 &
>> >"%" & "') AND " _
>> >        & " (option         Is Null OR option LIKE '"
>> & "%" & strOption1 &
>> >"%" & "') AND " _
>> >        & " (discharge      LIKE '" & "%" &
strDischarge
>> & "%" & "' OR " _
>> >        & " discharge       = 'a') AND " _
>> >        & " DrawingObsolete = False AND " _
>> >        & " [class/des]     LIKE '" & "%" & strClass
>> & "%" & "' " _
>> >        & " ORDER BY family"

>> >Any ideas or help would be greatly appreciated.

>> >Chris

>> >.

>.



Sun, 18 Apr 2004 03:27:47 GMT
 DAO Sql statement doesnt work in an ADO environment
Hello, I don't have any magic answers for you, but you
might try to break apart your sql statement and try it
with only 1 criteria at a time.  Try it with the most
basic criteria first, then gradually add the other
criteria.  I didn't see any right offhand, but sometimes
you can have reserved keywords as your fieldnames and that
will give you the same error.  I might suggest, that when
you run the code as is, when the error occurs, go to the
immediate window and print the sql statement (Print
sqlStringBom) and paste the results here.  That will give
us a clearer picture of what command you are trying to run.

Hope that helps,

Robert

Quote:
>-----Original Message-----
>The following SQL statement worked in a DAO environment
with no problems.
>It has no been ported to ADO and fails...Any ideas on

what is causing it to
Quote:
>fail????

> sqlStringBom = "SELECT * " _
>        & " FROM [tblDrawings]" _
>        & " WHERE (size      LIKE '" & "%" & strFanSize
& "%" & "' OR " _
>        & "        size         = 'a') AND " _
>        & "       type      LIKE '" & "%" & sWheelType
& "%" & "' AND " _
>        & "    Fantype      LIKE '" & "%" & sFanType
& "%" & "' AND " _
>        & " [single/double] LIKE '" & "%" & strWidth
& "%" & "' AND " _
>        & " arrangement     LIKE '" & "%" & strArr & "%"
& "' AND " _
>        & " (rotation       LIKE '" & strRotation & "%"
& "' OR " _
>        & " rotation        = 'a') AND " _
>        & " (BrgLife        Is Null OR BrgLife LIKE '"

& "%" & strBearing &
Quote:
>"%" & "') AND " _
>        & " (MotorSize      Is Null OR MotorSize LIKE '"
& "%" & strMotor &
>"%" & "') AND " _
>        & " (option2        Is Null OR option2 LIKE '"

& "%" & strOption2 &
Quote:
>"%" & "') AND " _
>        & " (option         Is Null OR option LIKE '"

& "%" & strOption1 &

- Show quoted text -

Quote:
>"%" & "') AND " _
>        & " (discharge      LIKE '" & "%" & strDischarge
& "%" & "' OR " _
>        & " discharge       = 'a') AND " _
>        & " DrawingObsolete = False AND " _
>        & " [class/des]     LIKE '" & "%" & strClass
& "%" & "' " _
>        & " ORDER BY family"

>Any ideas or help would be greatly appreciated.

>Chris

>.



Sun, 18 Apr 2004 03:36:18 GMT
 DAO Sql statement doesnt work in an ADO environment
SELECT *  FROM [tblDrawings] WHERE (Size      LIKE '%365%' OR         Size
= 'a') AND  [Class/Des]     LIKE '%2%'  ORDER BY Family

This was a simpliefied version of the failed one..

Above is the Immediate print of the following sql;

sqlStringBom = "SELECT * " _
        & " FROM [tblDrawings]" _
        & " WHERE (Size      LIKE '" & "%" & strFanSize & "%" & "' OR " _
        & "        Size         = 'a') AND " _
        & " [Class/Des]     LIKE '" & "%" & strClass & "%" & "' " _
        & " ORDER BY Family"


Quote:
> Hello, I don't have any magic answers for you, but you
> might try to break apart your sql statement and try it
> with only 1 criteria at a time.  Try it with the most
> basic criteria first, then gradually add the other
> criteria.  I didn't see any right offhand, but sometimes
> you can have reserved keywords as your fieldnames and that
> will give you the same error.  I might suggest, that when
> you run the code as is, when the error occurs, go to the
> immediate window and print the sql statement (Print
> sqlStringBom) and paste the results here.  That will give
> us a clearer picture of what command you are trying to run.

> Hope that helps,

> Robert

> >-----Original Message-----
> >The following SQL statement worked in a DAO environment
> with no problems.
> >It has no been ported to ADO and fails...Any ideas on
> what is causing it to
> >fail????

> > sqlStringBom = "SELECT * " _
> >        & " FROM [tblDrawings]" _
> >        & " WHERE (size      LIKE '" & "%" & strFanSize
> & "%" & "' OR " _
> >        & "        size         = 'a') AND " _
> >        & "       type      LIKE '" & "%" & sWheelType
> & "%" & "' AND " _
> >        & "    Fantype      LIKE '" & "%" & sFanType
> & "%" & "' AND " _
> >        & " [single/double] LIKE '" & "%" & strWidth
> & "%" & "' AND " _
> >        & " arrangement     LIKE '" & "%" & strArr & "%"
> & "' AND " _
> >        & " (rotation       LIKE '" & strRotation & "%"
> & "' OR " _
> >        & " rotation        = 'a') AND " _
> >        & " (BrgLife        Is Null OR BrgLife LIKE '"
> & "%" & strBearing &
> >"%" & "') AND " _
> >        & " (MotorSize      Is Null OR MotorSize LIKE '"
> & "%" & strMotor &
> >"%" & "') AND " _
> >        & " (option2        Is Null OR option2 LIKE '"
> & "%" & strOption2 &
> >"%" & "') AND " _
> >        & " (option         Is Null OR option LIKE '"
> & "%" & strOption1 &
> >"%" & "') AND " _
> >        & " (discharge      LIKE '" & "%" & strDischarge
> & "%" & "' OR " _
> >        & " discharge       = 'a') AND " _
> >        & " DrawingObsolete = False AND " _
> >        & " [class/des]     LIKE '" & "%" & strClass
> & "%" & "' " _
> >        & " ORDER BY family"

> >Any ideas or help would be greatly appreciated.

> >Chris

> >.



Sun, 18 Apr 2004 04:46:27 GMT
 DAO Sql statement doesnt work in an ADO environment
What do you mean with "fails" ???

You get errors executing it and if yes then which errors?

Evert
--
=======================================
My email address has been altered to
avoid unwanted email.

Replies to the newsgroup only, please.

Thanks,
Evert Timmer:
Transworld Software
VC++, VB6, SQL Server Database Solutions
http://www.transworld-software.com (under construction)
=======================================


Quote:
> The following SQL statement worked in a DAO environment with no problems.
> It has no been ported to ADO and fails...Any ideas on what is causing it
to
> fail????

>  sqlStringBom = "SELECT * " _
>         & " FROM [tblDrawings]" _
>         & " WHERE (size      LIKE '" & "%" & strFanSize & "%" & "' OR " _
>         & "        size         = 'a') AND " _
>         & "       type      LIKE '" & "%" & sWheelType & "%" & "' AND " _
>         & "    Fantype      LIKE '" & "%" & sFanType & "%" & "' AND " _
>         & " [single/double] LIKE '" & "%" & strWidth & "%" & "' AND " _
>         & " arrangement     LIKE '" & "%" & strArr & "%" & "' AND " _
>         & " (rotation       LIKE '" & strRotation & "%" & "' OR " _
>         & " rotation        = 'a') AND " _
>         & " (BrgLife        Is Null OR BrgLife LIKE '" & "%" & strBearing
&
> "%" & "') AND " _
>         & " (MotorSize      Is Null OR MotorSize LIKE '" & "%" & strMotor
&
> "%" & "') AND " _
>         & " (option2        Is Null OR option2 LIKE '" & "%" & strOption2
&
> "%" & "') AND " _
>         & " (option         Is Null OR option LIKE '" & "%" & strOption1 &
> "%" & "') AND " _
>         & " (discharge      LIKE '" & "%" & strDischarge & "%" & "' OR " _
>         & " discharge       = 'a') AND " _
>         & " DrawingObsolete = False AND " _
>         & " [class/des]     LIKE '" & "%" & strClass & "%" & "' " _
>         & " ORDER BY family"

> Any ideas or help would be greatly appreciated.

> Chris



Sun, 18 Apr 2004 04:45:30 GMT
 DAO Sql statement doesnt work in an ADO environment
That was the Exact error message

RunTime Error -214746726
Method 'Open' of Object recordset failed


Quote:
> Hi

> I mean what particular error message do you get?

> Thanks,

> Val
> >-----Original Message-----
> >Method of Open recordset object failed..

> >Fails on the SQL statement

> >Chris


> >> Hi,

> >> What kind of error do you get?

> >> Val

> >> >-----Original Message-----
> >> >The following SQL statement worked in a DAO environment
> >> with no problems.
> >> >It has no been ported to ADO and fails...Any ideas on
> >> what is causing it to
> >> >fail????

> >> > sqlStringBom = "SELECT * " _
> >> >        & " FROM [tblDrawings]" _
> >> >        & " WHERE (size      LIKE '" & "%" & strFanSize
> >> & "%" & "' OR " _
> >> >        & "        size         = 'a') AND " _
> >> >        & "       type      LIKE '" & "%" & sWheelType
> >> & "%" & "' AND " _
> >> >        & "    Fantype      LIKE '" & "%" & sFanType
> >> & "%" & "' AND " _
> >> >        & " [single/double] LIKE '" & "%" & strWidth
> >> & "%" & "' AND " _
> >> >        & " arrangement     LIKE '" & "%" & strArr
> & "%"
> >> & "' AND " _
> >> >        & " (rotation       LIKE '" & strRotation & "%"
> >> & "' OR " _
> >> >        & " rotation        = 'a') AND " _
> >> >        & " (BrgLife        Is Null OR BrgLife LIKE '"
> >> & "%" & strBearing &
> >> >"%" & "') AND " _
> >> >        & " (MotorSize      Is Null OR MotorSize
> LIKE '"
> >> & "%" & strMotor &
> >> >"%" & "') AND " _
> >> >        & " (option2        Is Null OR option2 LIKE '"
> >> & "%" & strOption2 &
> >> >"%" & "') AND " _
> >> >        & " (option         Is Null OR option LIKE '"
> >> & "%" & strOption1 &
> >> >"%" & "') AND " _
> >> >        & " (discharge      LIKE '" & "%" &
> strDischarge
> >> & "%" & "' OR " _
> >> >        & " discharge       = 'a') AND " _
> >> >        & " DrawingObsolete = False AND " _
> >> >        & " [class/des]     LIKE '" & "%" & strClass
> >> & "%" & "' " _
> >> >        & " ORDER BY family"

> >> >Any ideas or help would be greatly appreciated.

> >> >Chris

> >> >.

> >.



Sun, 18 Apr 2004 04:42:19 GMT
 DAO Sql statement doesnt work in an ADO environment
The problem was that the word 'Size' can not be used as a field name when
looking up through an SQL statement..

Thanx for everyone's help
Chris


Quote:
> The following SQL statement worked in a DAO environment with no problems.
> It has no been ported to ADO and fails...Any ideas on what is causing it
to
> fail????

>  sqlStringBom = "SELECT * " _
>         & " FROM [tblDrawings]" _
>         & " WHERE (size      LIKE '" & "%" & strFanSize & "%" & "' OR " _
>         & "        size         = 'a') AND " _
>         & "       type      LIKE '" & "%" & sWheelType & "%" & "' AND " _
>         & "    Fantype      LIKE '" & "%" & sFanType & "%" & "' AND " _
>         & " [single/double] LIKE '" & "%" & strWidth & "%" & "' AND " _
>         & " arrangement     LIKE '" & "%" & strArr & "%" & "' AND " _
>         & " (rotation       LIKE '" & strRotation & "%" & "' OR " _
>         & " rotation        = 'a') AND " _
>         & " (BrgLife        Is Null OR BrgLife LIKE '" & "%" & strBearing
&
> "%" & "') AND " _
>         & " (MotorSize      Is Null OR MotorSize LIKE '" & "%" & strMotor
&
> "%" & "') AND " _
>         & " (option2        Is Null OR option2 LIKE '" & "%" & strOption2
&
> "%" & "') AND " _
>         & " (option         Is Null OR option LIKE '" & "%" & strOption1 &
> "%" & "') AND " _
>         & " (discharge      LIKE '" & "%" & strDischarge & "%" & "' OR " _
>         & " discharge       = 'a') AND " _
>         & " DrawingObsolete = False AND " _
>         & " [class/des]     LIKE '" & "%" & strClass & "%" & "' " _
>         & " ORDER BY family"

> Any ideas or help would be greatly appreciated.

> Chris



Sun, 18 Apr 2004 05:03:59 GMT
 DAO Sql statement doesnt work in an ADO environment
Hi,

I am not sure if you can use "/" inside column name

Val

Quote:
>-----Original Message-----
>SELECT *  FROM [tblDrawings] WHERE (Size      LIKE '%

365%' OR         Size
Quote:
>= 'a') AND  [Class/Des]     LIKE '%2%'  ORDER BY Family

>This was a simpliefied version of the failed one..

>Above is the Immediate print of the following sql;

>sqlStringBom = "SELECT * " _
>        & " FROM [tblDrawings]" _
>        & " WHERE (Size      LIKE '" & "%" & strFanSize
& "%" & "' OR " _
>        & "        Size         = 'a') AND " _
>        & " [Class/Des]     LIKE '" & "%" & strClass
& "%" & "' " _
>        & " ORDER BY Family"



>> Hello, I don't have any magic answers for you, but you
>> might try to break apart your sql statement and try it
>> with only 1 criteria at a time.  Try it with the most
>> basic criteria first, then gradually add the other
>> criteria.  I didn't see any right offhand, but sometimes
>> you can have reserved keywords as your fieldnames and
that
>> will give you the same error.  I might suggest, that
when
>> you run the code as is, when the error occurs, go to the
>> immediate window and print the sql statement (Print
>> sqlStringBom) and paste the results here.  That will
give
>> us a clearer picture of what command you are trying to
run.

>> Hope that helps,

>> Robert

>> >-----Original Message-----
>> >The following SQL statement worked in a DAO environment
>> with no problems.
>> >It has no been ported to ADO and fails...Any ideas on
>> what is causing it to
>> >fail????

>> > sqlStringBom = "SELECT * " _
>> >        & " FROM [tblDrawings]" _
>> >        & " WHERE (size      LIKE '" & "%" & strFanSize
>> & "%" & "' OR " _
>> >        & "        size         = 'a') AND " _
>> >        & "       type      LIKE '" & "%" & sWheelType
>> & "%" & "' AND " _
>> >        & "    Fantype      LIKE '" & "%" & sFanType
>> & "%" & "' AND " _
>> >        & " [single/double] LIKE '" & "%" & strWidth
>> & "%" & "' AND " _
>> >        & " arrangement     LIKE '" & "%" & strArr
& "%"
>> & "' AND " _
>> >        & " (rotation       LIKE '" & strRotation & "%"
>> & "' OR " _
>> >        & " rotation        = 'a') AND " _
>> >        & " (BrgLife        Is Null OR BrgLife LIKE '"
>> & "%" & strBearing &
>> >"%" & "') AND " _
>> >        & " (MotorSize      Is Null OR MotorSize
LIKE '"
>> & "%" & strMotor &
>> >"%" & "') AND " _
>> >        & " (option2        Is Null OR option2 LIKE '"
>> & "%" & strOption2 &
>> >"%" & "') AND " _
>> >        & " (option         Is Null OR option LIKE '"
>> & "%" & strOption1 &
>> >"%" & "') AND " _
>> >        & " (discharge      LIKE '" & "%" &
strDischarge
>> & "%" & "' OR " _
>> >        & " discharge       = 'a') AND " _
>> >        & " DrawingObsolete = False AND " _
>> >        & " [class/des]     LIKE '" & "%" & strClass
>> & "%" & "' " _
>> >        & " ORDER BY family"

>> >Any ideas or help would be greatly appreciated.

>> >Chris

>> >.

>.



Mon, 19 Apr 2004 01:59:06 GMT
 DAO Sql statement doesnt work in an ADO environment
You can as long as its enclosed in [ ] brackets


Quote:
> Hi,

> I am not sure if you can use "/" inside column name

> Val

> >-----Original Message-----
> >SELECT *  FROM [tblDrawings] WHERE (Size      LIKE '%
> 365%' OR         Size
> >= 'a') AND  [Class/Des]     LIKE '%2%'  ORDER BY Family

> >This was a simpliefied version of the failed one..

> >Above is the Immediate print of the following sql;

> >sqlStringBom = "SELECT * " _
> >        & " FROM [tblDrawings]" _
> >        & " WHERE (Size      LIKE '" & "%" & strFanSize
> & "%" & "' OR " _
> >        & "        Size         = 'a') AND " _
> >        & " [Class/Des]     LIKE '" & "%" & strClass
> & "%" & "' " _
> >        & " ORDER BY Family"



> >> Hello, I don't have any magic answers for you, but you
> >> might try to break apart your sql statement and try it
> >> with only 1 criteria at a time.  Try it with the most
> >> basic criteria first, then gradually add the other
> >> criteria.  I didn't see any right offhand, but sometimes
> >> you can have reserved keywords as your fieldnames and
> that
> >> will give you the same error.  I might suggest, that
> when
> >> you run the code as is, when the error occurs, go to the
> >> immediate window and print the sql statement (Print
> >> sqlStringBom) and paste the results here.  That will
> give
> >> us a clearer picture of what command you are trying to
> run.

> >> Hope that helps,

> >> Robert

> >> >-----Original Message-----
> >> >The following SQL statement worked in a DAO environment
> >> with no problems.
> >> >It has no been ported to ADO and fails...Any ideas on
> >> what is causing it to
> >> >fail????

> >> > sqlStringBom = "SELECT * " _
> >> >        & " FROM [tblDrawings]" _
> >> >        & " WHERE (size      LIKE '" & "%" & strFanSize
> >> & "%" & "' OR " _
> >> >        & "        size         = 'a') AND " _
> >> >        & "       type      LIKE '" & "%" & sWheelType
> >> & "%" & "' AND " _
> >> >        & "    Fantype      LIKE '" & "%" & sFanType
> >> & "%" & "' AND " _
> >> >        & " [single/double] LIKE '" & "%" & strWidth
> >> & "%" & "' AND " _
> >> >        & " arrangement     LIKE '" & "%" & strArr
> & "%"
> >> & "' AND " _
> >> >        & " (rotation       LIKE '" & strRotation & "%"
> >> & "' OR " _
> >> >        & " rotation        = 'a') AND " _
> >> >        & " (BrgLife        Is Null OR BrgLife LIKE '"
> >> & "%" & strBearing &
> >> >"%" & "') AND " _
> >> >        & " (MotorSize      Is Null OR MotorSize
> LIKE '"
> >> & "%" & strMotor &
> >> >"%" & "') AND " _
> >> >        & " (option2        Is Null OR option2 LIKE '"
> >> & "%" & strOption2 &
> >> >"%" & "') AND " _
> >> >        & " (option         Is Null OR option LIKE '"
> >> & "%" & strOption1 &
> >> >"%" & "') AND " _
> >> >        & " (discharge      LIKE '" & "%" &
> strDischarge
> >> & "%" & "' OR " _
> >> >        & " discharge       = 'a') AND " _
> >> >        & " DrawingObsolete = False AND " _
> >> >        & " [class/des]     LIKE '" & "%" & strClass
> >> & "%" & "' " _
> >> >        & " ORDER BY family"

> >> >Any ideas or help would be greatly appreciated.

> >> >Chris

> >> >.

> >.



Sat, 24 Apr 2004 05:39:19 GMT
 
 [ 14 post ] 

 Relevant Pages 

1. DAO 3.5 doesnt work with office2000?

2. Percent wildcard in dynamic statement doesnt work ??

3. ADO Connection doesnt seem to work

4. SQL - Server MAIL doesnt work on WIN2000 Advanced Server

5. Access 97/SqlServer 7: Appending Access Tables into Sql Server tables doesnt work for big tables

6. Simple SQL statement (Insert entry if entry doesnt exist)

7. Access97/SqlServer7.0: Appending Access tables into Sql doesnt work for big tables

8. scheduling backup doesnt work on sql 6.5 sp 4

9. Access97/Sqlserver 97: Appending Access tables into sql Server doesnt work for big tables

10. Access 97/SqlServer 7: Appending Access tables into Sql server doesnt work for big tables

11. ADO - SQL statement that contains REPLACE doesn't work

12. SQL Mail doesnt work


 
Powered by phpBB® Forum Software