Access and SQL data

[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

[Douglas J. Steele]

Re: Access and SQL data

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" <ericv@village. gurnee.il.us> wrote in message
news:4c7b9cf8.0 409011235.3acb9 262@posting.goo gle.com...[color=blue]
> 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[/color]

[Neil Ginsberg]

Re: Access and SQL data

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.execu te, 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" <ericv@village. gurnee.il.us> wrote in message
news:4c7b9cf8.0 409011235.3acb9 262@posting.goo gle.com...[color=blue]
> 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[/color]

[Neil Ginsberg]

Re: Access and SQL data

Well, I just said he could, not that he should.

"Chuck Grimsby" <c.grimsby@worl dnet.att.net.in valid> wrote in message
news:otodj0520i o5oj7souuvm2738 2sgt4otm5@4ax.c om...[color=blue]
> On Wed, 01 Sep 2004 22:26:26 GMT, "Neil Ginsberg" <nrg@nrgconsult .com>
> wrote:[color=green]
>>If you store the password in the ODBC connect string (in Query
>>Properties) ,
>>then the user won't get prompted for it each time.[/color]
>
> Gack! Use a trusted connection instead. (Storing passwords is always
> a *bad* idea!)
>
>
> --
> A Prudent Man Does Not Make A Goat His Gardener.
>[/color]

[Trevor Best]

Re: Access and SQL data

EV wrote:
[color=blue]
> 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[/color]

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", dbSQLPassThroug h

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.
\__\