SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Running Stored Procedure from Access


Running Stored Procedure from Access

Author
Message
jwellington
jwellington
SSC-Enthusiastic
SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 Visits: 144
Hi,

I have not used Stored Procedures before so am unfamiliar with the syntax.

I would like to run a stored procedure (in SS2K5Express) from Access mdb.

Reason: I would like to stop some users reading, updating, inserting data in a table, but need them to be able to do INSERT and UPDATE commands on the table when I create a record in the background (of a form).

I need to know how (including code) to do the following:

1. Call a stored procedure from Access (and pass parameters).

2. If I can pass a variable with the INSERT or UPDATE command as one string, how would I write the code (in the stored procedure) to run that SQL string. I would then only need to have one stored procedure in SQL Server (to bypass the security settings on Access).

Thanks Smile
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23663 Visits: 9730
Access has wizards to help you write a stored procedure. They're limited, but they can help you get a start on it.

A form can have a stored proc as its data source. Then it has a section in the form properties where you can set the input parameters for the proc.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
jwellington
jwellington
SSC-Enthusiastic
SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 Visits: 144
Hi,

I don't know if I explained myself correctly then.

I want to write the stored procedure in SQL Server 2005 Express, and I want to call it from Access.

I would like to pass the SQLString (Insert or Update statement) to the Stored Procedure in SQL Server and then execute that string.

Can anyone help me with the code:

1. In Access to call the stored procedure.

2. In SQL Server to execute the SQL String that I pass into it.

Thanks
Matt Miller (4)
Matt Miller (4)
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12357 Visits: 18574
Create a passthrough query in Access... Connect it to your DB.

Just type in as the query:

Exec  MyStoredProcedureName @Myparameter='MyString'



Save query - run it.....

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23663 Visits: 9730
As far as the query to build, what you're describing is called "dynamic SQL". You'll need to take a look as sp_executesql in Books Online. It will tell you how to do that.

On the other hand, I recommend against doing what you're planning. Build the insert/update/delete commands as specific procs, then execute them with input parameters. Dynamic SQL opens up all kinds of security issues in your database.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
brad.ashforth
brad.ashforth
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 42
If you are planning on using one stored procedure to do either an insert or an update you need to remember that this may cause the procedure to be recompiled each time you run it ... defeating the purpose of the procedure.

My suggestion would be to use one SP for the insert and one for the update, or try the UPSERT statement if it works for your situation (I have to say, though, I have only read about that one and not yet had the chance to use it in production).
noeld
noeld
SSCrazy Eights
SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)

Group: General Forum Members
Points: 9674 Visits: 2048
You should explore the "ACCESS PROJECT" option ... is very customized to use this kind of things.


* Noel
jwellington
jwellington
SSC-Enthusiastic
SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 Visits: 144
Hi,

Thank you for all the replies.

I would like to use Access Project but cannot at present, as I do not have the time to switch all the programming over.

I think I will create 1 SP for INSERT and 1 for UPDATE.

In regard to the passthrough query, how can I run the SP directly from the code in Access. I am unsure what you mean by 'link the query to your DB', and would prefer to call it directly.

Is there a way to do this (ie. code)?, and if not, how do I link the passthrough query back to SQL (and then call the query from the code)?

I am new to this so apologise if these are simple questions.

Thanks again.
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23663 Visits: 9730
There are several ways to run a proc from Access to a different database. One is to create a VBA script that will run the proc. Another is a pass-through query. Both are in Access' documentation.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Matt Miller (4)
Matt Miller (4)
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12357 Visits: 18574
Since you have lots of parameters to pass - I would probably look up "ADO Command" and get really familiar with it. There's a fair amount of readily available code on how to wire up an ADODB Command object, set the various params to the right values, and execute it.

Otherwise - you can tackle it by essentially writing dynamic SQL calls to the passthrough query, and then using the DAO query.RunQuery to get it to execute.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
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