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