I am trying to pull data from a SQL Server database directly into Excel. Actually I am attempting to recreate a previous excel report which did this, only we have changed systems (server, databases etc...) and I am having issues with the connection to the new SQL Server database we are using.

The old connection string looked like this, but I can't seem to get it working correctly:

ConnectStr = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;" & _
"Initial Catalog=XXXXXXX;Data Source=XXXXXX;Use Procedure for Prepare=1;" & _
"Auto Translate=True;Packet Size=4096;Workstation ID=XXXXXX;" & _
"Use Encryption for Data=False;Tag with column collation when possible=False"

I have an ODBC (DSN) connection which works fine (I know it works because I run passthrough queries from MS Access 2007 all the time).


I tried simply pulling the connection string from one of my Access Passthrough queries and insert it into my excel code:

ConnectStr = "PROVIDER=SQLOLEDB;DSN=XXXXX Mirror;Description=New Server;UID=XXXXXX;Trusted_Connection=Yes;DATABASE= XXXXXX;LANGUAGE=us_english

This does not work...

So can someone post connection string that i should be using for this?

Thanks,
Gary