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

Answers


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

Need Your Help

Cairo RGB color range

haskell colors cairo

I'm using cairo in haskell, and the setSourceRGB function takes values between 0 and 1. Does anyone know how this corresponds to the standard 0-255 gradient? As in, is the 255 gradient evenly distr...

Jupyter R kernel crashes

r linux ipython-notebook anaconda jupyter

I have just installed the R essentials from Anaconda (having R already installed on my computer) in order to use R in Jupyter.