December 11, 2003 at 7:46 pm
Attempting to pass parameters to a Stored Procedure from a VBA Module in excel but I can't get passed opening the connection. What am I missing?!?
Sub TestADO()
Dim cnn1, strConn
Set cnn1 = New ADODB.Connection
cnn1.Open ("Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind;Data Source=MYSQLSERVER,user ID=sa;password=PASSWORD")
==============================================================
At this Point I get :
Microsoft Visual Basic Dialog
Run-Time Error '-2147217843 (80040e4d)':
Method 'Open' of object '_Connection' failed
==============================================================
cnn1.Close
Set cnn1 = Nothing
End Sub
December 12, 2003 at 8:34 am
Try This,
cnn1.Open ("Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Password=PASSWORD;User ID=sa;Initial Catalog=Northwind;Data Source=MYSQLSERVER")
December 12, 2003 at 10:21 am
You're mixing together two different security models. If you want to use SQL Server security, then don't use "Integrated Security=SSPI." If you want to use Windows security, then don't use "User ID" and "Password." For troubleshooting in SQL Server, it's also a good idea to use the WorkstationID and ApplicationName parameters.
Using Windows security:
strSQL = "Provider=SQLOLEDB;Integrated Security=SSPI;Persist Security Info=False; _
Initial Catalog=Northwind;Data Source=MySQLServer; _
WorkstationID=" & Application.UserName & ";ApplicationName=" & Application.ThisWorkbook.Name
Using SQL Server security:
strSQL = "Provider=SQLOLEDB;Persist Security Info=False;User ID=sa;Password=PASSWORD _
Initial Catalog=Northwind;Data Source=MySQLServer; _
WorkstationID=" & Application.UserName & ";ApplicationName=" & Application.ThisWorkbook.Name
--Jonathan
--Jonathan
December 14, 2003 at 6:19 pm
Thanks Johnathan, success at last! - I have much to learn.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply