http://support.microsoft.com/kb/306125 In the sample: I tried modifying the connection string from the sample with the appropriate credentials but cannot get it to work. This uses VBA within Excel.
Did you try this.. strConn = "Provider=SQLOLEDB;Data Source=YOURDBSERVER;Initial Catalog=YOURDBNAME;User Id=YOURUSERID;Password=YOURPASSWORD;Integrated Security=False; Connect Timeout=15;Network Library=dbmssocn;"
Just tried, didn't work. Got a generic error code -2147217887 Is this correct? Data Source=tcp:nnn.Winhost.com where nnn is replaced by the information for my account. Thanks for the quick response.
Just some additional information from microsoft site about registry setting (on the server side ???): http://support.microsoft.com/kb/269495/EN-US Thanks
Where exactly are you trying to connect from? Are you trying to connect directly form your Excel application on your computer?
Where is this Excel appilcation at? Is it inside your computer or did you upload it to our server? If it is on your computer you should be able to go to Data/Import External Data and connect to our SQL server.
Thanks for the hint. Got it working! I use macro recording and then look into the captured code and port the connection string over to my VBA application and it worked. For future reference for others: strConn = "Provider=SQLOLEDB.1;Password=yourPassword;Persist Security Info=True;User ID=yourUserId;Initial Catalog=yourDatabase;Data Source=theDatabaseServer;". If it doesn't work, use the above method to find the connection string. Thanks!