Access stored proc..... and SQL server

[bgseib]

This will be an easy one for you ACCESS guru's I am sure. I have built an application in ACCESS2000 and a backend in SQL 2000. I have a main form with many subforms that I am asking for user input with. When they enter the data it is stored in a few different tables in SQL. I then have a submit(command button)button on the form for the user to push once they have entered all the desired codes.
What I am trying to do is run a stored procedure residing on SQL server that will use the results from the tables as parameters against the main tables. So my real question is - what do I code in the access code for the command button that will call the stored procedure in SQL2000?
Thanks for the help.
BS

 

[Travis]

Using ADO, you can use the ADODB.COMMAND object.

 

[bgseib]

I have been trying to figure out the adodb.command and I keep getting errors... If I am in the subroutine for a command button, and I am just trying to connect to a sql server (SQLTEST1) database (DBASE1) and all I want to do is run a stored procedure (sp_test1) - What would the code be for the adodb.command? The book I am using keeps giving pieces of code, but nothing complete to go off of...
Please help - man am I getting boggled....

 

[Travis]

'Command Object that returns a RecordSet
=================================
On Error GoTo ErrorHandler
Dim CMD as New ADODB.COMMAND
Dim Conn as New ADODB.CONNECTION
Dim RS as ADODB.RecordSet

Conn.Connection String="Provider=SQLOLEDB.1;" & "Persist Security Info=False;User ID=sa; " & "password=;Initial Catalog=Northwind"
Conn.Open
CMD.ActiveConnection=Conn
CMD.CommandText="[Ten most expensive products]"
CMD.CommandType=adCmdStoredProc
Set RS=CMD.Execute


'Command Object that returns a Value
=================================
On Error GoTo ErrorHandler
Dim CMD as New ADODB.COMMAND
Dim Conn as New ADODB.CONNECTION
Dim VALUE as Integer

Conn.Connection String="Provider=SQLOLEDB.1;" & "Persist Security Info=False;User ID=sa; " & "password=;Initial Catalog=Northwind"
Conn.Open
CMD.ActiveConnection=Conn
CMD.CommandText="[Number Of products]"
CMD.CommandType=adCmdStoredProc
VALUE=CMD.Execute