Running a simple VBA script to test the connection

Question

I am trying to test a GoDaddy SQL Server database connection. I get an "invalid connection string attribute".

What happened to this script?

Dim cnn As ADODB.Connection Dim canConnect As Boolean Public Sub TestConnection() Set cnn = New ADODB.Connection cnn.Open "Provider=sqloledb;Data Source=GoDaddyServer.com;Initial Catalog=dBase1;UserID=userID; Password='password';" If cnn.State = adStateOpen Then canConnect = True cnn.Close End If MsgBox canConnect End Sub 

Answer 1

IIRC, you must specify the Provider property outside the connection string as follows:

 Dim conn Set conn = New ADODB.Connection conn.Provider = "sqloledb" conn.Open "Data Source=GoDaddyServer.com; Initial Catalog=dbase1; User ID=userid; Password=pass;" 

Answer 2

I have never seen the password specified in the SQL Server connection string like yours. Try removing quotes:

  "Provider = sqloledb; Data Source = GoDaddyServer.com; Initial Catalog = dBase1; User ID = userID; Password = password;"   

In the future, you can find connectionstrings.com .

Answer 3

You can specify the provider directly in the connection string

 Dim cnn as ADODB.Connection Dim cnn_str as String cnn_str = "Provider=SQLOLEDB;Data Source=SERVER;Initial Catalog=DB;User ID=sa;Password=PASSWORD" Set cnn = New ADODB.Connection cnn.Open cnn_str