February 23, 2009 at 8:44 am
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
February 28, 2010 at 5:03 pm
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
March 1, 2010 at 1:19 am
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é
March 2, 2010 at 9:05 am
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply