En referencias de su Proyecto, inserte Activex Data Objects 2.x Library.
Inserte este codigo:
Dim ConnectString As String
Dim anerror As ADODB.Error
Dim Sql As String
Dim i As Integer
Sql = "EXEC pr_MyStore"
Set cn = New ADODB.Connection
cn.ConnectionString = ConnectString
cn.Open
Set rsset = New ADODB.Recordset
rsset.Open Sql, cn, adOpenStatic, adLockReadOnly
En este momento, ya se conecto y ejecuto un Store.
Saludos.
ConnectString = "uid=<user>pwd=<pass>server=<Server>" & _
"driver={SQL Server};database=<database>" & "DSN='';"