MS Access: Call Stored Procedure in SQL Server from MS Access VBA

I had a requirement of downloading a table from SQL server into access. But before downloading I had to run a SP in SQL Server manually and then run the form. Then I thought of calling SQL Server SP from Access itself and I found the script as below
Dim db As DAO.Database
Dim cnn As ADODB.Connection

Set cnn = New ADODB.Connection
cnn.ConnectionString = "Provider=SQLOLEDB;Data Source=yourServername;Initial Catalog=yourDatabasename;User ID=yourUsername;Password=yourPassword"
cnn.Open
Set rs = New ADODB.Recordset
Set rs = cnn.Execute("EXEC SSP_YOUR_SP_NAME")
Set rs = Nothing
cnn.Close

If you are need to call the SP which is available in local SQL Server with Windows Authentication(No password required), Then you need to use the below script
Dim db As DAO.Database
Dim cnn As ADODB.Connection

Set cnn = New ADODB.Connection
cnn.ConnectionString = "Provider=SQLOLEDB;Data Source=.;Initial Catalog=yourdatabasename;Persist Security Info=False; Integrated Security=SSPI;"
cnn.Open
Set rs = New ADODB.Recordset
Set rs = cnn.Execute("EXEC SSP_YOUR_SP_NAME")
Set rs = Nothing
cnn.Close

If Your SP requires parameters, supposing two parameters in this case you can modify the above script as below
Set rs = cnn.Execute("EXEC SSP_YOUR_SP_NAME " & parm1 & "," & parm2 & "")

1 comment: