Calling stored procedure from seperate db

[dazzer123]

I'm using the following code to call a stored procedure and return it to a recordset.

DIM cnn As New ADODB.Connection
DIM cmdSQL As New ADODB.Command

Set cnn = CurrentProject.Connection
Set cmdSQL.ActiveConnection = cnn
cmdSQL.CommandText = "GetSortBy"
Set rstSortBy = cmdSQL.Execute()

How can I do the same thing but calling a storedprocedure from a different sql db

Thanks in advance for your help

 

[cmmrfrds]

Here is an example of setting up a connection string to the pubs database on the sql server bigtuna. This connection can be used just like you used the currentproject connection.

Dim cn As New adodb.Connection
Dim rs As Recordset, connString As String
connString = "provider=SQLOLEDB.1;" & _
"User ID=sa;Initial Catalog=pubs;" & _
"Data Source=bigtuna;" & _
"Persist Security Info=False"
cn.ConnectionString = connString
cn.Open connString

 

[dazzer123]

Thanks that worked well
Also found another way

Create a stored procedure in current db

Create Procedure ExecStoredProcedure
(
@strQuery VarChar(50)

)

As

EXEC @strQuery

/* set nocount on */
return

And then do

DIM cnn As New ADODB.Connection
DIM cmdSQL As New ADODB.Command

Set cnn = CurrentProject.Connection
Set cmdSQL.ActiveConnection = cnn
cmdSQL.CommandText = "ExecStoredProcedure 'OtherDb.dbtherProcedure'"
Set rstSortBy = cmdSQL.Execute()