Call store procedure from Access

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Microsoft Access » Microsoft Access » Call store procedure from Access

Call store procedure from Access

Rate Topic

Display Mode

Topic Options

Author

Message

Krasavita

Posted Monday, February 23, 2009 8:44 AM

Ten Centuries

Group: General Forum Members
Last Login: Friday, April 19, 2013 8:15 AM
Points: 1,251, Visits: 1,840

I have a form in Access, when I click on command button, I need to call store procedure from SQL Server. How can I do it?

Thank you

Post #662654

webway101

Posted Sunday, February 28, 2010 5:03 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, March 01, 2010 5:39 AM
Points: 1, Visits: 11

There are two ways of doing this.

1. Create a stored procedure, i.e. qsel_storedprocedure on the server. Create a Pass-through query which will call the stored procedure. The SQL statement in the Pass-through query would be as follows: EXEC qsel_storedprocedure.
In VB you can then do the DoCmd.OpenQuery "NameofthePassthroughquery". If you want a recordset you would have to declare a record set and again open the recordset by opening the "NameofthePassthrougquery".

2. The other technique is more complicated and I don't have it from memory but the jist is you have to create a Command object in the code. Executing the command calls the stored procedure. Don't have time for the details now.

Try number one first, it is simpler.

Dick

Post #874267

liebesiech

Posted Monday, March 01, 2010 1:19 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 10, 2013 3:55 AM
Points: 126, Visits: 751

Check this http://www.sqlservercentral.com/Forums/Topic509369-338-1.aspx or create an acceess project (adp).

Some time ago I played around with Access and stored procedures. I share this file on my SkyDrive (ZIP).
This is the link:
http://cid-fc70428b0c8daeb3.skydrive.live.com/self.aspx/.Public/MS%20Access?authkey=8k8hGJ7srSY%24

No warranties given of course.

René

Post #874332

kjaved

Posted Tuesday, March 02, 2010 9:05 AM

Valued Member

Group: General Forum Members
Last Login: Wednesday, February 29, 2012 6:55 AM
Points: 66, Visits: 64

Try the following code to execute a stored procedure from VBA

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim cmd As ADODB.Command
Dim strConnect As String

strConnect = "Connection String to the Database"

' Instantiate the connection object
Set cnn = New ADODB.Connection

' Open the connection based on the strConnect connect string arguments
cnn.Open strConnect

' Instantiate the command object
Set cmd = New ADODB.Command

' Assign the connection and set applicable properties
cmd.ActiveConnection = cnn
cmd.CommandText = "Stored Procedure Name"
cmd.CommandType = adCmdStoredProc

' Instantiate the recordset object by using the return value
' of the command's Execute method. Supply the parameters by
' packing them into a variant array
Set rst = cmd.Execute

Set rst = Nothing
Set cnn = Nothing
Set cmd = Nothing

Post #875248

« Prev Topic | Next Topic »

Permissions