08-03-2001, 10:11 AM
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?
08-04-2001, 12:23 PM
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.
CREATE PROCEDURE Add_Store
@Store_ID char(4), @Store_Name varchar(40),
@Address varchar(40), @City varchar(20),
@ST char(2), @Zip char(5)
INSERT INTO stores VALUES ( @Store_ID, @Store_Name, @Address, @City, @ST, @ZIP )
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.
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
.ConnectionString = strConnect
.Execute strSPCmd, intCnt, adCmdStoredProc
Set cnnSS7 = Nothing
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
I think therefore I am... sometimes right. images/icons/wink.gif