Ado Versus Odbc Sql Syntax

Dear All,

Please can you help with another issue which is testing my sanity!

My excel spreadsheet contains the following code which uses ADO to query a closed workbook - until I add the last line, all works perfectly:-

sSQL = sSQL + " select T1.* from "
sSQL = sSQL + " ( "
sSQL = sSQL + "  select iif(VAL.FRANGO='999',VAL1.[FRANGO CODE],VAL.FRANGO & VAL1.[FRANGO CODE]) as ""FRANGY"",' ' as ""DIM1"",sum(FRA.YTD_CURR) as ""VALUE"" from "
sSQL = sSQL + " `c:\downloads\01-FRANGO-BACKUP.xls`.FRANGO_MAIN FRA, "
sSQL = sSQL + " `c:\downloads\01-FRANGO-BACKUP.xls`.VALIDATE_LOOKUP_FRANGO_SECOND VAL, "
sSQL = sSQL + " `c:\downloads\01-FRANGO-BACKUP.xls`.VALIDATE_LOOKUP_FRANGO_THIRD VAL1 "
sSQL = sSQL + " where FRA.[Element 2] = VAL.Second_Element and FRA.[Element 3] = VAL1.Third_Element   "
sSQL = sSQL + " and right(iif(VAL.FRANGO='999',VAL1.[FRANGO CODE],VAL.FRANGO & VAL1.[FRANGO CODE]),3) <> '999' "
sSQL = sSQL + " group by iif(VAL.FRANGO='999',VAL1.[FRANGO CODE],VAL.FRANGO & VAL1.[FRANGO CODE]),' ' "
sSQL = sSQL + " union all "
sSQL = sSQL + " select frango as ""FRANGY"",DIM1,round(YTD_CURR*1000,0) as ""VALUE""  from "
sSQL = sSQL + " `c:\downloads\01-FRANGO-BACKUP.xls`.FRANGO_OTHER "
sSQL = sSQL + " ) T1 "


'Then add this line
sSQL = sSQL + " where T1.FRANGY= '107100' "


Debug.Print sSQL

However, when I add the last line for the "where" clause it bombs out with an "automation error" - I have fiddled and fiddled with it without joy all day (on my day off!) and am just lost for what to do next.

When I cut the debug.print sSQL output into a Querytable on the same sheet it works precisely as expected.

For some reason, ADO (i guess) doesn't like the build up of the sSQL string that way although it doesn't choke on any of the other "where" clauses.

Please someone tell me the errors of my ways!

I look forward to your replies.

Regards,

Pete

Re: Ado Versus Odbc Sql Syntax

Oh joy of joys - after a day of pulling my hair out, I (accidently) stumbled on the solution!

I'm not entirely sure "why" (and that does bother me, but just right now I'm more pleased that I got it to work!) but the solution was to change the "provider" in the connection string from "Provider=Microsoft.Jet.OLEDB.4.0" to "Provider=MSDASQL.1" and everything is fine.

Hope this may help others as well.

Regards,

Pete

Re: Ado Versus Odbc Sql Syntax

gollem,

I just checked, and was hoping that was the answer, but unfortunately I *had* included "Extended Properties=Excel 8.0;" so i guess thats not the problem?

My immediate worry has gone away now becuase I use the other provider but i would still like to know "why" if possible.

Thanks for you time,

Regards,

Pete