execute a stored procedure via a SQL Server back end database

[git2dn]

I have developed an application with Access as the front end and the back-end is a SQL server database. I am trying to execute stored procedures using ADO. I have coded the following but I get an error on the cnn.Open command that follows:
The error is
[DBNETLIB][Connection Open(Connect())]SQLServer does not exist or access denied.

Do you see what I am doing wrong or can you suggest an alternative method to execute a stored procedure via the SQL Server backend database ? I am using an MDB file.



Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim com As ADODB.Command
Dim objXL As Excel.Application
Dim objWS As Excel.Worksheet

set cnn = New ADODB.Connection
set rst = New ADODB.Recordset
set com = New ADODB.Command

cnn.ConnectionString = "Provider=SQLOLEDB.1;" & _
"Data Source=BKPEPRJCTB01;Initial Catalog=UDL;" & _
"User ID =Dan_Nito;Password="
cnn.Open

Set com = New ADODB.Command

With com
.ActiveConnection = cnn
.CommandText = "EXEC procTest"
End With

Set objXL = New Excel.Application
objXL.Workbooks.Add
strNextFile = GetNextFileName...

 

[cmmrfrds]

Try this syntax.

com.CommandType = adCmdStoredProc
com.CommandText = "dbo.procTest"
com.ActiveConnection = cnn
com.Execute

debug.print "first field returned = " com(0)

Did you give execute permissions on the stored procedure?