By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,641 Members | 2,101 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,641 IT Pros & Developers. It's quick & easy.

Access and SQL data

P: n/a
EV
Can a SQL Server stored procedure be called from Access? If so, how
does one do this? and can it be "linked" to a button on a form?

Thanks

Eric Venden
Village of Gurnee
Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
You need to create a pass-through query and put the call to the Stored
Procedure there.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"EV" <er***@village.gurnee.il.us> wrote in message
news:4c**************************@posting.google.c om...
Can a SQL Server stored procedure be called from Access? If so, how
does one do this? and can it be "linked" to a button on a form?

Thanks

Eric Venden
Village of Gurnee

Nov 13 '05 #2

P: n/a
Create a pass-through query which executes the sp, as in:

Exec sp_myspname @param1='blah blah'

Simply create a query; don't add a table; select the Query menu, SQL
Specific, Pass Through; enter the above statement (adjusted for your values)
in the query, and save.

You can then execute the pass-through query just as you would another query,
using Currentdb.execute, or Docmd.RunQuery.

If you store the password in the ODBC connect string (in Query Properties),
then the user won't get prompted for it each time.

Neil
"EV" <er***@village.gurnee.il.us> wrote in message
news:4c**************************@posting.google.c om...
Can a SQL Server stored procedure be called from Access? If so, how
does one do this? and can it be "linked" to a button on a form?

Thanks

Eric Venden
Village of Gurnee

Nov 13 '05 #3

P: n/a
Well, I just said he could, not that he should.

"Chuck Grimsby" <c.*******@worldnet.att.net.invalid> wrote in message
news:ot********************************@4ax.com...
On Wed, 01 Sep 2004 22:26:26 GMT, "Neil Ginsberg" <nr*@nrgconsult.com>
wrote:
If you store the password in the ODBC connect string (in Query
Properties),
then the user won't get prompted for it each time.


Gack! Use a trusted connection instead. (Storing passwords is always
a *bad* idea!)
--
A Prudent Man Does Not Make A Goat His Gardener.

Nov 13 '05 #4

P: n/a
EV wrote:
Can a SQL Server stored procedure be called from Access? If so, how
does one do this? and can it be "linked" to a button on a form?

Thanks

Eric Venden
Village of Gurnee


As well as the queries pointed out by Doug & Neil you can do it directly
by either DAO or ADO methods:

DAO (Assumes a Database type variable pointing at the server initialised
with OpenDatabase with a connection string instead of a database name)
db.Execute "exec MySP", dbSQLPassThrough

With ADO you do the same sort of thing using a connection object or for
more control use a Command object.

Note, if using a pass through query you MUST manually set the
ReturnsRecords property to No if the stored procedure doesn't return
records otherwise an error will occur.
--

\\\\\\
\\ \\ Windows is searching
\ \ For your sig.
\ \ Please Wait.
\__\

Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.