Running a simple VBA script to test a connection

Question

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.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 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;"

Answer 2

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.