Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Call store procedure from Access


Call store procedure from Access

Author
Message
Krasavita
Krasavita
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1455 Visits: 1894
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
webway101
webway101
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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
liebesiech
liebesiech
SSC-Enthusiastic
SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)

Group: General Forum Members
Points: 176 Visits: 857
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é
kjaved
kjaved
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 Visits: 74
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search