Running a simple VBA script to test a connection

I'm trying to test the connection of a GoDaddy SQL Server database. I'm getting an 'invalid connection string attribute.'

What's wrong with this script?

Dim cnn As ADODB.Connection
Dim canConnect As Boolean

Public Sub TestConnection()

    Set cnn = New ADODB.Conn开发者_运维百科ection   
    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

IIRC, you have to specify the Provider property outside the connection string like so:

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

I have never seen a password quoted in a SQL Server connection string like you have. Try removing the quotes:

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

You might find connectionstrings.com useful in the future.

One can specify the provider direct within 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