1

I have a VBA macro that connects to a database to pull some data and works fine. However, when another person from another department tries to run the file from his computer, he gets an error:

Here is the message

My VBA connection string is the following:

Sub exportLens()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim ConnectionString As String
Dim StrQuery As String
Sheets("data").Range("A2:C100000").Clear

ConnectionString = "Provider=SQLOLEDB.1;Password=xxxxx;Persist Security Info=True;User ID=SQLxxx;Data Source=xx.xx5.xx.90,xx33;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=NasrWeb"

Any idea, why this is not working from my friend's computer?

Thanks for the help.

Community
  • 1
  • 1
Cesar
  • 617
  • 3
  • 8
  • 17
  • Can he ping the server you are trying to connect to? – SteveB Feb 07 '17 at 22:41
  • Use Windows Authentication if possible. Don't store passwords and user ID's in a connection string in VBA code. VBA code, even protected, is not secure. – Mathieu Guindon Feb 07 '17 at 23:12
  • The DataSource should be xxx.xxx.xxx.xxx:xxx (semicolon) not xxx.xxx.xxx.xxx,xxxx (comma) – Jules Feb 07 '17 at 23:20
  • no. comma is correct http://stackoverflow.com/questions/5294721/how-to-specify-a-port-number-in-sql-server-connection-string – Shmukko Feb 07 '17 at 23:25

1 Answers1

1

It could be one of these things:

  1. Remote connection is not enabled for a SQL Server instance.

  2. Port is not added in the firewall exception list

  3. IP address of the SQL Server Instance is blocked the Firewall

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user3272686
  • 853
  • 2
  • 11
  • 23