How to execute a stored procedure (SQL 2005) from MS Access code

Good job.

 

I'd like to be able to execute a stored proc from aDASPSQL 2005 database - anyone have sample code or know what's wrong with mine? I tried this below but get an error on the connection (error = "SQL Server does not exist or access denied").

Dim cmd As New ADODB.Command, RSado As New ADODB.Recordset
Dim RSdao As DAO.Recordset

cmd.ActiveConnection = "Provider=SQLOLEDB;Data Source=sql2k510.discountasp.net" _
& "Initial Catalog=myDASPDatabaseName;UID=username;PWD=password;"
cmd.CommandTimeout = 600
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "mySPName"
cmd.Parameters("@Client").Value = 2

cmd.Execute
cmd.ActiveConnection.Close


Thanks! -

 

I just answered my own question. I was missing a ";" in the connection string, after the Data Source name. Works like a charm!:

Dim cmd As New ADODB.Command, RSado As New ADODB.Recordset
Dim RSdao As DAO.Recordset

cmd.ActiveConnection = "Provider=SQLOLEDB;Data Source=sql2k510.discountasp.net;" _
& "Initial Catalog=MyDatabaseName;UID=UserName;PWD=password;"
cmd.CommandTimeout = 600
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "spChargeReport"
cmd.Parameters("@ClientID").Value = 2

cmd.Execute
cmd.ActiveConnection.Close