Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Call store procedure from Access Expand / Collapse
Author
Message
Posted Monday, February 23, 2009 8:44 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 31, 2014 11:58 AM
Points: 1,279, Visits: 1,886
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
Posted Sunday, February 28, 2010 5:03 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 1, 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
Posted Monday, March 1, 2010 1:19 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, June 29, 2014 11:44 PM
Points: 131, Visits: 801
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
Posted Tuesday, March 2, 2010 9:05 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 10, 2014 9:12 AM
Points: 71, Visits: 67
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 »

Add to briefcase

Permissions Expand / Collapse