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:
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:
This does not work...
So can someone post connection string that i should be using for this?
Thanks,
Gary
The old connection string looked like this, but I can't seem to get it working correctly:
Code:
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