Automation Error trying to get data from SQL server into Excel using ADO

I am trying to get some data from a SQL server database into Excel.  

When I try and open the recordset (RS.OPEN .. ) in the code below I get

Run-time error '-2147217900 (80040e14)'

My references are :
Visual basic for Applications
Microsoft Excel 14.0 Object Library
Microsoft Office 14.0 Object Library
Microsoft Forms 2.0 Object Library
Microsoft ActiveX Data Objects 6.1 Library
OLE Automation
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
Couple of questions..

Have you tried running your query which you are using here in SQL..Does that work or you get an error??

Second can you open the query by..

   rs.Open sQRY, ac, adOpenStatic

Open in new window

Also please select microsoft scripting runtime as a reference it always good to have that reference selected..

Do you really need VBA to do this? Would you consider just using the Excel External Data functionality to pull the data from the SQL Server into a worksheet? This will pull the data and put a data table into a range you specify. You can specify you custom SELECT statement there. To refresh the data, there is a menu option "Data -> Refresh" on the Excel menus. This works in a standard Excel spreadsheet without macros etc.

See here:

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lindsay_kirbyAuthor Commented:
I didn't want to use the External Data functionality as in the past I have had to change the syntax of the SQL query to conform to T-SQL, but in this case as it was such a simple query I have done so.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.