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 12»»

Running Stored Procedure from Access Expand / Collapse
Author
Message
Posted Friday, May 30, 2008 2:27 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, May 17, 2011 1:00 AM
Points: 68, 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 :)
Post #509369
Posted Friday, May 30, 2008 2:36 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #509374
Posted Friday, May 30, 2008 5:38 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, May 17, 2011 1:00 AM
Points: 68, 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
Post #509427
Posted Friday, May 30, 2008 8:35 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:24 PM
Points: 7,121, Visits: 15,024
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?
Post #509440
Posted Monday, June 2, 2008 7:41 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #509851
Posted Tuesday, June 3, 2008 7:41 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 30, 2011 8:37 AM
Points: 18, 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).
Post #510656
Posted Tuesday, June 3, 2008 8:49 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 5:51 AM
Points: 6,266, Visits: 2,028
You should explore the "ACCESS PROJECT" option ... is very customized to use this kind of things.


* Noel
Post #510727
Posted Tuesday, June 3, 2008 5:27 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, May 17, 2011 1:00 AM
Points: 68, 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.
Post #511037
Posted Wednesday, June 4, 2008 1:44 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #511741
Posted Wednesday, June 4, 2008 1:56 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:24 PM
Points: 7,121, Visits: 15,024
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?
Post #511753
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse