SQL for ADO Control 1

[krzysrb]

hello guys,
I am running into problems with this ADO control I am using:

With adoEditTimes
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51;Persist Security Info=False;Data Source=" & mdbPATH
.RecordSource = "times"
.Refresh
End With

the above runs just fine. but if I want the recordsource to be some SQL statement, like:

rsSQL = "SELECT * FROM times"
rsSQL = rsSQL & " WHERE [Student Number] = " & CLng(txtAddID.Text)
rsSQL = rsSQL & " ORDER BY Switch([Day]= 'Monday',1,[Day]='Tuesday',2,[Day]='Wednesday',3,[Day]='Thursday',4,[Day]='Friday',5,[Day]='Saturday',6,[Day]='Sunday',7)"

and then set the recordsource of the DAO to that, I get an error "Syntax error in FROM clause".

What in hell causes this to happen, cuz i had this same sql statement bound to a DAO control, and it ran fine.

please help,
vlada

 

[CCLINT]

Wait. Are you using DAO or ADO, because you say:
"I am running into problems with this ADO control I am using:"
and later
"and then set the recordsource of the DAO"

Anyways, under ADO and JET Provider 3.51 it works fine for me.
Suggest for starters changing the Provider version to 4.0.

"Provider=Microsoft.Jet.OLEDB.4.0

If you are working on a Win9x or NT4 system, then make sure the MDAC has been updated to at least vers 2.5, or download the JET 4 SP 7 from

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q239114

Then I suggest posting here the exact connection and recordsource strings by doing this prior to setting the recordsource:

Debug.Print adoEditTimes.ConnectionString
Debug.Print rsSQL

This will give us a chance to see just what exactly is being assigned at run time to the ADODC.

 

[krzysrb]

CCLINT,
as always you were right on with help. and that DAO ADO deal was a typo, sorry about that. Anyhow, I got the SP7 for Xp and then changed few things around and now its running just fine.

here is the code if you care to see how it looks now,

With adoEditTimes
.CommandType = adCmdUnknown
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & mdbPATH
.RecordSource = "SELECT * FROM times WHERE [Student Number] = " & CLng(txtAddID.Text) & " ORDER BY Switch([Day]= 'Monday',1,[Day]='Tuesday',2,[Day]='Wednesday',3,[Day]='Thursday',4,[Day]='Friday',5,[Day]='Saturday',6,[Day]='Sunday',7)"
.Refresh
End With

it just wont work if the sql string is out of the WITH section.

thanks again man,
vlada

 

[CCLINT]

Thank you krzysrb. And that you for getting back with the solution.

>it just wont work if the sql string is out of the WITH section.

This I do not understand.
if you do this:

rsSQL = "SELECT * FROM ...."

There should be no problems assigning this outside of the "With", prior to that, and then assigning .RecordSource = rsSQL inside of the "With"

 

[krzysrb]

CCLINT,

There is no problems with assigning the SQL statement prior to the WITH section no more, I just moved the statement two lines up, and recompiled it, and it works.

Thanks again CCLINT
vlada