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:-

    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.



  • 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.



  • Re: Ado Versus Odbc Sql Syntax


    I've used it before and it works for me:

    cnn.Provider = "Microsoft.jet.oledb.4.0"
    cnn.ConnectionString = "Data Source=c:\tmp\test.xls;Extended Properties=Excel 8.0;"

    Did you add the extended properties?

  • Re: Ado Versus Odbc Sql Syntax


    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,



Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!