Query SQL Server from Excel with parameters linked to cells

Question

I have a bunch of Excel spreadsheet that have data connections to a SQL Server DB (Hosted by AWS RDS) and I follow exactly this procedure to get those connections up and running with the parameters linked to cells: https://superuser.com/a/758792/164558

My connection string ends up looking like this:

DRIVER=SQL Server Native Client 11.0;SERVER=blabla.yadayada.eu-west-1.rds.amazonaws.com;UID=MyUserID;PWD=MrPasswordMan;APP=Microsoft Office 2013;WSID=COMPUTER-NAME;

And this works perfectly on my PC (I have SQL Server installed), I can link to cells with dates and it accepts them as date parameters. However when I try refresh the data in this spreadsheet from other PCs (that do not have SQL Server installed on them), the data won't refresh throwing ODBC errors about default driver's not being specified.

If I change the driver in the connection string to DRIVER=SQL Server (i.e. drop the native client bit) then it will work on the other PCs. However, it now will only accept text cells for date parameters instead of date cells!

Is there a way to get those SQL Server native client 11.0 drivers installed on the other PCs? Is this something that can be solved via the odbcad32.exe (which I do not know anything about)? Or if not, does anyone know how to get the normal SQL Server driver to accept cells containing dates and not date strings to work as parameters?

Answer 1

I have found a driver that I can install on a PC that does not have SQL Server installed on it and it makes Excel's data connections connect to SQL Server with cell linked parameters that are dates. The install can be found here: http://www.microsoft.com/en-us/download/details.aspx?id=36434

You then need to change the driver in your connection string to look like:

DRIVER=ODBC Driver 11 for SQL Server;SERVER=blabla.yadayada.eu-west-1.rds.amazonaws.com;UID=MyUserID;PWD=MrPasswordMan;APP=Microsoft Office 2013;WSID=COMPUTER-NAME;