Stored Procs

I'm having trouble calling SQL Server stored procedures from an Access database(mdb). Do I need to work with an Access Project(.adp) in order to call these stored procedures or can I do it with a normal (.mdb) file? What's the syntax?

I had never tried this but I figured it would work so this is
what I did. First I added this Stored Procedure to the pubs database.
<pre>
CREATE PROCEDURE Add_Store
@Store_ID char(4), @Store_Name varchar(40),
@Address varchar(40), @City varchar(20),
@ST char(2), @Zip char(5)
AS
INSERT INTO stores VALUES ( @Store_ID, @Store_Name, @Address, @City, @ST, @ZIP )
Return 0
</pre>
Then I added a form to an Access database and placed one
command button on it. I opened the code window and set
a reference, on the tools menu, to Microsoft ActiveX Data
Objects Library. I then added this code to the Form code.
<pre>
Private Sub Command0_Click()
Dim cnnSS7 As ADODB.Connection
Dim intCnt As Integer
Dim strConnect As String, strSPCmd As String

strConnect = "Provider=SQLOLEDB;Data Source=(local);Database=pubs;UID=sa;PWD=;"
strSPCmd = "Add_Store('4321','Book Buy Book','1234 Anystreet St.','Anycity','AR','72701')"
Set cnnSS7 = New ADODB.Connection
With cnnSS7
.ConnectionString = strConnect
.Open
.Execute strSPCmd, intCnt, adCmdStoredProc
.Close
End With
Set cnnSS7 = Nothing
End Sub
</pre>

I opened the form and clicked the button. I opened Query
Analyzer and opened the stores table. There was my new
record. If you don't understand something about this example
post back.

I think therefore I am... sometimes right. images/icons/wink.gif