Execute Stored Proc. with ODBC

[Jacob]

From Access I need to execute a stored procedure in a SQL
Server 2000 database. I am connecting to tables through
an ODBC connection. I am currently executing code on SQL
Server by checking the ODBC connection of one of my linked
ODBC tables.

My problem is, I need to execute a stored proc. in another
database in which I have no linked tables. So, I guess
just need to hardcode the connection string for the other
database and execute the stored procedure. I need the
code for defining the connection string and executing the
stored proc.

Thanks for any help.

Jacob

 

[gandalf]

if using DAO

dim qdf as dao.querydef
set qdf=currentdb.createquerydef("")
qdf.connect="myconnectionstring"
qdf.sql="mystoredprocedure param1 param2"
.....

 

[Jeff Gyarmathy]

This code should help connect with a SQL Server and call a
stored procedure using ADO.

Function Add_Rep()
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
Dim frm As Form_frmEditingEmp
Dim cnSQLServer As ADODB.Connection

'OPen ADODB connection to a SQLServer thru ODBC
Set cnSQLServer = New ADODB.Connection

Let cnSQLServer.ConnectionString = "DRIVER={SQL
SERVER};" & _
"SERVER=SQLServer;" & _
"DATABASE=Employees;" & _
"UID=userid;" & _
"Trusted_Connection=yes"

cnSQLServer.Provider = "sqloledb"
cnSQLServer.Open

Set cmd.ActiveConnection = cnSQLServer

cmd.CommandText = "sp_Emp_AddEmployee"
cmd.CommandType = adCmdStoredProc
cmd.CommandTimeout = 0

cmd.Parameters.Refresh

cmd.Parameters("@EmployeeID").Value = frm.EmployeeID
frm.txtEmployeeName.SetFocus
cmd.Parameters("@EmployeeName").Value =
frm.txtEmployeeName.Text
frm.txtValue.SetFocus
cmd.Parameters("@TeamID").Value = frm.txtValue.Text
frm.chkActive.SetFocus
cmd.Parameters("@Active").Value = frm.chkActive.Value
frm.txtLoginID.SetFocus
cmd.Parameters("@Phone").Value = frm.txtLoginID.Text
frm.txtExtension.SetFocus
cmd.Parameters("@Ext").Value = frm.txtExtension

rs.Open cmd

cnSQLServer.Close

End Function



Also I would advise puting the setup and activation of
the connection in a seperate module. You don't how many
times you will need the code that connects to that server
and database. It doesn't make sense to hardcode the
coonection every time you need to get access to that
server; instead of just calling a function from a module.

 

[Douglas J. Steele]

I'm not sure that code will work if the stored procedure doesn't return a
recordset.

If it doesn't, you're best off using the ADO Command object, rather than the
ADO Recordset object, to execute the SQL.